Putting commas in sqlite databases via a variable

peramangk · Post by **peramangk** » Fri Nov 28, 2008 10:32 am

On Jun 17 2008 Aussiepup reported a problem where text containing a comma was put into a sqlite database via a variable.

All text from the comma onwards was mistakenly shuffled along to the next database field.

I am experiencing the same problem.

Has anyone found a workaround for this?

Cheers,
Grant

Mark · Post by **Mark** » Fri Nov 28, 2008 10:59 am

Hi Grant,

Can you show your script?

Mark

peramangk · Post by **peramangk** » Fri Nov 28, 2008 11:29 pm

The application is a Revolution GUI for data entry to a sqlite database. Viewing of the data also takes place in the Rev application.

The script is pasted below.

Parts are adapted (plagiarised?) from the sqlite_sampler.rev tutorial as I am new to lower level database work.

Grant

# **************************************************************************
# gcp 080810
# When the user presses the ENTER key (on a Mac), updates are carried out.
# 1. The comment currently in the photo_comments_entry field is sent to
# the photo_comments in the asb_photo table for the current record.
# 2. All data is updated in the data display fields.

on enterInField
global gConID
local tSQL, tPhotoID, tComments, tTmp, tSpeciesData

put field "photo_comments_entry" into tComments
put field "photo_id" into tPhotoID

# 1. Create the SQL query updating the species code for the current photo in the asb2_photo table
put "UPDATE asb2_photo SET photo_comments = " & quote & tcomments & quote & " WHERE photo_id =" & tPhotoID into tSQL

# Execute SQL
put revdb_execute(gConID,tSQL) into tTmp

handleRevDBerror tTmp
if the result is not empty then
answer warning the result
exit enterInField
end if

# 2. Construct the SQL query for using the photoID to get the current photo record from the photo table
put "SELECT * FROM asb2_photo WHERE photo_id = "& quote & tPhotoID & quote into tSQL

# Execute SQL
put revdb_querylist(",",,gConID,tSQL) into tSpeciesData

handleRevDBerror tSpeciesData
if the result is not empty then
answer warning the result
exit enterInField
end if

showPhotoData tSpeciesData

end enterInField

# **************************************************************************

# 080717 This script is called from the "photo" card.
# tData contains all the elements of one record from the asb2_photo table.
# These elements are then displayed in the relevant fields on the "photo" card.

on showPhotoData tData

local tPhotoStore64, tPhotoStore

put item 1 of tData into fld "photo_id" of card "photo"
put item 2 of tData into fld "photo_species_code" of card "photo"
put item 3 of tData into fld "photo_name" of card "photo"
put item 5 of tData & ", " & item 4 of tData into fld "photo_site" of card "photo"
put item 6 of tData into fld "photo_latitude" of card "photo"
put item 7 of tData into fld "photo_longitude" of card "photo"
put item 8 of tData into fld "photo_altitude" of card "photo"
put item 9 of tData into fld "photo_date" of card "photo"
put item 10 of tData into fld "photo_time" of card "photo"
put item 11 of tData into fld "photo_comments" of card "photo"
put item 12 of tData into fld "photo_file_path" of card "photo"
put item 13 of tData into fld "photo_orig_file_name" of card "photo"
put (item 14 of tData) into tPhotoStore64
put base64decode(tPhotoStore64) into tPhotoStore
put tPhotoStore into image "photo_photo" of card "photo"
put item 15 of tData into fld "photo_camera" of card "photo"
put item 16 of tData into fld "photo_lens" of card "photo"
put item 17 of tData into fld "photo_shutter" of card "photo"
put item 18 of tData into fld "photo_aperture" of card "photo"
put item 19 of tData into fld "photo_focal_length" of card "photo"
put item 20 of tData into fld "photo_iso" of card "photo"

showSpeciesData (item 2 of tData)

end showPhotoData

# **************************************************************************

bangkok · Post by **bangkok** » Fri Nov 28, 2008 11:41 pm

peramangk wrote: put "UPDATE asb2_photo SET photo_comments = " & quote & tcomments & quote & " WHERE photo_id =" & tPhotoID into tSQL

Are we not supposed to use single quote ( ' ) for string, not double quote ?

Janschenkel · Post by **Janschenkel** » Sat Nov 29, 2008 7:27 am

Whether you need to use a single or double quote around strings, depends on the underlying database engine - some are more "forgiving" than others.

To avoid the whole problem, you should probably use "prepared statements" for your SQL queries ; a technical term for putting placeholders into your SQL that are then filled up by the driver with the contents of variables.

Code: Select all

put "UPDATE asb2_photo SET photo_comments = " & quote & tcomments & quote & " WHERE photo_id =" & tPhotoID into tSQL
put revdb_execute(gConID,tSQL) into tTmp

would be rewritten as

Code: Select all

put "UPDATE asb2_photo SET photo_comments = :1 WHERE photo_id = :2" into tSQL
put revdb_execute(gConID,tSQL,"tcomments","tphotoid") into tTmp

The database driver is smart enough to put single or double quotes around the data if that's what the database engine expects. Quite handy and easier-to-read code to boot.

HTH,

Jan Schenkel.

peramangk · Post by **peramangk** » Sat Nov 29, 2008 9:53 am

Thanks for your help Jan.

Unfortunately it has not overcome the problem, but I will definitely use the placeholder coding convention in future.

I think the cause may not be in the database itself but in the code that reads and publishes the data.

The following is part of that code.

*************************

# Construct the SQL query for using the photoID to get the current photo record from the photo table
put "SELECT * FROM asb2_photo WHERE photo_id = "& quote & photoID & quote into tSQL

# Execute SQL
put revdb_querylist(",",,gConID,tSQL) into tPhotoData

(I tried, without success, to use a placeholder here.)
*************************

As you can see, I am selecting a whole record. The first parameter of the revdb_querylist is a comma which, I think, is asking the database to return the record as a csv list. This was in the sqlite_sampler.rev tutorial and the code does not work without it.

It appears that any comma in the database will be incorrectly assumed to be a field separator when the data is retrieved for publishing. Am I on the right track?

Grant

SparkOut · Post by **SparkOut** » Sat Nov 29, 2008 11:09 am

That's right - it doesn't seem to be a database problem, but just manipulation of the list of data after retrieval.

The first parameter in the revDataFromQuery function (that's a synonym for revdb_querylist, by the way) is the column delimiter.
So instead of

Code: Select all

put revdb_querylist(",",,gConID,tSQL) into tPhotoData

you can use

Code: Select all

put revdb_querylist(tab,,gConID,tSQL) into tPhotoData

which will produce a tab-delimited list. (The default column delimiter is tab anyway, so you can leave the parameter as blank to achieve the same result. The second parameter (blank above) is the row delimiter, which defaults to return if not otherwise specified.

If a tab-delimited list is no good for you because you absolutely have to have a csv file, you can always retrieve it that way (with tabs) and then do a "replace tab with quote & comma & quote in tPhotoData" to wrap all the database elements in double-quotes, separated by comments. (You will need to add a quote before and after each line of tPhotoData too.) That should usually be enough for the target application (eg Excel) to put the right data into the right cells (although Excel can work with tab-delimited lists as well).

LiveCode Forums