Sqlite: storing fields from multiple cards in one table

[mikemc]

I would like to create a simple database which contains about 42 number fields spread out
on six cards. Each record would represent a new deal. I don't need to query the data just need
to save it and recall a saved deal from a list of records or add new deals. What is the simplest way
to use the insert command to insert data into a table when the fields come from multiple cards?

[fgr33n]

Hi Mikemc, If I understand your concern, to access fields on multiple cards you need to add the card name that the field is on.

So instead of

Code: Select all

the text of field "fieldName"

use the full path:

Code: Select all

 the text of field "fieldName" of card "cardName"

By specifying the path, LC will be able to find you fields no matter which card they are on.

The hardest part of this type of app is setting up the statements to talk to the database and read and write the data.

In case you haven't found this tutorial yet, I was able to put together a simple test database app by following this.

http://lessons.runrev.com/m/4069/l/3051 ... e-database

[mikemc]

So I guess my code would be INSERT into Tablename(field1,field2) VALUES ('field1 of cd card1'.'field2 of cd card2') and the full path should tell
live code where the field is anywhere in the stack. Not ready to try it yet but it looks good to me. Thanks

[SparkOut]

In principle, yes, but be careful about mixing literals and field references or variables. Some parts need to be enclosed in quotes and some parts without, in order to resolve the value required from the reference.

[mikemc]

I am still having problems inserting fields from different cards into the table
put "INSERT into contact_details VALUES ('Joe Bloggs','joe.bloggs@email.com');" into tSQL
this line of code works fine the insert the txt Joe Bloggs and joe.bloggs@email.com
but what I want to insert is two fields form different cards fld field1 of cd card1 and fld field2 of cd card2
I tried to enter this into the single quotes but a you suspected it just treated it as txt not a direction to a field,
I'm sure there is a simple way to do this I just don't know how to write the code so both livecode and sqlite can
understand it help!

[zaxos]

Code: Select all

put "INSERT into contact_details VALUES ('"&fld 1 of card 1&"','"&fld 1 of card 2&"');" into tSQL

Notice the single quotes are still there within the double quotes.
or you could try this:

Code: Select all

local tFld1,tFld2
put fld 1 of card 1 into tFld1
put fld 2 of card 2 into tFld2
put "INSERT into contact_details VALUES ('"&tFld1"&"','"&tFld2&");" into tSQL

Try this also to see in the console if your query is correct:

Code: Select all

put "INSERT into contact_details VALUES ('"&fld 1 of card 1&"','"&fld 1 of card 2&"');"

[FourthWorld]

42x6 is only 252 data elements. Not sure you need a database for that. SQLite is great, but there are many ways to store and retrieve data in LiveCode.

[mikemc]

This version of code finally did it Hurray!
put "INSERT into contact_details VALUES ('"& fld c3 of cd card3 &"','joe.bloggs@email.com');" into tSQL
Now I know I can create my table and fill it with data from fields on multiple cards. I just have to learn how
to retrieve the records from the database and fill them into the fields in my app. Plus making it all
work together through the interface.
Thanks for the help I'm on to the next hurdle

[mikemc]

Hey ForthWorld I'm not sure either. I will have 20 or more deal records all containing 42 fields spread over 6 cards. I want to be able to
save deals recall them change them and delete them. What do you think?

[FourthWorld]

They could be stored as encoded arrays (see arrayEncode and arrayDecode in the Dictionary), or even as text files, or in one text file with some delimiter convention that works for you, or as custom properties in a stack file, or you could store the stack itself, or...the possibilities are nearly limitless.

[phaworth]

I agree with Richard. SQLite is great but a lot of learning and overhead for only 20 records in one table. If I ever find myself with a schema that has only 1 table, it's a good sign that an SQL database probably isn't the right tool.

I'd use a custom property of your stack to hold the data if you don;t need to store it across runs of the program, or a text file if you do. In either case, put each record on a separate line and separate the fields within each record with tabs (or some other character if your data can contain tabs).

After that, it's just a case of reading the data from wherever you choose to store it at startup. To insert a line, just add it to the end of the data. Depending on your search requirements, the filter command would probably be the easiest way to go.

Pete

[mikemc]

Any of these methods sound easier than SQLite but I have only been at the coding thing just over a
month so any specific instructions would be appreciated or the direction to some resources that lay
the method out for a beginner. Thanks

[phaworth]

Here's a few techniques, assuming you store your data in a custom property of your stack named cData.

First, put all your data handlers in the script of your main stack. That way, if you decide to store your data in arrays or in a text file, you just have to change your central handlers.

You might end up with some handlers that look like this.

on AddDataLine pline

--Appends a line to the end of cData

local tData

put the cData of me into tData
put pline & return after tData
set the cData of me to tData

end AddDataLine

on FIndLineNumbers psearchtext,pitem

--Returns a comma delimited list of line numbers which contain psearchText in item pitem of the cData

local tData,tLineNUmber

put the cData of me into tData
put zero into tLineNumber
repeat for each line rLine in tData
add 1 to LineNumber
if item pitem of rData contains psearchtext then
put tLineNumber & comma after tHits
end if
end if

end FindLineNumbers

on GetDataLine plinenumber

--Retunrs the data from line plineNUmber of the cData

return line plinenumber of the cData of me

end GetDataLine

on DeleteDataLine plinenumber

--Delete line plinenumber from the cData

local tData

put the cData of me into tData
delete line plinenumber of tData
set the cData of me to tData

end DeleteDataLine

on replaceDataLine plinenumber pdata

--Replaces an existing line with new contents in the cData

local tData

put the cData of me into tData
put pdata into line plineNumber of tData
set the cData of me to tData

end ReplaceDataLine

Haven't tested any of these but that will get you started.

Pete

[mikemc]

ok thanks i'll give it a try