Database syntax

Pete pete at mollysrevenge.com
Tue Dec 20 15:15:55 EST 2011


HI Michael,
To get the myKey column automatically incremented by 1 each time you add a
row to your table, add "NOT NULL" to it's definition in your CREATE TABLE
statement, and do not supply a value for myKey when you issue an INSERT
command for the table.

I recommend that you define default values for each of you columns using
the DEFAULT keyword.  In your case, that means zero for RecordType.  If you
don;t specify a DEFAULT value and don;t provide a value in the INSERT
command, SQL will put a NULL in there and my experience is that will cause
you complications further down the line so I'd recommend that you come up
with actual default values for the other columns (Space, zero, etc) and put
them in the CREATE statement

If you do all that, the command to add a row to your table will be

INSERT INTO ContactData (DataArray) VALUES (<yourencodestring>)

Write some code to make that statement then call revexecuteSQL to insert
the row into your table.  NOte that your encoded sting must be enclosed in
single quotes.

To update an entry, the SQL sytax is:

UPDATE  ContactData SET <co1name=col1value>, <col2name=col2value>, ...
WHERE <selection criteria>

Use revExecuteSQL again to issue the command. In this case, you only need
to specify the columns and their values that have changed.  Frequently, the
selection critria will be the value of your myKey column, eg WHERE myKEY=3
but you can update multiple records with one UPDATE statement by using the
WHERE to select them, if that's what you need to do.

There are LC products pout there that will make some of this easier,
SQLYoga for one, and I will be releasing my SQL plugin helper for LC in
January which elminate the need for you to write any SQL or LC code to do
what you want to do :-)

Hope that helps,

Pete

On Tue, Dec 20, 2011 at 11:31 AM, Michael Doub <mike at doub.com> 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
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>



More information about the use-livecode mailing list