Advanced Database usage questions

[jpottsx1]

Hi, I'll start with the fact that I'm fairly new to Livecode. I am currently writing a small application with three tables in it. I have a view created that combines the data into one place for the purpose of display. THX to those who helped me out with that excercise! I'm now faced with getting Livecode and SQLite to update the various tables. I understand that I cannot update all the tables at once due to a limitation in SQLite. So I've turned to creating a view with triggers, and subsequent updates to each table in sequential form.

Table 1 Table 2 Table 3
------------------------ ---------------------- ----------------------
ID1 ID2 ID3
Date created Table2ID Table3ID
Date Modified Table2Date created Table3Date created
Table2Date Modified Table3Date Modified
Data2 Data3

The problem I have is understanding how to get the Table 1 ID1 into the the remaining tables to create the referential key needed to link the records togather. Do I need seperate statements to be executed, ie do I need to create the view seperately from the update statements?

I guess my question is does anyone have an example kicking around or advice on preparing the SQL and subsequent rev commands for execution

[dglass]

I'm not clear on your table structure.

[jpottsx1]

Sorry it got jumbled in the formatting of the post. Here is the table info

Table 1
------------------------
ID1
Date created
Date Modified

Table 2
----------------------
ID2
Table2ID
Table2Date created
Table2Date Modified
Data2

Table 3
----------------------
ID3
Table3ID
Table3Date created
Table3Date Modified
Data3

The tables are linked via ID1 so that entries for Tables 2 and 3 include ID1 as there TableXID for the joins.

[mwieder]

If I understand this correctly, your Table2ID and Table3ID fields are foreign keys linked to your Table1's ID1 primary key (which is defined as autoincrement integer). In that case, when you're adding a new record to Table 2 you'd use the corresponding ID1 entry. Is that what you're asking?

[jpottsx1]

Yes that is what I'm looking to do. I can't seem to find out if there is a last row command or some such way of calling the Table1 ID for use in my other insert statements. Currently I programmatically built an insert statement with the following code;

Code: Select all

put "INSERT INTO control (" into tSQL
put empty into tFieldNames
put empty into tFieldValues
put revDatabaseColumnNames(conID, "Control") into ptext
replace comma with return in ptext

repeat for each line linColumn in ptext
   put tFieldNames & linColumn & "," into tFieldNames
   put tFieldValues & "'" & linColumn & "'," into tFieldValues
end repeat

repeat while the last character of tFieldNames is ","
   delete last character of tFieldNames
end repeat

--do these separately just in case
repeat while the last character of tFieldValues is ","
   delete last character of tFieldValues
end repeat

put tSQL & tFieldNames & ") VALUES (" into tSQL
put  tFieldValues & ")" after tSQL
answer tSQL
---------------------------------------------------------------------------------------------------
revExecuteSQL conID, tSQL

I also want to know if I can cram the entire transaction into one SQL statement executed by using revExecuteSQL?

Is it me or is it just way too difficult and roundabout to create simple database apps