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
_______________________________________________
use-livecode mailing list
[email protected]
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
