Database syntax

[Bob Sneidar]

I should probably also mention that it would be better to set all your column values in the insert statement. Otherwise you will have to query for the last inserted primary key and use that in your next UPDATE statement's WHERE clause. 

Bob


> put "INSERT into <tblName> (RecordType,DataArray) VALUES (0,<ArrayText>)" into theSQL
> replace "<tblName>" with theTblName in tSQL
> replace "<ArrayText>" with quote & theArrayText & quote in tSQL
> revExecuteSQL tID, tSQL
> 
> That is how I would do it. It's easier sometimes to create an SQL statement using placeholders like <arrayText> and then replace them with a variable containing the actual value in the code. 
> 
> I may have the syntax messed up there (not tested use at your own risk) but that is essentially how it's done. 
> 
> In answer to your second question, yes the myKey will get a unique value each time a record is inserted. It will get a number equal to the last inserted record +1. It does this because your table is defined having the autoincrement set for the myKey column. 
> 
> Also, the advice for using defaults whenever you can is good advice, but sometimes you don't want that. Sometimes you want to test for NULL to see if a value has ever been assigned, putting an empty string if a value is ever deleted. It's a matter of technique I suppose. But if you don't provide a default, then NULL will be assigned. If NOT NULL is set for a column, and you do not provide a value or a default, it will throw an error. That is the gotcha. 
> 
> Bob
> 
> 
> On Dec 20, 2011, at 11:31 AM, Michael Doub wrote:
> 
>> Could I trouble someone for an example of how to insert a row in a SQLite database with a live code array then update that same record with an updated array?
>> 
>> put revOpenDatabase("sqlite", thePath, , , , ) into tID
>> put the "CREATE TABLE ContactData ( " & \
>>           "myKey integer PRIMARY KEY autoincrement," & \
>>           "RecordType text," & \
>>           "RecID text," & \
>>           "Name text," & \
>>           "GroupNbr text," & \
>>           "LastContact, text," & \
>>           "DaysTill text," & \
>>           "DataArray blob) "  into tSQL
>> revExecuteSQL tID, tSQL   
>> 
>> I think that I am ok up to here.   I want to insert a record that has a RecordType of "0", all other fields empty except I want to put an livecode array into DataArray.
>> 
>> This will get the array into text:
>> put arrayEncode(tSettings) into tSettings;  put base64Encode(tSettings) into tSettings
>> 
>> I am unclear on how to do the insert or an update.  Do I somehow have to create a long SQL statement with the actual data in the statement? 
>> 
>> A second database question.  Does the myKey variable automatically get set to a unique value when data is inserted? 
>> 
>> Thanks in advance,
>>   Mike