Batch processing of $sqltext

[Mischa]

Hi Terry,

what I'm doing is creating indexes in tables like this:

Do mSessionObject.$assign($root.$sessions.[mSessionName].$sessionobject)
Returns #F
Do mSessionObject.$newstatement() Returns mStatementObject

Begin statement (Carriage return,Linefeed)     ;; default indexes
Sta: CREATE UNIQUE INDEX [pDataClassName]UID ON [pDataClassName] ( UID );
Sta: CREATE UNIQUE INDEX [pDataClassName]LongUID ON [pDataClassName] (
LongUID );
Sta: CREATE UNIQUE INDEX [pDataClassName]Sort ON [pDataClassName] ( Sort );
End statement

Do mStatementObject.$prepare() Returns #F
Do mStatementObject.$execute() Returns #F

... so, no pending result is available in $fetch. I've already tried to
issue the $execute() method repeatedly after the prepare, this however
results in an error message saying the first index has been created already.
There is a begin/commit/end transaction construct in SQLite, however I don't
think this is intended to be used here. I have not seen a scripting meta
language in SQLite, also it would not make much sense as nearly everyone
uses php or asp. I've also looked already for a pending scripts/results
property in the SQLite DAM but have not found any.

Thanks a lot for trying to help, anyway ;) !

---------------------------------------------------
  T H E   O M N I S   L /\ B     www.omnislab.com
---------------------------------------------------


-----Ursprüngliche Nachricht-----
Von: omnisdev-en-bounces at lists.omnis-dev.com
[mailto:omnisdev-en-bounces at lists.omnis-dev.com] Im Auftrag von Terence J.
young, DC
Gesendet: Donnerstag, 03. Juli 2014 01:56
An: omnisdev-en at lists.omnis-dev.com
Betreff: Re: Batch processing of $sqltext

Hi Mischa,

In sybase, if you send a series of sql select  statements, you need to fetch
the rows until a null fetch is returned, and then , execute another set of
fetches for the second set of results, and so on.  
getting error messages also requires repeated sets of query.

Perhaps you only think the dam has stopped after execution of the first sql,
repeat the fetch sequence after you thing it has stopped.  Maybe you will
get a second set data for the second sql statement.

Is there a begin end block equivalent in sqlite to place a series of
statements in that block?

Is there a property of sql session or statement object that addresses
multiple result sets?

I have run out of ideas.

terry

On 7/2/14, 6:46 PM, Mischa wrote:
> Thanks Terry for your suggestions!
>
> Using [';'] does not make any difference on my side, while using '\;' 
> leads to a syntax error (unrecognized token '\')
>
> When I understand the working of the DAMs correct, they issue the SQL 
> script to the backend and return to the user with an error message 
> when the backend yells. So, obviously, the SQLite backend understands 
> the sent script, but the DAM then stops after the execution of the 
> first statement, maybe because it got a 'did it' reply from the 
> backend. On the other hand, there seem to be DAMs/backends like Sybase 
> or Frontbase, that can process SQL scripts with more than one 
> statement. Maybe they reply with a cumulated message once the stack is 
> processed so the DAM is not interrupted, or maybe it has to do with 
> different transaction settings of the DAM (?)
>
> Best greetings and thanks
> Mischa
>
> ---------------------------------------------------
>    T H E   O M N I S   L /\ B     www.omnislab.com
> ---------------------------------------------------
>
>
> -----Ursprüngliche Nachricht-----
> Von: omnisdev-en-bounces at lists.omnis-dev.com
> [mailto:omnisdev-en-bounces at lists.omnis-dev.com] Im Auftrag von Terence J.
> young, DC
> Gesendet: Donnerstag, 03. Juli 2014 00:38
> An: omnisdev-en at lists.omnis-dev.com
> Betreff: Re: Batch processing of $sqltext
>
> Hi Mischa,
>
> Have you tried backspacing the ; with a backslash? (ie \;
>
> Have you tried wrapping the ; in brakets and quotes, ie   [';']
>
> sybase allows for multiple statements woithout having to put in 
> command line seperators like ';'.
>
> terry
>
>
> On 7/2/14, 3:56 PM, Mischa wrote:
>> Hi Stephen,
>>
>> my words ;) - however, it seems that with some other backends the 
>> processing of multiple SQL statements works or used have to work in 
>> the past (thanks Kelly!). I've looked at the SQLite page and found 
>> some examples of multiple SQL statements delimited with ';', so there 
>> should not be an issue with SQLite. For me, it appears to be a bug 
>> with Omnis, because it resolves all bind variables within the stacked 
>> SQL statements and validates all of them against the backend - 
>> something that does not make sense when you only want to process the 
>> first
> one.
>> Best greetings
>> Mischa
>>
>> ---------------------------------------------------
>>     T H E   O M N I S   L /\ B     www.omnislab.com
>> ---------------------------------------------------
>>
>>
>> -----Ursprüngliche Nachricht-----
>> Von: omnisdev-en-bounces at lists.omnis-dev.com
>> [mailto:omnisdev-en-bounces at lists.omnis-dev.com] Im Auftrag von 
>> Stephen Miller
>> Gesendet: Dienstag, 01. Juli 2014 00:56
>> An: OmnisDev List - English
>> Betreff: Re: Batch processing of $sqltext
>>
>> Hi Mischa
>>
>> My only suggestion is always try to make your sql code as generic as 
>> possible - by that I mean database agnostic - or - to write into your 
>> code a means of switching connections and executing the prepare 
>> against alternate databases - or - writing the executed code to a 
>> list or file where the code can be fired against an alternate 
>> database using a
> command line tool.
>> If you write this code into you generic routines now then this could 
>> save you a lot of work later.
>>
>>
>> Kind Regards,
>>
>> Stephen Miller
>>
>> +61 (0)467 898010
>> http://lnkd.in/b9nmPNz
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>>
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com