Re: [sqlite] How do I write this query

[James K. Lowden]

On Mon, 29 Apr 2013 16:14:00 -0500
"Michael Black"  wrote:

> sqlite> CREATE TABLE foo( a integer, b integer);
> sqlite> INSERT INTO foo VALUES(1,1);
> sqlite> INSERT INTO foo VALUES(1,2);
> sqlite> INSERT INTO foo VALUES(1,3);

With 3.7.13 at least, 

insert into foo values (1,1),(1,2),(1,3);

works. 

HTH.

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

Re: [sqlite] How to write this code?

[Igor Korot]

Simon,

On Mon, Apr 29, 2013 at 2:10 PM, Simon Slavin  wrote:

>
> On 29 Apr 2013, at 8:56pm, Igor Korot  wrote:
>
> > Yes, I read this link. But I don't understand how it applies here.
> > Can you make some pseudo code, please?
>
> I'm sorry, but if you're using a class someone else wrote you may not be
> able to use sqlite3_reset().  It depends on what the class assumes about
> _step().
>

No, this is my class.


>
> The idea is that instead of
>
> _prepare(statement1)
> _step(statement1), _step(statement1) ...
> _finalize(statement1)
>
> _prepare(statement2)
> _step(statement2), _step(statement2) ...
> _finalize(statement2)
>
> you can do this
>
> _prepare(statement1)
> _step(statement1), _step(statement1) ...
> _reset(statement1)
> _bind(statement1)
> _step(statement1), _step(statement1) ...
> _finalize(statement1)
>

I think you are missing "statement2" somewhere here...

Now, statement1 is an update and statement2 is insert. It shouldn't change
anything, though.

Thank you.


>
> As you correctly noted, it's the _prepare() step that takes a lot of time.
>  This second form conforms to your original request
>
> > Problem is sqlite3_prepare_v2() wastes time by compiling SQL statements
> and
> > I'd like to avoid it by making this call only once and just
> > bind the appropriate parameters on each loop iteration.
>
>  Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I write this query

[Igor Korot]

Thx.

On Mon, Apr 29, 2013 at 2:14 PM, Michael Black  wrote:

> sqlite> CREATE TABLE foo( a integer, b integer);
> sqlite> INSERT INTO foo VALUES(1,1);
> sqlite> INSERT INTO foo VALUES(1,2);
> sqlite> INSERT INTO foo VALUES(1,3);
> sqlite> INSERT INTO foo VALUES(2,1);
> sqlite> INSERT INTO foo VALUES(2,2);
> sqlite> INSERT INTO foo VALUES(2,3);
> sqlite> CREATE TABLE bar( a integer, b integer, c integer);
> sqlite> INSERT INTO bar SELECT a,b,1 FROM foo;
> sqlite> select * from bar;
> 1|1|1
> 1|2|1
> 1|3|1
> 2|1|1
> 2|2|1
> 2|3|1
>
> Mike
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Korot
> Sent: Monday, April 29, 2013 4:06 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] How do I write this query
>
> Hi, ALL,
>
> CREATE TABLE foo( a integer, b integer);
> INSERT INTO foo VALUES( 1,1);
> INSERT INTO foo VALUES( 1,2);
> INSERT INTO foo VALUES( 1,3);
> INSERT INTO foo VALUES( 2,1);
> INSERT INTO foo VALUES( 2,2);
> INSERT INTO foo VALUES( 2,3);
>
> CREATE TABLE bar( a integer, b integer, c integer);
>
> INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
> INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails
>
> What is the correct syntax?
>
> Basically I need bar to have records from foo with field c to be 1, so:
>
> SELECT * FROM bar;
> 1 1 1
> 1 2 1
> 1 3 1
> 2 1 1
> 2 2 1
> 2 3 1
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I write this query

[Michael Black]

sqlite> CREATE TABLE foo( a integer, b integer);
sqlite> INSERT INTO foo VALUES(1,1);
sqlite> INSERT INTO foo VALUES(1,2);
sqlite> INSERT INTO foo VALUES(1,3);
sqlite> INSERT INTO foo VALUES(2,1);
sqlite> INSERT INTO foo VALUES(2,2);
sqlite> INSERT INTO foo VALUES(2,3);
sqlite> CREATE TABLE bar( a integer, b integer, c integer);
sqlite> INSERT INTO bar SELECT a,b,1 FROM foo;
sqlite> select * from bar;
1|1|1
1|2|1
1|3|1
2|1|1
2|2|1
2|3|1

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Korot
Sent: Monday, April 29, 2013 4:06 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How do I write this query

Hi, ALL,

CREATE TABLE foo( a integer, b integer);
INSERT INTO foo VALUES( 1,1);
INSERT INTO foo VALUES( 1,2);
INSERT INTO foo VALUES( 1,3);
INSERT INTO foo VALUES( 2,1);
INSERT INTO foo VALUES( 2,2);
INSERT INTO foo VALUES( 2,3);

CREATE TABLE bar( a integer, b integer, c integer);

INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails

What is the correct syntax?

Basically I need bar to have records from foo with field c to be 1, so:

SELECT * FROM bar;
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1

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

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

Re: [sqlite] How to write this code?

[Simon Slavin]

On 29 Apr 2013, at 8:56pm, Igor Korot  wrote:

> Yes, I read this link. But I don't understand how it applies here.
> Can you make some pseudo code, please?

I'm sorry, but if you're using a class someone else wrote you may not be able 
to use sqlite3_reset().  It depends on what the class assumes about _step().

The idea is that instead of

_prepare(statement1)
_step(statement1), _step(statement1) ...
_finalize(statement1)

_prepare(statement2)
_step(statement2), _step(statement2) ...
_finalize(statement2)

you can do this

_prepare(statement1)
_step(statement1), _step(statement1) ...
_reset(statement1)
_bind(statement1)
_step(statement1), _step(statement1) ...
_finalize(statement1)

As you correctly noted, it's the _prepare() step that takes a lot of time.  
This second form conforms to your original request

> Problem is sqlite3_prepare_v2() wastes time by compiling SQL statements and
> I'd like to avoid it by making this call only once and just
> bind the appropriate parameters on each loop iteration.

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

Re: [sqlite] How do I write this query

[markus diersbock]

Why are basic SQL questions being asked in the SQLite forum?



On Mon, Apr 29, 2013 at 5:06 PM, Igor Korot  wrote:
> Hi, ALL,
>
> CREATE TABLE foo( a integer, b integer);
> INSERT INTO foo VALUES( 1,1);
> INSERT INTO foo VALUES( 1,2);
> INSERT INTO foo VALUES( 1,3);
> INSERT INTO foo VALUES( 2,1);
> INSERT INTO foo VALUES( 2,2);
> INSERT INTO foo VALUES( 2,3);
>
> CREATE TABLE bar( a integer, b integer, c integer);
>
> INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
> INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails
>
> What is the correct syntax?
>
> Basically I need bar to have records from foo with field c to be 1, so:
>
> SELECT * FROM bar;
> 1 1 1
> 1 2 1
> 1 3 1
> 2 1 1
> 2 2 1
> 2 3 1
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 

Markus Diersbock
SwingNote
Marblehead, MA

C 781.929.0693
F 888.654.6068

This e-mail is intended solely for the person or entity to which it is
addressed and may contain confidential information. If you have
received this e-mail in error, please contact the sender immediately
and delete the material from any computer. Any review, dissemination,
copying, printing or other use of this e-mail by persons or entities
other than the addressee is prohibited.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I write this query

[Igor Korot]

Hi, ALL,

CREATE TABLE foo( a integer, b integer);
INSERT INTO foo VALUES( 1,1);
INSERT INTO foo VALUES( 1,2);
INSERT INTO foo VALUES( 1,3);
INSERT INTO foo VALUES( 2,1);
INSERT INTO foo VALUES( 2,2);
INSERT INTO foo VALUES( 2,3);

CREATE TABLE bar( a integer, b integer, c integer);

INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails

What is the correct syntax?

Basically I need bar to have records from foo with field c to be 1, so:

SELECT * FROM bar;
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1

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

Re: [sqlite] How to write this code?

[Igor Korot]

Simon,

On Mon, Apr 29, 2013 at 12:18 PM, Simon Slavin  wrote:

>
> On 29 Apr 2013, at 7:55pm, Igor Korot  wrote:
>
> > Problem is sqlite3_prepare_v2() wastes time by compiling SQL statements
> and
> > I'd like to avoid it by making this call only once and just
> > bind the appropriate parameters on each loop iteration.
> >
> > Would it be possible if I make local function variable "static
> sqlite3_stmt
> > *stmt;" instead of class member variable?
> > Problem is in this case I will need 2 of them for each query, right?
>
> Just define and prepare the statement outside of your loop.


I wish. ;-)
This class (CDb) is nicely encapsulating the database communication. All
it's doing is inserting/retrieving data from the db.
It does not know anything about outside world.
Now on the outside I have a GUI application, which on exit ask the user if
(s)he wanst to save data. If the answer is yes, then I start transaction
and in the loop saving all changes.

I would very much like to keep those 2 pieces independent - GUI and DB
there are.



>  You can reuse a prepared statement by resetting it.  After you've reset
> it you can leave the existing bindings alone or rebind any variables to new
> values.  See section 3.0 of
>
> 
>
> You can have any number of statements defined and prepared at once.
>

Yes, I read this link. But I don't understand how it applies here.
Can you make some pseudo code, please?


>
> By the way, there is no need to ROLLBACK a statement that fails.  A
> statement that fails, fails.  It just does nothing.  You might be rolling
> back other statements in the same transaction but that doesn't seem to be
> in the code you showed.
>

Yes, I'm rolling back some previous statements.

Thank you.


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

Re: [sqlite] How to write this code?

[Simon Slavin]

On 29 Apr 2013, at 7:55pm, Igor Korot  wrote:

> Problem is sqlite3_prepare_v2() wastes time by compiling SQL statements and
> I'd like to avoid it by making this call only once and just
> bind the appropriate parameters on each loop iteration.
> 
> Would it be possible if I make local function variable "static sqlite3_stmt
> *stmt;" instead of class member variable?
> Problem is in this case I will need 2 of them for each query, right?

Just define and prepare the statement outside of your loop.  You can reuse a 
prepared statement by resetting it.  After you've reset it you can leave the 
existing bindings alone or rebind any variables to new values.  See section 3.0 
of



You can have any number of statements defined and prepared at once.

By the way, there is no need to ROLLBACK a statement that fails.  A statement 
that fails, fails.  It just does nothing.  You might be rolling back other 
statements in the same transaction but that doesn't seem to be in the code you 
showed.

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

[sqlite] How to write this code?

[Igor Korot]

Hi, ALL,
I need to make a loop and in the loop I need to do following:

void CDb::Bar()
{
 int result;
 if( ( result = sqlite3_prepare_v2( m_handle, query1, -1, , 0 ) ==
SQLITE_OK )
  sqlite3_step( stmt );
else
{
  // error message
 sqlite3_exec( m_handle, "ROLLBACK"... );
  return;
}
if( result == SQLITE_OK )
{
  if( ( result = sqlite3_prepare_v2( m_handle, query2, -1, , 0
) == SQLITE_OK )
  sqlite3_step( stmt );
  else
  {
   // error message
   sqlite3_exec( m_handle, "ROLLBACK", );
   return;
  }
}
}

Problem is sqlite3_prepare_v2() wastes time by compiling SQL statements and
I'd like to avoid it by making this call only once and just
bind the appropriate parameters on each loop iteration.

Would it be possible if I make local function variable "static sqlite3_stmt
*stmt;" instead of class member variable?
Problem is in this case I will need 2 of them for each query, right?

Or is there a better way to improve this code?

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

[sqlite] sqlite_interrupt() does not cancel ATTACHing of blocked database

[Григорий Григоренко]

 Hi,

seems like sqlite_interrupt() does not cancel ATTACHing of blocked database. 

Steps to reproduce problem:
 1. open 'foo.db' in shell,  run 'BEGIN EXCLUSIVE';
 2. switch to custom application and open ":memory:" database in it; set busy 
timeout to 15 seconds and run "ATTACH 'foo.db' as db" in thread A;
 3. wait 2-3 seconds and call sqlite_interrupt() in 
thread B;
 4. nothing happens: application still waits 15 seconds and receives 
SQLITE_BUSY error.

SQLite should immediately stop waiting for database (upon sqlite_interrupt() 
call) and return with SQLITE_INTERRUPT error, perhaps?

Platform: Windows 7 x64, SQLite 3.7.16.2

-- 
Григорий Григоренко
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in resolving aliases with parentheses in join

[Lucas Clemente]

Ah, I overlooked that and just assumed it was correct since sqlite accepted it. 
Thanks for your help!

The reason we need this is that we're procedurally generating SQL queries, and 
it's easier to insert parentheses everywhere than just in the (very) few join 
clauses where they are actually needed.

Thanks again!
Luke


On Monday, April 29, 2013 at 00:08, James K. Lowden wrote:

> On Sun, 28 Apr 2013 12:23:42 +0200
> Lucas Clemente  
> wrote:
> 
> > SELECT * FROM (t1 AS a) JOIN (t2 AS b) USING(k) WHERE a.k = 1;
> ...
> > Looks like this is due to some problem with resolving the alias in
> > the parantheses. Now, as far as I understand SQL this should just
> > work since the () don't introduce a new scope.
> > 
> 
> 
> http://www.sqlite.org/lang_select.html
> http://www.sqlite.org/syntaxdiagrams.html#single-source
> 
> As far as I can tell, the syntax you're using is invalid. If it's a
> bug, it's not a failure to interpret your SQL correctly, but a failure
> to report the error. 
> 
> The "single-source" section of the selection statement allows
> parentheses around a join-source or around a single-source. In either
> case, the closing parenthesis must appear before the AS keyword. 
> 
> May I ask why you're using these parentheses in the first place?
> I have never felt the need to use parentheses in SQL except to control
> arithmetic or boolean precedence. 
> 
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 


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

Re: [sqlite] SQLite.NET.chm pages are empty

[Mathieu Schroeter]

Le 29.04.2013 11:35, Joe Mistachkin a écrit :

Mathieu Schroeter wrote:

I downloaded the SQLite.NET.chm from the trunk in order to access the .NET
documentation but the pages are empty.


Actually, this is the result of a Windows security feature that disables
downloaded
content.  To enable it, navigate to the file in Windows Explorer,
right-click the
file, go to "Properties", and then click the "Unblock" button.



Good to know, thank you


Regards,

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

Re: [sqlite] SQLite.NET.chm pages are empty

[Joe Mistachkin]

Mathieu Schroeter wrote:
> 
> I downloaded the SQLite.NET.chm from the trunk in order to access the .NET
> documentation but the pages are empty.
> 

Actually, this is the result of a Windows security feature that disables
downloaded
content.  To enable it, navigate to the file in Windows Explorer,
right-click the
file, go to "Properties", and then click the "Unblock" button.

--
Joe Mistachkin

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

[sqlite] SQLite.NET.chm pages are empty

[Mathieu Schroeter]

Hi,

I downloaded the SQLite.NET.chmfrom the trunk [1]in order to access the
.NET documentation but the pages are empty.


Regards,

Mathieu Schroeter


[1]: 
http://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimetype=application/x-chm


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