Re: [sqlite] Problems compiling php 5.0.3 with sqlite3

[chorlya]

On Wed, 26 Jan 2005 00:21:52 +0100, José Miguel López Coronado
<[EMAIL PROTECTED]> wrote:
> Hello everybody.
> I'm trying to compile php 5.0.3 using the
> --with-sqlite=/usr/local/sqlite/lib option among some others and I
> obtain the following error code:
> checking whether to enable UTF-8 support in sqlite (default:
> ISO-8859-1)... no
> checking for sqlite support... yes
> checking for sqlite_open in -lsqlite... no
> configure: error: wrong sqlite lib version or lib not found
> 
> Any idea why is this problem arising?

Because PHP doesn't support SQLite3 yet.
This is PHP related so you should post on some of the PHP lists to get
more info on PHP-SQLite3 plans.

--
chorlya

Re: [sqlite] SQLite 3.1.0 Column Names

[Kurt Welgehausen]

Steve,

Well, you forced me to download v3.1 (;-). I confirmed your
results in the SQLite shell; I think it's a bug. Also, there
are some typos in the docs: the section on short_column-names
is labeled full_column_names, and there are words missing
from both sections. I think probably someone should write a
bug ticket.

sqlite> pragma short_column_names = 0 ;
sqlite> pragma full_column_names = 0 ;
sqlite> SELECT A.*, B.* FROM A, B WHERE A.a_col = B.a_col ;
a_col   b_col   a_col 
--  --  --
a3  b1  a3
sqlite> pragma full_column_names = 1 ;
sqlite> SELECT A.*, B.* FROM A, B WHERE A.a_col = B.a_col ;
a_col   b_col   a_col 
--  --  --
a3  b1  a3
sqlite> pragma short_column_names = 1 ;
sqlite> pragma full_column_names = 0 ;
sqlite> SELECT A.*, B.* FROM A, B WHERE A.a_col = B.a_col ;
a_col   b_col   a_col 
--  --  --
a3  b1  a3
sqlite> pragma full_column_names = 1 ;
sqlite> SELECT A.*, B.* FROM A, B WHERE A.a_col = B.a_col ;
a_col   b_col   a_col 
--  --  --
a3  b1  a3

Regards

Re: [sqlite] SQLite 3.1.0 Group By problem

[Derrell . Lipman]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:

> Hi All,
>
> First, Thank you for excellent piece of software!
> I am finding following problem would like to share:
> ---
> SELECT a FROM [table] GROUP BY a;
> Error Message in Sqlite 3.1.0 alpha: "SQL error: GROUP BY may only be used on
> aggregate queries"

Although the query you are issuing used to be allowed, it probably
(technically) shouldn't have been since GROUP BY is primarily intended for
applying aggregate functions.  The query that you likely intend by this is,
and that should give the same result as you used to get, is:

  SELECT DISTINCT a FROM [table];

Here's an example, using an older version that allowed the GROUP BY, showing
the same result set with this DISTINCT query:

% sqlite :memory:
SQLite version 2.8.15
Enter ".help" for instructions
sqlite> create table [table] (a integer, b integer);
sqlite> insert into [table] values (23, 42);
sqlite> insert into [table] values (2, 3);
sqlite> insert into [table] values (2, 5);
sqlite> insert into [table] values (23, 56);
sqlite> insert into [table] values (42, 23);
sqlite> select a from [table] group by a order by a;
a = 2

a = 23

a = 42
sqlite> select distinct a from [table] order by a;
a = 2

a = 23

a = 42
sqlite> 

Derrell

Re: [sqlite] SQLite 3.1.0 Group By problem

[Kurt Welgehausen]

> SELECT a FROM [table] GROUP BY a;
> Error Message in Sqlite 3.1.0 alpha: "SQL error: GROUP BY may only be
> used on aggregate queries"
> ... Hope this can be fixed.

The behavior in 3.1 is correct. It makes no sense to write
'select c from t group by c'. If you want to get distinct
values of c, write 'select distinct c ... '.

Regards

[sqlite] SQLite 3.1.0 Group By problem

[[EMAIL PROTECTED]]

Hi All,
First, Thank you for excellent piece of software!
I am finding following problem would like to share:
---
SELECT a FROM [table] GROUP BY a;
Error Message in Sqlite 3.1.0 alpha: "SQL error: GROUP BY may only be 
used on aggregate queries"
---

I test this sql in Sqlite 3.0.8, it runs fine. Hope this can be fixed.
Thanks,
Ming

Re: [sqlite] How to create and uses a stored procedure for Sqlite

[Steve Frierdich]

Dennis
Do you have an example on how to do this?
Steve
Dennis Cote wrote:
Steve Frierdich wrote:
Anyone know of any links or have example on how to create a stored
procedure and how to use that stored procedure in code on a sqlite
database? Thanks
Steve

As someone pointed out earlier, SQLite does not support stored 
procedures.

You can store SQL scripts in text files, or in a table in the 
database. Your application can then retrieve the scripts and execute 
them whenever necessary. Its not the same as a stored procedure, but 
it is often all that is needed.

HTH
Dennis Cote

[sqlite] Problems compiling php 5.0.3 with sqlite3

[José Miguel López Coronado]

Hello everybody.
I'm trying to compile php 5.0.3 using the 
--with-sqlite=/usr/local/sqlite/lib option among some others and I 
obtain the following error code:
checking whether to enable UTF-8 support in sqlite (default: 
ISO-8859-1)... no
checking for sqlite support... yes
checking for sqlite_open in -lsqlite... no
configure: error: wrong sqlite lib version or lib not found

Any idea why is this problem arising?
Thanks in advance.
--
JM Lopez-Coronado.

Re: [sqlite] one SQL statement that will insert a new row in a table if a certain string is not found in a column?

[D. Richard Hipp]

On Tue, 2005-01-25 at 09:05 -0700, Dennis Cote wrote:
> [REPLACE] works as long as the column containing the string you want to 
> search 
> for is the primary key of the table (like user above). If the primary key 
> doesn't exist, it does an insert. If it does, it replaces that row with the 
> new values.
> 

A technical point:  REPLACE also works if there is a UNIQUE index
or a UNIQUE constraint on the column containing the string you are
inserting.

Re: [sqlite] How to create and uses a stored procedure for Sqlite

[Dennis Cote]

Steve Frierdich wrote:
Anyone know of any links or have example on how to create a stored
procedure and how to use that stored procedure in code on a sqlite
database? Thanks
Steve
As someone pointed out earlier, SQLite does not support stored procedures.
You can store SQL scripts in text files, or in a table in the database. Your 
application can then retrieve the scripts and execute them whenever 
necessary. Its not the same as a stored procedure, but it is often all that 
is needed.

HTH
Dennis Cote 

Re: [sqlite] Making a column a primary key using SqliteExplorer?

[Dennis Cote]

Steve Frierdich wrote:
Is there any way to use the SqliteExplorer to make a column a primary
key as can be done in SQL Server 2000,  Oracle , or Access interfaces?
Thank
Steve
Not directly.
Copy your existing table into a temp table, drop the existing table, create 
a new table with the same name as the existing table but declare the 
appropriate column as the primary key, finally copy the data back from the 
temp table into the new empty table.

HTH
Dennis Cote 

[sqlite] How to create and uses a stored procedure for Sqlite

[Steve Frierdich]

Anyone know of any links or have example on how to create a stored 
procedure and how to use that stored procedure in code on a sqlite database?
Thanks
Steve

 

[sqlite] Making a column a primary key using SqliteExplorer?

[Steve Frierdich]

Is there any way to use the SqliteExplorer to make a column a primary 
key as can be done in SQL Server 2000,  Oracle , or Access interfaces?
Thank
Steve

[sqlite] INSERT OR REPLACE INTO or REPLACE INTO does work

[Steve Frierdich]

I was incorrect  REPLACE INTO is working as stated in  the sqlite reference.
Sorry about that.
Steve
Steve Frierdich wrote:
I tried using  REPLACE INTO in an sql statement that was suppose to 
insert a user into a table if the user did not already in the table 
and  it did not work. The error was SQL error?  In the sqlite document 
it states
that REPLACE INTO  is an alias for  INSERT OR REPLACE. Any one know 
why this failed?

   REPLACE
sql-statement ::= REPLACE INTO [database-name .] table-name [( 
column-list )] VALUES ( value-list ) |
REPLACE INTO [database-name .] table-name [( column-list )] 
select-statement

I also tried using INSERT OR REPLACE INTO in an sql statement that was 
suppose to insert a user into a table if the user did not already in 
the table and it did not work. The error again was SQL error?  If I 
just use INSERT in the sql statement it work, user is inserted into 
the table. Does know why INSERT OR REPLACE INTO does not work?

Steve
Cory Nelson wrote:
Try the UNIQUE keyword when creating your table..
On Tue, 25 Jan 2005 09:34:53 -0500, Steve Frierdich
<[EMAIL PROTECTED]> wrote:
 

Does anyone have any code they can send me that shows how to check 
for a
string in a column in a table, and if the string is not there on how to
insert the string in the column in the table in a new row. Then finally
on how to update the table?

And if possible on how to maybe write a trigger for the above operation
and use the trigger in a Visaul C++ or a Window's 32 program?
Thanks
Steve
727 455 4668
727-547-9799 ext 1766
  

 

Re: [sqlite] embedded TCL and sqlite in one app

[Randall Fox]

FYI: for those interested..


>> Re: Schema change error when using sqlite
>> with TCL as a macro language inside an
>> application that already has an open db


I took a look at the code (tclsqlite.c) and saw I could easily change
it to support passing in a pointer to an existing database connection
(db already open).

I didn't see anything on your website on how to submit changes.  So,
here is the file I changed.  Diff it against 3.0.8.  The changes were
minimal, and in preliminary testing seems to work just fine.

The TCL command line is still the same with one minor difference:

sqlite3 db1 0x -use-existing-connection

if the -use-existing-connection is present as above, it will assume
0x is a pointer to an already opened sqlite3 db.  Otherwise
the old behavior of opening the supplied filename is used.

No testing is done on the open db to see if it is healthy.  I assume
the user is smart enough to know.

If you feel it is worthy, use the changes in your next release.   I
would prefer if you did, since I won't have to maintain the code
myself..  :)


Randall Fox

[sqlite] INSERT OR REPLACE INTO or REPLACE INTO does not work

[Steve Frierdich]

I tried using  REPLACE INTO in an sql statement that was suppose to 
insert a user into a table if the user did not already in the table and  
it did not work. The error was SQL error?  In the sqlite document it states
that REPLACE INTO  is an alias for  INSERT OR REPLACE. Any one know why 
this failed?

   REPLACE
sql-statement ::= 	REPLACE INTO [database-name .] table-name [( 
column-list )] VALUES ( value-list ) |
REPLACE INTO [database-name .] table-name [( column-list )] 
select-statement

I also tried using INSERT OR REPLACE INTO in an sql statement that was 
suppose to insert a user into a table if the user did not already in the 
table and it did not work. The error again was SQL error?  If I just use 
INSERT in the sql statement it work, user is inserted into the table. 
Does know why INSERT OR REPLACE INTO does not work?

Steve
Cory Nelson wrote:
Try the UNIQUE keyword when creating your table..
On Tue, 25 Jan 2005 09:34:53 -0500, Steve Frierdich
<[EMAIL PROTECTED]> wrote:
 

Does anyone have any code they can send me that shows how to check for a
string in a column in a table, and if the string is not there on how to
insert the string in the column in the table in a new row. Then finally
on how to update the table?
And if possible on how to maybe write a trigger for the above operation
and use the trigger in a Visaul C++ or a Window's 32 program?
Thanks
Steve
727 455 4668
727-547-9799 ext 1766
   


 

Re: [sqlite] CHECKING fOR A STRING IN A COLUMN IN A TABLE AND IF STRING IS NOT IN THE COLUMN ADD IT

[Cory Nelson]

Try the UNIQUE keyword when creating your table..


On Tue, 25 Jan 2005 09:34:53 -0500, Steve Frierdich
<[EMAIL PROTECTED]> wrote:
> Does anyone have any code they can send me that shows how to check for a
> string in a column in a table, and if the string is not there on how to
> insert the string in the column in the table in a new row. Then finally
> on how to update the table?
> 
> And if possible on how to maybe write a trigger for the above operation
> and use the trigger in a Visaul C++ or a Window's 32 program?
> 
> Thanks
> 
> Steve
> 727 455 4668
> 727-547-9799 ext 1766
> 
> 


-- 
Cory Nelson
http://www.int64.org

RE: [sqlite] SQLite 3.1.0 Column Names

[Drew, Stephen]

Kurt,

Apologies.  I believe I have also tried:

Exec("PRAGMA full_column_names=1;")

Steve 

-Original Message-
From: Drew, Stephen 
Sent: Tuesday, January 25, 2005 4:33 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite 3.1.0 Column Names

Kurt,

Thanks for the reply. Assume my pseudo-calls below do the following:

Open:   calls sqlite3_open
Exec:   calls sqlite3_exec
Query:  calls sqlite3_prepare, sqlite3_step, ..., sqlite3_finalize
Close:  calls sqlite3_close

And check return values of these functions.

I am doing the following:

Open();
Exec("BEGIN");
Exec("PRAGMA full_column_names;");
Exec("CREATE TABLE A (a_col TEXT, PRIMARY KEY (a_col));") Exec("CREATE
TABLE B (b_col TEXT, a_col TEXT, PRIMARY KEY (b_col));") Exec("COMMIT");
Exec("SELECT A.*, B.* FROM A, B WHERE A.a_col = B.b_col");

Now these tables are empty, but the column names are still retrievable.

In SQLite 2.8.15, the following names are returned:

A.a_col, B.b_col, B.a_col

In SQLite 3.1.0, regardless of whether either pragma (full_column_names
or short_column_names) is executed at the location above, the following
names are returned:

a_col, b_col, a_col.

I need these to be unique!  Am I missing something?  Are they
compile-time pragmas?

Thanks,
Steve


-Original Message-
From: Kurt Welgehausen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 25, 2005 4:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite 3.1.0 Column Names

> PRAGMA full_column_names;
>
> PRAGMA short_column_names;

Did you really set them, or just query them?

:

 PRAGMA full_column_names;
 PRAGMA full_column_names = 0|1;

 Query or change the short-column-names flag. ...

Regards

RE: [sqlite] SQLite 3.1.0 Column Names

[Drew, Stephen]

Kurt,

Thanks for the reply. Assume my pseudo-calls below do the following:

Open:   calls sqlite3_open
Exec:   calls sqlite3_exec
Query:  calls sqlite3_prepare, sqlite3_step, ..., sqlite3_finalize
Close:  calls sqlite3_close

And check return values of these functions.

I am doing the following:

Open();
Exec("BEGIN");
Exec("PRAGMA full_column_names;");
Exec("CREATE TABLE A (a_col TEXT, PRIMARY KEY (a_col));")
Exec("CREATE TABLE B (b_col TEXT, a_col TEXT, PRIMARY KEY (b_col));")
Exec("COMMIT");
Exec("SELECT A.*, B.* FROM A, B WHERE A.a_col = B.b_col");

Now these tables are empty, but the column names are still retrievable.

In SQLite 2.8.15, the following names are returned:

A.a_col, B.b_col, B.a_col

In SQLite 3.1.0, regardless of whether either pragma (full_column_names
or short_column_names) is executed at the location above, the following
names are returned:

a_col, b_col, a_col.

I need these to be unique!  Am I missing something?  Are they
compile-time pragmas?

Thanks,
Steve


-Original Message-
From: Kurt Welgehausen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 25, 2005 4:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite 3.1.0 Column Names

> PRAGMA full_column_names;
>
> PRAGMA short_column_names;

Did you really set them, or just query them?

:

 PRAGMA full_column_names;
 PRAGMA full_column_names = 0|1;

 Query or change the short-column-names flag. ...

Regards

Re: [sqlite] Are there any links on how to use a stored procedure in Sqllite

[Clay Dowling]

Steve Frierdich said:
> Is there any reference on links on how to used a stored procedure in
> sqlite/

The lack of links is directly related to the level of support for Stored
Procedures.  Simply put, they aren't available, except for functions that
you write in C that are tied specifically to your application.

Clay Dowling

-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

Re: [sqlite] SQLite 3.1.0 Column Names

[Kurt Welgehausen]

> PRAGMA full_column_names;
>
> PRAGMA short_column_names;

Did you really set them, or just query them?

:

 PRAGMA full_column_names;
 PRAGMA full_column_names = 0|1;

 Query or change the short-column-names flag. ...

Regards

Re: [sqlite] one SQL statement that will insert a new row in a table if a certain string is not found in a column?

[Dennis Cote]

Steve Frierdich wrote:
Is there a way to write one SQL statement that will insert a new row
in a table if a certain string is not found in a column in that same
table? Instead of having two write two SQL statements where one
checks to see if a user in a table, and the other that either updates
the current user if the user is  in the table or inserts the user in
a new row in the table if the user is not in the table. Anyone now of
how to accomplish this in just one SQL statement ?
Thanks
Steve
Mrs. Brisby wrote:
You should look at the sqlite documentation for REPLACE.
Steve,
This was good advice even if it was a little terse.
Here is an example:
sqlite> create table t (user primary key, data);
sqlite> insert into t values ('a', 1);
sqlite> insert or replace into t values ('b', 2);
sqlite> select * from t;
a|1
b|2
sqlite> insert or replace into t values ('b', 3);
sqlite> select * from t;
a|1
b|3
sqlite>
This works as long as the column containing the string you want to search 
for is the primary key of the table (like user above). If the primary key 
doesn't exist, it does an insert. If it does, it replaces that row with the 
new values.

HTH
Dennis Cote 

[sqlite] Are there any links on how to use a stored procedure in Sqllite

[Steve Frierdich]

Is there any reference on links on how to used a stored procedure in sqlite/
Thanks
Steve

[sqlite] one SQL statement that will insert a new row in a table if a certain string is not found in a column?

[Steve Frierdich]

Is there a way to write one SQL statement that will insert a new row in 
a table if a certain string is not found in a column in that same table? 
Instead of having two write two SQL statements where one checks to see 
if a user in a table, and the other that either updates the current user 
if the user is  in the table or inserts the user in a new row in the 
table if the user is not in the table. Anyone now of how to accomplish 
this in just one SQL statement ?
Thanks
Steve

Mrs. Brisby wrote:
On Tue, 2005-01-25 at 09:34 -0500, Steve Frierdich wrote:
 

Does anyone have any code they can send me that shows how to check for a 
string in a column in a table, and if the string is not there on how to 
insert the string in the column in the table in a new row. Then finally 
on how to update the table?
   

You should look at the sqlite documentation for REPLACE.
 

And if possible on how to maybe write a trigger for the above operation 
and use the trigger in a Visaul C++ or a Window's 32 program?
   

I don't understand why you think you need a trigger for this.
 

Re: [sqlite] CHECKING fOR A STRING IN A COLUMN IN A TABLE AND IF STRING IS NOT IN THE COLUMN ADD IT

[Steve Frierdich]

Oh thought maybe there was a trigger I could use to this operation for me.
Thanks for your input.
Steve
Mrs. Brisby wrote:
On Tue, 2005-01-25 at 09:34 -0500, Steve Frierdich wrote:
 

Does anyone have any code they can send me that shows how to check for a 
string in a column in a table, and if the string is not there on how to 
insert the string in the column in the table in a new row. Then finally 
on how to update the table?
   

You should look at the sqlite documentation for REPLACE.
 

And if possible on how to maybe write a trigger for the above operation 
and use the trigger in a Visaul C++ or a Window's 32 program?
   

I don't understand why you think you need a trigger for this.
 

Re: [sqlite] CHECKING fOR A STRING IN A COLUMN IN A TABLE AND IF STRING IS NOT IN THE COLUMN ADD IT

[Mrs. Brisby]

On Tue, 2005-01-25 at 09:34 -0500, Steve Frierdich wrote:
> Does anyone have any code they can send me that shows how to check for a 
> string in a column in a table, and if the string is not there on how to 
> insert the string in the column in the table in a new row. Then finally 
> on how to update the table?

You should look at the sqlite documentation for REPLACE.

> And if possible on how to maybe write a trigger for the above operation 
> and use the trigger in a Visaul C++ or a Window's 32 program?

I don't understand why you think you need a trigger for this.

[sqlite] CHECKING fOR A STRING IN A COLUMN IN A TABLE AND IF STRING IS NOT IN THE COLUMN ADD IT

[Steve Frierdich]

Does anyone have any code they can send me that shows how to check for a 
string in a column in a table, and if the string is not there on how to 
insert the string in the column in the table in a new row. Then finally 
on how to update the table?

And if possible on how to maybe write a trigger for the above operation 
and use the trigger in a Visaul C++ or a Window's 32 program?

Thanks
Steve
727 455 4668
727-547-9799 ext 1766

[sqlite] SQLite 3.1.0 Column Names

[Drew, Stephen]

Hello,
 
I have two 
tables:
 
"CREATE TABLE A (a_col 
TEXT, PRIMARY KEY (a_col))"

"CREATE TABLE B 
(b_col TEXT, a_col TEXT, PRIMARY KEY (b_col))"
I am trying to test the column names returned by the following 
query:
SELECT A.*, B.* FROM A, B WHERE A.a_col = 
B.a_col
Now whether or not I have one of these pragmas set after opening 
the transaction:
PRAGMA full_column_names;
PRAGMA short_column_names;
, the resulting column names are always:
a_col, b_col, a_col
breaking the unique 
constraint on column names in my column set.  Can anyone spot anything 
wrong with this?
 
Regards,Stephen Drew 

[sqlite] How to make a text widget hidden?

[Anirban Sarkar]

Hi all,

I need to store some values in a hidden text widget which is in a frame in tcl 
so that I can use those values to perform some mathematical operations when I 
click on the submit button. My problem is that I cannot make a text widget 
hidden. I am giving below the text widget code which I am using. What more do I 
need to specify in my code so that the text widget is hidden?

Code:

frame .f2 -width 250 -height 270
pack .f2
pack propagate .f2 0

label .f2.l1 -text "Name" -font $fnt9
place .f2.l1 -x 0 -y 25 
   
text .f2.t1 -bd 1 -highlightbackground black - highlightthickness 1 -font $fnt9
.f2.t1 insert end [string trim $nm]
.f2.t1 configure -state disabled
place .f2.t1 -x 65 -y 25 -width 170 -height 16

In the above code, $nm contains a value which is to be used for a mathematical 
operation. So .f2.t1 is to be made hidden.

Any help will be appreciated.
Thanks in advance.

Anirban Sarkar