Re: [sqlite] DBD::SQLite
I've just add an unlink of the db in the script to test it and found
that the code does not work correctly with DBD::SQLite version 1.08 (on
my host) but works with version 1.11 (on another host).
So I'll upgrade my host version. Thanks a lot.
use strict;
use DBI qw(:sql_types);
my $db='/tmp/stest.db';
my $sql;
my $sth;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=$db",
{
RaiseError => 1,
AutoCommit => 1
}
);
$dbh->do("CREATE TABLE mytable(varint text);");
my $stmt = $dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param( 1, '01237', { TYPE => SQL_VARCHAR } );
eval { $stmt->execute(); };
if ($@) {
print STDERR "[EMAIL PROTECTED]";
exit(1);
}
$sql = "SELECT varint FROM mytable";
$sth = $dbh->prepare($sql);
eval { $sth->execute(); };
if ($@) {
print STDERR "[EMAIL PROTECTED]";
exit(1);
}
my $ary_ref = $sth->fetchrow_arrayref;
print STDERR "RETURN IS $ary_ref->[0] \n";
unlink $db;
__END__
Chris Werner a écrit :
I cannot reproduce the problem. Your [slightly modified] code and output
follows: {you did "use strict" of course...}
#!/opt/web/bin/perl -w
use strict;
use DBI qw(:sql_types);
my $sql;
my $sth;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=/tmp/stest.db",
{
RaiseError => 1,
AutoCommit => 1
}
);
$dbh->do("CREATE TABLE mytable(varint text);");
my $stmt = $dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param( 1, '01237', { TYPE => SQL_VARCHAR } );
eval { $stmt->execute(); };
if ($@) {
print STDERR "[EMAIL PROTECTED]";
exit(1);
}
$sql = "SELECT varint FROM mytable";
$sth = $dbh->prepare($sql);
eval { $sth->execute(); };
if ($@) {
print STDERR "[EMAIL PROTECTED]";
exit(1);
}
my $ary_ref = $sth->fetchrow_arrayref;
print STDERR "RETURN IS $ary_ref->[0] \n";
__END__
bash-3.00$ rm /tmp/stest.db
bash-3.00$ ./tst.pl
RETURN IS 01237
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 11:04 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DBD::SQLite
.schema
CREATE TABLE mytable(varint text);
Sorry, but it's working with the do function.
However it's not working when I use bind variables even if I force the
SQL_VARCHAR type :
use DBI;
$dbh= DBI->connect("dbi:SQLite:dbname=./test.db",{ RaiseError => 1,
AutoCommit => 1 });
$stmt=$dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param(1, '01237', { TYPE => SQL_VARCHAR });
#$stmt->execute('01238');
$stmt->execute();
Chris Werner a écrit :
Can you run .schema on the table?
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 1:16 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DBD::SQLite
just a $dbh->do("insert into mytable(varint) values ('01234')";
It's not working correctly with SQLite but no problem with Oracle.
Chris Werner a écrit :
Can you give a code example? I have just tried, and can load string
values
with a leading 0 and m/^\d+$/
I suspect the problem is in your treatment of perl...
Christian Werner
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 16, 2006 2:56 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] DBD::SQLite
Hi,
I use DBD::SQLite for accessing a SQLite database, but there's an issue
when I tyr to insert a number starting with a 0. In fact, DBD::SQLite
seems to trim the starting 0 of the number. So, when I insert 0234 in a
table I find 234 instead.
Anyone has encoutered and resolved this bug ?
[sqlite] varchar(20) column reported as SQLITE_NULL?
While executing 'SELECT REGION FROM EMPLOYEE' I came across a column that is SQLITE_NULL as far as sqlite3_column_type is concerned, but really is varchar(20) if you ask for sqlite3_column_decltype? Is varchar(20) illegal in SQLite? Certainly looks like "typename ( number )" is okay at http://www.sqlite.org/lang_createtable.html Thanks! -Boris -- +1.604.689.0322 DeepCove Labs Ltd. 4th floor 595 Howe Street Vancouver, Canada V6C 2T5 [EMAIL PROTECTED] CONFIDENTIALITY NOTICE This email is intended only for the persons named in the message header. Unless otherwise indicated, it contains information that is private and confidential. If you have received it in error, please notify the sender and delete the entire message including any attachments. Thank you. smime.p7s Description: S/MIME cryptographic signature
[sqlite] Possible threadsafe conflict in Windows
I've started writing my first wrappers for SQLite compiled inside the Frontier Kernel and I've run into a snag. I have one verb that calls sqlite3_open. It returns successfully and sets the db properly. If, in that same routine, I call sqlite3_close, it works successfully. But, if I return from that verb and then call sqlite3_close in a separate verb (all in the same thread), I get the "library routine called out of sequence" error. I'm building this on Windows (VC2K3) and everything else seems fine. I've read about this problem occuring with a THREADSAFE conflict, but I can't tell if I should disable threadsafe (or if I even can, in Windows) or if I'm missing something else. Any help would be appreciated. Thanks! -- David
RE: [sqlite] DBD::SQLite
I cannot reproduce the problem. Your [slightly modified] code and output
follows: {you did "use strict" of course...}
#!/opt/web/bin/perl -w
use strict;
use DBI qw(:sql_types);
my $sql;
my $sth;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=/tmp/stest.db",
{
RaiseError => 1,
AutoCommit => 1
}
);
$dbh->do("CREATE TABLE mytable(varint text);");
my $stmt = $dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param( 1, '01237', { TYPE => SQL_VARCHAR } );
eval { $stmt->execute(); };
if ($@) {
print STDERR "[EMAIL PROTECTED]";
exit(1);
}
$sql = "SELECT varint FROM mytable";
$sth = $dbh->prepare($sql);
eval { $sth->execute(); };
if ($@) {
print STDERR "[EMAIL PROTECTED]";
exit(1);
}
my $ary_ref = $sth->fetchrow_arrayref;
print STDERR "RETURN IS $ary_ref->[0] \n";
__END__
bash-3.00$ rm /tmp/stest.db
bash-3.00$ ./tst.pl
RETURN IS 01237
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 11:04 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DBD::SQLite
.schema
CREATE TABLE mytable(varint text);
Sorry, but it's working with the do function.
However it's not working when I use bind variables even if I force the
SQL_VARCHAR type :
use DBI;
$dbh= DBI->connect("dbi:SQLite:dbname=./test.db",{ RaiseError => 1,
AutoCommit => 1 });
$stmt=$dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param(1, '01237', { TYPE => SQL_VARCHAR });
#$stmt->execute('01238');
$stmt->execute();
Chris Werner a écrit :
> Can you run .schema on the table?
>
> -Original Message-
> From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 17, 2006 1:16 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] DBD::SQLite
>
>
> just a $dbh->do("insert into mytable(varint) values ('01234')";
>
> It's not working correctly with SQLite but no problem with Oracle.
>
> Chris Werner a écrit :
>
>> Can you give a code example? I have just tried, and can load string
values
>> with a leading 0 and m/^\d+$/
>>
>> I suspect the problem is in your treatment of perl...
>>
>> Christian Werner
>>
>> -Original Message-
>> From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, March 16, 2006 2:56 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] DBD::SQLite
>>
>>
>> Hi,
>>
>> I use DBD::SQLite for accessing a SQLite database, but there's an issue
>> when I tyr to insert a number starting with a 0. In fact, DBD::SQLite
>> seems to trim the starting 0 of the number. So, when I insert 0234 in a
>> table I find 234 instead.
>>
>> Anyone has encoutered and resolved this bug ?
>>
>>
>>
>
>
Re: [sqlite] DBD::SQLite
Maybe you declared the column as numeric (integer, number)? In that case,
later versions of SQLite might be doing the conversion for you (I can't
remember for sure, but I seem to recall this is the case). You might need to
declare the column as text.
At least what you posted is missing the closing paren for $dbh->do(); But
that would throw a perl syntax error, so assuming your syntax is correct, you
might try it like this:
$dbh->do("insert into mytable(varint) values (?)", undef, '01234');
With the vagaries of quoting, I try to use placeholders wherever possible.
-Clark
- Original Message
From: Cyril Scetbon <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, March 16, 2006 11:15:30 PM
Subject: Re: [sqlite] DBD::SQLite
just a $dbh->do("insert into mytable(varint) values ('01234')";
It's not working correctly with SQLite but no problem with Oracle.
Chris Werner a écrit :
> Can you give a code example? I have just tried, and can load string values
> with a leading 0 and m/^\d+$/
>
> I suspect the problem is in your treatment of perl...
>
> Christian Werner
>
> -Original Message-
> From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 16, 2006 2:56 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] DBD::SQLite
>
>
> Hi,
>
> I use DBD::SQLite for accessing a SQLite database, but there's an issue
> when I tyr to insert a number starting with a 0. In fact, DBD::SQLite
> seems to trim the starting 0 of the number. So, when I insert 0234 in a
> table I find 234 instead.
>
> Anyone has encoutered and resolved this bug ?
>
>
Re: [sqlite] DBD::SQLite
.schema
CREATE TABLE mytable(varint text);
Sorry, but it's working with the do function.
However it's not working when I use bind variables even if I force the
SQL_VARCHAR type :
use DBI;
$dbh= DBI->connect("dbi:SQLite:dbname=./test.db",{ RaiseError => 1,
AutoCommit => 1 });
$stmt=$dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param(1, '01237', { TYPE => SQL_VARCHAR });
#$stmt->execute('01238');
$stmt->execute();
Chris Werner a écrit :
Can you run .schema on the table?
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 1:16 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DBD::SQLite
just a $dbh->do("insert into mytable(varint) values ('01234')";
It's not working correctly with SQLite but no problem with Oracle.
Chris Werner a écrit :
Can you give a code example? I have just tried, and can load string values
with a leading 0 and m/^\d+$/
I suspect the problem is in your treatment of perl...
Christian Werner
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 16, 2006 2:56 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] DBD::SQLite
Hi,
I use DBD::SQLite for accessing a SQLite database, but there's an issue
when I tyr to insert a number starting with a 0. In fact, DBD::SQLite
seems to trim the starting 0 of the number. So, when I insert 0234 in a
table I find 234 instead.
Anyone has encoutered and resolved this bug ?
Re: [sqlite] Simple Addition Question
Hello Deepak, your example is valid SQL. I have tried it and it works in sqlite (of course). Martin Martin Deepak Kaul wrote: I have an addition question. Is is possible to increment a value in an update statement? If so how would the sql statement be written? For example Table addition with the following columns unique_id, count I want something like the following UPDATE addition SET count = count + 1 WHERE unique_id = 1; Any help would be greatly appreciated. Thanks in advance
[sqlite] Simple Addition Question
I have an addition question. Is is possible to increment a value in an update statement? If so how would the sql statement be written? For example Table addition with the following columns unique_id, count I want something like the following UPDATE addition SET count = count + 1 WHERE unique_id = 1; Any help would be greatly appreciated. Thanks in advance -- Software Engineer [EMAIL PROTECTED] 301.286.7951
RE: [sqlite] DBD::SQLite
Can you run .schema on the table?
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 1:16 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DBD::SQLite
just a $dbh->do("insert into mytable(varint) values ('01234')";
It's not working correctly with SQLite but no problem with Oracle.
Chris Werner a écrit :
> Can you give a code example? I have just tried, and can load string values
> with a leading 0 and m/^\d+$/
>
> I suspect the problem is in your treatment of perl...
>
> Christian Werner
>
> -Original Message-
> From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 16, 2006 2:56 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] DBD::SQLite
>
>
> Hi,
>
> I use DBD::SQLite for accessing a SQLite database, but there's an issue
> when I tyr to insert a number starting with a 0. In fact, DBD::SQLite
> seems to trim the starting 0 of the number. So, when I insert 0234 in a
> table I find 234 instead.
>
> Anyone has encoutered and resolved this bug ?
>
>
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I have never used SQLite with sqlite3_prepare(), sqlite3_step(), sqlite3_reset() and sqlite3_finalize(). Do you have any code example that i can use to avoid the use of sqlite_get_table() ? Thank you, Eduardo ---Mensaje original---I don't use the sqlite_get_table() function, and don't recommend it to others to use. As I understand it, it's there for legacy application support. New programs written for SQLite should use sqlite3_prepare(), sqlite3_step(), sqlite3_reset() and sqlite3_finalize() instead. Robert - Original Message - From: To: ; Sent: Friday, March 17, 2006 7:08 AM Subject: [sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using PRAGMA cache size=8, that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been taking a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. I have attached to this email a memory graphic of sqlite3_get_table problem El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref=web=homespot=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using PRAGMA cache size=8, that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been taking a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. I have attached to this email a memory graphic of sqlite3_get_table problem ---Mensaje original Original Message - From: I have run your program on the CE emulator (Pocket PC 2003) and i got the same memory leak. I have inserted 2 buttons on a MFC dialog application. The first button executes your code and the second button closes the application. If you examine the memory you will discover that the program only free the memory once you exit from the apllication, meanwhile it reserves memory as its needed (on demand, but see details below). the memory behaviour of SQLite is quite strange, an example: lets say that a select sentence reserves 1000kb of memory, once this local process has finished memory keeps reserved for the program (it should be freed), if another process executes a select sentence that needs 200kb SQLite will not reserve 200k more, it will use 200k of the previous 1000k reserved. if a 3rd process executes a select sentence that needs 1300k SQlite will reserve 300kb more and those 1300kb will not be freed until the main dialog application closes (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. The problem is that if a select sentence consume most of the memory it will not be freed and the program will execute very slow until you exit from the application because there will be so little memory left for other not SQLite process that the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing PRAGMA cache_size=8 or some really low number and tell me if its still leaking. Robert El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref=web=homespot=Regalos%20Día%20del%20Padre
RE: [sqlite] REGEXP
> Hi Ulrich, thanks for the answer but I need to implement a > function regexp() for SQLite inside a program in C++. Look at boost: http://www.boost.org/libs/regex/doc/introduction.html Regards, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
Re: [sqlite] REGEXP
Hi Ulrich, thanks for the answer but I need to implement a function regexp() for SQLite inside a program in C++. Thanks, Danilo. Ulrich Schöbel ha scritto: Hi Danilo, take a look at http://aspn.activestate.com/ASPN/docs/ActiveTcl/tcl/TclCmd/re_syntax.htm Kind regards Ulrich On Friday 17 March 2006 10:13, Danilo wrote: Hi to All , I'm looking for further information about REGEXP (http://www.sqlite.org/lang_expr.html). Someone knows how to point out me some examples! Thanks, Danilo. Home Page: http://www.digitazero.org venerdì 17 marzo 2006, 9.07
Re: [sqlite] REGEXP
Hi Danilo, take a look at http://aspn.activestate.com/ASPN/docs/ActiveTcl/tcl/TclCmd/re_syntax.htm Kind regards Ulrich On Friday 17 March 2006 10:13, Danilo wrote: > Hi to All , > I'm looking for further information about REGEXP > (http://www.sqlite.org/lang_expr.html). > Someone knows how to point out me some examples! > > Thanks, Danilo. > Home Page: http://www.digitazero.org > venerdì 17 marzo 2006, 9.07
[sqlite] REGEXP
Hi to All , I'm looking for further information about REGEXP (http://www.sqlite.org/lang_expr.html). Someone knows how to point out me some examples! Thanks, Danilo. Home Page: http://www.digitazero.org venerdì 17 marzo 2006, 9.07
