Odd question

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Odd question

Nelson, Erik - 2
For unfortunate reasons, I need a query that does an insert and also returns at least one row... for example, something along the lines of

Select 1 as value from (insert into table1 values(a, b, c))

Or

Select coalesce((insert into table1 values(a, b, c)), 1) as value

I've tried a number of options but haven't been able to get anything to work.  Is it possible?


----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Keith Medcalf

Your method of achieving a solution to whatever problem you are trying to solve will not work.

Perhaps you can state the problem you are trying to solve without making an assumption as to how to solve that problem, someone may be able to solve the problem with a workable solution rather than an unworkable solution.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Nelson, Erik - 2
>Sent: Saturday, 16 December, 2017 12:53
>To: SQLite mailing list
>Subject: [sqlite] Odd question
>
>For unfortunate reasons, I need a query that does an insert and also
>returns at least one row... for example, something along the lines of
>
>Select 1 as value from (insert into table1 values(a, b, c))
>
>Or
>
>Select coalesce((insert into table1 values(a, b, c)), 1) as value
>
>I've tried a number of options but haven't been able to get anything
>to work.  Is it possible?
>
>
>---------------------------------------------------------------------
>-
>This message, and any attachments, is for the intended recipient(s)
>only, may contain information that is privileged, confidential and/or
>proprietary and subject to important terms and conditions available
>at http://www.bankofamerica.com/emaildisclaimer.   If you are not the
>intended recipient, please delete this message.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

R Smith
In reply to this post by Nelson, Erik - 2

On 2017/12/16 9:53 PM, Nelson, Erik - 2 wrote:
> For unfortunate reasons, I need a query that does an insert and also returns at least one row... for example, something along the lines of
>
> Select 1 as value from (insert into table1 values(a, b, c))
>
> Or
>
> Select coalesce((insert into table1 values(a, b, c)), 1) as value
>
> I've tried a number of options but haven't been able to get anything to work.  Is it possible?

That is not possible, and certainly not part of the spirit or letter of
the SQL Standard, and not done by any of the standard SQL Engines.
(However, I think in MySQL there exists a way to to do it - but I'm not
100% sure).

May I ask what are the "unfortunate reasons"?  Perhaps someone here
had/have a similar scenario and can assist - but the way you are trying
to solve your problem will almost certainly not work (unless perhaps
through forking and fudging the SQLite code itself, but that is a
different can of worms and must be a very last resort...)

Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by Keith Medcalf
Keith Medcalf wrote on Sent: Saturday, December 16, 2017 4:04 PM

>Your method of achieving a solution to whatever problem you are trying to solve will not work.

>Perhaps you can state the problem you are trying to solve without making an assumption as to how to solve that problem, someone may be able to solve the problem with a workable solution rather than an unworkable solution.

I'm using an application that I can't change.  I can give it multiple queries to run but the application assumes that each query will produce at least one row and causes an error if that's not the case.  I want to cause some rows to be inserted into a table but plain insert queries don't work because they violate the application's assumption that a result will be returned.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Nelson, Erik - 2
>Sent: Saturday, 16 December, 2017 12:53
>To: SQLite mailing list
>Subject: [sqlite] Odd question
>
>For unfortunate reasons, I need a query that does an insert and also
>returns at least one row... for example, something along the lines of
>
>Select 1 as value from (insert into table1 values(a, b, c))
>
>Or
>
>Select coalesce((insert into table1 values(a, b, c)), 1) as value
>
>I've tried a number of options but haven't been able to get anything
>to work.  Is it possible?
>
=

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

petern
In reply to this post by Keith Medcalf
Re: Nelson "odd". This will make the desired (?) side effect happen:

.load eval.so
SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value;

If INSERT references columns from an outer scope then use printf() inside
the eval().
From a program, use https://sqlite.org/c3ref/load_extension.html instead of
".load".

The source of eval.c is in the SQLite source tree or
https://www.sqlite.org/src/artifact/f971962e92ebb8b0

That one is eval(X,Y) where Y is optional column separator.  If you need
the improved eval(X,Y,Z) where Z is an optional line separator, let me know
and I'll post that upgrade.

Peter


On Sat, Dec 16, 2017 at 1:04 PM, Keith Medcalf <[hidden email]> wrote:

>
> Your method of achieving a solution to whatever problem you are trying to
> solve will not work.
>
> Perhaps you can state the problem you are trying to solve without making
> an assumption as to how to solve that problem, someone may be able to solve
> the problem with a workable solution rather than an unworkable solution.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Nelson, Erik - 2
> >Sent: Saturday, 16 December, 2017 12:53
> >To: SQLite mailing list
> >Subject: [sqlite] Odd question
> >
> >For unfortunate reasons, I need a query that does an insert and also
> >returns at least one row... for example, something along the lines of
> >
> >Select 1 as value from (insert into table1 values(a, b, c))
> >
> >Or
> >
> >Select coalesce((insert into table1 values(a, b, c)), 1) as value
> >
> >I've tried a number of options but haven't been able to get anything
> >to work.  Is it possible?
> >
> >
> >---------------------------------------------------------------------
> >-
> >This message, and any attachments, is for the intended recipient(s)
> >only, may contain information that is privileged, confidential and/or
> >proprietary and subject to important terms and conditions available
> >at http://www.bankofamerica.com/emaildisclaimer.   If you are not the
> >intended recipient, please delete this message.
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Simon Slavin-3
In reply to this post by Nelson, Erik - 2


On 16 Dec 2017, at 9:43pm, Nelson, Erik - 2 <[hidden email]> wrote:

> I'm using an application that I can't change.  I can give it multiple queries to run but the application assumes that each query will produce at least one row and causes an error if that's not the case.

Will the application recognise two statements separated by a semicolon ?  This happens automatically if it is using sqlite3_exec().  If it does, then you can do

INSERT into table1 values(1, 2, 3); SELECT 1

As if it’s a single statement and it’ll return the result of SELECT 1

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by petern
Petern wrote on Saturday, December 16, 2017 4:53 PM
Re: Nelson "odd". This will make the desired (?) side effect happen:

>.load eval.so
>SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value;

>If INSERT references columns from an outer scope then use printf() inside the eval().

...snip...

>That one is eval(X,Y) where Y is optional column separator.  If you need
>the improved eval(X,Y,Z) where Z is an optional line separator, let me know
>and I'll post that upgrade.

That's a great suggestion... I'm not using the sqlite shell and I don't have control of the application but there's a reasonable chance that eval() is statically compiled into the app.  I'll investigate that further.

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by Simon Slavin-3
Simon Slavin wrote on Saturday, December 16, 2017 5:15 PM
>>On 16 Dec 2017, at 9:43pm, Nelson, Erik - 2 <[hidden email]> wrote:

>> I'm using an application that I can't change.  I can give it multiple queries to run but the application assumes that each query will produce at least one row and causes an error if that's not the case.

>Will the application recognise two statements separated by a semicolon ?  This happens automatically if it is using sqlite3_exec().  If it does, then you can do

>INSERT into table1 values(1, 2, 3); SELECT 1

>As if it’s a single statement and it’ll return the result of SELECT 1

This might work- I've never tried passing two statements in as a single 'query' but afaik the semicolon doesn't have any particular significance to the application.  I believe the application is using the prepare()/step() interface but it's worth a try.  Thanks for the suggestion!

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

nomad
In reply to this post by Nelson, Erik - 2
On Sat Dec 16, 2017 at 07:53:22PM +0000, Nelson, Erik - 2 wrote:

> For unfortunate reasons, I need a query that does an insert and also
> returns at least one row... for example, something along the lines of
>
> Select 1 as value from (insert into table1 values(a, b, c))
>
> Or
>
> Select coalesce((insert into table1 values(a, b, c)), 1) as value
>
> I've tried a number of options but haven't been able to get anything
> to work.  Is it possible?

How about a user defined function that does the insert in the
background?

    SELECT my_insert(table_name, val1, val2);

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Keith Medcalf
In reply to this post by Nelson, Erik - 2

How do you know the insert is not working?  Have you verified that the data is not being inserted or are you simply relying on a popup provided by the application?  If the later, you should be aware that the application can pop-up whatever it wants at any time.  For example, it could claim the statement failed because the moon is not made of blue stilton.  While the moon is indeed not made of blue stilton, that is not (and cannot possibly be) the reason that the statement failed.  It does not mean that the statement in fact failed at all, only that the application popped up a pop saying it failed bue to the moon not being made of blue stilton.

By the time the "application" knows that no results were returned it is already too late, the statement has already been run to completion.  The application is likely just "spitting out a message" based on their being no results.

Unless of course the application has been deliberately designed to preclude insert/update statements by wrapping each statement execution in a transaction, which it then does a rollback on.  Or perhaps by making sure the first token of the statement is the word "SELECT" ...  If this is the case then perhaps you can get a version of the application that does not have these, rather intentionally created, restrictions.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Nelson, Erik - 2
>Sent: Saturday, 16 December, 2017 14:44
>To: SQLite mailing list
>Subject: Re: [sqlite] Odd question
>
>Keith Medcalf wrote on Sent: Saturday, December 16, 2017 4:04 PM
>
>>Your method of achieving a solution to whatever problem you are
>trying to solve will not work.
>
>>Perhaps you can state the problem you are trying to solve without
>making an assumption as to how to solve that problem, someone may be
>able to solve the problem with a workable solution rather than an
>unworkable solution.
>
>I'm using an application that I can't change.  I can give it multiple
>queries to run but the application assumes that each query will
>produce at least one row and causes an error if that's not the case.
>I want to cause some rows to be inserted into a table but plain
>insert queries don't work because they violate the application's
>assumption that a result will be returned.
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Nelson, Erik - 2
>>Sent: Saturday, 16 December, 2017 12:53
>>To: SQLite mailing list
>>Subject: [sqlite] Odd question
>>
>>For unfortunate reasons, I need a query that does an insert and also
>>returns at least one row... for example, something along the lines
>of
>>
>>Select 1 as value from (insert into table1 values(a, b, c))
>>
>>Or
>>
>>Select coalesce((insert into table1 values(a, b, c)), 1) as value
>>
>>I've tried a number of options but haven't been able to get anything
>>to work.  Is it possible?
>>
>=
>
>---------------------------------------------------------------------
>-
>This message, and any attachments, is for the intended recipient(s)
>only, may contain information that is privileged, confidential and/or
>proprietary and subject to important terms and conditions available
>at http://www.bankofamerica.com/emaildisclaimer.   If you are not the
>intended recipient, please delete this message.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by Nelson, Erik - 2
Nelson, Erik - 2 Sent: Saturday, December 16, 2017 5:27 PM

>Petern wrote on Saturday, December 16, 2017 4:53 PM
>>Re: Nelson "odd". This will make the desired (?) side effect happen:

>>.load eval.so
>>SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value;

>>If INSERT references columns from an outer scope then use printf() inside the eval().

>>That one is eval(X,Y) where Y is optional column separator.  If you need
>>the improved eval(X,Y,Z) where Z is an optional line separator, let me know
>>and I'll post that upgrade.

>That's a great suggestion... I'm not using the sqlite shell and I don't have control of the application but there's a reasonable chance that eval() is statically compiled into the app.  I'll investigate that further.

I tracked this down, 'eval' isn't available.  :( My only input possibility is sql statements/pragmas... anything that will pass through a prepare()/step() and produce at least one row of results.

Thanks for the suggestion, though... I never would have thought of trying that!

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by nomad
Nomad Sent: Sunday, December 17, 2017 4:11 PM
>On Sat Dec 16, 2017 at 07:53:22PM +0000, Nelson, Erik - 2 wrote:
>> For unfortunate reasons, I need a query that does an insert and also
>> returns at least one row... for example, something along the lines of
 
>> Select 1 as value from (insert into table1 values(a, b, c))
>> I've tried a number of options but haven't been able to get anything
...snip...
>> to work.  Is it possible?

>How about a user defined function that does the insert in the
>background?

>    SELECT my_insert(table_name, val1, val2);

That's a good suggestion- the only input control I have is sql statements that must return at least one row.  AFAIK there's no way to make UDFs within that constraint?

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Peter da Silva
In reply to this post by Nelson, Erik - 2
What I don’t understand is this app that’s making SQLite calls, so it’s using the SQLite library, and it’s expecting a result from updates and inserts?

That seems like a bug or design flaw in the application.

Possibly it’s looking for the number of rows effected result and not finding it for some reason?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
Peter Da Silva Sent: Monday, December 18, 2017 12:24 PM


>What I don’t understand is this app that’s making SQLite calls, so it’s using the SQLite library, and it’s expecting a result from updates and inserts?

Yes, exactly.  The app designers assumed that rows are returned from every statement.  That's the 'unfortunate reason'.

>That seems like a bug or design flaw in the application.



I agree with either of those characterizations!



>Possibly it’s looking for the number of rows effected result and not finding it for some reason?



I investigated a further while exploring some of the list suggestions.  The app halts with an error unless sqlite3_column_count() > 0.  That means I misspoke when I mentioned that the sql statement needed to return at least one row.  I’m not sure if that makes a difference, though.



https://sqlite.org/c3ref/column_count.html









----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Kees Nuyt
In reply to this post by Nelson, Erik - 2
On Sat, 16 Dec 2017 21:43:44 +0000, "Nelson, Erik - 2"
<[hidden email]> wrote:

> I'm using an application that I can't change.
> I can give it multiple queries to run but the
> application assumes that each query will produce
> at least one row and causes an error if that's
> not the case.  I want to cause some rows to be
> inserted into a table but plain insert queries
> don't work because they violate the application's
> assumption that a result will be returned.

Something like:
https://sqlite.org/pragma.html#pragma_count_changes
(deprecated, but might still work)

--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by Keith Medcalf
Keith Medcalf Sent: Sunday, December 17, 2017 4:30 PM

>How do you know the insert is not working?  Have you verified that the data is not being inserted or are you simply relying on a popup provided by the application?  If the later, you should be aware that the application can pop-up whatever it wants at any time.

The insert() does work when step() is called.

>For example, it could claim the statement failed because the moon is not made of blue stilton.  While the moon is indeed not made of blue stilton, that is not (and cannot possibly be) the reason that the statement failed.  It does not mean that the statement in fact failed at all, only that the application popped up a pop saying it failed bue to the moon not being made of blue stilton.

Also correct- the application effectively does  "if (sqlite3_column_count() == 0) throw("the moon is made of blue stilton")

>By the time the "application" knows that no results were returned it is already too late, the statement has already been run to completion.  The application is likely just "spitting out a message" based on their being no results.

Exactly.  The statement is run, and it runs correctly.  Just the application halts the run because sqlite3_column_count() is zero.

>Unless of course the application has been deliberately designed to preclude insert/update statements by wrapping each statement execution in a transaction, which it then does a rollback on.

That's not the case, it's just a poor assumption on the part of the application designers that sqlite3_column_count() > 0

>Or perhaps by making sure the first token of the statement is the word "SELECT" ...  If this is the case then perhaps you can get a version of the application that does not have these, rather intentionally created, restrictions.

Right- hence the 'For unfortunate reasons' clause.  It's not possible to change the application at this time, so I was hoping for some workaround using the things I can control (the sql statements being fed to the application)


----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Keith Medcalf
In reply to this post by Nelson, Erik - 2
>I investigated a further while exploring some of the list
>suggestions.  The app halts with an error unless
>sqlite3_column_count() > 0.  That means I misspoke when I mentioned
>that the sql statement needed to return at least one row.  I’m not
>sure if that makes a difference, though.

That makes a *huge* difference.  sqlite3_column_count() is available after the prepare and before the first step.  This means that the statement can be cancelled BEFORE it is executed (step'ed).  "returns no rows" can only be determined by running (step'ing) the statement and requires the statement to be both prepared and run (and that it returns SQLITE_DONE with no SQLITE_ROW).

In the former case all statements which are not select/pragma statements returning data do not have to be run.  In the latter case, all statements will be run and you will get the "no data was returned" if no data was returned.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by Kees Nuyt
Kees Nuyt Sent: Monday, December 18, 2017 12:51 PM

On Sat, 16 Dec 2017 21:43:44 +0000, "Nelson, Erik - 2" <[hidden email]> wrote:

>> I'm using an application that I can't change.
>> I can give it multiple queries to run but the
>> application assumes that each query will produce
>> at least one row and causes an error if that's
>> not the case.  I want to cause some rows to be
>> inserted into a table but plain insert queries
>> don't work because they violate the application's
>> assumption that a result will be returned.

>Something like: https://sqlite.org/pragma.html#pragma_count_changes
 (deprecated, but might still work)

Great suggestion!  The classical form of the pragma doesn't return any columns, and I don't think this pragma is available in the pragma functions (and wouldn't help much anyway, the app is built with sqlite 3.13.0)

These are all good suggestions, thanks to everyone who has responded!

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Nelson, Erik - 2
In reply to this post by Keith Medcalf
Keith Medcalf  Sent: Monday, December 18, 2017 1:07 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Odd question

>>I investigated a further while exploring some of the list
>>suggestions.  The app halts with an error unless
>>sqlite3_column_count() > 0.  That means I misspoke when I mentioned
>>that the sql statement needed to return at least one row.  I’m not
>>sure if that makes a difference, though.


> That makes a *huge* difference.  sqlite3_column_count() is available after the prepare and before the first step.

Yes, the sqlite3_column_count() call happens after prepare() and before step().  

>This means that the statement can be cancelled BEFORE it is executed (step'ed).  "returns no rows" can only be determined by running (step'ing) the statement and requires the statement to be both prepared and run (and that it returns SQLITE_DONE with no SQLITE_ROW).

Right, all of the statements are step'd regardless of the result of sqlite3_column_count().  SQLITE_DONE is returned from the first step() for insert queries.

>In the former case all statements which are not select/pragma statements returning data do not have to be run.

All the statements are run, the check for column count happens further downstream

>In the latter case, all statements will be run and you will get the "no data was returned" if no data was returned.

Yes, all statements are run but the error comes from the fact that sqlite3_column_count() == 0

In pseudocode, it's

prepare("insert...")  //okay
int num_col = sqlite3_column_count()  //okay
step() until sqlite_done  //okay

assert(num_col  > 0)  // blows up here, even though the query was successful

So effectively I need sqlite3_column_count() > 0 in order to bypass the faulty assertion.

Thanks for your help in pushing me to think about it and describe it more clearly- even if we don't find a solution it's a helpful conversation.







----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Odd question

Keith Medcalf
On Monday, 18 December, 2017 11:35

>Keith Medcalf  Sent: Monday, December 18, 2017 1:07 PM
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Odd question

>>>I investigated a further while exploring some of the list
>>>suggestions.  The app halts with an error unless
>>>sqlite3_column_count() > 0.  That means I misspoke when I mentioned
>>>that the sql statement needed to return at least one row.  I’m not
>>>sure if that makes a difference, though.

>> That makes a *huge* difference.  sqlite3_column_count() is
>available after the prepare and before the first step.

>Yes, the sqlite3_column_count() call happens after prepare() and
>before step().

>>This means that the statement can be cancelled BEFORE it is executed
>(step'ed).  "returns no rows" can only be determined by running
>(step'ing) the statement and requires the statement to be both
>prepared and run (and that it returns SQLITE_DONE with no
>SQLITE_ROW).

>Right, all of the statements are step'd regardless of the result of
>sqlite3_column_count().  SQLITE_DONE is returned from the first
>step() for insert queries.

>>In the former case all statements which are not select/pragma
>statements returning data do not have to be run.

>All the statements are run, the check for column count happens
>further downstream

>>In the latter case, all statements will be run and you will get the
>"no data was returned" if no data was returned.

>Yes, all statements are run but the error comes from the fact that
>sqlite3_column_count() == 0

>In pseudocode, it's

>prepare("insert...")  //okay
>int num_col = sqlite3_column_count()  //okay
>step() until sqlite_done  //okay

>assert(num_col  > 0)  // blows up here, even though the query was
>successful

>So effectively I need sqlite3_column_count() > 0 in order to bypass
>the faulty assertion.

>Thanks for your help in pushing me to think about it and describe it
>more clearly- even if we don't find a solution it's a helpful
>conversation.

So if the insert statement was step'd, then how is it that it has not been executed?  Is the code more like:

prepare(BEGIN)
step(BEGIN)
prepare(INSERT)
num_col = ....
(step() until sqlite_done)
prepare(ROLLBACK)
step(ROLLBACK)

Because if the INSERT is stepped until done, then UNLESS each statement (or group of statements) is taking place within an explicit transaction that is then rolled back, the INSERT must have run to completion and been committed, and the data MUST have been inserted ... notwithstanding the assert failure.  As far as I can tell, the only way this would not be true is if the statement were run inside an explicit transaction which was "aborted" by de-rugging before it was committed.






_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12