[sqlite] DBD:SQLite and sqlite3_column_decltype()
Hello -- I'm trying to track down a segfault that happens when I'm using DBD::SQLite for Perl, and I'm confused by the documentation for sqlite3_column_decltype(). I think I understand what it does, but I think there are some typos that make me uncertain: http://sqlite.org/capi3ref.html#sqlite3_column_decltype The first argument is a prepared SQL statement. If this statement is a SELECT statement, the Nth column of the returned result set of the SELECT is a table column then the declared type of the table column is returned. If the Nth column of the result set is not at table column, then a NULL pointer is returned. The returned string is UTF-8 encoded for sqlite3_column_decltype() and UTF-16 encoded for sqlite3_column_decltype16(). For example, in the database schema: CREATE TABLE t1(c1 INTEGER); And the following statement compiled: SELECT c1 + 1, 0 FROM t1; Then this routine would return the string "INTEGER" for the second result column (i==1), and a NULL pointer for the first result column (i==0). Is the first sentence supposed to be "If this statement is a SELECT statement [and if] the Nth column..."? And the next sentence should be "is not [a] table column"? And is the final paragraph correct, or are the numbers reversed for which is NULL and which is "INTEGER"? I think what's happening with the Perl interface is that sqlite3_column_decltype() returns NULL if the table is created without an explicit type, instead of the "" or "TEXT" that I would have expected. Is this correct? Or should it return something else? Thanks! Nathan Kurz [EMAIL PROTECTED]
Re: [sqlite] delete all tables
This is definitely the fastest way, if it succeeds. If its performed via SQLite DDL then it does the appropriate locking on the file etc and returns nice error codes. By doing it via the file system (at least on linux), other readers of the file will continue to read from it... I don't know if they are allowed to write to it... In either case it doesn't seem appropriate to me. If your on windows the remove request could even fail if you or others have the file open... It may be slower, but you probably want to drop the tables via sqlite ddl. --paul On 2/9/06, Xavier Noria <[EMAIL PROTECTED]> wrote: > > On Feb 9, 2006, at 23:02, Marian Olteanu wrote: > > > I would say that the fastest way (CPU cycles and lines of code) to > > delete all tables would be to delete the file in which the database > > is stored. > > Clever! > >
Re: [sqlite] delete all tables
On Feb 9, 2006, at 23:02, Marian Olteanu wrote: I would say that the fastest way (CPU cycles and lines of code) to delete all tables would be to delete the file in which the database is stored. Clever!
Re: [sqlite] about DELETE and JOIN
On 2/9/06, Cyrille37 <[EMAIL PROTECTED]> wrote: > Jay Sprenkle a écrit : > >> Thanks Jay, > >> > >> the "not in" was the syntax I didn't know for adding the select query to > >> the delete command. I haven't found the not in to be slow, but I haven't tried it on sqlite. In all the other systems I've tried I believe they're pretty similar in performance. I think they actually do the same thing underneath, but I don't know for sure.
Re: [sqlite] delete all tables
I would say that the fastest way (CPU cycles and lines of code) to delete all tables would be to delete the file in which the database is stored. On Thu, 9 Feb 2006, Xavier Noria wrote: In the schema definition I would like to avoid the combo delete if exists table_name; create table_name ( ... ); Can I query sqlite_master about tables, indexes, etc. in a way that allows the deletion of everything in one shot beforehand? -- fxn
[sqlite] delete all tables
In the schema definition I would like to avoid the combo delete if exists table_name; create table_name ( ... ); Can I query sqlite_master about tables, indexes, etc. in a way that allows the deletion of everything in one shot beforehand? -- fxn
Re: [sqlite] Re: How to read column names
Igor Tandetnik wrote: Eugen Stoianovici wrote: Is there a way of reading the names of the columns in a table? how about the table names in a data base? select * from sqlite_master where type='table' pragma table_info(table_name); Igor Tandetnik Thanks
Re: [sqlite] about DELETE and JOIN
Jay Sprenkle a écrit : Thanks Jay, the "not in" was the syntax I didn't know for adding the select query to the delete command. You're welcome. You can also do it with a join but it's not as clear and simple. Perhaps the "Join" version is faster than "Not In" ?? I read that "not" usually got bad performance ... But it was not about SQLite. What do you think about ? cyrille.
RE: [sqlite] How to read column names
select name from sqlite_master where type='table' That will get you the names of tables in the database. -Original Message- From: Eugen Stoianovici [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 3:57 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to read column names Is there a way of reading the names of the columns in a table? how about the table names in a data base?
Re: [sqlite] How to read column names
Eugen Stoianovici wrote: Is there a way of reading the names of the columns in a table? how about the table names in a data base? Eugen, This will give you the table names: select name from sqlite_master where type = 'table'; and this will give you the columns in that table: pragam table_info(table_name); HTH Dennis Cote
[sqlite] Re: How to read column names
Eugen Stoianovici wrote: Is there a way of reading the names of the columns in a table? how about the table names in a data base? select * from sqlite_master where type='table' pragma table_info(table_name); Igor Tandetnik
[sqlite] How to read column names
Is there a way of reading the names of the columns in a table? how about the table names in a data base?
Re: [sqlite] about DELETE and JOIN
> Thanks Jay, > > the "not in" was the syntax I didn't know for adding the select query to > the delete command. > You're welcome. You can also do it with a join but it's not as clear and simple.
Re: [sqlite] about DELETE and JOIN
Jay Sprenkle a écrit : How about this: delete from Words where IID not in ( select Words_IID from Feedbacks_has_Words ) Thanks Jay, the "not in" was the syntax I didn't know for adding the select query to the delete command. Thank you. cyrille
Re: [sqlite] about DELETE and JOIN
Jay Sprenkle a écrit : How about this: delete from Words where IID not in ( select Words_IID from Feedbacks_has_Words ) Thanks Jay, the "not in" was the syntax I didn't know for adding the select query to the delete command. Thank you. cyrille
Re: [sqlite] about DELETE and JOIN
Radu Lodina a écrit :
Hi cyrille,
Please try:
DELETE FROM Words WHERE IID NOT IN
( SELECT Words_ID FROM Feedbacks_has_Words )
YEAH!!! Thanks a lot.
Your answer was so fast that I could not believe.
Surely I'm dreaming ...
;o)
thanks again.
cyrille
Regards
Radu Lodina
On 2/9/06, Cyrille37 <[EMAIL PROTECTED]> wrote:
Hello,
I've got a SELECT query that I would like to transform as a DELETE query ,
but I could not write that DELETE query ;o{
Here is the case :
In a search engine, I would like to erase all words that are no more
attached to a feedback entry.
table Words ( IID, Word )
table Feedbacks_has_Words ( Feedbacks_IID, Words_ID )
table Feedbacks( IID, )
When a word has no more feedback attached (relation is table
Feedbacks_has_Words) ,
it should be deleted.
To find such words, this SELECT query works fine :
select IID,Word, Feedbacks_IID
from Words LEFT JOIN Feedbacks_has_Words
on IID = Words_IID
where Feedbacks_IID is null
I could not figure how to right the DELETE Query ...
Have you got a idea about it ???
Thanks a lot,
cyrille
Re: [sqlite] about DELETE and JOIN
On 2/9/06, Cyrille37 <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I've got a SELECT query that I would like to transform as a DELETE query ,
> but I could not write that DELETE query ;o{
>
> Here is the case :
>
> In a search engine, I would like to erase all words that are no more
> attached to a feedback entry.
>
> table Words ( IID, Word )
> table Feedbacks_has_Words ( Feedbacks_IID, Words_ID )
> table Feedbacks( IID, )
>
> When a word has no more feedback attached (relation is table
> Feedbacks_has_Words) ,
> it should be deleted.
>
> To find such words, this SELECT query works fine :
>
> select IID,Word, Feedbacks_IID
> from Words LEFT JOIN Feedbacks_has_Words
> on IID = Words_IID
> where Feedbacks_IID is null
>
> I could not figure how to right the DELETE Query ...
> Have you got a idea about it ???
How about this:
delete from Words
where IID not in ( select Words_IID from Feedbacks_has_Words )
Re: [sqlite] about DELETE and JOIN
Hi cyrille,
Please try:
DELETE FROM Words WHERE IID NOT IN
( SELECT Words_ID FROM Feedbacks_has_Words )
Regards
Radu Lodina
On 2/9/06, Cyrille37 <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> I've got a SELECT query that I would like to transform as a DELETE query ,
> but I could not write that DELETE query ;o{
>
> Here is the case :
>
> In a search engine, I would like to erase all words that are no more
> attached to a feedback entry.
>
> table Words ( IID, Word )
> table Feedbacks_has_Words ( Feedbacks_IID, Words_ID )
> table Feedbacks( IID, )
>
> When a word has no more feedback attached (relation is table
> Feedbacks_has_Words) ,
> it should be deleted.
>
> To find such words, this SELECT query works fine :
>
> select IID,Word, Feedbacks_IID
> from Words LEFT JOIN Feedbacks_has_Words
> on IID = Words_IID
> where Feedbacks_IID is null
>
> I could not figure how to right the DELETE Query ...
> Have you got a idea about it ???
>
> Thanks a lot,
> cyrille
>
>
[sqlite] about DELETE and JOIN
Hello,
I've got a SELECT query that I would like to transform as a DELETE query ,
but I could not write that DELETE query ;o{
Here is the case :
In a search engine, I would like to erase all words that are no more
attached to a feedback entry.
table Words ( IID, Word )
table Feedbacks_has_Words ( Feedbacks_IID, Words_ID )
table Feedbacks( IID, )
When a word has no more feedback attached (relation is table
Feedbacks_has_Words) ,
it should be deleted.
To find such words, this SELECT query works fine :
select IID,Word, Feedbacks_IID
from Words LEFT JOIN Feedbacks_has_Words
on IID = Words_IID
where Feedbacks_IID is null
I could not figure how to right the DELETE Query ...
Have you got a idea about it ???
Thanks a lot,
cyrille
Re: [sqlite] print for debugging from triggers
> >> >program). But this didn't work. So I wrote a simple print method that > >> >takes > >> >one argument and printf it to the standard out. This works and actually > >> >solves my problem. > >> There isn't a way to get this output printed directly, but you can > >> easily create a log of your applications activity in a table and then > >> use a select to dump the table. > > > > The problem with logging to a table is that if the transaction aborts > > (ie: because there's a bug in the trigger), you won't get anything in > > the table. So when you need the info most you can't get it. There's always unix syslog() and the windows event log. With good infrastructure you can view errors remotely too.
Re: [sqlite] print for debugging from triggers
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Thu, Feb 09, 2006 at 09:51:44AM -0700, Dennis Cote wrote: >> >program). But this didn't work. So I wrote a simple print method that takes >> >one argument and printf it to the standard out. This works and actually >> >solves my problem. >> There isn't a way to get this output printed directly, but you can >> easily create a log of your applications activity in a table and then >> use a select to dump the table. > > The problem with logging to a table is that if the transaction aborts > (ie: because there's a bug in the trigger), you won't get anything in > the table. So when you need the info most you can't get it. In the 2.8 series, if you ATTACH to a different database after beginning a transaction and DETACH before ending the transaction, anything written to the attached database will be retained regardless of whether the transaction is committed or rolled back. If this "feature" still exists in the 3.x series, it may be of use to you for this purpose. Just attach, insert, detach as part of your trigger. (I've never tried doing that in a trigger, so caveat emptor.) Derrell
Re: [sqlite] print for debugging from triggers
On Thu, Feb 09, 2006 at 09:51:44AM -0700, Dennis Cote wrote: > >program). But this didn't work. So I wrote a simple print method that takes > >one argument and printf it to the standard out. This works and actually > >solves my problem. > There isn't a way to get this output printed directly, but you can > easily create a log of your applications activity in a table and then > use a select to dump the table. The problem with logging to a table is that if the transaction aborts (ie: because there's a bug in the trigger), you won't get anything in the table. So when you need the info most you can't get it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] print for debugging from triggers
Ran wrote:
Hi all,
I use many triggers and for debugging purposes I wanted to know which one is
triggered and when.
At first I thought that if I write a SELECT within the BEGIN-END block, this
SELECT results will be printed (at least when using sqlite3 command line
program). But this didn't work. So I wrote a simple print method that takes
one argument and printf it to the standard out. This works and actually
solves my problem.
I can write something like:
create trigger bla after delete on foo
begin
select print('bla trigger is triggered');
end;
But I still wonder - this solution is quite simple, yet very useful - so I
suspect I missed an existing feature. Is there a builtin feature like this?
Thanks,
Ran.
Ran,
There isn't a way to get this output printed directly, but you can
easily create a log of your applications activity in a table and then
use a select to dump the table.
create table log(event text);
create trigger ...
begin
insert into log values('blah blah blah...');
...
end;
Then after your program stops, or using another connection to the
database, you can dump the log using select * from log. If you want you
can event get more sophisticated and add a timestamp field to your log,
or add other fields to track important internal state information.
HTH
Dennis Cote
Re: [sqlite] String to numeric conversion
[EMAIL PROTECTED] wrote:
Ticket #1662 (http://www.sqlite.org/cvstrac/tktview?tn=1662)
complains that SQLite is not converting strings into numbers
if the string contains leading spaces. This happens because
SQLite just hands the string to strtod() and strtod() does not
recognize numbers with leading spaces. (Actually, strtod is
not used - our own internal implementation gets called, but
it works about the same.)
So the question is: should this be changed. Should SQLite
ignore leading space in strings when trying to determine if
the string looks like a number.
This has far-reaching implications. Right now if you say:
CREATE TABLE t1(a REAL);
INSERT INTO t1 VALUES('12.34');
The t1.a column will be filled with a string because the
leading spaces on the string make it not look like a number,
as far as SQLite is concerned. If you had said:
INSERT INTO t1 VALUES('23.45');
Then the string does look like a number and gets converted.
If the change requested by ticket #1662 is implemented then
both INSERTs above would convert their arguments to numbers.
As it currently stands, only the second INSERT converts its
argument.
Which is the correct behavior? Is this important enough to
change (and possible cause problems in legacy code)?
--
D. Richard Hipp <[EMAIL PROTECTED]>
Richard,
I think SQLite should be changed to trim this leading space
automatically when storing a string representation of a number into a
column that is explicitly declared REAL or INTEGER.
As Igor has already pointed out, strtod() and strtol() are defined to
skip leading whitespace. So there should not be any implementation
difficulty.
This change will give correct and expected behavior for columns that are
declared to hold numeric data. It will have no effect for columns that
are declared to hold textual data, or columns that with no type declared
(which are common for SQLite because of its typeless history) since they
are treated as text columns.
Furthermore, I'm fairly sure that anyone who really required these
values to be stored as strings would have (or at least should have)
declared the column type as textual and not REAL or INTEGER. This is
really the same kind of distinction that needs to be made to store
numeric strings with leading zeros. If they are inserted into a numeric
column the leading zero are removed, if they are stored into a text
column the leading zeros are retained. If you need to keep the leading
zeros or spaces you should be using a text column and not a numeric one.
Finally, if the change isn't made there is no way for the user to get
the correct numeric interpretation of the value back out of SQLite.
Casting (i.e. cast(a as real)) won't work since it returns the same 0.0
value. Therefore the user will not be able to use any of the numeric
functions (i.e. sum(), avg(), min() etc.) to get correct (or at least
the expected) results using this column.
Dennis Cote
[sqlite] print for debugging from triggers
Hi all,
I use many triggers and for debugging purposes I wanted to know which one is
triggered and when.
At first I thought that if I write a SELECT within the BEGIN-END block, this
SELECT results will be printed (at least when using sqlite3 command line
program). But this didn't work. So I wrote a simple print method that takes
one argument and printf it to the standard out. This works and actually
solves my problem.
I can write something like:
create trigger bla after delete on foo
begin
select print('bla trigger is triggered');
end;
But I still wonder - this solution is quite simple, yet very useful - so I
suspect I missed an existing feature. Is there a builtin feature like this?
Thanks,
Ran.
RE: [sqlite] sql problem
Using the LIKE qualifier instead of = will result in case insensitive matches. Shawn M. Downey MPR Associates 10 Maxwell Drive, Suite 204 Clifton Park, NY 12065 518-371-3983 x113 (work) 860-508-5015 (cell) -Original Message- From: manoj marathayil [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 1:25 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] sql problem i am able to get the result but the problem is it is case sensitive, i want a case insensitive manoj marathayil <[EMAIL PROTECTED]> wrote: i have two tables like this; CREATE TABLE sp_objects ( dbname varchar(256), object_name varchar (256), arglist varchar(256), type integer, primary key ( dbname, object_name, arglist ) ); CREATE TABLE sp_objects ( dbname varchar(256), object_name varchar (256), arglist varchar(256), type integer, primary key ( dbname, object_name, arglist ) ); then i issued a query like this; select * from ((select distinct(tablename), type from objects where dbname = 't_test' union select object_name as 'a', type from sp_objects where dbname = 't_test') as t) order by 2 asc; this is working as i expected but if i use "order by 1 asc" its not working, how can i solve this, thanks in adv... - Jiyo cricket on Yahoo! India cricket Yahoo! Messenger Mobile Stay in touch with your buddies all the time. T h a n k s & R e g a r d s , Manoj M | Webyog | Bangalore | Voice: 91 9886171984, 91 80 51216434(off) "A winner is not one who never fails, but one who never quits!" - Jiyo cricket on Yahoo! India cricket Yahoo! Messenger Mobile Stay in touch with your buddies all the time.
Re: [sqlite] String to numeric conversion
[EMAIL PROTECTED] a écrit : So the question is: should this be changed. Should SQLite ignore leading space in strings when trying to determine if the string looks like a number. [...] Which is the correct behavior? Is this important enough to change (and possible cause problems in legacy code)? As you said in a previous mail : "Other RDBMSes do similar automatic conversions. If you try to insert a string into an integer column of other database and the string looks like an integer, the database will do the conversion for you. If the string does *not* look like an integer, however, most other database engines will throw an error. SQLite does not do this. It goes ahead and stores the string. The unusual behavior of SQLite is considered a feature, not a bug." i would say that you can "improve" automatic datatype casting, but where to go ? Does SQLite have to take in charge what a developper doesn't want to do himself (ie removing leading and trailing spaces) ? IMHO, i would let things like they're now. Regards, -- Guillaume MAISON - [EMAIL PROTECTED] 83, Cours Victor Hugo 47000 AGEN Tél : 05 53 87 91 48 - Fax : 05 53 68 73 50 e-mail : [EMAIL PROTECTED] - Web : http://nauteus.com
Re: [sqlite] testing Avg() function in other database engines
Sybase ASE 12.5.3: 3 3.33 Regards, Jarek > -Wiadomość oryginalna- > Od: Dennis Cote [mailto:[EMAIL PROTECTED] > Wysłano: 8 lutego 2006 20:11 > Do: sqlite-users > Temat: [sqlite] testing Avg() function in other database engines > > Hi All, > > I have a question that I hope you can help with. I would like > to know what results other database engines (i.e. mySQL, > PostgreSQL, Firebird, Oracle, MS SQL, etc) give for the following SQL. > > create table t (a integer, b real); > insert into t values (3, 3); > insert into t values (3, 3); > insert into t values (4, 4); > select avg(a), avg(b) from t; > > In SQLite I get the following: > > SQLite version 3.3.2 > Enter ".help" for instructions > sqlite> create table t (a integer, b real); > sqlite> insert into t values (3, 3); > sqlite> insert into t values (3, 3); > sqlite> insert into t values (4, 4); > sqlite> select avg(a), avg(b) from t; > 3.33|3.33 > > I'm particularly interested in the value of avg(a). My > reading of the standard leads me to believe that avg(a) > should be 3 (i.e. an integer > value) and not a floating point value. This is similar to the > earlier discussion of the results for division with integers > (i.e. 5/2 -> 2 vs > 5/2 -> 2.5). > > I would like to know if other databases follow the standard > here or not. > If you have ready access to another database, please try this > and post the result. It will save me lots of time setting up > other programs (and hopefully get info on programs I don't > have access to like Oracle). > > TIA > Dennis Cote >
