Re: [sqlite] commas in columns and temporary tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Dennis, | | It won't because the temp tables created by your program ar private to its | database connection. They are not visible through the database connection | opened by sqlite.exe. You can verify this by running two copies of | sqlite.exe in seperate command prompt windows (both looking at the same db | file). You can create permanent tables in one and they will be displayed in | the other, but temp tables will only be displayed by the program that | created them. Aha! That's the other thing I was wondering about... I added a logger to watch which queries were going through and discovered that the TEMPORARY TABLE was being created and also was sticking around. Turns out that I had the wrong column name, and so it was segfaulting. Thanks so much for your help! I really appriciate it :). - -- Taj -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBvdq0qFV3Jf1Ui+IRApvqAJ0e3aUXryQzf0busuNcs9VzUNjvCgCgvQLz pKDWM6CL3LSOk8SCYZy8+xc= =ZsMg -END PGP SIGNATURE-
Re: [sqlite] commas in columns and temporary tables
Taj Morton wrote: > http://www.torry.net/db/direct/db_directsql/sqlitedb.zip should work. > Sorry about that. > I will check this out. > No go :(. I used breakpoints and checked right after the first > DB.ExecSQL, then looked at .tables from sqlite.exe...it didn't show up > their either. It won't because the temp tables created by your program ar private to its database connection. They are not visible through the database connection opened by sqlite.exe. You can verify this by running two copies of sqlite.exe in seperate command prompt windows (both looking at the same db file). You can create permanent tables in one and they will be displayed in the other, but temp tables will only be displayed by the program that created them.
Re: [sqlite] commas in columns and temporary tables
Dennis Cote wrote: Taj Morton wrote: Dennis, Indeed. Actually, I'm using an OO wrapper around that library, TSQLiteDB (http://homepages.borland.com/torry/db/direct/db_directsql/tsqlite.zip). This link gives me a 404 error so I can't look at the code myself. http://www.torry.net/db/direct/db_directsql/sqlitedb.zip should work. Sorry about that. What do DB.Open and DB.Close do? I think maybe they should surround the other code as well, like this: procedure TSQLiteDB.Open; begin if FSQL <> '' then begin FDb.Query(FSQL, FData); CreateFields; end; // if FBof := True; FEof := False; FRecordCount := FData.Count; if FRecordCount > 0 then FCurRec := 1 else Exit; RemoveQuotation; RetrieveRecord; FActive := True; FFieldCount := FFields.Count; end; procedure TSQLiteDB.Close; begin FData.Clear; FFields.Clear; FCurRec := -1; FRecordCount := 0; FBof := True; FEof := True; FActive := False; end; So, no destruction is being done of the FDB object (that's done in TSQLiteDB.Free). // DB is a TSQLiteDB created on FormCreate DB.Open; DB.SQL:='CREATE TEMPORARY TABLE cname2cid(cid INTEGER,name VARCHAR(512));'; DB.ExecSQL; DB.SQL:='SELECT id,firstname,lastname,business from customers;'; //... DB.Close; No go :(. I used breakpoints and checked right after the first DB.ExecSQL, then looked at .tables from sqlite.exe...it didn't show up their either. Thanks! -- Taj
Re: [sqlite] commas in columns and temporary tables
Taj Morton wrote: > Dennis, > > Indeed. Actually, I'm using an OO wrapper around that library, > TSQLiteDB > (http://homepages.borland.com/torry/db/direct/db_directsql/tsqlite.zip). This link gives me a 404 error so I can't look at the code myself. > > Here's my TEMPORARY TABLE code: > // DB is a TSQLiteDB created on FomCreate > DB.SQL:='CREATE TEMPORARY TABLE cname2cid(cid INTEGER,name > VARCHAR(512));'; DB.ExecSQL; > > DB.SQL:='SELECT id,firstname,lastname,business from customers;'; > DB.Open; > //... > DB.Close; What do DB.Open and DB.Close do? I think maybe they should surround the other code as well, like this: // DB is a TSQLiteDB created on FomCreate DB.Open; DB.SQL:='CREATE TEMPORARY TABLE cname2cid(cid INTEGER,name VARCHAR(512));'; DB.ExecSQL; DB.SQL:='SELECT id,firstname,lastname,business from customers;'; //... DB.Close; Let me know if this works.
Re: [sqlite] commas in columns and temporary tables
Dennis, From your description it sounds like you are using the Delphi wrapper listed in the wiki as: Delphi class for SQLite. http://www.torry.net/db/direct/db_directsql/tsqlite.zip Indeed. Actually, I'm using an OO wrapper around that library, TSQLiteDB (http://homepages.borland.com/torry/db/direct/db_directsql/tsqlite.zip). I've read through the code of the the wrapper, and the only time that the FDB (TSQLite object for TSQLiteDB) is destroyed is when the TSQLiteDB object is destroyed as well... destructor TSQLiteDB.Destroy; begin FreeAndNil(FDb); FreeAndNil(FData); inherited Destroy; end; Here's my TEMPORARY TABLE code: // DB is a TSQLiteDB created on FomCreate DB.SQL:='CREATE TEMPORARY TABLE cname2cid(cid INTEGER,name VARCHAR(512));'; DB.ExecSQL; DB.SQL:='SELECT id,firstname,lastname,business from customers;'; DB.Open; //... DB.Close; As far as I can tell, cname2cid is created and then destroyed right away. It's created at the beginning of DB.ExecSQL, and then destroyed at the end of ExecSQL. BUT, reading the ExecSQL code, I see no place where FDB is destroyed: procedure TSQLiteDB.ExecSQL; begin if FSQL <> '' then FDb.Query(FSQL, nil); end; So I don't know what to make of it... Maybe I could make FDB public and manually access it, but actually getting TEMPORARY TABLEs to work with TSQLiteDB would be neat... Thanks for all your help so far, any ideas about what's causing this? If not, guess I'll just go back and use TSQLite ;). -- Taj
Re: [sqlite] commas in columns and temporary tables
Taj Morton wrote:
> Dennis Cote wrote:
>
>> Taj,
>>
>> I'm not sure what wrapper you are using, but the sqlite.dll provided
>> by SQLite has two API functions that are used to open and close the
>> database connection. You use sqlite3_open() to open the connection,
>> and sqlite3_close() to close it. Between these two calls you can
>> execute as many queries as you want, either using sqlite3_exec() or
>> the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() APIs.
>>
>>
> Dennis,
> I've done some testing and the wrapper and it appears that TEMPORARY
> TABLEs don't stick around between sqlite_exec's :(. (I'm using SQLite
> 2.8.15, and thus not using the sqlite3_* functions, because the
> TSQLite wrapper uses the SQLite 2). I'm not really sure what's going
> on... It appears that the Query function just calles sqlite_exec. If
> I do this: DB.SQL:='CREATE TEMPORARY TABLE cname2cid(id INTEGER,name
> VARCHAR(512))'; DB.ExecSQL;
>
> DB.SQL does this:
> ...
> SQLite_Exec(fSQLite, PChar(Sql), @ExecCallback, Self, fPMsg);
> ...
>
> Other queries don't know about cname2cid...even the command line
> doesn't know about it. Of course, if I CREATE TEMPORARY TABLE from the
> commandline, then queries from anywhere (either from the wraper/DLL or
> the command line)...
>
> Any ideas?
>
> Thanks for the help,
Taj,
>From your description it sounds like you are using the Delphi wrapper listed
in the wiki as:
Delphi class for SQLite.
http://www.torry.net/db/direct/db_directsql/tsqlite.zip
I'm not sure since this class doesn't have an ExecSQL method or a pulic SQL
property which you mention. If it is, I have looked at this Delphi class and
believe it should work correctly with SQLite 2.8 and temp tables.
If you do something like this:
procedure Test();
var
MyDB: TSQLite;
Table: TStringList;
begin
//sqlite db is opened here
MyDB := TSQLite.Create('MyDbFile.db');
//create temp table
MyDB.Query('create temp table t(a, b)');
//write into temp table
MyDB.Query('insert into t values (1,2)');
MyDB.Query('insert into t values (3,4)');
//read from temp table
MyDB.Query('select * from t', Table);
//do something with table here
//sqlite db is closed here by destuctor when MyDB goes out of scope
end;
I suspect that your TSQLite variable does not have a long enough lifetime.
It must persist between your calls to the Query method.
I hope this helps.
Dennis Cote
Re: [sqlite] commas in columns and temporary tables
Dennis Cote wrote: Taj, I'm not sure what wrapper you are using, but the sqlite.dll provided by SQLite has two API functions that are used to open and close the database connection. You use sqlite3_open() to open the connection, and sqlite3_close() to close it. Between these two calls you can execute as many queries as you want, either using sqlite3_exec() or the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() APIs. Dennis, I've done some testing and the wrapper and it appears that TEMPORARY TABLEs don't stick around between sqlite_exec's :(. (I'm using SQLite 2.8.15, and thus not using the sqlite3_* functions, because the TSQLite wrapper uses the SQLite 2). I'm not really sure what's going on... It appears that the Query function just calles sqlite_exec. If I do this: DB.SQL:='CREATE TEMPORARY TABLE cname2cid(id INTEGER,name VARCHAR(512))'; DB.ExecSQL; DB.SQL does this: ... SQLite_Exec(fSQLite, PChar(Sql), @ExecCallback, Self, fPMsg); ... Other queries don't know about cname2cid...even the command line doesn't know about it. Of course, if I CREATE TEMPORARY TABLE from the commandline, then queries from anywhere (either from the wraper/DLL or the command line)... Any ideas? Thanks for the help, -- Taj
Re: [sqlite] commas in columns and temporary tables
Taj Morton wrote: > "The database connection is closed"...means...? If I'm using the > SQLite DLL, when is a connection closed? Is it started when I call > Open, and closed when I call Close (that would mean that a connection > only lasts for one query)... Can you please explain? > Taj, I'm not sure what wrapper you are using, but the sqlite.dll provided by SQLite has two API functions that are used to open and close the database connection. You use sqlite3_open() to open the connection, and sqlite3_close() to close it. Between these two calls you can execute as many queries as you want, either using sqlite3_exec() or the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() APIs. If you can provide more details about the wrapper you are using, I may be able to confirm that the Open and Close functions you mention do map directly to the native sqlite functions. Dennis Cote
Re: [sqlite] commas in columns and temporary tables
Hi Ulrik, Now, that's all fine and everything for interactive SQL, but when I'm using this in a program (written in Delphi), the comma messes up the returned values (since they are comma seperated) You can do escaping that replaces the comma with something else when writing to the table, then converts it back after you have gotten the info from the comma-separated format. For example, URLs regularly use %XX to escape characters such as space, where XX is the hexadecimal ASCII value. So "space" (ASCII 32) will be "%20". Just remember to escape not only the comma, but also the percentage sign or whatever signals your escape sequences. Ahh! That's a good idea! What characters would I need to escape? Comma, obviously, percentage, what else would freak SQLite out? I guess that when stuff is returned from the database or inserted into it, I should escape it or unescape the text? Thanks for the tip! -- Taj
Re: [sqlite] commas in columns and temporary tables
Dennis Cote wrote: Taj Morton wrote: Now, I've got two questions. The first is that I have a column (company) in a customers table. The problem is that this column has a comma in it for some rows. That's no good :(. Does anyone have any suggestions on what to do about this problem? I'm using SQLite 2.8.15. If the Delphi interface handles quoted strings in the CSV data then you can quote the columns that may contain commas. Use something like this: SELECT lastname, '"' || bussiness || '"' FROM customers ... The || operator is the concatenation operator. This adds double quotes around all the bussiness name strings. Right. Unfortunately, the SQLite header uses regular expressions to split the results. However, I can probably muck around in the header file and make that work... The obvious problem with that I have to update all my SQL queries... Temporary tables are destroyed when the database connection is closed or when the user explicitly drops them. "The database connection is closed"...means...? If I'm using the SQLite DLL, when is a connection closed? Is it started when I call Open, and closed when I call Close (that would mean that a connection only lasts for one query)... Can you please explain? I hope this helps. Thanks for the suggestions! -- Taj
Re: [sqlite] commas in columns and temporary tables
Taj, > Now, I've got two questions. > The first is that I have a column (company) in a customers table. The > problem is that this column has a comma in it for some rows. For example: > > sqlite> select lastname,business FROM customers WHERE id=449; > Toleser|St Lawrence University, Biology Dept. > > Now, that's all fine and everything for interactive SQL, but when I'm > using this in a program (written in Delphi), the comma messes up the > returned values (since they are comma seperated). So, the Delphi > interface ends up returning: > Toleser|St Lawrence University|Biology Dept. > > That's no good :(. Does anyone have any suggestions on what to do about > this problem? I'm using SQLite 2.8.15. You can do escaping that replaces the comma with something else when writing to the table, then converts it back after you have gotten the info from the comma-separated format. For example, URLs regularly use %XX to escape characters such as space, where XX is the hexadecimal ASCII value. So "space" (ASCII 32) will be "%20". Just remember to escape not only the comma, but also the percentage sign or whatever signals your escape sequences. > As for my question about temporary tables: How long does SQLite keep the > temporary tables around? Only for 1 query? Or until the table hasn't > been modified for X amount of time? Or something I haven't thought of > yet... Can't answer this one, sorry. Ulrik -- Ulrik Petersen, Denmark
[sqlite] commas in columns and temporary tables
Hi All, First off, I've been using SQLite in an open-source POS (inventory) program and am quite happy with it. Thank you all developers and patch submitters! Now, I've got two questions. The first is that I have a column (company) in a customers table. The problem is that this column has a comma in it for some rows. For example: sqlite> select lastname,business FROM customers WHERE id=449; Toleser|St Lawrence University, Biology Dept. Now, that's all fine and everything for interactive SQL, but when I'm using this in a program (written in Delphi), the comma messes up the returned values (since they are comma seperated). So, the Delphi interface ends up returning: Toleser|St Lawrence University|Biology Dept. That's no good :(. Does anyone have any suggestions on what to do about this problem? I'm using SQLite 2.8.15. As for my question about temporary tables: How long does SQLite keep the temporary tables around? Only for 1 query? Or until the table hasn't been modified for X amount of time? Or something I haven't thought of yet... Thank, and keep up the good work! -- Taj
