Re: [sqlite] Odd question

[Nelson, Erik - 2]

Mark Sent: Tuesday, December 19, 2017 1:32 AM

On Mon Dec 18, 2017 at 04:48:27PM +, Nelson, Erik - 2 wrote:
> Nomad Sent: Sunday, December 17, 2017 4:11 PM
> >On Sat Dec 16, 2017 at 07:53:22PM +, Nelson, Erik - 2 wrote:
>  
> >> 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?

>That is quite a constraint. Unfortunately then the UDF option is not
>available to you.

Alas, yes.  Thanks to all for the excellent ideas, we can consider my question 
closed.

--
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[nomad]

On Mon Dec 18, 2017 at 04:48:27PM +, Nelson, Erik - 2 wrote:
> Nomad Sent: Sunday, December 17, 2017 4:11 PM
> >On Sat Dec 16, 2017 at 07:53:22PM +, Nelson, Erik - 2 wrote:
>  
> >> 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?

That is quite a constraint. Unfortunately then the UDF option is not
available to you.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Keith Medcalf  Sent: Monday, December 18, 2017 2:31 PM

...snipped a lot...

>>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 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 if the insert statement was step'd, then how is it that it has not been 
>executed?  Is the code more like:

It *has* been executed successfully.  All of the statements are step'd 
regardless of the result of sqlite3_column_count().  The assertion still fails.

...snip... 

>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.


*The insert did run to correct completion*.  There aren't any transactions, 
only a rogue num_col  > 0 post-condition that throws an error.  That's the 
thing I'm trying to get around; I'm trying to get sqlite3_column_count() > 0 
from a SQL statement that effects an insert.

--
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <sqlite-users@mailinglists.sqlite.org>
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Keith Medcalf  Sent: Monday, December 18, 2017 1:07 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Kees Nuyt Sent: Monday, December 18, 2017 12:51 PM

On Sat, 16 Dec 2017 21:43:44 +, "Nelson, Erik - 2" 
 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Kees Nuyt]

On Sat, 16 Dec 2017 21:43:44 +, "Nelson, Erik - 2"
 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Peter Da Silva]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Nomad Sent: Sunday, December 17, 2017 4:11 PM
>On Sat Dec 16, 2017 at 07:53:22PM +, 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Keith Medcalf]

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-
>boun...@mailinglists.sqlite.org] 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-
>>boun...@mailinglists.sqlite.org] 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[nomad]

On Sat Dec 16, 2017 at 07:53:22PM +, 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Simon Slavin wrote on Saturday, December 16, 2017 5:15 PM
>>On 16 Dec 2017, at 9:43pm, Nelson, Erik - 2  
>>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Simon Slavin]

On 16 Dec 2017, at 9:43pm, Nelson, Erik - 2  
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[petern]

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  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-
> >boun...@mailinglists.sqlite.org] 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
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[Nelson, Erik - 2]

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-
>boun...@mailinglists.sqlite.org] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[R Smith]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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-
>boun...@mailinglists.sqlite.org] 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users