Re: SQL Insert Syntax
Hi all.
Here's a new toy for everyone to play with. It takes a numbered array of
key/value pairs and converts it to a MEMORY database. Why you ask? So you can
query against it of course!
In my particular case I am getting an array of every object on a card EXCLUDING
objects that are elements of a datagrid (that would be way too much data for me
to care about) so I can query against it. For instance, given one of the
columns is the name of the object and I want all the images, I can query for
all image records:
select * from arraydata where name LIKE 'image %'
You can see how handy this would be, rather than iterating through every
element of an array to find ONLY the array records you want.
Here's the code:
function arrayToMemoryDB aArrayData
put the keys of aArrayData into tArrayKeys
sort tArrayKeys numeric ascending
put ":MEMORY:" into tDBFile
put revOpenDatabase("sqlite", tDBFile) into tDBID
put "drop table arraydata" into tDropSQL
try
revExecuteSQL tDBID, tDropSQL
put the result into tResult
catch tError
end try
-- create the table
put "create table" && quote & "arraydata" & quote & cr \
into tCreateCommand
put "(" & quote & "recordid" & quote && "NUMERIC PRIMARY KEY NOT NULL
UNIQUE, " & cr \
after tCreateCommand
put the keys of aArrayData [1] into tRecordKeyList
repeat for each line tRecordKey in tRecordKeyList
put quote & tRecordKey & quote && "VARCHAR, " & cr after tCreateCommand
end repeat
delete char -3 to -1 of tCreateCommand
put ")" after tCreateCommand
try
revExecuteSQL tDBID, tCreateCommand
put the result into tResult
if tResult is not 0 then breakpoint
catch tError
breakpoint
end try
-- insert data
repeat for each line tKey in tArrayKeys
put aArrayData [tKey] into aRecordData
put 1 into tCounter
put "recordid" into item tCounter of tColumns
put ":" & tCounter into item tCounter of tColumnData
put tKey into aColumnData [tCounter]
repeat for each line tRecordKey in tRecordKeyList
add 1 to tCounter
put tRecordKey into item tCounter of tColumns
put ":" & tCounter into item tCounter of tColumnData
put aRecordData [tRecordKey] into aColumnData [tCounter]
end repeat
put "(" & tColumns & ")" into tColumns
put "(" & tColumnData & ")" into tColumnData
put "insert into arraydata" && tColumns && "VALUES" && tColumnData
into tInsertSQL
replace quote with "\" & quote in tInsertSQL
replace "'" with quote in tInsertSQL
try
revExecuteSQL tDBID, tInsertSQL, "aColumnData"
put the result into tResult
if tResult is not 1 then breakpoint
catch tError
breakpoint
end try
end repeat
return tDBID
end arrayToMemoryDB
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQL Insert Syntax
For now I am just dropping the table and recreating it. Bob S > On Jan 5, 2018, at 14:28 , Bob Sneidar via use-livecode > wrote: > > Thanks for your help all. > > I think with everyone's help I have it working, but now I am trying to use a > memory based database using :MEMORY: for the database name. I ensured all > databases are closed using revOpenDatabases() which returns empty. But now > each time I attempt to create the database I get the result "table "array > data" already exists. I though closing a memory database deletes it from > memory. If not, how do you clear a memory database? > > Bob S > ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQL Insert Syntax
Thanks for your help all. I think with everyone's help I have it working, but now I am trying to use a memory based database using :MEMORY: for the database name. I ensured all databases are closed using revOpenDatabases() which returns empty. But now each time I attempt to create the database I get the result "table "array data" already exists. I though closing a memory database deletes it from memory. If not, how do you clear a memory database? Bob S > On Jan 5, 2018, at 13:38 , Devin Asay via use-livecode > wrote: > > Bob, > > You could try parameterizing your statements as described in the dictionary. > Also, you don’t want to quote your column names. > > put 0,43,500,543 into tRect > put “image “ & quote & “fresh_now.png” & quote into tObj > put “card “ & quote & “Main” & quote into tCard > put false into tVis > revExecuteSQL myID, "insert into arraydata (rect,controlname,owner,visible) > values(:1,:2,:3,:4)", “tRect" ,”tObj”,”tCard”, “tVis” > > This is also best practice, because it protects against SQL injection > attacks. (Admittedly more of a concern with network databases.) > > HTH > > Devin ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQL Insert Syntax
the only problem is your quoted column names. On Fri, Jan 5, 2018 at 4:38 PM, Devin Asay via use-livecode < use-livecode@lists.runrev.com> wrote: > Bob, > > You could try parameterizing your statements as described in the > dictionary. Also, you don’t want to quote your column names. > > put 0,43,500,543 into tRect > put “image “ & quote & “fresh_now.png” & quote into tObj > put “card “ & quote & “Main” & quote into tCard > put false into tVis > revExecuteSQL myID, "insert into arraydata (rect,controlname,owner,visible) > values(:1,:2,:3,:4)", “tRect" ,”tObj”,”tCard”, “tVis” > > This is also best practice, because it protects against SQL injection > attacks. (Admittedly more of a concern with network databases.) > > HTH > > Devin > > > > On Jan 5, 2018, at 2:24 PM, Bob Sneidar via use-livecode < > use-livecode@lists.runrev.com> wrote: > > > > Possibly, but every sqLite reference I've looked at seems to indicate > that I should enclose string values with single quotes, and since my text > contains double quotes, I thought that would do the trick. I have tried > escaling quotes ie. \" but that doesn't work either. > > > > This BTW is why I use sqlYoga, so I don't have to deal with this syntax > crap, but I am writing a function that I want to be universally viable. > > > > Bob S > > > > > >> On Jan 5, 2018, at 13:18 , Devin Asay via use-livecode < > use-livecode@lists.runrev.com> wrote: > >> > >> Bob, > >> > >> Some SQL dialects accept both single and double quotes as string > delimiters. Might that be the problem? > >> > >> Devin > > > > > > ___ > > use-livecode mailing list > > use-livecode@lists.runrev.com > > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > > http://lists.runrev.com/mailman/listinfo/use-livecode > > Devin Asay > Director > Office of Digital Humanities > Brigham Young University > > ___ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > -- On the first day, God created the heavens and the Earth On the second day, God created the oceans. On the third day, God put the animals on hold for a few hours, and did a little diving. And God said, "This is good." ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQL Insert Syntax
Bob, You could try parameterizing your statements as described in the dictionary. Also, you don’t want to quote your column names. put 0,43,500,543 into tRect put “image “ & quote & “fresh_now.png” & quote into tObj put “card “ & quote & “Main” & quote into tCard put false into tVis revExecuteSQL myID, "insert into arraydata (rect,controlname,owner,visible) values(:1,:2,:3,:4)", “tRect" ,”tObj”,”tCard”, “tVis” This is also best practice, because it protects against SQL injection attacks. (Admittedly more of a concern with network databases.) HTH Devin > On Jan 5, 2018, at 2:24 PM, Bob Sneidar via use-livecode > wrote: > > Possibly, but every sqLite reference I've looked at seems to indicate that I > should enclose string values with single quotes, and since my text contains > double quotes, I thought that would do the trick. I have tried escaling > quotes ie. \" but that doesn't work either. > > This BTW is why I use sqlYoga, so I don't have to deal with this syntax crap, > but I am writing a function that I want to be universally viable. > > Bob S > > >> On Jan 5, 2018, at 13:18 , Devin Asay via use-livecode >> wrote: >> >> Bob, >> >> Some SQL dialects accept both single and double quotes as string delimiters. >> Might that be the problem? >> >> Devin > > > ___ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode Devin Asay Director Office of Digital Humanities Brigham Young University ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQL Insert Syntax
Use the placeholder method?
put your data into a numerically keyed array and change the insert to this..
INSERT INTO arraydata ("rect","controlname","owner","visible") VALUES
(:1,:2,:3,:4)
On Fri, Jan 5, 2018 at 2:24 PM, Bob Sneidar via use-livecode <
use-livecode@lists.runrev.com> wrote:
> Possibly, but every sqLite reference I've looked at seems to indicate that
> I should enclose string values with single quotes, and since my text
> contains double quotes, I thought that would do the trick. I have tried
> escaling quotes ie. \" but that doesn't work either.
>
> This BTW is why I use sqlYoga, so I don't have to deal with this syntax
> crap, but I am writing a function that I want to be universally viable.
>
> Bob S
>
>
> > On Jan 5, 2018, at 13:18 , Devin Asay via use-livecode <
> use-livecode@lists.runrev.com> wrote:
> >
> > Bob,
> >
> > Some SQL dialects accept both single and double quotes as string
> delimiters. Might that be the problem?
> >
> > Devin
>
>
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQL Insert Syntax
Possibly, but every sqLite reference I've looked at seems to indicate that I should enclose string values with single quotes, and since my text contains double quotes, I thought that would do the trick. I have tried escaling quotes ie. \" but that doesn't work either. This BTW is why I use sqlYoga, so I don't have to deal with this syntax crap, but I am writing a function that I want to be universally viable. Bob S > On Jan 5, 2018, at 13:18 , Devin Asay via use-livecode > wrote: > > Bob, > > Some SQL dialects accept both single and double quotes as string delimiters. > Might that be the problem? > > Devin ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQL Insert Syntax
Bob,
Some SQL dialects accept both single and double quotes as string delimiters.
Might that be the problem?
Devin
On Jan 5, 2018, at 2:16 PM, Bob Sneidar via use-livecode
mailto:use-livecode@lists.runrev.com>> wrote:
Can anyone find anything wrong with this sqLite insert statement?
INSERT INTO arraydata ("rect","controlname","owner","visible") VALUES
('0,43,500,543','image "fresh_snow.png"','card "Main"','false')
It doesn't generate an error, but neither does it insert anything! There is a
primary key column that I don't include because it's supposed to auto increment
automatically if no column/value is supplied.
Bob S
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Devin Asay
Director
Office of Digital Humanities
Brigham Young University
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
SQL Insert Syntax
Can anyone find anything wrong with this sqLite insert statement?
INSERT INTO arraydata ("rect","controlname","owner","visible") VALUES
('0,43,500,543','image "fresh_snow.png"','card "Main"','false')
It doesn't generate an error, but neither does it insert anything! There is a
primary key column that I don't include because it's supposed to auto increment
automatically if no column/value is supplied.
Bob S
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
