thinking through parsing multi-line database commands with cr in the data

[Peter Haworth]

I'd be inclined to have a handler to execute/buffer the SQL statements
based on what type of db is in use.

For example you could have a handler dbCommand which took SQL statements as
a parameter.  If the DB is SQLite, the handler would execute the statement
immediately.  If some other version of SQL, it would buffer up the
statement in a global or script local.  You could use the BEGIN statement
to clear the buffer and the COMMIT statement to signify that the block of
statements was complete and should be executed.

As far as the issue of cr's in data, that's a problem whether you're
buffering commands or not.  I long since switched over to using the
"variableslist" option of the various rev database commands, usually with
an array rather than a variable, since it completely avoids the problem of
cr's, escaping quotes, etc.  And it protects against SQL injection which
would seem to be an important issue for you since you're handling sensitive
information over the internet.

Pete
lcSQL Software <http://www.lcsql.com>


On Sat, May 25, 2013 at 10:49 AM, Dr. Hawkins <dochawk at gmail.com> wrote:

> I'm still kind of thinking aloud on this, and don't want to shoot my foot
> clean off.
>
> I'm developing with the ipad version in mind for the future, too.
>
> My preferred db will be postgres, with mysql as an alternate (if I don't
> keep tripping across it's "whoops!"s).  However, for performance reasons
> (as well as the demo & single use versions working without having to
> install a db server!), it will be necessary to support sqlite as well,
> keeping the local db.
>
> I'm writting a wrapper to take multiple commands, and possibly an
> update/sync command, which could be to the table in the same or another db.
>
> If I'm feeding postgres/mysql, I can have a begin/update this/insert
> that/commit sequence, no problem.
>
> SQLite, though, seems to have at least sporadic issues with multi-line
> command blocks, and may need them sent one at a time.
>
> What I'm scratching my brain for is "unpeeling" a multi-command block where
> the data will usually include cr.
>
> All I'm coming up with so far is putting a line in, and testing for things
> like "INSERT INTO" "UPDATE" and any other command I use on the next line to
> see if it is indeed a new line, and otherwise appending to the current
> working line.
>
> But this will trip up on, for example, an address like:
>
>    Joe Scmoe, President
>    Update your mac!
>    123 E. Main St.
>
>
> Or am I (hopefully) missing an obvious solution (other than dump sqlite :)
>
>
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
> _______________________________________________
> 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
>