[sqlite] Problems with Wrapper
Hi, I have some trouble to get SQLite Java Wraper running on my openSuSE10. Here's what I did: 1) I've downloaded the sqlite3-3.3.5.bin.gz from sqlite.org and extracted it into my personal folder: I have now /sqlite/sqlite3-3.3.5.bin there. Creating a test db works 2) I've downloaded the wrapper javasqlite-20050608.tar.gz from http://www.ch-werner.de/javasqlite/ and unpacked it somewhere on the desktop 3) Whenever I try to run ./configure, even with the dir options that tell where to find sqlite, I get the error NO SQLite found. This occurs even if I put the SQLite bin into the wrapper folder. Any ideas? tia pasquale
RE: [sqlite] Transactions and 'library routine called out of sequence' locks the database
Hi D. Richard, Your comments / suggestions made me think and widen my exception tracking - and it does indeed seem that it is caused by statement that fails with a NULL-pointer exception when sqlite3_prepare is called. Why this is I need to figure out - but I suspect that the Finisar.SQLite wrapper maybe has a weird bug somewhere which causes sqlite3_prepare to be called with a NULL-pointer (propably the string containing the sql statement). Why thousands of sql-statements can be run before the exception is thrown I do not know - but at least I got a bit further. Thank you for you help :) /Michael -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 9. maj 2006 12:26 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Transactions and 'library routine called out of sequence' locks the database Michael B. Hansen [EMAIL PROTECTED] wrote: However, I keep getting 'library routine called out of sequence' at random interval. This is caused when you do things like try to use a database connection that has already been closed or try to use a prepared statement that has already been finalized or by trying to use the same database handle at the same time in two different threads. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] checking 'PRAGMA integrity_check'
Background: Monotone has a db check command, that goes through and verifies that everything in the database is valid and accessible. While our existing tests basically hit every row of every table, so most things should be caught, we didn't previously call sqlite's PRAGMA integrity_check. In principle, I guess, this could mean that subtle things like a corrupted index that only caused issues on certain access patterns could be missed... So, we just committed a patch to call the PRAGMA as part of the db check process, but now this raises the issue of how we can _test_ this new code path. So, my question is -- what is a simple way to reliably corrupt a sqlite db so that PRAGMA integrity_check will fail? Thanks, -- Nathaniel -- ...these, like all words, have single, decontextualized meanings: everyone knows what each of these words means, everyone knows what constitutes an instance of each of their referents. Language is fixed. Meaning is certain. Santa Claus comes down the chimney at midnight on December 24. -- The Language War, Robin Lakoff
Re: [sqlite] VB.Net DataSet DataGrid with ADO.Net Provider
John Newby wrote: Hi, I am trying to access a SQLite database using the ADO.Net provider and bring all the data back in a tabular form, I can bring back the data using a datareader but I don't know if I can put the data into a datset or a datagrid. Does anyone know if you can do this? Many thanks. John. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 9/05/2006 Hello, I use a virtual listview directly on top of sqlite3, its in c++ but it works very well (even on PDA). It could easily be turned in an activeX (its ATL/WTL, no MFC). Best wishes. -- Noël Frankinet Gistek Software SA http://www.gistek.net
RE: [sqlite] VB.Net DataSet DataGrid with ADO.Net Provider
Hi John, There is a number of .NET-wrappers for SQLite (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers). I personally use Finisar.SQLite (http://adodotnetsqlite.sourceforge.net/) which performs acceptable (although I currently have one single issue with it - look for the subject Transactions and 'library routine called out of sequence' locks the database in this list). /Michael -Original Message- From: John Newby [mailto:[EMAIL PROTECTED] Sent: 9. maj 2006 17:18 To: sqlite-users@sqlite.org Subject: [sqlite] VB.Net DataSet DataGrid with ADO.Net Provider Hi, I am trying to access a SQLite database using the ADO.Net provider and bring all the data back in a tabular form, I can bring back the data using a datareader but I don't know if I can put the data into a datset or a datagrid. Does anyone know if you can do this? Many thanks. John.
RE: [sqlite] accurate progress indication
All, I have implemented the method suggested and it works well for me. I am running on an embedded device and the general performance we get using SQlite is not great. Additionally the database is not that large compared to some of the other users' needs. Our database can only ever be a mamixmum of 25MB. And in most cases it will never reach this size. On our PC simulator all commands execute quickly including our quick delete and indeed the progress bar shoots up to 100% in a second or two. The IO also completes quickly. On our target unit, it takes alot longer, deleting approx 800 records from one table and 2400 from another table will take approx 1min 30 secs. The bulk of this time is before the COMMIT, therefore before the IO stage I believe, the COMMIT only takes a few seconds. So in this case we would like a progress indicator to represent a reasonably accurate representation of progress so user will not think the unit has locked up. Currently the method specified by Dr Hipp seems to work well for us. Thanks for all your help and interest. Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 May 2006 17:44 To: sqlite-users@sqlite.org Subject: Re: [sqlite] accurate progress indication Dennis Cote [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: Yes. To get reasonable performance you will need to enclose the SELECT and all the individual DELETEs within a BEGIN...COMMIT. Richard, Won't this quickly run through the select and delete commands, so that the progress bar quickly reaches 100%, and then sit there for some time while the I/O is done during the commit? Yes. So the answer to the original question becomes that there is not good way to give a progress bar on a big delete. -- D. Richard Hipp [EMAIL PROTECTED] DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Re: [sqlite] VB.Net DataSet DataGrid with ADO.Net Provider
Hi Michael, I am already using the Finisar.SQLite wrapper, I can successfully connect to the database, create a table, insert data and display it to the console or in a message box, but I am unsure as how to display it into a grid (like ms access) On 10/05/06, Michael B. Hansen [EMAIL PROTECTED] wrote: Hi John, There is a number of .NET-wrappers for SQLite (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers). I personally use Finisar.SQLite (http://adodotnetsqlite.sourceforge.net/) which performs acceptable (although I currently have one single issue with it - look for the subject Transactions and 'library routine called out of sequence' locks the database in this list). /Michael -Original Message- From: John Newby [mailto:[EMAIL PROTECTED] Sent: 9. maj 2006 17:18 To: sqlite-users@sqlite.org Subject: [sqlite] VB.Net DataSet DataGrid with ADO.Net Provider Hi, I am trying to access a SQLite database using the ADO.Net provider and bring all the data back in a tabular form, I can bring back the data using a datareader but I don't know if I can put the data into a datset or a datagrid. Does anyone know if you can do this? Many thanks. John.
Re: [sqlite] Disk I/o Error : SQLITE_IOERR while performing Select/Delete on database
deepali rajale [EMAIL PROTECTED] wrote: I am getting SQLITE_IOERR error while trying to perform select or delete operation on a record when Database has more than 3000 records. The table is likely to grow to 1 records. Can someone give any pointers to the solution. An SQLITE_IOERR indicates that a disk I/O operation (a read or a write) failed. This indicates some kind of problem with your embedded hardware. Or perhaps your custom OS interface layer is not working quite right. Or perhaps you are trying to use one of the OS interface layers for Unix or Windows on an embedded platform that does not fully support the Unix or Win32 interface. What operating system are you running on your embedded platform? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] checking 'PRAGMA integrity_check'
Nathaniel Smith [EMAIL PROTECTED] wrote: So, my question is -- what is a simple way to reliably corrupt a sqlite db so that PRAGMA integrity_check will fail? For the SQLite regression tests, we open the database file separately using fopen() and then write bad stuff into the middle of the file. Here is a recipe for generating some corruption that PRAGMA integrity_check will detect but which will not cause lasting damage to the database - the damage can be repaired by running REINDEX - and will not cause an SQLITE_CORRUPT error. 1. Create a new table: CREATE TABLE test1(x UNIQUE); The UNIQUE argument is important because we want an index for this test. 2. Look at the SQLITE_MASTER table to find the ROOTPAGE for this table. Use fopen() to make a copy of this page, taking care to remember that SQLite page numbers begin with 1, not 0. 3. Insert one small row into the test table: INSERT INTO test1 VALUES(1); Commit this change. 4. Overwrite the rootpage of the test table with the copy of that page that was saved in step 2. This effectively deletes the entry you just inserted but without updating the index - so that the index and the table are now out of sync. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Possible bug with non-standard characters in column names
Creating a table like this: CREATE TABLE test1 (loader_id INTEGER PRIMARY KEY, loader_status VARCHAR(255)); ALTER TABLE test1 ADD name VARCHAR(255); ALTER TABLE test1 ADD straße VARCHAR(255); ALTER TABLE test1 ADD plz VARCHAR(255); Raises an SQL error: malformed database schema - near plz: syntax error The suspected bug is that the ALTER TABLE that adds straße should raise the error, not the following statement? Cheers, Andreas Andreas Goetz Accenture Communications High Tech cell +49 175 576 2383 [EMAIL PROTECTED] This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
[sqlite] Problem importing csv file
I can't seem to get an 8MB csv import a comma-separated file into Sqlite 3.3.5. The csv in question contains only three columns of comma-delimited data, with the first line of the file containing the column headings: Time,Easting,Northing 184102229,651807.39,5165628.749959 184102230,651807.890587,5165628.680512 184102231,651807.890878,5165628.668937 184102232,651807.890587,5165628.680512 184102233,651807.890587,5165628.680512 184102234,651807.890587,5165628.680512 184102235,651807.890296,5165628.692087 184102236,651807.890587,5165628.680512 184102237,651807.892917,5165628.587916 Here's the import syntax I used: $ sqlite3 SQLite version 3.3.5 Enter .help for instructions sqlite sqlite .separator , sqlite sqlite .import Opilio_2005030_Nav_UTM20_1Sec.csv_test Opilio_2005030_Nav_UTM20_1Sec Segmentation fault $ Any ideas what I'm doing wrong here? ~ Eric.
Re: [sqlite] accurate progress indication
On 5/10/06, Allan, Mark [EMAIL PROTECTED] wrote: On our target unit, it takes alot longer, deleting approx 800 records from one table and 2400 from another table will take approx 1min 30 secs. The bulk of this time is before the COMMIT, therefore before the IO stage I believe, the COMMIT only takes a few seconds. So in this case we would like a progress indicator to represent a reasonably accurate representation of progress so user will not think the unit has locked up. Do you have an index on your tables that allows Sqlite to find the rows to delete quickly?
Re: [sqlite] how to speed up da insertion?
On 5/9/06, Will [EMAIL PROTECTED] wrote: I insert like followings : rc = sqlite3_prepare(m_pDB, szSQL, -1, pStmt, 0); sqlite3_bind_int64(pStmt, 1, m_pTBDataDBData-lTime); ... rc = sqlite3_step(pStmt); rc =sqlite3_finalize(pStmt); Putting your insert statements inside a transaction will speed them up a lot: begin transaction; insert into mytable values( '10:30 2004/04/01'); insert into mytable values( '10:31 2004/04/01'); insert into mytable values( '10:32 2004/04/01'); insert into mytable values( '10:33 2004/04/01'); commit; A large number of indexes on the tables can also slow down inserts.
[sqlite] Error message: too much SQL parameters
I'm inserting into a table with 71 columns, trying to populate all columns in one (prepared) statement with JDBC. I'm receiving this error message: SQLITE.Exception: too much SQL parameters. Any ideas? Thanks, Andi This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Re: [sqlite] Error message: too much SQL parameters
[EMAIL PROTECTED] wrote: I'm inserting into a table with 71 columns, trying to populate all columns in one (prepared) statement with JDBC. I'm receiving this error message: SQLITE.Exception: too much SQL parameters. Any ideas? Look in your JDBC bindings, not in the SQLite core. SQLite did not generate this error message. -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] Problem importing csv file
First of all, remove the header line from your CSV. SQLite expects only data, no headers. -Original Message- From: Patton, Eric [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 3:04 PM To: 'sqlite-users@sqlite.org' Subject: [sqlite] Problem importing csv file I can't seem to get an 8MB csv import a comma-separated file into Sqlite 3.3.5. The csv in question contains only three columns of comma-delimited data, with the first line of the file containing the column headings: Time,Easting,Northing 184102229,651807.39,5165628.749959 184102230,651807.890587,5165628.680512 184102231,651807.890878,5165628.668937 184102232,651807.890587,5165628.680512 184102233,651807.890587,5165628.680512 184102234,651807.890587,5165628.680512 184102235,651807.890296,5165628.692087 184102236,651807.890587,5165628.680512 184102237,651807.892917,5165628.587916 Here's the import syntax I used: $ sqlite3 SQLite version 3.3.5 Enter .help for instructions sqlite sqlite .separator , sqlite sqlite .import Opilio_2005030_Nav_UTM20_1Sec.csv_test Opilio_2005030_Nav_UTM20_1Sec Segmentation fault $ Any ideas what I'm doing wrong here? ~ Eric.
RE: [sqlite] Problem importing csv file
Andre, I receive the same seg-fault if the headers are removed. I had initially tried to import without headers, and added them after the import was unsuccessful. -- Eric Patton [EMAIL PROTECTED] -Original Message- From: André Goliath [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 10:44 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Problem importing csv file First of all, remove the header line from your CSV. SQLite expects only data, no headers. -Original Message- From: Patton, Eric [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 3:04 PM To: 'sqlite-users@sqlite.org' Subject: [sqlite] Problem importing csv file I can't seem to get an 8MB csv import a comma-separated file into Sqlite 3.3.5. The csv in question contains only three columns of comma-delimited data, with the first line of the file containing the column headings: Time,Easting,Northing 184102229,651807.39,5165628.749959 184102230,651807.890587,5165628.680512 184102231,651807.890878,5165628.668937 184102232,651807.890587,5165628.680512 184102233,651807.890587,5165628.680512 184102234,651807.890587,5165628.680512 184102235,651807.890296,5165628.692087 184102236,651807.890587,5165628.680512 184102237,651807.892917,5165628.587916 Here's the import syntax I used: $ sqlite3 SQLite version 3.3.5 Enter .help for instructions sqlite sqlite .separator , sqlite sqlite .import Opilio_2005030_Nav_UTM20_1Sec.csv_test Opilio_2005030_Nav_UTM20_1Sec Segmentation fault $ Any ideas what I'm doing wrong here? ~ Eric.
RE: [sqlite] accurate progress indication
Jay, Yes there is a index on the date column. The delete operation is on date, specifically dates older than a date entered by the user (so he/she can clear out old records and keep newer ones). I don't think the time taken is slowed by the time SQLite takes to find the rows as I do a SELECT COUNT with the same query and this does not take as long as the DELETE. I don't know why it takes so long, I dont think it is the writing to the file as this should only happen on COMMIT and I can see in debug output that the COMMIT only takes a few seconds. It is before COMMIT that takes the time. Mark -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 10 May 2006 14:10 To: sqlite-users@sqlite.org Subject: Re: [sqlite] accurate progress indication On 5/10/06, Allan, Mark [EMAIL PROTECTED] wrote: On our target unit, it takes alot longer, deleting approx 800 records from one table and 2400 from another table will take approx 1min 30 secs. The bulk of this time is before the COMMIT, therefore before the IO stage I believe, the COMMIT only takes a few seconds. So in this case we would like a progress indicator to represent a reasonably accurate representation of progress so user will not think the unit has locked up. Do you have an index on your tables that allows Sqlite to find the rows to delete quickly? DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Re: [sqlite] Problem importing csv file
Time,Easting,Northing 184102229,651807.39,5165628.749959 184102230,651807.890587,5165628.680512 184102231,651807.890878,5165628.668937 184102232,651807.890587,5165628.680512 184102233,651807.890587,5165628.680512 184102234,651807.890587,5165628.680512 184102235,651807.890296,5165628.692087 184102236,651807.890587,5165628.680512 184102237,651807.892917,5165628.587916 Here's the import syntax I used: $ sqlite3 SQLite version 3.3.5 Enter .help for instructions sqlite sqlite .separator , sqlite sqlite .import Opilio_2005030_Nav_UTM20_1Sec.csv_test Opilio_2005030_Nav_UTM20_1Sec Segmentation fault $ Any ideas what I'm doing wrong here? I saved your data in a file named x1.csv then did this: [EMAIL PROTECTED]:~/sqlite/bld ./sqlite3 SQLite version 3.3.5 Enter .help for instructions sqlite create table t1(a,b,c); sqlite .separator , sqlite .import x1.csv t1 sqlite select * from t1; 184102229,651807.39,5165628.749959 184102230,651807.890587,5165628.680512 184102231,651807.890878,5165628.668937 184102232,651807.890587,5165628.680512 184102233,651807.890587,5165628.680512 184102234,651807.890587,5165628.680512 184102235,651807.890296,5165628.692087 184102236,651807.890587,5165628.680512 184102237,651807.892917,5165628.587916 sqlite In other words, I am unable to reproduce your problem. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] accurate progress indication
On 5/10/06, Allan, Mark [EMAIL PROTECTED] wrote: Jay, Yes there is a index on the date column. The delete operation is on date, specifically dates older than a date entered by the user (so he/she can clear out old records and keep newer ones). I don't think the time taken is slowed by the time SQLite takes to find the rows as I do a SELECT COUNT with the same query and this does not take as long as the DELETE. I don't know why it takes so long, I dont think it is the writing to the file as this should only happen on COMMIT and I can see in debug output that the COMMIT only takes a few seconds. It is before COMMIT that takes the time. Is it possible to use a profiler tool on your embedded system? One technique I've seen for debugging systems like that is to make a null modem cable (either serial or parallel) and hook the embedded system to a terminal or another computer. You can then put write statements into your code to write debugging info to the port for collection and analysis. It's pretty simple if you don't have any better tools. You can at least see what part of the code is taking so long to execute.
[sqlite] Creating a database
Hi, I know how to create a databse from a DOS prompt by going to the directory the .exe file is and by typing sqlite3 name.db which then creates the database, but what I do not know is how to create a database from my application I am building using VB.Net using the .dll file, I can successfully connect to and create tables and insert data, but as soon as I close the connection all the information is lost. The sqlite3 name.db command does not work as I am not accessing the .exe file. Does anyone out there have any ideas? Many thanks. John.
Re: [sqlite] VB.Net DataSet DataGrid with ADO.Net Provider
Hi, I have managed to display the data into the datagrid using a dataset, many thanks for all your help. John. On 10/05/06, John Newby [EMAIL PROTECTED] wrote: Hi Michael, I am already using the Finisar.SQLite wrapper, I can successfully connect to the database, create a table, insert data and display it to the console or in a message box, but I am unsure as how to display it into a grid (like ms access) On 10/05/06, Michael B. Hansen [EMAIL PROTECTED] wrote: Hi John, There is a number of .NET-wrappers for SQLite (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers). I personally use Finisar.SQLite (http://adodotnetsqlite.sourceforge.net/) which performs acceptable (although I currently have one single issue with it - look for the subject Transactions and 'library routine called out of sequence' locks the database in this list). /Michael -Original Message- From: John Newby [mailto:[EMAIL PROTECTED] Sent: 9. maj 2006 17:18 To: sqlite-users@sqlite.org Subject: [sqlite] VB.Net DataSet DataGrid with ADO.Net Provider Hi, I am trying to access a SQLite database using the ADO.Net provider and bring all the data back in a tabular form, I can bring back the data using a datareader but I don't know if I can put the data into a datset or a datagrid. Does anyone know if you can do this? Many thanks. John.
Re: [sqlite] Creating a database
I am using the C api and here a database i created if it dosen't exist. // Anders John Newby skrev: Hi, I know how to create a databse from a DOS prompt by going to the directory the .exe file is and by typing sqlite3 name.db which then creates the database, but what I do not know is how to create a database from my application I am building using VB.Net using the .dll file, I can successfully connect to and create tables and insert data, but as soon as I close the connection all the information is lost. The sqlite3 name.db command does not work as I am not accessing the .exe file. Does anyone out there have any ideas? Many thanks. John.
Re: [sqlite] Creating a database
To be more clearer the OPEN commad makes a database if it is missing // Anders Anders Persson skrev: I am using the C api and here a database i created if it dosen't exist. // Anders John Newby skrev: Hi, I know how to create a databse from a DOS prompt by going to the directory the .exe file is and by typing sqlite3 name.db which then creates the database, but what I do not know is how to create a database from my application I am building using VB.Net using the .dll file, I can successfully connect to and create tables and insert data, but as soon as I close the connection all the information is lost. The sqlite3 name.db command does not work as I am not accessing the .exe file. Does anyone out there have any ideas? Many thanks. John.
[sqlite] Dumping Memory-DB to File
Hi all, I'm using SQLite with the ADO .NET - Povider and I'd like to create a database in memory, insert data into it and then save it to a file (for performance reasons). What's the best way to archive this? There's a sqlite3_write_to_file() - function in the c-lib., but I haven't found anything like that in the dotnet-library. I've read that ATTACH database ... is the way to go, but that seems rather cumbersome. Isn't there a better way? And if I have to use ATTACH, what would be the best (and most flexible) way to copy the data to a file? Cheers Henning -- View this message in context: http://www.nabble.com/Dumping-Memory-DB-to-File-t1592559.html#a4321646 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Possible bug with non-standard characters in column names
[EMAIL PROTECTED] said: CREATE TABLE test1 (loader_id INTEGER PRIMARY KEY, loader_status VARCHAR(255)); ALTER TABLE test1 ADD name VARCHAR(255); ALTER TABLE test1 ADD straße VARCHAR(255); ALTER TABLE test1 ADD plz VARCHAR(255); Raises an SQL error: malformed database schema - near plz: syntax error The suspected bug is that the ALTER TABLE that adds straße should raise the error, not the following statement? I'm guessing that straße was indeed what ticked off the engine, but it took until plz for the parser to realize the problem. Have you tried using the 16 bit character functions instead? The 8 bit functions could be choking on the essen. Clay Dowling -- Simple Content Management http://www.ceamus.com
RE: [sqlite] Possible bug with non-standard characters in column names
Not sure- I've tried this through JDBC and the command line client, I'm not a developer at that level :( Thanks, Andreas -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: Mittwoch, 10. Mai 2006 18:42 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Possible bug with non-standard characters in column names [EMAIL PROTECTED] said: CREATE TABLE test1 (loader_id INTEGER PRIMARY KEY, loader_status VARCHAR(255)); ALTER TABLE test1 ADD name VARCHAR(255); ALTER TABLE test1 ADD straße VARCHAR(255); ALTER TABLE test1 ADD plz VARCHAR(255); Raises an SQL error: malformed database schema - near plz: syntax error The suspected bug is that the ALTER TABLE that adds straße should raise the error, not the following statement? I'm guessing that straße was indeed what ticked off the engine, but it took until plz for the parser to realize the problem. Have you tried using the 16 bit character functions instead? The 8 bit functions could be choking on the essen. Clay Dowling -- Simple Content Management http://www.ceamus.com This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Re: [sqlite] Disk I/o Error : SQLITE_IOERR while performing Select/Delete on database
Thanks for your reply. We use powerpc Linux on the embedded platform (CPU Model number : MPC885 ). I have downloaded the source code for sqlite version 3.3.4 and compiled it for powerpc linux gnu. Thanks Deepali On 5/10/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: deepali rajale [EMAIL PROTECTED] wrote: I am getting SQLITE_IOERR error while trying to perform select or delete operation on a record when Database has more than 3000 records. The table is likely to grow to 1 records. Can someone give any pointers to the solution. An SQLITE_IOERR indicates that a disk I/O operation (a read or a write) failed. This indicates some kind of problem with your embedded hardware. Or perhaps your custom OS interface layer is not working quite right. Or perhaps you are trying to use one of the OS interface layers for Unix or Windows on an embedded platform that does not fully support the Unix or Win32 interface. What operating system are you running on your embedded platform? -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] SQLite wrapper Java Linux: No suitable driver
Hi,
I am using the sqlite wrapper by Christian Werner for Java on my Linux
machine. I use eclipse and I have create da new project which contains
the SQLite.jar stuff.
My code looks like this:
import SQLite.JDBCDriver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class JDBCTest {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Class.forName(SQLite.JDBCDriver);
String url=jdbc:sqlite://home/pasquale/test.db;
Connection db = DriverManager.getConnection(url);
Statement st = db.createStatement();
//Execute query
ResultSet rs = st.executeQuery(SELECT * +FROM tbl1);
System.out.println(Output:);
while (rs.next()){
System.out.println(rs.getString(0)+ +
rs.getString(1));
}
} catch (ClassNotFoundException e){
System.err.println(Error: +e);
} catch (SQLException e){
System.err.println(Error: +e );
}
}
}
Whenever I try to compile, I get the following error:
java.lang.ClassNotFoundException: SQLite.JDBC2y.JDBCConnection
Error: java.sql.SQLException: No suitable driver
I found this link from Sun's Java Technology site
(http://forum.java.sun.com/thread.jspa?threadID=602869messageID=3248193),
where it says I can't use this wrapper as is for 1.5. (I am using JDK 5.0.6).
But I don't have clear what to do.
Could someone please help?
tia
Pasquale
RE: [sqlite] accurate progress indication
Jay,
Yes we debug on our target using a serial link to the PC and catching printf
statements in hyperterminal. From this we can deduce that it is the line of
code:-
sqlite3_exec( m_hDatabase, strSQL, NULL, NULL, NULL )
where strSQL is DELETE FROM EXAMINATIONS WHERE DATE datetime('2006-05-09
00:00:00')
that is taking the time.
We have not tried to place printf statements within SQLite to determine what is
taking the time internally to SQLite.
Note: the performance problem is not a new issue, only the ability to display a
progress bar was new. There is an existing thread on this archived at:-
http://www.mail-archive.com/sqlite-users@sqlite.org/msg10818.html
Thanks
Mark
-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: 10 May 2006 15:16
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] accurate progress indication
On 5/10/06, Allan, Mark [EMAIL PROTECTED] wrote:
Jay,
Yes there is a index on the date column. The delete
operation is on date, specifically dates older than a date
entered by the user (so he/she can clear out old records and
keep newer ones). I don't think the time taken is slowed by
the time SQLite takes to find the rows as I do a SELECT COUNT
with the same query and this does not take as long as the
DELETE. I don't know why it takes so long, I dont think it is
the writing to the file as this should only happen on COMMIT
and I can see in debug output that the COMMIT only takes a
few seconds. It is before COMMIT that takes the time.
Is it possible to use a profiler tool on your embedded system?
One technique I've seen for debugging systems like that is to
make a null modem
cable (either serial or parallel) and hook the embedded
system to a terminal
or another computer. You can then put write statements into
your code to
write debugging info to the port for collection and analysis.
It's pretty
simple if you don't have any better tools. You can at least
see what part
of the code is taking so long to execute.
DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is addressed
and may contain information that is privileged, confidential, and exempt from
disclosure under applicable law. If the reader of this message is not the
intended recipient, or the employee or agent responsible for delivering the
message to the intended recipient, you are hereby notified that any
dissemination, distribution, forwarding, or copying of this communication is
strictly prohibited. If you have received this communication in error, please
notify the sender immediately by return email, and delete the original message
immediately.
RE: [sqlite] Problem importing csv file
Have you tried PRAGMA integrity_check 'ing your DB? Have you tried with a clean DB / new Table? -Original Message- From: Patton, Eric [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 3:59 PM To: 'sqlite-users@sqlite.org' Subject: RE: [sqlite] Problem importing csv file Andre, I receive the same seg-fault if the headers are removed. I had initially tried to import without headers, and added them after the import was unsuccessful. -- Eric Patton [EMAIL PROTECTED] -Original Message- From: André Goliath [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 10:44 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Problem importing csv file First of all, remove the header line from your CSV. SQLite expects only data, no headers. -Original Message- From: Patton, Eric [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 3:04 PM To: 'sqlite-users@sqlite.org' Subject: [sqlite] Problem importing csv file I can't seem to get an 8MB csv import a comma-separated file into Sqlite 3.3.5. The csv in question contains only three columns of comma-delimited data, with the first line of the file containing the column headings: Time,Easting,Northing 184102229,651807.39,5165628.749959 184102230,651807.890587,5165628.680512 184102231,651807.890878,5165628.668937 184102232,651807.890587,5165628.680512 184102233,651807.890587,5165628.680512 184102234,651807.890587,5165628.680512 184102235,651807.890296,5165628.692087 184102236,651807.890587,5165628.680512 184102237,651807.892917,5165628.587916 Here's the import syntax I used: $ sqlite3 SQLite version 3.3.5 Enter .help for instructions sqlite sqlite .separator , sqlite sqlite .import Opilio_2005030_Nav_UTM20_1Sec.csv_test Opilio_2005030_Nav_UTM20_1Sec Segmentation fault $ Any ideas what I'm doing wrong here? ~ Eric.
RE: [sqlite] SQLite wrapper Java Linux: No suitable driver
Make sure the sqlite_jni.dll DLL is in your path!
Andi
-Original Message-
From: Pasquale Imbemba [mailto:[EMAIL PROTECTED]
Sent: Mittwoch, 10. Mai 2006 18:41
To: SQLite
Subject: [sqlite] SQLite wrapper Java Linux: No suitable driver
Hi,
I am using the sqlite wrapper by Christian Werner for Java on my Linux
machine. I use eclipse and I have create da new project which contains
the SQLite.jar stuff.
My code looks like this:
import SQLite.JDBCDriver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class JDBCTest {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Class.forName(SQLite.JDBCDriver);
String
url=jdbc:sqlite://home/pasquale/test.db;
Connection db =
DriverManager.getConnection(url);
Statement st = db.createStatement();
//Execute query
ResultSet rs = st.executeQuery(SELECT * +FROM
tbl1);
System.out.println(Output:);
while (rs.next()){
System.out.println(rs.getString(0)+
+ rs.getString(1));
}
} catch (ClassNotFoundException e){
System.err.println(Error: +e);
} catch (SQLException e){
System.err.println(Error: +e );
}
}
}
Whenever I try to compile, I get the following error:
java.lang.ClassNotFoundException: SQLite.JDBC2y.JDBCConnection
Error: java.sql.SQLException: No suitable driver
I found this link from Sun's Java Technology site
(http://forum.java.sun.com/thread.jspa?threadID=602869messageID=3248193
), where it says I can't use this wrapper as is for 1.5. (I am using
JDK 5.0.6). But I don't have clear what to do.
Could someone please help?
tia
Pasquale
This message is for the designated recipient only and may contain privileged,
proprietary, or otherwise private information. If you have received it in
error, please notify the sender immediately and delete the original. Any other
use of the email by you is prohibited.
[sqlite] Serialising memory DB
Is there a way to serialise a memory DB so that it can be sent over a network socket and re-opened on another computer as a memory DB? I was thinking of using a SQLite table as a configuration system (like a .ini file) that can be sent over the network. Obviously you could use a file based DB instead and pass that around but I would like to be able to use memory DB for computers that either don't have hard drives or an OS that doesn't handle POSIX file operations (fopen et al). Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Error message: too much SQL parameters
You're right- found the limiting DEFINE in the JDBC driver sources- thanks! Andi -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mittwoch, 10. Mai 2006 15:38 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Error message: too much SQL parameters [EMAIL PROTECTED] wrote: I'm inserting into a table with 71 columns, trying to populate all columns in one (prepared) statement with JDBC. I'm receiving this error message: SQLITE.Exception: too much SQL parameters. Any ideas? Look in your JDBC bindings, not in the SQLite core. SQLite did not generate this error message. -- D. Richard Hipp [EMAIL PROTECTED] This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Re: [sqlite] Serialising memory DB
Is there a way to serialise a memory DB so that it can be sent over a network socket and re-opened on another computer as a memory DB? search for 'dump' and run_schema_dump_query in sqlite/src/shell.c __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Serialising memory DB
On 5/10/06, Brandon, Nicholas (UK) [EMAIL PROTECTED] wrote: Is there a way to serialise a memory DB so that it can be sent over a network socket and re-opened on another computer as a memory DB? I was thinking of using a SQLite table as a configuration system (like a .ini file) that can be sent over the network. Obviously you could use a file based DB instead and pass that around but I would like to be able to use memory DB for computers that either don't have hard drives or an OS that doesn't handle POSIX file operations (fopen et al). Hey Nick, did you consider an RPC call to a computer that will act as the 'config server'?
Re: [sqlite] Serialising memory DB
--- Brandon, Nicholas (UK) [EMAIL PROTECTED] wrote: Is there a way to serialise a memory DB so that it can be sent over a network socket and re-opened on another computer as a memory DB? I was thinking of using a SQLite table as a configuration system (like a .ini file) that can be sent over the network. Obviously you could use a file based DB instead and pass that around but I would like to be able to use memory DB for computers that either don't have hard drives or an I don't know if it will help you, but I've been working on something almost, but not completely unlike that here: http://sqlcached.sourceforge.net It's work in progress, it may explode if looked at harshly, etc. :) OS that doesn't handle POSIX file operations (fopen et al). If you have an OS that doesn't offer fopen() similar functions at compile time, you'll probably have a hard time compiling anything using sqlite... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Encryption
I'm having trouble compiling the 3.4.5 version with encryption extensions.. I have done it a hundred times before, but i just switched to a new computer and don't know what stupid mistake i am making... (NOTE: *.c is everything in the zip file with the addition of crypto3.c and exclusion of tclsqlite.c) gcc -DSQLITE_HAS_CODEC=1 *.c -o sqlite3.4.5.exe the exe is built, but i can't open an encrypted database and .rekey command gives me the following: 'unknown command or invalid arguments: rekey. Enter .help for help' So what am i doing wrong? thanks, Preston
Re: [sqlite] Serialising memory DB
We do this in a very general way by running a comms program (actually a CGI process) which transforms the structure of the DB into XML and sends it to the client. The client knows the DTD so that it can parse it and write to its database, which does not have to be Sqlite. A simpler way would be for you to dump the database as SQL then execute the SQL at the destination. Also look at how the vacuum command works. JS Brandon, Nicholas (UK) wrote: Is there a way to serialise a memory DB so that it can be sent over a network socket and re-opened on another computer as a memory DB? I was thinking of using a SQLite table as a configuration system (like a .ini file) that can be sent over the network. Obviously you could use a file based DB instead and pass that around but I would like to be able to use memory DB for computers that either don't have hard drives or an OS that doesn't handle POSIX file operations (fopen et al). Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite] Encryption
Preston Chrystie [EMAIL PROTECTED] wrote: I'm having trouble compiling the 3.4.5 version with encryption extensions.. I have done it a hundred times before, but i just switched to a new computer and don't know what stupid mistake i am making... (NOTE: *.c is everything in the zip file with the addition of crypto3.c and exclusion of tclsqlite.c) gcc -DSQLITE_HAS_CODEC=1 *.c -o sqlite3.4.5.exe the exe is built, but i can't open an encrypted database and .rekey command gives me the following: 'unknown command or invalid arguments: rekey. Enter .help for help' So what am i doing wrong? Question answered by private email. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Encryption
[EMAIL PROTECTED] wrote: I'm having trouble compiling the 3.4.5 version with encryption extensions.. Question answered by private email. Version 3.4.5... I wonder if this is a typo or the mythical Cinnamon. Editing in the wiki and questions being answered by private email makes it all seem very hush hush. ;-) I guess we will just have to wait and see. Dennis Cote
Re: [sqlite] Encryption
Dennis Cote [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: I'm having trouble compiling the 3.4.5 version with encryption extensions.. Question answered by private email. Version 3.4.5... I wonder if this is a typo or the mythical Cinnamon. Editing in the wiki and questions being answered by private email makes it all seem very hush hush. ;-) A typo, I'm sure. The questioner is an Encryption Extension customer and is not connected with the Cinnamon project. Cinnamon is the code name for the effort to add full text search to SQLite. I did not come up with the name - the main project sponsor did. If that sponsor wishes to identify themselves, they are welcomed to do so, but as is the usual policy in this kinds of things, I will not. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] How can I get the type of a column?
I want to know the type of a column, ie.: INTEGER, TEXT, REAL or BLOB There seems to be no function to do this. I can get the declared type of a column but that's not easy to decipher as SQL has a zillion data types. I tried sqlite3_column_type() but that function only works when there's a valid row (and when there's a valid row it fails when a column is empty!) Is there any way to get the column type?
Re: [sqlite] How can I get the type of a column?
No such thing, really. In SQLite the data types are associated with the cell, not the column. The declared type of a column that you've found is used to establish preferences for how cells are stored, but it is not a hard requirement (unlike most other SQL implementations). -Eric On 10 May 2006, at 16:25, sqlite wrote: I want to know the type of a column, ie.: INTEGER, TEXT, REAL or BLOB There seems to be no function to do this. I can get the declared type of a column but that's not easy to decipher as SQL has a zillion data types. I tried sqlite3_column_type() but that function only works when there's a valid row (and when there's a valid row it fails when a column is empty!) Is there any way to get the column type?
Re: [sqlite] How can I get the type of a column?
Eric Scouten wrote: No such thing, really. In SQLite the data types are associated with the cell, not the column. Yes, I saw that. I'm not sure it's a good feature. The declared type of a column that you've found is used to establish preferences for how cells are stored, but it is not a hard requirement (unlike most other SQL implementations). Internally SQLite seems to have a column affinity. That's what I need...
Re: [sqlite] How can I get the type of a column?
On 10 May 2006, at 16:31, sqlite wrote: Eric Scouten wrote: No such thing, really. In SQLite the data types are associated with the cell, not the column. Yes, I saw that. I'm not sure it's a good feature. Depends on your application. For us, it's been a very natural fit. It may not fit your data or coding style well; if so, then you may want to consider other DB engines. The declared type of a column that you've found is used to establish preferences for how cells are stored, but it is not a hard requirement (unlike most other SQL implementations). Internally SQLite seems to have a column affinity. That's what I need... SQLite derives that by parsing the string that you've already found. I think it does store that in some internal fashion, so it's not *re- parsing* it constantly, but that is not available through the API. -Eric
Re: [sqlite] How can I get the type of a column?
Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself...
Re: [sqlite] How can I get the type of a column?
The type can vary according to the data. The declared type is what you declare it to be and the type returned when you get a row is the type which Sqlite decided it should be based on its format. You can make the declared type anything you want it to be, for example WIDGET and when you get a row returning a type FLOAT you can decide what you do with a FLOAT when it is in your class WIDGET. For more detail look up the Sqlite documentation on manifest typing. JS sqlite wrote: I want to know the type of a column, ie.: INTEGER, TEXT, REAL or BLOB There seems to be no function to do this. I can get the declared type of a column but that's not easy to decipher as SQL has a zillion data types. I tried sqlite3_column_type() but that function only works when there's a valid row (and when there's a valid row it fails when a column is empty!) Is there any way to get the column type?
Re: [sqlite] How can I get the type of a column?
sqlite [EMAIL PROTECTED] wrote: Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. This has been a constant theme for 6 years now. And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. The best theory I have is that people who have always driven a stick shift must have difficulty driving a car with an automatic transmission. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] How can I get the type of a column?
I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. This has been a constant theme for 6 years now. And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. because in most of the languages we use (when dealing with other database packages) it throws an error when you don't match the variable's type to the database column type. VB.NET and MS SQL leap to mind...
Re: [sqlite] How can I get the type of a column?
[EMAIL PROTECTED] wrote: sqlite [EMAIL PROTECTED] wrote: Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. This has been a constant theme for 6 years now. And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. The best theory I have is that people who have always driven a stick shift must have difficulty driving a car with an automatic transmission. -- D. Richard Hipp [EMAIL PROTECTED] Only six years? How many years did it take for programmers and others to get past the concept of 80 column Hollerith cards, and how many haven't yet made it? JS
Re: [sqlite] How can I get the type of a column?
[EMAIL PROTECTED] wrote: sqlite [EMAIL PROTECTED] wrote: Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. We're C++ programmers and C++ is all about data types. It's a statically typed language. This has been a constant theme for 6 years now. I'm sure it has... :-) And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. Ummm...perhaps it's because when you create a column you give it a type. It's only natural to believe that the type might actually be used for something... The best theory I have is that people who have always driven a stick shift must have difficulty driving a car with an automatic transmission. Yep. We spend a week stomping on the huge American brake pedal every time we reach a traffic light and want to de-clutch.
[sqlite] Re: - Re: [sqlite] How can I get the type of a column?
Perhaps the analogy is better the other way around - try driving a stick shift after driving an automatic. Takes a gear change or two for you to realise what the other pedal is for. As you mention, this is a constant point of discussion on this board. Perhaps 'Version 3 Data Types' should be given more prominence under 'Documentation'. Alternatively, perhaps the subject should be touched upon briefly in 'SQLite in 5 Minutes Or Less'. Regards. rayB |-+ | | [EMAIL PROTECTED]| | || | | 11/05/2006 10:23 | | | Please respond to| | | sqlite-users | | || |-+ --| | | | To: sqlite-users@sqlite.org | | cc: | | Subject: - Re: [sqlite] How can I get the type of a column? | --| sqlite [EMAIL PROTECTED] wrote: Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. This has been a constant theme for 6 years now. And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. The best theory I have is that people who have always driven a stick shift must have difficulty driving a car with an automatic transmission. -- D. Richard Hipp [EMAIL PROTECTED] ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
Re: [sqlite] How can I get the type of a column?
Eric Scouten wrote: On 10 May 2006, at 16:31, sqlite wrote: Eric Scouten wrote: No such thing, really. In SQLite the data types are associated with the cell, not the column. Yes, I saw that. I'm not sure it's a good feature. Depends on your application. For us, it's been a very natural fit. It may not fit your data or coding style well; if so, then you may want to consider other DB engines. It's like dynamic typing vs. static typing in your computer language. Both have advantages... Whatever, I've reworked the code to associate the type with the data value instead of the column and it's all working now. I'm writing a wrapper and I figure it will be easier to make other database engines work the SQLite way than to make SQLite work the other way around.
Re: [sqlite] Re: - Re: [sqlite] How can I get the type of a column?
[EMAIL PROTECTED] wrote: As you mention, this is a constant point of discussion on this board. To me this would indicate a problem. Perhaps 'Version 3 Data Types' should be given more prominence under 'Documentation'. Alternatively, perhaps the subject should be touched upon briefly in 'SQLite in 5 Minutes Or Less'. I think real problem is that you have a function called column_type which doesn't actually return the type of a column. This is counter-intutive. If you could make it return the column affinity between the call to prepare() and the first call to step() then the problem would probably go away. My $0.02...
RE: [sqlite] How can I get the type of a column?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 7:23 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How can I get the type of a column? sqlite [EMAIL PROTECTED] wrote: Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. This has been a constant theme for 6 years now. And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. The best theory I have is that people who have always driven a stick shift must have difficulty driving a car with an automatic transmission. -- D. Richard Hipp [EMAIL PROTECTED] Well for one: ([EMAIL PROTECTED] * @#%DRefrt1) / $34545ere@ = #WDW%^$RTFM$ :-)
RE: [sqlite] How can I get the type of a column?
-Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 7:37 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How can I get the type of a column? [EMAIL PROTECTED] wrote: sqlite [EMAIL PROTECTED] wrote: Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. This has been a constant theme for 6 years now. And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. The best theory I have is that people who have always driven a stick shift must have difficulty driving a car with an automatic transmission. -- D. Richard Hipp [EMAIL PROTECTED] Only six years? How many years did it take for programmers and others to get past the concept of 80 column Hollerith cards, and how many haven't yet made it? JS Well in 196x we forward thinkers in the Polaris Navy used 90 column Univac punch cards (With ROUND holes) for our target cards. We always fancied the one with D.C. as the terminal destination :-) Fred
RE: [sqlite] Low Level API for SQLite3
Dennis,
Thank you so much for sending me the code. Its works fine!! It
took 8 sec to insert 10 records into the table where previously it
was taking 30 sec to insert 1 records. Dennis, if u could do me a
favour, can u explain me how to retrieve datas from the table. Or if u
have the code for that, can u pls send it to me...
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 09, 2006 9:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Low Level API for SQLite3
Anish Enos Mathew wrote:
Thank you so much cote, for ur valuable suggestion. I would be greatly
thankful to u if u could send me a simple c program which uses the
above
API's to insert some values into the SQLite database.
Anish,
The sample program below shows how to use precompiled inserts in
SQLite. On my computer I get the following results for 1,000,000
inserts. This produces a database file that is 46.9 MB.
Executed 100 inserts in 11 seconds, 90909 inserts/sec
For comparison I made a second program that builds equivalent SQL
statements and then passes them to SQLite to compile and execute using
sqlite3_exec().
Executed 100 inserts in 34 seconds, 29412 inserts/sec
This program takes 3 times longer to execute, so it is spending 2/3 of
its time generating and parsing the SQL statements.
Note that surrounding the insert loop with a begin transaction/commit
transaction pair is extremely important to getting these high insert
rates. Without a transaction these programs are both much slower since
they become I/O bound. They both take about 50 seconds to do 500 inserts
at about 10 inserts/sec. This is slower by a factor of about 3000 or
9000.
HTH
Dennis Cote
P.S. I apologize if Anish is not your first name, but I believe that
most people use the normal spoken order of their names for when setting
up an email account.
Prepared insert statements.
#include stdio.h
#include stdlib.h
#include time.h
#include sqlite3.h
// struct for sample records.
typedef struct {
int a;
float b;
charc[50];
} record;
// error checking elimnated for clarity
int main(int argc, char *argv[])
{
char *database = test.db;
sqlite3 *db;
sqlite3_stmt *insert;
record sample;
int samples = 100;
int i;
time_t bgn, end;
double t;
// open a new database (after deleting any previous database)
remove(database);
sqlite3_open(database, db);
// create a table
sqlite3_exec(db, create table t (a integer, b float, c text),
NULL, NULL, NULL);
// open transaction to speed inserts
sqlite3_exec(db, begin transaction, NULL, NULL, NULL);
// compile an SQL insert statement
sqlite3_prepare(db, insert into t values (?, ?, ?), -1, insert,
NULL);
// records start time
bgn = time(NULL);
// loop to insert sample values
for (i = 0; i samples; i++) {
// generate the next sample values
sample.a = i;
sample.b = i * 1.1;
sprintf(sample.c, sample %d %f, sample.a, sample.b );
// bind parameter values
sqlite3_bind_int(insert, 1, sample.a);
sqlite3_bind_double(insert, 2, sample.b);
sqlite3_bind_text(insert, 3, sample.c, -1, SQLITE_STATIC);
// execute the insert
sqlite3_step(insert);
// reset for next loop
sqlite3_reset(insert);
}
// record end time
end = time(NULL);
// finalize compiled statement to free memory
sqlite3_finalize(insert);
// close transaction
sqlite3_exec(db, commit transaction, NULL, NULL, NULL);
// close the database
sqlite3_close(db);
// report timing
t = difftime(end, bgn);
printf(Executed %d inserts in %.0f seconds, %.0f inserts/sec\n,
samples, t, samples / t);
return 0;
}
Compiled insert statements.
#include stdio.h
#include stdlib.h
#include time.h
#include sqlite3.h
// struct for sample records.
typedef struct {
int a;
float b;
charc[50];
} record;
// error checking elimnated for clarity
int main(int argc, char *argv[])
{
char *database = test.db;
sqlite3 *db;
char insert[200];
record sample;
int samples = 100;
int i;
time_t bgn, end;
double t;
// open a new database (after deleting any previous database)
remove(database);
sqlite3_open(database, db);
// create a table
sqlite3_exec(db, create table t (a integer, b float, c text),
NULL, NULL, NULL);
// open transaction to speed inserts
sqlite3_exec(db, begin transaction, NULL, NULL, NULL);
// records start time
bgn = time(NULL);
// loop to insert sample values
for (i = 0; i samples; i++) {
// generate the next sample values
sample.a = i;
sample.b = i * 1.1;
sprintf(sample.c, sample %d %f, sample.a, sample.b );
// build next insert statement
sprintf(insert, insert into t values (%d, %#f, '%s'),
sample.a, sample.b,
Re: [sqlite] How can I get the type of a column?
Fred Williams wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 7:37 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How can I get the type of a column? [EMAIL PROTECTED] wrote: sqlite [EMAIL PROTECTED] wrote: Eric Scouten wrote: SQLite derives that by parsing the string that you've already found. I found the method in section 2.1 of this page: http://www.sqlite.org/datatype3.html I think it does store that in some internal fashion, so it's not *re-parsing* it constantly, but that is not available through the API. Pity. I guess I'll have to parse it myself... I continue to be bewildered by programmers' fixation on datatypes. This has been a constant theme for 6 years now. And in all that time, I have never been able to figure out why so many people think they need to know the type of a column. The best theory I have is that people who have always driven a stick shift must have difficulty driving a car with an automatic transmission. -- D. Richard Hipp [EMAIL PROTECTED] Only six years? How many years did it take for programmers and others to get past the concept of 80 column Hollerith cards, and how many haven't yet made it? JS Well in 196x we forward thinkers in the Polaris Navy used 90 column Univac punch cards (With ROUND holes) for our target cards. We always fancied the one with D.C. as the terminal destination :-) Fred Sounds like a dangerously radical idea to me, like manifest typing. For example, how many hanging chads can you get when you have round holes?
Re: [sqlite] How can I get the type of a column?
We're C++ programmers and C++ is all about data types. It's a statically typed language. The types point still baffles me. If your code already knows which column it is dealing with then surely the code should know what type to expect. (Eg if you are dealing with a column named 'title' then you would expect a string) The C api will also do conversion (eg if you ask for float and the value was int then it will be converted). Do people using your code go around randomly changing the database schema and the values stored without changing the corresponding C code? In the case when you don't know which column you are dealing with (eg you are generically handling columns) then why wouldn't you use generic code. For example if you are producing CSV output then the type of the columns doesn't matter, just the type of each data item. Roger
Re: [sqlite] SQLite wrapper Java Linux: No suitable driver
On 10/05/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Make sure the sqlite_jni.dll DLL is in your path! Andi I don't have that file, but this libsqlite_jni.so . I understand I must have this also in my build path but I don't figure out how to do it in eclipse. I do Build Path - Link additional source to project and then point to the location where the .so is contained. Doesn't work. Pasquale
Re: [sqlite] Low Level API for SQLite3
On Thu, May 11, 2006 at 10:20:26AM +0530, Anish Enos Mathew wrote: Dennis, Thank you so much for sending me the code. Its works fine!! It took 8 sec to insert 10 records into the table where previously it was taking 30 sec to insert 1 records. Dennis, if u could do me a favour, can u explain me how to retrieve datas from the table. Or if u have the code for that, can u pls send it to me... Anish, Dennis has provided you with lots of help. At a certain point (which he may or may not have reached) he will want to stop providing this help. This is OK, but unfortunately he may also decide to stop providing help to other people, like me. This would be bad. I think it may be the time for you to do more research, try things out, and then come back when you have more specific questions. This is of course only my personal opinion. Here's a link that expresses this more tactfully than I can: http://www.slash7.com/pages/vampires Nathan Kurz [EMAIL PROTECTED]
