Re: [sqlite] Odd question
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
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
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
On Monday, 18 December, 2017 11:35
>Keith Medcalf Sent: Monday, December 18, 2017 1:07 PM
>To: SQLite mailing list
>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
Keith Medcalf Sent: Monday, December 18, 2017 1:07 PM
To: SQLite mailing list
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
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
>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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Odd question
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
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
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
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
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 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
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
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
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
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
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
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
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] Odd question
Nelson, Erik - 2 , on Sat, 16 Dec 2017 11:54:06 , 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? No. Data and schema modification operations return no data from the DB. (Data modification operations return a count of affected rows, but even that is not available as part of a query.) What you probably want is to use a transaction. That's a guess because you have not explained your real problem -- only how you imagine you might solve it. You'll get better help, and viable alternatives, if you explain what you are trying to do. Cheers, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Odd question
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
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] 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
