Database syntax

Pete pete at mollysrevenge.com
Tue Dec 20 16:21:28 EST 2011


Hi Michael,
I guess the use of DEFAULT is a philosophical thing.  I like to have the
database do everything it can do for me rather than have to add code to the
application but not everyone wants to do that.SImilarly with NULL values, I
don;t like having them in my database because it feels like I wasn't sure
what to put into that field, plus if you forget to include a value for a
column during INSERT, it will end up as NULL which may produce unexpected
results down the line.  So I always code my columns with NOT NULL and
specify a default value.  But once again, it's a philosophical decision on
my part.

Since my last reply, I looked back at the db I'm storing arrays in and I
use a TEXT column to store the bas64encoded values in, not a blob.  I
believe I tried a blob and had problems getting it to work, but don;t
remember for sure.  All I can say for sure is that storing a base64 encoded
value in a TEXT column works just fine.

Your shortened INSERT statement would be correct if you supply default
values in your schema - you don;t need the" put empty into x" or "put 0
into tRT"

The syntax for declaring a DEFUAL VALUE would be (for example):

GroupNbr NOT NULL DEFAULT 0

Since you're developing for iOS, I assume your using SQLite.  IF so, you
can find the documentation for it at http://www.sqlite.org/lang.html

Pete

On Tue, Dec 20, 2011 at 12:51 PM, Michael Doub <mike at doub.com> wrote:

> Thanks Pete,
>
> I just found an example that indicated that I don't need to use the
> base64encoding.  Is this true?  "*b"
>
>  put  "INSERT into ContactData (" & \
>            "myKey," & \
>            "RecordType," & \
>            "RecID," & \
>            "Name," & \
>            "GroupNbr," & \
>            "LastContact," & \
>            "DaysTill," & \
>            "DataArray) " & \
>            "Values (:1,:2,:3,:4,:5,:6,:7,:8 )" into tUpSQL
>      put the pNetDB of stack "MyNetwork" into sNetConnID
>      put empty into x
>      put 0 into tRT
>      revExecuteSQL sNetConnID, tUpSQL,
> "x","tRT","x","x","x","x","x","*btSettings"
>
> If I understand you correctly you are recommending that I make sure that x
> = Space or something else
> other than empty.  Correct?
>
> I need to go lookup the details of the default key word.   If I use it
> would the above syntax be
> simplified to:
>
>  put  "INSERT into ContactData (" & \
>            "RecordType," & \
>            "DataArray) " & \
>            "Values (:1,:2 )" into tUpSQL
>      put the pNetDB of stack "MyNetwork" into sNetConnID
>      put empty into x
>      put 0 into tRT
>      revExecuteSQL sNetConnID, tUpSQL,"tRT","*btSettings"
>
>
> Can I use this substitution trick with any SQL statement, specifically the
> updating?
>
>
> I am working on an app for IOS and I am worried about memory consumption.
>  Ideally I would
> have liked to just use arrays and been done with it, but memory concerns
> have me trying to learn
> SQL and rev's implementation.   This is why I am shying away from helper
> tools.   My thinking is
> that these tools must be pretty sophisticated, and that translates into
> memory usage.   For a PC
> app, i would use them for sure.
>
> -= 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