Re: [sqlite] Are parenthesis really needed?
Hi, On 8/25/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > I assume you're referring to this: > > http://marc.info/?l=sqlite-users&m=118737502703454&w=2 > Yep, that's it. > In that specific case, the parens are not needed. But that's not always > the case. In general, LEFT OUTER JOIN is not commutative, nor is it > associative. You cannot blindy strip the parens or reorder the outer joins > out of context. > hmmm I see. Thanks for you attention, Joe. Regards, Bruno -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação - UFLA * Mestrando em Inteligência Computacional - UFPR * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
[sqlite] Are parenthesis really needed?
Hi there, Is there any sort of expression inside a query that needs to be surrounded by parenthesis? My program will receive queries built to run in MySQL, Oracle and Postgrees. The problem, as posted before, is that SQLite has problems with parenthesis around JOINs... So, what I want to do is to remove every possible parenthesis present in the incoming query. So that my problem is resolved fast. Can I do that safely? Thanks in advance. Regards -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação - UFLA * Mestrando em Inteligência Computacional - UFPR * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] Columns from nested joins aren't properly propagated
=/ Well... that was just to confirm that there's no way to make those queries work... As we say, hope is the last to die... Thanks a lot for your attention. Bruno On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > Given that this issue has existed for years, and many simple workarounds > are known, I doubt it will change any time soon. If you want to use > SQLite, you have to rewrite the queries. The benefit of changing the > generated SQL is that it will work on all the databases you mentioned. > > The sqlite source code is available. You can modify it as see you fit. > > --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > > As I told you, I know how to avoid this. But I can't rewrite these > > queries. That's my problem... > > > > Thanks for your attention. > > > > Regards, > > Bruno > > > > On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > > SELECT t_dados.id > > > FROM Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id > > > ORDER BY t_dados.id, Points1.geom_id; > > > > > > --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > > > > I'm having problems with the following query (and, in general, in > > > > queries using left joins): > > > > > > > > [EMAIL PROTECTED]:~$ sqlite3 parana > > > > SQLite version 3.4.1 > > > > Enter ".help" for instructions > > > > sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON > > > > Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY > > > > t_dados.id, Points1.geom_id; > > > > SQL error: no such column: t_dados.id > > > > > > Choose the right car based on your needs. Check out Yahoo! Autos new Car > Finder tool. > http://autos.yahoo.com/carfinder/ > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] Columns from nested joins aren't properly propagated
Hi, As I told you, I know how to avoid this. But I can't rewrite these queries. That's my problem... Thanks for your attention. Regards, Bruno On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > SELECT t_dados.id > FROM Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id > ORDER BY t_dados.id, Points1.geom_id; > > --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > > I'm having problems with the following query (and, in general, in > > queries using left joins): > > > > [EMAIL PROTECTED]:~$ sqlite3 parana > > SQLite version 3.4.1 > > Enter ".help" for instructions > > sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON > > Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY > > t_dados.id, Points1.geom_id; > > SQL error: no such column: t_dados.id > > > > > > Looking for a deal? Find great prices on flights and hotels with Yahoo! > FareChase. > http://farechase.yahoo.com/ > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
[sqlite] Columns from nested joins aren't properly propagated
Hi all, I'm having problems with the following query (and, in general, in queries using left joins): [EMAIL PROTECTED]:~$ sqlite3 parana SQLite version 3.4.1 Enter ".help" for instructions sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY t_dados.id, Points1.geom_id; SQL error: no such column: t_dados.id sqlite> .q Apparently, as I've discovered in the SQLite's "Known Issues with descriptions" page ( http://www.sqlite.org/cvstrac/rptview?rn=15 , mainly issue #1994 ) and in related posts in the mailing lists (such as http://marc.10east.com/?t=11537869901 ), this is related to the columns from nested joins which aren't propagated correctly and is a low-priority bug. In order to avoid this problem, one can simply remove the parenthesis or set proper aliases. That's fine. The issue is: the above and similar queries are generated automatically, the same code (query) is used in MySQL, Postgrees and Oracle AND, as it is not my code that generates these expressions, I cannot modify the generating code of these queries. Does anyone has an idea of how can I solve this? Will this issue be corrected in a near future SQLite update? Any hint will be pretty much appreciated. Thanks in advance. Best regards, Bruno -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
[sqlite] sqlite3_step
Hi all, I'm having problems with the sqlite3_step function. I have a table with only one record and then I execute the sqlite3_step twice (with some processing in between). In the first run of the function everything goes fine, but, in the second one, it returns SQLITE_ROW even with only one record in the table and with no modifications in the SQL statement. Is this the correct behaviour of that function? Does anyone knows why this happens? I'm using the sqlite3_prepare before executing the sqlite3_step. Thanks in advance. Regards, Bruno -- /** * Bruno Silva de Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] auto_increment and not null
Thanks Andrew and everyone. I'll see what I can get with it. Thanks for your attention, Bruno On 6/28/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 6/28/07, Bruno S. Oliveira <[EMAIL PROTECTED]> wrote: > > Hi all, > > Now I see the what's happening. > But what if I need to create a table with two primary keys and one > auto incremented (and I DO need that!)? Isn't this possible? > If by primary key you mean alternate (unique) key, then yes. create table andy_loves_sqlite ( pk integer not null primary key autoincrement, ak integer not null unique ); -- /** * Bruno Silva de Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] auto_increment and not null
Hi all, Now I see the what's happening. But what if I need to create a table with two primary keys and one auto incremented (and I DO need that!)? Isn't this possible? Thanks a lot Trevor and all. Thanks for the attention. Best regards, Bruno On 6/28/07, Trevor Talbot <[EMAIL PROTECTED]> wrote: > To add the guarantee that automatically generated values will never be > reused, your only option is: > > create table foo(id integer primary key autoincrement); Got involved testing this and forgot what I originally went to look for. You can add "not null" on the end of that, if you want. It's already implicitly not null, but it's still valid syntax and will work as intended. "integer primary key autoincrement" is the magical phrase that must not be broken up. - To unsubscribe, send email to [EMAIL PROTECTED] - -- /** * Bruno Silva de Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] auto_increment and not null
Hi,
On 6/28/07, Trevor Talbot <[EMAIL PROTECTED]> wrote:
"integer primary key" is special, and the only column "autoincrement"
can be applied to. It's not a general-purpose sequence option, like
some other database engines.
Yep, I knew that.
The problem is that, with a table created like this:
sqlite> CREATE TABLE t1 (a INTEGER AUTO INCREMENT NOT NULL, b TEXT,
PRIMARY KEY (a));
An inserting like this:
sqlite> INSERT INTO t1 (b) VALUES ('bruno');
Doesn't work. The error is:
SQL error: t1.a may not be NULL
See http://sqlite.org/autoinc.html
There's nothing about insertions in this link...
=(
Thanks!
Regards.
Bruno
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
/**
* Bruno Silva de Oliveira
* Bacharel em Ciência da Computação
* Mestrando em Inteligência Computacional
* http://www.inf.ufpr.br/brunoso/
*
* http://www.last.fm/user/bsoliveira/
*/
Re: [sqlite] auto_increment and not null
Hi Griggs and everyone, Nop, the problem isn't the underscore. The sqlite works with both keywords: AUTOINCREMENT and AUTO_INCREMENT. Thanks for the attention! Regards, Bruno On 6/28/07, Griggs, Donald <[EMAIL PROTECTED]> wrote: Regarding: "I'm aware of the create table syntax. And according to it I can declare a column as INTEGER AUTO_INCREMENT NOT NULL. So I wonder why this doesn't work (in MySQL it does!)." To underscore -- the difficulty: Maybe it was just a typing mistake in your message, but according to http://sqlite.org/lang_createtable.html The keyword is AUTOINCREMENT and not AUTO_INCREMENT - To unsubscribe, send email to [EMAIL PROTECTED] - -- /** * Bruno Silva de Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] auto_increment and not null
Hi Eugene and all,
On 6/28/07, Eugene Wee <[EMAIL PROTECTED]> wrote:
Why not declare repres_id as INTEGER PRIMARY KEY? You can refer to the
documentation on CREATE TABLE:
Well, I am declaring it as INTEGER PRIMARY KEY. Or do you mean without
the NOT NULL and AUTO_INCREMENT?
I'm aware of the create table syntax. And according to it I can
declare a column as INTEGER AUTO_INCREMENT NOT NULL. So I wonder why
this doesn't work (in MySQL it does!).
I just tried some more examples and discovered that the sqlite
complains only when one declares a column as auto_increment:
create table t1 (a integer not null, b text, primary key(a));
insert into t1 (b) values('bruno');
It works!
create table t2 (a integer auto_increment not null, b text, primary key(a));
insert into t2 (b) values('bruno');
Doesn't work!
Anyway, my program (which creates tables and generates queries like
these) must specify, for several tables, which columns must be auto
incremented. But, if you say to me that every primary key column
behaves as an auto_increment even if not declared so, my problems are
almost solved (I hope so!).
Thanks for the attention, Eugene.
Best regards,
Bruno
http://www.sqlite.org/lang_createtable.html
Regards,
Eugene Wee
Bruno S. Oliveira wrote:
> Hi all,
>
> I'm having some trouble in inserting data into a table which its
> primary key is set as auto_increment and not null.
> The table was created as follows:
>
> CREATE TABLE te_representation (repres_id INTEGER AUTO_INCREMENT
> NOT NULL ,layer_id INTEGER ,geom_type INTEGER ,geom_table TEXT
> ,description TEXT ,lower_x REAL ,lower_y REAL ,upper_x REAL
> ,upper_y REAL ,res_x REAL ,res_y REAL , num_cols INTEGER ,num_rows
> INTEGER ,initial_time TEXT ,final_time TEXT , PRIMARY KEY
> (repres_id) );
> CREATE INDEX te_idx_representation ON te_representation(layer_id);
>
> And I'm executing this query:
>
> INSERT INTO te_representation (layer_id, geom_type, geom_table,
> description,lower_x, lower_y, upper_x, upper_y, res_x, res_y,
> num_cols, num_rows) VALUES( 1, 4, 'Points1', '',3.40e+37,
> 3.40e+37, -3.40e+37, -3.40e+37, 0.00e+00,
> 0.00e+00, 0, 0);
>
> The sqlite3 complains for the repres_id may not be NULL. I know that
> it will work if I remove the repres_id's not null constraint, but this
> I wouldn't like to do.
>
> Does the sqlite auto_increment work only with primary keys WITHOUT the
> not null constraint? The same query on the same table worked on MySQL.
>
> I would appreciate any idea to work through this.
>
> Thanks in advance.
>
> Best regards.
>
> Bruno
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
/**
* Bruno Silva de Oliveira
* Bacharel em Ciência da Computação
* Mestrando em Inteligência Computacional
* http://www.inf.ufpr.br/brunoso/
*
* http://www.last.fm/user/bsoliveira/
*/
[sqlite] auto_increment and not null
Hi all, I'm having some trouble in inserting data into a table which its primary key is set as auto_increment and not null. The table was created as follows: CREATE TABLE te_representation (repres_id INTEGER AUTO_INCREMENT NOT NULL ,layer_id INTEGER ,geom_type INTEGER ,geom_table TEXT ,description TEXT ,lower_x REAL ,lower_y REAL ,upper_x REAL ,upper_y REAL ,res_x REAL ,res_y REAL , num_cols INTEGER ,num_rows INTEGER ,initial_time TEXT ,final_time TEXT , PRIMARY KEY (repres_id) ); CREATE INDEX te_idx_representation ON te_representation(layer_id); And I'm executing this query: INSERT INTO te_representation (layer_id, geom_type, geom_table, description,lower_x, lower_y, upper_x, upper_y, res_x, res_y, num_cols, num_rows) VALUES( 1, 4, 'Points1', '',3.40e+37, 3.40e+37, -3.40e+37, -3.40e+37, 0.00e+00, 0.00e+00, 0, 0); The sqlite3 complains for the repres_id may not be NULL. I know that it will work if I remove the repres_id's not null constraint, but this I wouldn't like to do. Does the sqlite auto_increment work only with primary keys WITHOUT the not null constraint? The same query on the same table worked on MySQL. I would appreciate any idea to work through this. Thanks in advance. Best regards. Bruno -- /** * Bruno Silva de Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
