Re: [sqlite] HOWTO: Parse user SQL and add persistent SQL
Kristoffer Danielsson wrote:> Consider an application where the user enters a search string:> SELECT Year FROM Car WHERE Brand = 'Audi'> > Now I want to add some conditions, depending on settings in the application. For instance, I would like to modify the above SQL string to get this:> SELECT Year, Color FROM Car WHERE Brand = 'Audi' AND DateCreated > '2008-10-10'> > Obviously, making string replacements is not a good idea. The only smart solution is to somehow edit the tree-representation of the SQL statement. Probably the same technique used for statement optimization.> > The parser is already there so the question is; how do I edit the SQL-statement on a low level, before executing it?> > Thanks. Darren Duncan wrote:> A better safer solution for most cases is to not let user type raw SQL at all, > but rather you can just provide a collection of form fields where they enter the > basic information and then you generate a SQL query from it. In that case, you > are *starting* with the tree as it were. -- Darren Duncan Thanks, but that's plan B. However, if it turns out that parsing the VM-structure is incredibly hard, then I will stick to your solution. _ Var sommaren för kort? Här hittar du solen! http://resor.se.msn.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
D. Richard Hipp wrote: > On Oct 9, 2008, at 9:11 PM, dcharno wrote: > >>> If you have issues or concerns with any aspect of the upcoming >>> release, now would be a good time to raise them. >> Is there any way to have both the BNF and syntax diagrams in the SQL >> Syntax? > > > Not really. Explain to me again why you want BNF instead of syntax > diagrams? Most people find the syntax diagrams to be much easier to > understand. I could imagine its a lot of effort to create those syntax diagrams. They didn't seem any easier to follow, at least to me. Maybe they grow on you ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HOWTO: Parse user SQL and add persistent SQL
Kristoffer Danielsson wrote: > Consider an application where the user enters a search string: > SELECT Year FROM Car WHERE Brand = 'Audi' > > Now I want to add some conditions, depending on settings in the application. > For instance, I would like to modify the above SQL string to get this: > SELECT Year, Color FROM Car WHERE Brand = 'Audi' AND DateCreated > > '2008-10-10' > > Obviously, making string replacements is not a good idea. The only smart > solution is to somehow edit the tree-representation of the SQL statement. > Probably the same technique used for statement optimization. > > The parser is already there so the question is; how do I edit the > SQL-statement on a low level, before executing it? > > Thanks. A better safer solution for most cases is to not let user type raw SQL at all, but rather you can just provide a collection of form fields where they enter the basic information and then you generate a SQL query from it. In that case, you are *starting* with the tree as it were. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] HOWTO: Parse user SQL and add persistent SQL
Consider an application where the user enters a search string: SELECT Year FROM Car WHERE Brand = 'Audi' Now I want to add some conditions, depending on settings in the application. For instance, I would like to modify the above SQL string to get this: SELECT Year, Color FROM Car WHERE Brand = 'Audi' AND DateCreated > '2008-10-10' Obviously, making string replacements is not a good idea. The only smart solution is to somehow edit the tree-representation of the SQL statement. Probably the same technique used for statement optimization. The parser is already there so the question is; how do I edit the SQL-statement on a low level, before executing it? Thanks. _ Senaste sportnyheterna & rykande färska resultat! http://sport.msn.se/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database is locked
it wont it was a quick example, that prep is suppose to be sqlite3_step ~Shaun ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database is locked
you don't need to prepare when you use exec...
The following is not a step.. Don't see how this even would compile...
while(sqlite3_prep(plineInfo) == SQLITE_ROW)
--- On Fri, 10/10/08, Shaun R. <[EMAIL PROTECTED]> wrote:
> From: Shaun R. <[EMAIL PROTECTED]>
> Subject: [sqlite] database is locked
> To: sqlite-users@sqlite.org
> Date: Friday, October 10, 2008, 3:30 PM
> I have some code that i'm having problems with. i open
> the database,
> prepare, and the step through. During each step i'm
> trying to run a
> seperate exec which is attempting to update a row. I keep
> getting a error
> that says database is locked. I tried creating a
> additional open to just
> use on the updates but i get the same error. Short example
> below with no
> error checking.
>
>
>
> sqlite3_open("my.db", &db);
>
> sqlite3_prepare(db, "SELECT ip_address FROM ips WHERE
> ip_owner=''",
> &plineInfo, 0);
>
> while(sqlite3_prep(plineInfo) == SQLITE_ROW) {
>
>zSQL = sqlite3_mprintf("UPDATE ips set
> ip_owner='%q' WHERE ip_owner=''
> and ip_address='%q'", username,
> sqlite3_column_text(plineInfo, 0));
>
> sqlite3_exec(db, zSQL, 0, 0, 0) == SQLITE_OK);
>
> }
>
> Agian this is just a quick/basic example showing what
> i'm trying to do. I
> keep getting database is locked when checking the error
> returned by exec.
>
> ~Shaun
>
>
> ___
> 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] sqlite3_open() problem
Hello Team.
I have a problem with running SQLite.
I am running linux 2.6.17 on *ARM* and basically problem is that my
application
is crushing on *sqlite3_open*() function while the sqlite3 command
shell is
running without problems.
I added the sqlite3.h file to my project.
/**/
#include
#include "sqlite3.h"
static int callback(void *NotUsed, int argc, char **argv, char
**azColName){
int i;
for(i=0; ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick FTS3 question: partial matches
So while trying to find a solution to my problem of finding partial strings in several columns ( finding 'this' in 'HellothisIsATest' using MATCH '*this*' ), I found out that sqlite fts3 only supports prefix matching and that if I want to do partial string matching, I would be forced to use LIKE, or create my own tokenizer in order to index my terms the way I want. I wouldn't mind sacraficing size for performance since I do not have a size constraint for my project. My question is, has anyone already written a tokenizer that would do what I want? It seems as if this problem should have already been solved for me, and I really don't want to reinvent the wheel if it already has been done :) Thanks, Jon On Thu, Oct 9, 2008 at 9:51 PM, Jonathon <[EMAIL PROTECTED]> wrote: > Thanks for the reply. I was just curious if anyone has found a viable > workaround for this issue? Using the dreaded "Like" is a bit too slow for > what I need. > > Jon > > > On Thu, Oct 9, 2008 at 8:50 PM, Alexandre Courbot <[EMAIL PROTECTED]>wrote: > >> > I would like to look up partial strings across several columns using >> FTS3 >> > and MATCH. For example, if I have a column that has data, >> > 'helloThisIsATest'... and I do: >> > >> > select * from myTable where myTable match '*This*'; >> >> You can only use wildcards as a suffix - this is due to the nature of >> the indexes fts3 uses. >> >> I don't know of a possible workaround for this while still using fts3. >> I'd say that for this kind of request, you are condemned to use '... >> like "%This%". >> >> Alex. >> ___ >> 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] database is locked
I have some code that i'm having problems with. i open the database,
prepare, and the step through. During each step i'm trying to run a
seperate exec which is attempting to update a row. I keep getting a error
that says database is locked. I tried creating a additional open to just
use on the updates but i get the same error. Short example below with no
error checking.
sqlite3_open("my.db", &db);
sqlite3_prepare(db, "SELECT ip_address FROM ips WHERE ip_owner=''",
&plineInfo, 0);
while(sqlite3_prep(plineInfo) == SQLITE_ROW) {
zSQL = sqlite3_mprintf("UPDATE ips set ip_owner='%q' WHERE ip_owner=''
and ip_address='%q'", username, sqlite3_column_text(plineInfo, 0));
sqlite3_exec(db, zSQL, 0, 0, 0) == SQLITE_OK);
}
Agian this is just a quick/basic example showing what i'm trying to do. I
keep getting database is locked when checking the error returned by exec.
~Shaun
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Merging N databases
ferrety ferrety <[EMAIL PROTECTED]> wrote: > Let me give you how the DBs looks like in a real example. > Each DB contains a uniq table called URL : > > CREATE TABLE if not exists URL (name VARCHAR PRIMARY KEY, nbr > INTEGER); > > $ sqlite3 /tmp/links1.db : > sqlite> select * from URL; > www.foo.com|17 > www.bar.com|100 > www.lost.com|5 > > $ sqlite3 /tmp/links2.db : > sqlite> select * from URL; > www.lost.com|22 > www.bar.com|9 > www.dharma.com|1 > > The objective is to merge these 2 DBs in the first one > "/tmp/links1.db" to get this: > $ sqlite3 /tmp/links1.db : > sqlite> select * from URL; > www.foo.com|17 > www.lost.com|27 > www.bar.com|109 > www.dharma.com|1 Perhaps something like this: $ sqlite3 /tmp/links1.db attach '/tmp/links2.db' as second; insert or replace into main.URL(name, nbr) select u2.name, u2.nbr + coalesce(u1.nbr, 0) from second.URL u2 left join main.URL u1 on (u2.name=u1.name); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Merging N databases
On 10/10/08, ferrety ferrety <[EMAIL PROTECTED]> wrote: > Hi, > > I'm new to SQLite and to list !!! So ... > > I've "N" sqlite DBs all produced with the same version of SQLite3 and > all having the same DB schema (only 1 table). > I'd like to merge them all in one single DB. The merge process needs > to be done as fast as possible. > > Let me give you how the DBs looks like in a real example. > Each DB contains a uniq table called URL : > > CREATE TABLE if not exists URL (name VARCHAR PRIMARY KEY, nbr INTEGER); > > $ sqlite3 /tmp/links1.db : > sqlite> select * from URL; > www.foo.com|17 > www.bar.com|100 > www.lost.com|5 > > $ sqlite3 /tmp/links2.db : > sqlite> select * from URL; > www.lost.com|22 > www.bar.com|9 > www.dharma.com|1 > > The objective is to merge these 2 DBs in the first one > "/tmp/links1.db" to get this: > $ sqlite3 /tmp/links1.db : > sqlite> select * from URL; > www.foo.com|17 > www.lost.com|27 > www.bar.com|109 > www.dharma.com|1 > > I'll generalize the solution to "N" DBs after that. > > Any advice or code will be very welcome? $ sqlite3 /tmp/links1.db sqlite> ATTACH '/tmp/link2.db' AS foo; sqlite> INSERT INTO URL SELECT * FROM foo; sqlite> DETACH foo; sqlite> ATTACH '/tmp/link3.db' AS foo; sqlite> INSERT INTO URL SELECT * FROM foo; lather, rinse, repeat N times. Script using your favorite language. There are many more ways this can be done. > > Regards > Ferret > ___ > 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] strcpy and sqlite3_column_text
Yes, this is a little irritating. Back in the time when I used to do C, I always used strncpy and then stuck a '0' at the end of the new string. -Original Message- From: Dave Dyer [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2008 12:25 PM To: Teg; General Discussion of SQLite Database; Teg; General Discussion of SQLite Database Subject: Re: [sqlite] strcpy and sqlite3_column_text > >strcpy(result->>ip_address,(const char*)pszData); Strcpy is one of those evil "standard" c functions that no one should every use, because it copies an unlimited and unknown amount of data into an obviously finite buffer. If some joker puts a little extra data in a sqlite IP address, and you've got a bug that could take forever to find. Standard strncpy is also pretty evil, because it doesn't guarantee a null at the end. I know you're all too smart to be so sloppy, and this little lecture is totally redundant and unnecessary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Merging N databases
Hi, I'm new to SQLite and to list !!! So ... I've "N" sqlite DBs all produced with the same version of SQLite3 and all having the same DB schema (only 1 table). I'd like to merge them all in one single DB. The merge process needs to be done as fast as possible. Let me give you how the DBs looks like in a real example. Each DB contains a uniq table called URL : CREATE TABLE if not exists URL (name VARCHAR PRIMARY KEY, nbr INTEGER); $ sqlite3 /tmp/links1.db : sqlite> select * from URL; www.foo.com|17 www.bar.com|100 www.lost.com|5 $ sqlite3 /tmp/links2.db : sqlite> select * from URL; www.lost.com|22 www.bar.com|9 www.dharma.com|1 The objective is to merge these 2 DBs in the first one "/tmp/links1.db" to get this: $ sqlite3 /tmp/links1.db : sqlite> select * from URL; www.foo.com|17 www.lost.com|27 www.bar.com|109 www.dharma.com|1 I'll generalize the solution to "N" DBs after that. Any advice or code will be very welcome? Regards Ferret ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
On Fri, Oct 10, 2008 at 12:59:31PM -0400, Igor Tandetnik scratched on the wall: > Shaun R. <[EMAIL PROTECTED]> wrote: > > I'm getting this warning when doing the following > > > > warning: pointer targets in passing argument 2 of 'strcpy' differ in > > signedness > > > > strcpy(result->ip_address,sqlite3_column_text(plineInfo, 2)); > > sqlite3_column_text returns unsigned char*. Most CRT functions expect > char* (on most implementations, plain char is signed). Igor brings up a good point. It is true that on most systems "char" is signed, but it is worth remembering that this is not universally true. This is why "char" and "signed char" are actually different types in C, unlike "int" and "signed int", which are assumed to be the exact same type. Something to remember when trying to figure out strange compiler warnings. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding index to table makes SELECT much slower. Why?
Ken and others- Thanks for your help. I have put a copy of the data and a python script that exercise the data via sqlite3 at http://oxbow.sr.unh.edu/nogoogle/wod/ for you to play with and see if these results hold on other architectures. The script is straightforward, and you can easily cut and paste the appropriate commands directly to the sqlite command line. It should run un-altered on any python version 2.5 or greater with no additional modules beyond the base install. The script access the data from three databases: 1. a database that is un-ordered, and has no index, 2. a database that is un-ordered, and has an index, 3. a database that is ordered and has an index. The databases are as described above in this thread, but truncated to reduce the time it takes to download them. The output of the test on my iMac is plume:% python test_ocean_data_script.py results on un-ordered database with no index start select select finished in 9.43625998497 seconds and found 95852 records results on un-ordered database with index on depth field start select select finished in 125.182437897 seconds and found 95852 records results on a database ordered by depth with an index on the depth field start select select finished in 0.681365013123 seconds and found 95852 records I get similar results on my linux boxes. I would be interested in hearing what you find. The data is from the World Ocean Atlas 2005 data set described in http://www.nodc.noaa.gov/OC5/WOA05/pr_woa05.html , and should be acknowledged in the event you use it for any oceanographic research. Cheers, Jamie On Thu, Oct 9, 2008 at 12:16 PM, James Pringle <[EMAIL PROTECTED]> wrote: > Hi all- > >Thanks for your many suggestions. I have tried many of your > suggestions, and found the following: > > >1. If I create the database with depth ordered from least to greatest, >and then create an index, the searches run an order of magnitude faster > than >without an index. I.e. the search for matching entries in the data base >concludes in about 9 seconds, instead of the 90 seconds it had before. >Thanks to "developir" for this suggestion! >2. searching via "rowid" makes no difference. >3. doing vacuum makes no difference, since this is a write once, read >many database. >4. ulimit on my machine is unlimited, so this was not important for me. > >5. Cache size seems to make no difference -- and the SQLite >documentation says it should only be important for DELETE's and UPDATE's. >6. I like the idea of using RTREE, however, I don't want each of the >students who use this database to have to recompile the version of SQLite >that comes with their version of python. > > Several of you, in particular Ken, suggested that I run SQL queries that > would provide some output that would be helpful in figuring out what was > going on.The database is now more than fast enough for me, so I would > not do this on my own. However, if others on the mailing list would like to > see this output in order to make SQLite better (or just for their own > curiosity), please feel free to let me know, and I can make the runs. > > I have a small subset of the data (450Mb) which exhibits the same > behaviour. The data is public (it is from the National Ocean Database), and > so if anyone wants to see it I would be happy to put it on my web server. > > Cheers, > and thanks to everyone who helped me! > Jamie Pringle > > > On Wed, Oct 8, 2008 at 6:50 PM, James Pringle <[EMAIL PROTECTED]> wrote: > > Hi- > > > > I am new to sqlite, and am having a puzzling problem. I have read > > that adding an INDEX to a sqlite table can never make a SELECT > > statement slower. However, in my case, it seems to be making the > > select statement an order of magnitude slower, which is not what I > > wanted! What could be going on? > > > > I am calling SQLite from python 2.5 from fink useing the sqlite3 > > module on OS X 10.5 with 2Gb of memory. My data base contains 32 > > million lines of ocean hydrographic data, stored on disk, with the > > table created with the following statement: > > > >CREATE TABLE hydro (lat REAL, lon REAL, year INTEGER, > >month INTEGER, day INTEGER, time REAL, cast_id INTEGER, > >depth REAL, T REAL, S REAL, water_depth REAL) > > > > When I perform a SELECT that returns about 0.6% of the data in the > > database (about 200,000 records) > > > >SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth <= > 50.99 > > > > It selects and returns the records in 82 seconds. I wished to improve > > on this time, so I created an index with the following command: > > > >CREATE INDEX hydro_indx ON hydro (depth) > > > > I then tried the SELECT command again, and read the results into > > memory, and it took 717 seconds!?! > > > > The "depth" field contains many distinct numeric values, with no > > specific value matching more then 0.5% of the data base. When I D
Re: [sqlite] strcpy and sqlite3_column_text
> >strcpy(result->>ip_address,(const char*)pszData); Strcpy is one of those evil "standard" c functions that no one should every use, because it copies an unlimited and unknown amount of data into an obviously finite buffer. If some joker puts a little extra data in a sqlite IP address, and you've got a bug that could take forever to find. Standard strncpy is also pretty evil, because it doesn't guarantee a null at the end. I know you're all too smart to be so sloppy, and this little lecture is totally redundant and unnecessary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
> >strcpy(result->>ip_address,(const char*)pszData); Strcpy is one of those evil "standard" c functions that no one should every use, because it copies an unlimited and unknown amount of data into an obviously finite buffer. If some joker puts a little extra data in a sqlite IP address, and you've got a bug that could take forever to find. Standard strncpy is also pretty evil, because it doesn't guarantee a null at the end. I know you're all too smart to be so sloppy, and this little lecture is totally redundant and unnecessary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
Hello Teg,
Dooh
strcpy(result->>ip_address,(const char*)pszData);
Is what I meant.
C
Friday, October 10, 2008, 1:04:08 PM, you wrote:
T> Hello Shaun,
T> In my mind, that's a crash waiting to happen.
T> It returns an unsigned char* and strcpy wants a signed one
T> const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
T> const unsigned char* pszData =
T> sqlite3_column_text(m_pStatement,static_cast(nCol));
T> if( !pszData )
T> {
T> return(E_ABORT);
T> }
T> strcpy(result->>ip_address,(const
T> char*)sqlite3_column_text(plineInfo, 2));
T> I'd check the return value before I used it. I didn't test the cast
T> so, you might need to tweek it. I actually do a UTF8 translation after
T> I get the text back.
T>
T> C
T> Friday, October 10, 2008, 12:25:45 PM, you wrote:
SR>> I'm getting this warning when doing the following
SR>> warning: pointer targets in passing argument 2 of 'strcpy' differ in
SR>> signedness
strcpy(result->>>ip_address,sqlite3_column_text(plineInfo, 2));
result->>>ip_address is from struct result { char ip_address[17]; };
SR>> Anybody know what needs to be done here.
SR>> ~Shaun
SR>> ___
SR>> sqlite-users mailing list
SR>> sqlite-users@sqlite.org
SR>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
Hello Shaun,
In my mind, that's a crash waiting to happen.
It returns an unsigned char* and strcpy wants a signed one
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const unsigned char* pszData =
sqlite3_column_text(m_pStatement,static_cast(nCol));
if( !pszData )
{
return(E_ABORT);
}
strcpy(result->>ip_address,(const char*)sqlite3_column_text(plineInfo,
2));
I'd check the return value before I used it. I didn't test the cast
so, you might need to tweek it. I actually do a UTF8 translation after
I get the text back.
C
Friday, October 10, 2008, 12:25:45 PM, you wrote:
SR> I'm getting this warning when doing the following
SR> warning: pointer targets in passing argument 2 of 'strcpy' differ in
SR> signedness
strcpy(result->>ip_address,sqlite3_column_text(plineInfo, 2));
result->>ip_address is from struct result { char ip_address[17]; };
SR> Anybody know what needs to be done here.
SR> ~Shaun
SR> ___
SR> sqlite-users mailing list
SR> sqlite-users@sqlite.org
SR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
Shaun R. <[EMAIL PROTECTED]> wrote: > I'm getting this warning when doing the following > > warning: pointer targets in passing argument 2 of 'strcpy' differ in > signedness > > strcpy(result->ip_address,sqlite3_column_text(plineInfo, 2)); sqlite3_column_text returns unsigned char*. Most CRT functions expect char* (on most implementations, plain char is signed). To avoid the warning, simply cast the return value of sqlite3_column_text to char*. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
On Fri, Oct 10, 2008 at 10:45:11AM +0300, Cariotoglou Mike wrote: > well, since you asked, BNF is indeed more difficult to read, BUT it is > machine-readable, which means validation tools and parsers can be built. If that's what you want then there's already the grammar that Lemon uses to generate the pasrser for SQLite3. Someone suggested that the BNF stuff is text, and therefore more easily accessible to blind users. That need not mean that BNF is the way to go. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] strcpy and sqlite3_column_text
I'm getting this warning when doing the following
warning: pointer targets in passing argument 2 of 'strcpy' differ in
signedness
strcpy(result->ip_address,sqlite3_column_text(plineInfo, 2));
result->ip_address is from struct result { char ip_address[17]; };
Anybody know what needs to be done here.
~Shaun
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] howto setup SQLite with Powershell ?
> What I'm wondering is, am I the only one who wants to use SQLite together > with Powershell? You're the first person I've heard of trying to do it. Really I think your SQLite.Net provider is incorrectly installed. can you please provide the output of the following command: gacutil /l System.Data.SQLite Mine produces the following output: Microsoft (R) .NET Global Assembly Cache Utility. Version 2.0.50727.42 Copyright (c) Microsoft Corporation. All rights reserved. The Global Assembly Cache contains the following assemblies: System.Data.SQLite, Version=1.0.60.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=x86 System.Data.SQLite, Version=1.0.53.0, Culture=neutral, PublicKeyToken=1fdb50b1b62b4c84, processorArchitecture=MSIL If you dont get any lines returned that means you don't have the ADO.Net provider installed and you'll need to install it. Download it from : http://sqlite.phxsoftware.com. On Fri, Oct 10, 2008 at 7:56 AM, Brad Stiles <[EMAIL PROTECTED]> wrote: >>> access ... dll from PowerShell ... PowerShell docs >> >> the procedure given there is to register the dll with installutil. > > The use of the word "register" implies to me that they might be > assuming a COM dll or .NET com visible assembly. I don't believe the > SQLITE3.DLL qualifies as either. :) As well, as far as I know, > InstallUtil is only capable of installing assemblies that have been > designed to be installed that way. It's not effective for original > style DLLs. > > You might try revisiting the use of one of the .NET providers by > installing it in the GAC, or perhaps even using REGASM. For those, > though, I will repeat the need for asking on the forums dedicated to > those products. If you want a robust, well supported one, Robert > Simpson's ADO.NET provider, hosted at http://sqlite.phxsoftware.com, > is excellent. I've used it in a number of programs, and it works > quite well. > > Since PowerShell is highly .NET aware, using a provider designed for > that platform seems more likely to result in success than using a > plain dll. Installed into the GAC, I suspect that provider would work > quite well under PowerShell. > >> What I'm wondering is, am I the only one who wants to use SQLite together >> with Powershell? > > Starting to look that way, isn't it? > > Brad > ___ > 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] ATTACH problem
On Oct 10, 2008, at 1:32 PM, [EMAIL PROTECTED] wrote: > > Thank you. I've finally found the problem also thanks to error > message. I haven't called sqlite3_reset() after SQLITE_BUSY so > retrying step() returned MISUSE. But what to do if I'm fetching the > rows for SELECT statement? Reset() would put me to beginning. Can > SQLITE_BUSY (or _LOCK) occur after fetching of 1st row anyway? SQLITE_BUSY can only occur before fetching the first row. > > > It would be nice to give some example of robust error handling after > sqlite_step() at the web page to show what to do for various errors. > (Errors are not described in detail in the documentation, only their > cause but not appropriate retry action). > > But users are reporting also other strange errors after > sqlite_step() which I cannot reproduce, like: > > - SQLITE_NOMEM (after "Select count(*) where...". I don't believe > there is really no free memory, I cannot see any memory leaks in > application, whole database file has maybe 10-20 MB). How to handle > this? SQLITE_NOMEM is only returned when SQLite is unable to allocate the memory it needs. > > - SQLITE_CANTOPEN - I've added the same handler for this - > Sleep(...), retry. Is it correct? Do I need to call reset() too? Usually this is returned when the underlying operation system call is unable to open the file. Often that means a file permission problem, in which case sleeping is unlikely to help. This can also be returned if the filename is too long. > > > - SQLITE_ERROR after COMMIT although I'm using prepare_v2() which is > documented to return specific error code instead of SQLITE_ERROR. > reset() returned the same code. SQLITE_ERROR is returned if there is a syntax error in the SQL. Often sqlite3_errmsg() will provide more information. What does it say? > > > Thank you! > > On May 13, 2008, at 7:57 AM, [EMAIL PROTECTED] wrote: > >> >> I‘m running ATTACH database on SQlite 3.5.8 on windows. >> I‘m opening one database >> file and attaching the second one. >> On my computer it works, but it fails at other computer. The >> database is opened in more >> than one thread (it is failing in worker thread). >> sqlite3_step() returns SQLITE_MISUSE , but _sqlite3_errmsg() returns >> "database is locked". >> What can be the problem? > > My guess is that SQLITE_MISUSE is being returned when you try to do > something with a prepared statement that has been finalized or a > database connection that has been closed. The error checking to > detect these things and return SQLITE_MISUSE is probabilistic - it is > not guaranteed to succeed. But when it does, it is helpful in finding > application errors. > > SQLITE_MISUSE returns do not set the error message. So > sqlite3_errmsg() will continue to return the previous error, whatever > that was. > > D. Richard Hipp > [EMAIL PROTECTED] > > > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ATTACH problem
Thank you. I've finally found the problem also thanks to error message. I haven't called sqlite3_reset() after SQLITE_BUSY so retrying step() returned MISUSE. But what to do if I'm fetching the rows for SELECT statement? Reset() would put me to beginning. Can SQLITE_BUSY (or _LOCK) occur after fetching of 1st row anyway? It would be nice to give some example of robust error handling after sqlite_step() at the web page to show what to do for various errors. (Errors are not described in detail in the documentation, only their cause but not appropriate retry action). But users are reporting also other strange errors after sqlite_step() which I cannot reproduce, like: - SQLITE_NOMEM (after "Select count(*) where...". I don't believe there is really no free memory, I cannot see any memory leaks in application, whole database file has maybe 10-20 MB). How to handle this? - SQLITE_CANTOPEN - I've added the same handler for this - Sleep(...), retry. Is it correct? Do I need to call reset() too? - SQLITE_ERROR after COMMIT although I'm using prepare_v2() which is documented to return specific error code instead of SQLITE_ERROR. reset() returned the same code. Thank you! On May 13, 2008, at 7:57 AM, [EMAIL PROTECTED] wrote: > > I‘m running ATTACH database on SQlite 3.5.8 on windows. > I‘m opening one database > file and attaching the second one. > On my computer it works, but it fails at other computer. The > database is opened in more > than one thread (it is failing in worker thread). > sqlite3_step() returns SQLITE_MISUSE , but _sqlite3_errmsg() returns > "database is locked". > What can be the problem? My guess is that SQLITE_MISUSE is being returned when you try to do something with a prepared statement that has been finalized or a database connection that has been closed. The error checking to detect these things and return SQLITE_MISUSE is probabilistic - it is not guaranteed to succeed. But when it does, it is helpful in finding application errors. SQLITE_MISUSE returns do not set the error message. So sqlite3_errmsg() will continue to return the previous error, whatever that was. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ATTACH problem
On May 13, 2008, at 7:57 AM, [EMAIL PROTECTED] wrote: > > I‘m running ATTACH database on SQlite 3.5.8 on windows. > I‘m opening one database > file and attaching the second one. > On my computer it works, but it fails at other computer. The > database is opened in more > than one thread (it is failing in worker thread). > sqlite3_step() returns SQLITE_MISUSE , but _sqlite3_errmsg() returns > "database is locked". > What can be the problem? My guess is that SQLITE_MISUSE is being returned when you try to do something with a prepared statement that has been finalized or a database connection that has been closed. The error checking to detect these things and return SQLITE_MISUSE is probabilistic - it is not guaranteed to succeed. But when it does, it is helpful in finding application errors. SQLITE_MISUSE returns do not set the error message. So sqlite3_errmsg() will continue to return the previous error, whatever that was. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] howto setup SQLite with Powershell ?
>> access ... dll from PowerShell ... PowerShell docs > > the procedure given there is to register the dll with installutil. The use of the word "register" implies to me that they might be assuming a COM dll or .NET com visible assembly. I don't believe the SQLITE3.DLL qualifies as either. :) As well, as far as I know, InstallUtil is only capable of installing assemblies that have been designed to be installed that way. It's not effective for original style DLLs. You might try revisiting the use of one of the .NET providers by installing it in the GAC, or perhaps even using REGASM. For those, though, I will repeat the need for asking on the forums dedicated to those products. If you want a robust, well supported one, Robert Simpson's ADO.NET provider, hosted at http://sqlite.phxsoftware.com, is excellent. I've used it in a number of programs, and it works quite well. Since PowerShell is highly .NET aware, using a provider designed for that platform seems more likely to result in success than using a plain dll. Installed into the GAC, I suspect that provider would work quite well under PowerShell. > What I'm wondering is, am I the only one who wants to use SQLite together > with Powershell? Starting to look that way, isn't it? Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ATTACH problem
Am 09.10.2008 um 18:21 schrieb [EMAIL PROTECTED]: > > Ok it seems last time I've posted too many errors so I'll try to ask > about this one. Maybe other are just consequences of this. I've > finally received this error also on my computer, but only in release > build. > > - windows XP, sqlite 3.6.3, VS 2005 > - sqlite3_threadsafe() -> 1 > - 20 worker threads, each opens db, attaches another db, does some > writes (into first db only), closes, thread exit, new thread start > > After somer time the "ATTACH" fails with error code SQLITE_MISUSE > and error message is that database is locked. > Why does it return MISUSE and not LOCKED or busy? > > Any ideas what can be wrong? Is this bug in SQLite or compiler or am > I missing something? Do you happen to have a transaction open when trying to attach the database? IIRC, this was a reason I got a SQLITE_MISUSE once when trying to attach a database ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] howto setup SQLite with Powershell ?
> It's very possible you're the first one. then google will not help > Google is your friend. Not in this case. Be sure that I look around extensively before starting a thread. And so I already found your links. Both are rather nice approaches, but they depend on (an) installed and working database[s]. And that is what I'm still looking for. Original-Nachricht > Datum: Thu, 9 Oct 2008 17:28:40 -0500 > Von: "Ribeiro, Glauber" <[EMAIL PROTECTED]> > An: "General Discussion of SQLite Database" > Betreff: Re: [sqlite] howto setup SQLite with Powershell ? > It's very possible you're the first one. > > How about adapting something like this: > http://theessentialexchange.com/blogs/michael/archive/2008/01/07/multi-p > latform-database-access-with-powershell.aspx > > Even closer to what you want: > http://mcmanus.typepad.com/grind/2007/09/data-driven-asp.html > > Google is your friend. > > g > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 09, 2008 2:58 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] howto setup SQLite with Powershell ? > > [...] > > What I'm wondering is, am I the only one who wants to use SQLite > together with Powershell? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
D. Richard Hipp schrieb: > I suppose that announcements of this kind should really only be posted > on the sqlite-dev mailing list, not on sqlite-users. So I will likely > reframe from making subsequent pre-release announcements on sqlite- > users and use only sqlite-dev for such purposes. If you think this > revised policy is out of line with the usual practice on other > projects, please let me know. I think its nice to see this on sqlite-users also. If i mostly use/embed SQLite into some project i probably don't really need the extra details added by sqlite-dev, but its nice to get heads up notice about upcoming releases so i can schedule or reschedule needed work (e.g. no reason to do an upgrade to 3.6.3 when knowing 3.6.4 will come just days later.) But if the plans are still rather vague and could change quite easily your probably right that sqlite-dev is the right place. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
well, since you asked, BNF is indeed more difficult to read, BUT it is machine-readable, which means validation tools and parsers can be built. so, if you did have the BNF grammar *available* (as opposed to part of the web documentation), I personally would be happier. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
