Re: [sqlite] Dumping Memory-DB to File
Hello everyone, thanks for your replies. I will try to speed things up by using transactions. Cheers + happy weekend, Henning -- View this message in context: http://www.nabble.com/Dumping-Memory-DB-to-File-t1592559.html#a4353870 Sent from the SQLite forum at Nabble.com.
[sqlite] BLOB
Hi (...at the first time... ;-) My App (up to 1500 Users, up to 15 Million Records in some tables in a SQL-Server-DB) offers as a specially feature Data-Export. The Export-Functionally is one of the most-used feature by Users. Exports are controlled and done by a Report, controlled and done by a Tableview and last by a feature I named query-container. This query-container can connect to a SQL-Server-DB, to a Access-MDB, to Schema-Ini-guided Text-Files and to a SQLite-DB. Here can the user perform user-defined sql-select-queries, which result first will be displayed in a table, optionally followed by the export to one or more files (more files = separate files if select the query blob-colums (as bitmaps). The exported Data goes (rarely) to a Oracle-DB, often to CSV-Textfiles and a Excel-Sheet, to a portable Access-MDB on Laptops and now (I want) to a Sqlite-DB to offer the Data displayed by a Browser on a Pocket-PC (Windows- Mobile (Win CE)). If the user performs a user-defined Query I dont know, which colums he select and which Type the columns are - while ever its a __user-defined__ query such as select fieldone as name from customer . and so on. The query-container creates a SQLite-DB and export Data with correct type. But the Browser on Pocket-PC never knows, what the User has done. And now display a Bitmap in a Blob-Type- Column in a Formview-Text-Control as Text, while sqlite3_column_type returns SQLITE_TEXT for a Blob-Type. How can I eliminate this problem ?... I think, to know the correct columntype is fundamental, significant Can anyone help me? Best Greetings from Germany Anne -- Analog-/ISDN-Nutzer sparen mit GMX SmartSurfer bis zu 70%! Kostenlos downloaden: http://www.gmx.net/de/go/smartsurfer
Re: [sqlite] How can I get the type of a column?
John Stanton wrote: I endorse Jay's approach. In our web page software using Sqlite everything is TEXT except for dates in Sqlite format. We use declared types to indicate classes of text, such as decimal numbers and dates. There are no radix conversion involved in data manipulation (apart from dates), and we use a set of routines which perform decimal arithmetic on text strings so that we are not trapped in the floating point bearpit. It all fits in very elegantly with Sqlite and makes web page generation simple. Once you appreciate that Sqlite's typing makes it easier, not harder, it is easier. JS Jay Sprenkle wrote: On 5/10/06, sqlite <[EMAIL PROTECTED]> wrote: We're C++ programmers and C++ is all about data types. It's a statically typed language. I dealt with it pretty simply in my application. It produces web pages and web pages are text. Anything I retrieve is always retrieved as text. No conversions needed, nice and simple. Regarding dates, given the many different ways DATES are handled across platforms, I stick to using dates in char format, always stored as MMDD (optionally with HHNNSSss with 24-hour format, and always UTC). Easy to sort, easy to search, easy to handle, and very portable. I do have to create routines in the particular programming language to do the conversion, data entry and formatting, but it saves me a lot of work knowing that storage is always the same. jp
Re: [sqlite] Re: - Re: [sqlite] How can I get the type of a column?
Joe Wilson wrote: I also agree with Ralf's proposal for sqlite3_column_affinity(). (Not that a vote on this topic will likely make a difference. ;-) Another vote from me.
[sqlite] library routine called out of sequence
I am getting this error and have viewed the archives but am still at a loss.
Can I load a script file that creates a view tables:
sqlite3 test.db < myscript
then from a c program sd_open("test.db", db_handle);
It keeps giving me the library routine error. My program is not multi-threaded
and only makes this one sqlite call?
-
How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Re: [sqlite] Re: - Re: [sqlite] How can I get the type of a column?
The current API is complete regarding types. It returns both the declared type and the type actually stored. There is nothing else. Note that the actual storage type can vary between rows according to the data format, which is why the actual type is returned row by row. JS sqlite wrote: Joe Wilson wrote: I also agree with Ralf's proposal for sqlite3_column_affinity(). (Not that a vote on this topic will likely make a difference. ;-) I don't think you need a new function, just make the existing one do the obvious thing right after you call "prepare". --- Ralf Junker <[EMAIL PROTECTED]> wrote: I second this. 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. A more telling name for sqlite3_column_type would probably be sqlite3_cell_type. 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. Yes, some function like sqlite3_column_affinity would indeed be nice to have. Just for the sake of the wrapper writers which try to link sqlite3 to database concepts which required fixed datatypes. It should be available right aftercalling sqlite3_prepare. Just another 2 cent ... Ralf __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] BLOB
[EMAIL PROTECTED] wrote: Hi (...at the first time... ;-) My App (up to 1500 Users, up to 15 Million Records in some tables in a SQL-Server-DB) offers as a specially feature Data-Export. The Export-Functionally is one of the most-used feature by Users. Exports are controlled and done by a Report, controlled and done by a Tableview and last by a feature I named “query-container”. This query-container can connect to a SQL-Server-DB, to a Access-MDB, to Schema-Ini-guided Text-Files and to a SQLite-DB. Here can the user perform user-defined sql-select-queries, which result first will be displayed in a table, optionally followed by the export to one or more files (more files = separate files if select the query blob-colums (as bitmaps). The exported Data goes (rarely) to a Oracle-DB, often to CSV-Textfiles and a Excel-Sheet, to a portable Access-MDB on Laptops and now (I want) to a Sqlite-DB to offer the Data displayed by a Browser on a Pocket-PC (Windows- Mobile (Win CE)). If the user performs a user-defined Query I don’t know, which colums he select and which Type the columns are - while ever it’s a __user-defined__ query such as “select fieldone as name from customer ….” and so on. The query-container creates a SQLite-DB and export Data with correct type. But the Browser on Pocket-PC never knows, what the User has done. And now display a Bitmap in a Blob-Type- Column in a Formview-Text-Control as Text, while sqlite3_column_type returns SQLITE_TEXT for a Blob-Type. How can I eliminate this problem… ?... I think, to know the correct columntype is fundamental, significant… Can anyone help me? Best Greetings from Germany Anne Anne, We have a similar function to yours and use XML as the transfer medium because of its extreme generality. The column names are returned by the Sqlite API as the queries are executed and are then used as XML entities. All data are stored in text format as required by XML. You could use CSV to go to Excel, SQL to Oracle etc. As for type you can place whatever type you choose into your transfer data stream, but the formats you would generally use would all require the data to be transformed into TEXT. Sqlite returns the actual data type to make that possible. You can make whatever transformation is needed by your destination using atoi, atof and sprintf at the destination. If you don't want to use the Sqlite API then you have probably chosen the wrong software tool. Sqlite is aimed at embedded systems where it is linked into applications, not as a replacement for enterprise SQL servers. JS
Re: [sqlite] How can I get the type of a column?
JP wrote: John Stanton wrote: I endorse Jay's approach. In our web page software using Sqlite everything is TEXT except for dates in Sqlite format. We use declared types to indicate classes of text, such as decimal numbers and dates. There are no radix conversion involved in data manipulation (apart from dates), and we use a set of routines which perform decimal arithmetic on text strings so that we are not trapped in the floating point bearpit. It all fits in very elegantly with Sqlite and makes web page generation simple. Once you appreciate that Sqlite's typing makes it easier, not harder, it is easier. JS Jay Sprenkle wrote: On 5/10/06, sqlite <[EMAIL PROTECTED]> wrote: We're C++ programmers and C++ is all about data types. It's a statically typed language. I dealt with it pretty simply in my application. It produces web pages and web pages are text. Anything I retrieve is always retrieved as text. No conversions needed, nice and simple. Regarding dates, given the many different ways DATES are handled across platforms, I stick to using dates in char format, always stored as MMDD (optionally with HHNNSSss with 24-hour format, and always UTC). Easy to sort, easy to search, easy to handle, and very portable. I do have to create routines in the particular programming language to do the conversion, data entry and formatting, but it saves me a lot of work knowing that storage is always the same. jp We have agonized over date formats over the years, but find the Sqlite way correct because it is theoretically sound by using a correct Julian method. We got into trouble using Gregorian dates in char format when we had to work internationally and present dates in the Middle East and Asia for example. Sqlite's method make no assumption that Gregorian dates are being used and can fit in with any of the world's date systems. It also makes relatively simple the generation of day of the week. number of working days in this accounting period, date differences etc. I also like the ingenious way that date and time have been folded into one FP number. JS
Re: [sqlite] library routine called out of sequence
Davide Berti wrote:
I am getting this error and have viewed the archives but am still at a loss.
Can I load a script file that creates a view tables:
sqlite3 test.db < myscript
then from a c program sd_open("test.db", db_handle);
It keeps giving me the library routine error. My program is not multi-threaded
and only makes this one sqlite call?
-
How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.
Have you opened the database?
Re: [sqlite] library routine called out of sequence
my typo here was the sqlite3_open call to open the database that is returning
the error
John Stanton <[EMAIL PROTECTED]> wrote: Davide Berti wrote:
> I am getting this error and have viewed the archives but am still at a loss.
>
> Can I load a script file that creates a view tables:
>
> sqlite3 test.db < myscript
>
> then from a c program sd_open("test.db", db_handle);
>
> It keeps giving me the library routine error. My program is not
> multi-threaded and only makes this one sqlite call?
>
>
>
>
> -
> How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Have you opened the database?
-
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Re: [sqlite] library routine called out of sequence
Davide Berti wrote:
my typo here was the sqlite3_open call to open the database that is returning
the error
John Stanton <[EMAIL PROTECTED]> wrote: Davide Berti wrote:
I am getting this error and have viewed the archives but am still at a loss.
Can I load a script file that creates a view tables:
sqlite3 test.db < myscript
then from a c program sd_open("test.db", db_handle);
It keeps giving me the library routine error. My program is not multi-threaded
and only makes this one sqlite call?
-
How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.
Have you opened the database?
-
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Are you opening it twice?
Re: [sqlite] library routine called out of sequence
execute sqlite3 test.db < myscript from a bash shell.
myscript creates a few tables.
After running this, is my test.db in an open state, do I have to open test.db
in my c program via sqlite3_open("test.db", &db_handle);
John Stanton <[EMAIL PROTECTED]> wrote: Davide Berti wrote:
> my typo here was the sqlite3_open call to open the database that is returning
> the error
>
> John Stanton wrote: Davide Berti wrote:
>
>>I am getting this error and have viewed the archives but am still at a loss.
>>
>>Can I load a script file that creates a view tables:
>>
>>sqlite3 test.db < myscript
>>
>>then from a c program sd_open("test.db", db_handle);
>>
>>It keeps giving me the library routine error. My program is not
>>multi-threaded and only makes this one sqlite call?
>>
>>
>>
>>
>>-
>>How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
>
> Have you opened the database?
>
>
>
> -
> New Yahoo! Messenger with Voice. Call regular phones from your PC and save
> big.
Are you opening it twice?
-
Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min
with Yahoo! Messenger with Voice.
[sqlite] Trying to determine if a column exists through a SQL Statement...
With MySQL I would simply do: SHOW COLUMNS FROM `war3users` LIKE 'playerip'; Can I do something similar in SQLite ? Thanks!! Josh
Re: [sqlite] library routine called out of sequence
The sqlite3 program automatically opens the database and holds it open.
It will create one if it is missing. The DB stays open until you exit
Sqlite3.
JS
Davide Berti wrote:
execute sqlite3 test.db < myscript from a bash shell.
myscript creates a few tables.
After running this, is my test.db in an open state, do I have to open test.db in my c program
via sqlite3_open("test.db", &db_handle);
John Stanton <[EMAIL PROTECTED]> wrote: Davide Berti wrote:
my typo here was the sqlite3_open call to open the database that is returning
the error
John Stanton wrote: Davide Berti wrote:
I am getting this error and have viewed the archives but am still at a loss.
Can I load a script file that creates a view tables:
sqlite3 test.db < myscript
then from a c program sd_open("test.db", db_handle);
It keeps giving me the library routine error. My program is not multi-threaded
and only makes this one sqlite call?
-
How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.
Have you opened the database?
-
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Are you opening it twice?
-
Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min
with Yahoo! Messenger with Voice.
Re: [sqlite] library routine called out of sequence
My answer wasn't complete. If you populate a database using sqlite3 and
exit sqlite3 the DB is closed. To access it from a C program you have
to first open it with sqlite3_open.
Sqlite is not an SQL server but instead a library which is linked into
every application. Think of it just as a smart file and treat it as you
would opening and closing a file in your programs.
JS
Davide Berti wrote:
execute sqlite3 test.db < myscript from a bash shell.
myscript creates a few tables.
After running this, is my test.db in an open state, do I have to open test.db in my c program
via sqlite3_open("test.db", &db_handle);
John Stanton <[EMAIL PROTECTED]> wrote: Davide Berti wrote:
my typo here was the sqlite3_open call to open the database that is returning
the error
John Stanton wrote: Davide Berti wrote:
I am getting this error and have viewed the archives but am still at a loss.
Can I load a script file that creates a view tables:
sqlite3 test.db < myscript
then from a c program sd_open("test.db", db_handle);
It keeps giving me the library routine error. My program is not multi-threaded
and only makes this one sqlite call?
-
How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.
Have you opened the database?
-
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Are you opening it twice?
-
Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min
with Yahoo! Messenger with Voice.
Re: [sqlite] Trying to determine if a column exists through a SQL Statement...
On Fri, 12 May 2006, Josh wrote: With MySQL I would simply do: SHOW COLUMNS FROM `war3users` LIKE 'playerip'; Can I do something similar in SQLite ? [...] Hello Josh, as far as I know, exactly equivalent functionality does not exist in SQLite3. But you can use the data you get via PRAGMA table_info(war3users); or that which you get via SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'war3users'; to achieve what you want with a little additional code on your application's side. For example one could write in CHICKEN Scheme: (define (sqlite3:table-has-column? db table column) (member column (sqlite3:map-row (project 1) db (sprintf "PRAGMA table_info(~S);" table cu, Thomas
[sqlite] Problems with the Tcl extension (when threaded)
Hello! I've downloaded sqlite-3_3_5-tea and compiled it correctly. It
generates libsqlite335.so perfectly, but there is a problem.
I compiled it with threads (I did ./configure --enable-thread and I
checked -DTHREAD_SAFE=1 in the Makefile) but I can't write to the same
database/table at the same time. I open a transaction in one thread
and do lots of sql inserctions, then I close. While there is an opened
transaction in one thread, I can't do (insert/update/delete) anything
in another thread. I get "database is locked". Any idea?
Here is the script I used to test:
# --- BEGIN
package require Thread
load ./libsqlite335.so
sqlite3 db db
db eval "DROP TABLE tabela;"
db eval "CREATE TABLE tabela(n integer);"
set thread_id [thread::create {
load libsqlite335.so
sqlite3 db db
set i 0
db eval "BEGIN DEFERRED TRANSACTION;"
while 1 {
db eval "INSERT INTO tabela VALUES($i);"
puts $i
incr i
}
db eval "COMMIT TRANSACTION;"
vwait forever
}]
set i 1
while 1 {
db eval "INSERT INTO tabela VALUES($i);"
puts $i
incr i
}
# --- END
See I'm using the Thread extension and Tcl is "thread-compiled"(?).
Any clue?
Thank you! Bye!
--
Silas Justiniano
