Re: [sqlite] Nested calls to prepare/step/prepare
On Feb 29, 2008, at 5:21 AM, Ken wrote:
> I wanted to find out if the following is allowed in Sqlite.
>
>sqlite3_prepare_v2
> while ( ) {
>sqlite3_step
> sqlite3_prepare_v2 --- I;m getting a segv here.
> while ( ) {
>sqlite3_step
> }
> }
>
>
> So my questing is, does sqlite allow a prepare to be started while
> a prior prepared statement is still open?
Hi,
This is supposed to be Ok. If you can supply an example program
to demonstrate the crash that would be very helpful.
Dan.
> Thanks,
> Ken
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimization Question for SQLite Experts
You obviously have a set of UID's at the time of the loop, how about
creating a huge select .. from where ...IN (list_of_uids_comma_separated)?
It'll be one single query (or you can break it down into blocks of 50,
or 100, etc).
Will save the overhead of generating the queries over and over again
(essentially giving you loop unrolling).
Then you can just loop over the records in the one single dataset returned.
Mark Gilbert wrote:
> Folks.
>
> Looking for some advice from hardened SQliters...
>
> - Our application uses an SQLite 3.4.1 database with 8 tables. 1 of
> the tables may contain tens or maybe hundreds of thousands of records
> with about 30 fields.
>
> - The tables form a linked tree type hierarchy where one table is the
> trunk, another is the boughs and then branches, twigs and finally
> very many leaves. UIDs in the tables allow us to find the children
> of a record in the next table.
>
> In one operation we have to construct a summary of a portion of the
> tree. This basically means picking the stem, then recursively
> finding its branches, their twigs and so on.
>
> For each twig we have to find all the leaves. The Leaves table has
> maybe 15000 records and we have a query where we search the Leaves
> table once for each twig we find. In our part of the tree there
> might be 200 twigs, and so we end up searching the leaves DB 200
> times, to build up the list of leaves attached to the twigs
>
> Unlike a real tree, our leaves may be attached to more than one twig,
> so we need to keep the tables linked but abstracted, hence the
> separate tables.
>
> The problem we have is performance..
>
> forgetting the main part of the tree, let's jump to leaves:
>
> - We have queried all the way down to the twigs quite quickly, and we
> have found 200 twigs. Each twig is connected to a few leaves.
>
> in pseudocode:
> for (z=0;z {
> sqlite3_get_table("select * from LEAVES where leafID =
> theTwig[z].childLeafID")
> // we end up performing this query 200 times, once for each of the
> parent twigs.
> }
>
> It may take 100mSec to perform each select query on our 15000 entry
> LEAVES table (which have about 30 fields each, most of which we
> want), this ends up taking 20 seconds which is not acceptable in our
> application.
>
> I am certain that we are missing some important SQL language or
> SQLite optimisations, and I wanted to ask for advice.We are
> already using BEGIN and COMMIT transactions around this code
> (although see later for my concerns about that, since maybe its not
> working)
>
> Profiling the code, it spends alot of the time reading into the SQLite pager.
>
> Other information:
> - the app is a highly multithreaded server with query connections
> spawing new threads per request. Each connection needs to query the
> database, and our approach thus far (in order to improve performance)
> is to open the database ONCE (on the first thread that needs it) call
> BEGIN and leave it open. We then have a lock which allows later
> threads to request the database lock (we pass the global DB handle
> back to the new thread). When its finished it releases it and another
> thread can get the handle. Once every minute we COMMIT to flush the
> database, back it up, then BEGIN another transaction.
>
> I am slightly concerned that our BEGIN, COMMIT and usage of the
> handle are made on different threads, and this may be somehow
> defeating the transaction or not taking full advantage of the page
> cache. Perhaps we should close the DB down properly and do the
> entire open process and close transaction locally in the new thread ?
> This is all a READ ONLY transaction, by the way.
>
> We have considered copying the entire database into a memory resident
> SQLite database at the start of this transaction (finding all the
> records ONCE takes less than a second, so we may be able to afford
> this drastic step) but I have read that memory resident databases may
> be not much faster.
>
> We have even considered finding all the records ONCE into our own
> cache them manually performing the searches using our own code
> (hoping it would be faster), but I can't help feeling that we should
> be able to do this faster just using SQLite.
>
> We have considered preparing the transaction first binding the
> parameter, and I wondered if that might help ? Would preparing:
> "select * from LEAVES where leafID = boundParam1"
> really make much difference than just running it ?
>
> We are typically using a Quad 3.0GHz processor Macintosh so we have
> plenty of horsepower. I can't really believe that we should accept
> that this process will take as long as 20 seconds. I am convinced it
> can be massively optimised.
>
> There are so many options, each of which would require quote alot of
> effort to prototype and test, I wondered what wisdom any hardened
> SQlite folk could offer. What is the best approach to attack this
> problem ?
>
> Thanks
Re: [sqlite] DATETIME data type
Store it in '-mm-dd' format, or use the julian date that's suggested at:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Yong Zhao wrote:
> It seems that sqlite3 does not support DATETIME data type.
>
> If I have the following data in table t1, how do I select people who is
> older than certain date?
>
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
>
> select * from t1 where dob < '3/24/1950';
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimization Question for SQLite Experts
Here's two suggestions. First the simple suggestion is instead of this..
for (z=0;z wrote:
> Folks.
>
> Looking for some advice from hardened SQliters...
>
> ...
For each twig we have to find all the leaves. The Leaves table has
> maybe 15000 records and we have a query where we search the Leaves
> table once for each twig we find. In our part of the tree there
> might be 200 twigs, and so we end up searching the leaves DB 200
> times, to build up the list of leaves attached to the twigs
>
> ...in pseudocode:
> for (z=0;z {
>sqlite3_get_table("select * from LEAVES where leafID =
> theTwig[z].childLeafID")
> // we end up performing this query 200 times, once for each of the
> parent twigs.
> }
> ...
>
> Thanks for any experience you can share..
>
> Mark
>
>
--
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Optimizing an insert/update
I apologize if this is a double-post; I just got approved for the mailing list, and I can't find the older message in the archives, so I'm reposting it. I have a table with two columns, the first with a string and the second with an integer. Given a set of input strings, I want to perform this operation 50,000+ times preferably in a single transaction: "If the string doesn't exist in the table, create a new row with the string in the first column and 1 in the second column. If the string does exist in the table, increment the second column by 1" What I am doing now is using the UPDATE statement, and checking (via C#) the number of rows affected, and if this is zero, running an insert statement. Doing this 50,000 times, without an encapsulating transaction, is an expensive operation. Is there any way to encapsulate this into SQL so that no outside interaction in C# is needed to perform this operation, and so I can put 50,000 of these into a transaction and commit all at once? Thanks, Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DATETIME data type
"Yong Zhao" <[EMAIL PROTECTED]> wrote
in message
news:[EMAIL PROTECTED]
> It seems that sqlite3 does not support DATETIME data type.
>
> If I have the following data in table t1, how do I select people who
> is older than certain date?
>
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
>
> select * from t1 where dob < '3/24/1950';
Store dates in -MM-DD format instead, then simple string comparison
will also order dates correctly.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Thanks John & Dennis; Looks like I am getting ahead of myself here. I'm just doing a simple PIM app as a BASH script that uses SQLite to store data. Probably I can't do this in a script as you could in C. I find the formating of the text from a file is not saved if I read it and insert it into a table, so I was hoping there was a way to save the binary file and thus preserve all the formatting. Also it would let me save images etc. in the database. When I get back to learning Python, maybe one of the SQLite wrappers will help me do this more easily. My present program is really just a way to get started until I move to Python. Also, it is good practice in BASH scripting, SQL and basic usage of SQLite. Thanks for your information. Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] undefined symbol: sqlite3_prepare_v2
Hi All, The same problem that I had one of linux server and this server didn't have the sqlite3 So I couldn't run command sqlite3 :memory to check for the sqllite version. On this server it has only the sqlite library which is libsqlite3.so.0.8.6. The question is how to check the sqlite version from this library(libsql3.so.0.8.6. Thanks JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, February 27, 2008 10:04:36 AM Subject: Re: [sqlite] undefined symbol: sqlite3_prepare_v2 I have been using 3.5.2 sqlite. I forgot the mention this in my previous email. If you know the answer to this question please help. Thanks, JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, February 27, 2008 9:34:05 AM Subject: [sqlite] undefined symbol: sqlite3_prepare_v2 Hi All, I used the sqlite3_prepare_v2 in my code and some of the servers are ok but one of my server return an error message that "undefined symbol: sqlite3_prepare_v2 " when the process is started and access the database using sqlite3_prepare_v2 . I don't have any clue why this problem is only happened on one single server but no others. Please help if you have an answer Thanks, JP Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DATETIME data type
Friday, February 29, 2008, 8:29:16 AM, you wrote:
YZ> It seems that sqlite3 does not support DATETIME data type.
YZ> If I have the following data in table t1, how do I select people who is
YZ> older than certain date?
YZ> create table t1(dob text, name text);
YZ> insert into t1('11/12/1930', 'Larry');
YZ> insert into t1('2/23/2003', 'Mary');
YZ> select * from t1 where dob < '3/24/1950';
Well I'm very new to SQLite but I think you need to use the formats
specified on "Date And Time Functions" Wiki page:
http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions
Trying to compare dates in the format you have used would require
conversion to something sensible like -MM-DD
I am personally a big fan of the ISO-8601 format and use them
everywhere.
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DATETIME data type
Try using the Sqlite date functions.
Yong Zhao wrote:
> It seems that sqlite3 does not support DATETIME data type.
>
> If I have the following data in table t1, how do I select people who is
> older than certain date?
>
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
>
> select * from t1 where dob < '3/24/1950';
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Join Syntax Questions
I could swear I've done this type of thing before and am sure I'm overlooking something simple. Is this correct syntax? SELECT im.invoice_date as invoice_date,im.pay_by as due_date,im.invoice_id as invoice_id, im.invoice_number as invoice_number,im.invoice_date as created,im.status as status, im.next_invoice as next_invoice, im.tax as tax,im.tax2 as tax2,im.subtotal as subtotal,im.total as total,im.balance_due as balance_due, im.customer_number as customer_number, im.customer_name as customer_name FROM invoice_master as im LEFT JOIN ( SELECT coalesce(sum(payment_applied), 0.00) as total_paid,invoice_id as theiid FROM payments WHERE void='f' AND created <= 1204243199) the_payments on im.invoice_id = the_payments.theiid WHERE im.invoice_date between 1201478400 And 1204243199 AND im.status != 'Forwarded' GROUP BY im.invoice_id ORDER BY im.balance_due DESC,im.invoice_date,im.total DESC,im.customer_name With or without the join I get the exact same result set. I don't even see null results for the columns that are supposed to be pulled in from the join. I have a habit of mixing SQLite and PostgreSQL syntax, have I done it again? -- - Mitchell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DATETIME data type
On 28-Feb-2008, at 1:29 PM, Yong Zhao wrote: > It seems that sqlite3 does not support DATETIME data type. > > If I have the following data in table t1, how do I select people who > is > older than certain date? Use -MM-DD instead of M/D/Y. Available formats described here under Time Strings: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimization Question for SQLite Experts
Folks. Looking for some advice from hardened SQliters... - Our application uses an SQLite 3.4.1 database with 8 tables. 1 of the tables may contain tens or maybe hundreds of thousands of records with about 30 fields. - The tables form a linked tree type hierarchy where one table is the trunk, another is the boughs and then branches, twigs and finally very many leaves. UIDs in the tables allow us to find the children of a record in the next table. In one operation we have to construct a summary of a portion of the tree. This basically means picking the stem, then recursively finding its branches, their twigs and so on. For each twig we have to find all the leaves. The Leaves table has maybe 15000 records and we have a query where we search the Leaves table once for each twig we find. In our part of the tree there might be 200 twigs, and so we end up searching the leaves DB 200 times, to build up the list of leaves attached to the twigs Unlike a real tree, our leaves may be attached to more than one twig, so we need to keep the tables linked but abstracted, hence the separate tables. The problem we have is performance.. forgetting the main part of the tree, let's jump to leaves: - We have queried all the way down to the twigs quite quickly, and we have found 200 twigs. Each twig is connected to a few leaves. in pseudocode: for (z=0;zhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Nested calls to prepare/step/prepare
I wanted to find out if the following is allowed in Sqlite.
sqlite3_prepare_v2
while ( ) {
sqlite3_step
sqlite3_prepare_v2 --- I;m getting a segv here.
while ( ) {
sqlite3_step
}
}
So my questing is, does sqlite allow a prepare to be started while a prior
prepared statement is still open?
Thanks,
Ken
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DATETIME data type
It seems that sqlite3 does not support DATETIME data type.
If I have the following data in table t1, how do I select people who is
older than certain date?
create table t1(dob text, name text);
insert into t1('11/12/1930', 'Larry');
insert into t1('2/23/2003', 'Mary');
select * from t1 where dob < '3/24/1950';
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m
<[EMAIL PROTECTED]> wrote:
>
>
> Hi,
> My table looks like:
> IdName
> 1 1aaa
> 2 01345
> 3 1asdf
>
> I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
> I want to bind it as 'a'.
>
> My Prepare statement is as follows:
>
> Unsigned char u8_ClassificationCode=1;
>
> Unsigned short u16_Input=0x0061;
>
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
> Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK)
>
> {
>
> return SQLITE_DB_ERROR;
>
> }
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
> *)u16_Input,-1,SQLITE_STATIC);
>
> }
>
Since nobody else mentioned it: there's something seriously wrong with
your database design.
But first: Your usage of sqlite3_bind_text16 is incorrect. The
fourth argument, -1, means "My string is NUL-terminated. Use strlen()
to figure out how long my string is and use that.".
However, for that to always work correctly, u16_input needs to be an
array with a NUL terminator:
>> unsigned short u16_input[] = { 'a', '\0' }; <<
Anyway, back to what I was saying: your database design needs
rethinking. 1NF (http://en.wikipedia.org/wiki/First_normal_form)
states that a column should only have one value. However, you seem
to be combining *two* values (Classification Code and Input) into one
column (Name). Therefore, you should be doing this:
>> create table Music ( id integer not null primary key,
classificationCode integer, input text) <<
Table:
id classificationCode input
-- -- -
1 1 aaa
2 0 1345
3 1 asdf
At this point, you would do this:
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE classificationCode = ? AND
input >= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); <<
Note that, if you you want the original form, you can do
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode
|| input as Name FROM MUSIC WHERE classificationCode = ? AND input >=
? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); <<
This will convert classificationCode to a string and join it against
the 'input' column to return your original Name.
>> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
sqlite3_bind_text(pst_SearchPrepareStmt, 2, "a", -1, SQLITE_STATIC);
<<
This also means you can index the string portion of your Name column
separately, and quickly search for something with a specific name
without knowing its classification.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to recover from database corruption? (and why does it happen?)
[resending, with small clarification edits, since I didn't see it,
probably because I wasn't yet subscribed, I hope it won't appear twice]
Hello,
I'm using sqlite 3.3.8 under linux (mandriva 2007.1).
Maybe it's something that's not advisable (I don't know) but in my
program I have various threads, each with the same database file opened.
Additionally I have a couple more programs (one guiless and the other
with a gui to view manipulate the database) that also open the same
database file.
The main program has to run unattended 24/7.
A while ago, the visualization program crashed, leaving the database in
an inconsistent state, hence the main program gave sqlite_corrupt problems.
In order to avoid those problems (and probably I did it wrong), each
time I open the database, I issue a "pragma integrity_check". In case it
fails or every time, with the database opened, I detect an
sqlite_corrupt, I try a vacuum (which I saw that fixed the problems if
invoked manually with the command line utility).
This provokes that the other open connections see the SQLITE_SCHEMA
error (and sometimes, stupidly, I try a vacuum again, that exacerbates
the problem, I'll change that to just retry).
Well, eventually one of the vacuum wiped the database completely clean.
This is what I see in the log of my program(s):
(the uppercase name after [INFO], [ERROR], [AVISO] identifies the thread
and/or the program, in this case "LISTA CARGA" is another program that
is spawned from time to time).
[INFO] LISTA CARGA: startup
[ERROR] LISTA CARGA: PRAGMA integrity_check devuelve error:
[ERROR] LISTA CARGA: rowid 17653 missing from index fifo_carroceria
[ERROR] LISTA CARGA: wrong # of entries in index fifo_carroceria
[ERROR] LISTA CARGA: wrong # of entries in index fifo_referencias
[ERROR] LISTA CARGA: trying a VACUUM
[INFO] LISTA CARGA: VACUUM ok
[INFO] LISTA CARGA: terminated
I would like to know why this happened (I don't see any error previous
to this one).
Then this provoked the following cascade of events:
[ERROR] DESCARGA: sqlite3_step: UPDATE bandejas SET
cantidad=cantidad-1 WHERE numero=4 AND posicion=1 SQLITE_SCHEMA -
database schema has changed
[ERROR] DESCARGA: Detectado fallo en la base de datos, intento un VACUUM
[INFO] DESCARGA: VACUUM ok
this is my fault, I suppose I shouldn't vacuum here, but simply retry,
I'll fix it.
[ERROR] COMPR.NIVEL CARGA: sqlite3_step: SELECT
modelos.referencia,count(*) FROM fifo_fis LEFT OUTER JOIN modelos ON
fifo_fis.modelo=modelos.modelo and fifo_fis.color=modelos.color GROUP BY
fifo_fis.modelo,fifo_fis.color SQLITE_SCHEMA - database schema has changed
[INFO] COMPR.NIVEL CARGA: reintenta 1
ok, the above succeeded by simply retrying.
[ERROR] COMPR.NIVEL CARGA: sqlite3_step: SELECT sum(cantidad) FROM
bandejas WHERE referencia='W06J3809905 S3H' SQLITE_SCHEMA - database
schema has changed
[INFO] COMPR.NIVEL CARGA: reintenta 1
same here
[ERROR] CARGA: sqlite3_step: SELECT ocupacion,cantidad_por_caja FROM
referencias WHERE referencia='W06L6809905G C9Z' SQLITE_SCHEMA - database
schema has changed
[INFO] CARGA: reintenta 1
and here
Then the real problem ensues:
[ERROR] COMUNICACION FIS: sqlite3_step: SELECT secuencia FROM fifo_fis
WHERE nr_carroceria='60920850' SQLITE_CORRUPT - database disk image is
malformed
[ERROR] COMUNICACION FIS: intento un VACUUM
[INFO] COMUNICACION FIS: VACUUM ok
[INFO] COMUNICACION FIS: reintenta 1
[ERROR] COMUNICACION FIS: sqlite3_prepare: SELECT secuencia FROM
fifo_fis WHERE nr_carroceria='60920850' SQLITE_ERROR - no such table:
fifo_fiS
at this point the database is clean: there are no tables.
Apart from the glaring errors on my part that I'll try to fix:
1) is sqlite suitable when you have multiple threads accessing the same
database? Or should I delegate the access in a single thread and
serialize the queries from the various threads?
2) is sqlite suitable when you access the database from multiple programs?
3) why did the first error (rowid missing, wrong # of entries) occur?
4) is "VACUUM" the best way to correct such problems?
5) what is the way to recover from "SQLITE_SCHEMA"? close and reopen the
database?
In case it is useful to spot what I did wrong, this is the procedure I
use to execute a query (it is pascal, but it should be readable):
function TSQLiteQuery.Execute(query: string): boolean;
var vm:pointer;
finished:boolean;
procedure AddRow;
var i:integer;
TempRow:TStringList;
begin
TempRow:=TStringList.create;
for i:=0 to sqlite3_column_count(vm)-1 do
TempRow.Add(StrPas(sqlite3_column_text(vm,i)));
FRows.Add(TempRow);
end;
begin
result:=false;
finished:=false;
FRows.Clear;
if FSQLiteHandle=nil then exit;
FSQLiteReturnId:=sqlite3_prepare(FSQLiteHandle,PChar(query),-1,@vm,nil);
if FSQLiteReturnId<>SQLITE_OK then
begin
Flog.Error('sqlite3_prepare: '+query+' '+SqliteReturnString);
exit;
end;
re
Re: [sqlite] How long time to index this table
On Thu, Feb 28, 2008 at 11:48 AM, Lars Aronsson <[EMAIL PROTECTED]> wrote: > Gilles Ganault wrote: > > > But then, how many people use SQLite to handle 68 milions rows > > in a table? I've got 60 million in one table, and this is with an sqlite2 database. Works just fine, BTW. Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
Simon Davies <[EMAIL PROTECTED]> wrote: > You need a placeholder in the SQL in order to bind a value. > > "SELECT id, Name FROM MUSIC WHERE Name >= '?' LIMIT 1;", '?' is a string literal consisting of a single question mark character - _not_ a parameter placeholder. You want ? without quotes. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How long time to index this table
Gilles Ganault wrote: > But then, how many people use SQLite to handle 68 milions rows > in a table? That's a good question. I don't know. And I don't know if there is a recommended size for SQLite databases. But I was able to create the index in 12 minutes after I set the right cache_size. The usage I foresee for SQLite is that structured data is provided for download and personal analysis, rather than XML dumps or Excel spreadsheets. And today I can easily download a 3 gigabyte database file, which is only 600 megabytes compressed. Over on the unison mailing list (Unison is a tool for doing 2-way rsync, file transfers) we're constantly amazed that people complain they are unable to transfer terabytes in a single transfer. Who could have imagined. Maybe Gordon Moore. My 68 million rows come from the Swedish Wikipedia, which is my small experimentation base before I try this on the full size German or English Wikipedia. But I might have to back down to the Estonian or Faroese Wikipedia to get an even smaller dataset. -- Lars Aronsson ([EMAIL PROTECTED]) Aronsson Datateknik - http://aronsson.se ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
On 28-Feb-2008, at 6:22 AM, Mahalakshmi.m wrote: > if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC > WHERE > Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK) That's not what a bind point looks like. Take a look here: http://www.sqlite.org/c3ref/bind_blob.html I'm not sure you can bind two values in a string this way. Maybe take a look at using one of the sqlite3_printf functions to build the value right into your query string. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 and 5.5.6 Amalgamation
On 2/28/08, John Elrick <[EMAIL PROTECTED]> wrote: > I've poked around the SQLite website and found nothing really definitive > so I am asking here. > > > At one point, the FTS3 extension was listed on the download page, > however, it is not any longer. Based on a few Google hits, I attempted > an experiment to see if it was included in the amalgamation. The > following line produces an error The FTS3 extension source is in the source tree, be it amalgamation or individual files. You have to, however, set the ENABLE_FTS3 flag during compilation to get the capability. See my notes on the wiki on how to do that. http://www.sqlite.org/cvstrac/wiki?p=CompilingFts http://www.sqlite.org/cvstrac/wiki?p=CompilingFtsThree > > create virtual table foo using fts3(content, id primary key) > > Error finalizing prepared statement 1: SQL error or missing database. > > So, can I assume that I am missing something? Since FTS3 received some > major coverage for a while, perhaps a very visible link to whatever has > happened or needs be done would be in order? > > > > > John Elrick > Fenestra Technologies > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
You misunderstand binding. You use it like this -
sql - "SELECT name FROM customers WHERE cust_id = ?";
this_cust_id - "CUST1275";
sqlite3_prepare_v2(...);
sqlite3)bind_text(..., 1, this_cust_id, ...);
You bind a value to the data represented by the ?. Then you reuse the
compiled SQL by successively binding data values to it.
If you are not re-using the compiled SQL you do not use bind.
Mahalakshmi.m wrote:
>
>
> Hi,
>
>
>
> My table looks like:
>
>
>
> IdName
>
> 1 1aaa
>
> 2 01345
>
> 3 1asdf
>
>
>
>
>
> I want the statement to be like:
>
> "SELECT id, Name FROM MUSIC WHERE Name >= '1a' LIMIT 1;"
>
> But using prepare I could not able to get the desired statements.
>
> I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
> I want to bind it as 'a'.
>
>
>
> My Prepare statement is as follows:
>
>
>
> Unsigned char u8_ClassificationCode=1;
>
> Unsigned short u16_Input=0x0061;
>
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
> Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK)
>
> {
>
> return SQLITE_DB_ERROR;
>
> }
>
> else
>
> {
>
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
> *)u16_Input,-1,SQLITE_STATIC);
>
> }
>
>
>
> For the above the return status of sqlite3_prepare is success but not
> properly binded.
>
>
>
> Please help me to solve this.
>
>
>
> Thanks & Regards,
>
> Mahalakshmi.M
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Just get a pointer to the data in the file and the number of bytes and use the sqlite API call to transfer it into the DB. You can get the pointer by either reading the file into local memory or by mmap'ing it. Also look at the API calls which let you process a blob in chunks. A BLOB is called a Binary Large OBject because it accepts anything. Fred J. Stephens wrote: > Mike McGonagle wrote: >> Hello all, >> I was hoping that someone might share some tips on working with Blobs? > I am also curious about this. > For instance, how can I store a file in a table? > Not read the file and store the text, but the binary file itself? > Thanks. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
On Feb 27, 2008, at 7:35 PM, Mike McGonagle wrote: > Wow, Peter, didn't expect that anyone would go to the trouble of > writing a > program on the spot I didn't. :-) That was just a snippet of something I wrote for myself when I first started playing with SQLite. > Just curious, but from those few things that I have seen, it > appears that > you can only put a Blob into the DB if it is already on disc, > right? All > three examples I have seen passed the filename to the database, and > one of > them was working within a server context, so I wasn't sure how the > local > filename would be of any use to a machine that is in another part > of the > room (or anywhere else...). The insert statement has two parameters. One of them happens to be the filename and is bound as a text parameter. That's just there as a means to identify the images and allow for wildcard searches. The actual file data is loaded into memory by the code, not SQLite itself. You just bind the binary blob to the appropriate parameter with the address of the loaded data. Whether the image data was loaded from a file or fetched over a TCP connection doesn't matter, the method of storing the data is the same. Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Rather than doing malloc you can mmap the file and then copy it into the
blob.
Peter A. Friend wrote:
> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
>
>> Hello all,
>> I was hoping that someone might share some tips on working with
>> Blobs? I
>> would like to be able to store some images and sound files in a
>> database,
>> but never having dealt with them, I am kind of at a loss for some
>> examples.
>> I have looked on the web, and there are few examples that were of use.
>
> Well, I wrote a quick and dirty program for stuffing image files into
> a database. You just provide a directory and it stats() each file,
> allocates enough space for the image data, then loads it from disk.
> Sql statement is something like:
>
> char* sql = "insert into i (name, data) values (?, ?);";
>
> Of course if your images are huge this method coud be problematic. I
> believe SQLite supports an incremental way to do this but I haven't
> looked at those calls yet.
>
> while ( (dentry = readdir(dir)) != '\0') {
>if (dentry->d_name[0] == '.')
> continue;
>
>if (fd != -1) {
> close(fd);
> fd = -1;
>}
>
>if (data != '\0') {
> free(data);
> data = '\0';
>}
>
>snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
>stat(fname, &sb);
>
>if ( (data = malloc(sb.st_size)) == '\0') {
> fprintf(stderr, "malloc() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>if ( (fd = open(fname, O_RDONLY, )) == -1) {
> fprintf(stderr, "open() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>if ( (retval = read(fd, data, sb.st_size)) == -1) {
> fprintf(stderr, "read() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>if (retval != sb.st_size) {
> fprintf(stderr, "read() failed\n");
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
> SQLITE_STATIC);
>
>if (rc != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
> (db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
>
>if (rc != SQLITE_OK) {
> fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
> (db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>rc = sqlite3_step(stmt);
>
>if (rc != SQLITE_DONE) {
> fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> exit(1);
>}
>
>sqlite3_reset(stmt);
> }
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
You need a placeholder in the SQL in order to bind a value.
The following is untested but shows the sort of approach...
Unsigned char u8_ClassificationCode=1;
Unsigned short u16_Input=0x0061;
if ( sqlite3_prepare(
gpst_SqliteInstance,
"SELECT id, Name FROM MUSIC WHERE Name >= '?' LIMIT 1;",
-1,
&pst_SearchPrepareStmt,
0) != SQLITE_OK )
{
return SQLITE_DB_ERROR;
}
else
{
char sqlParam[ 1000 ];
sprintf( sqlParam, "%1d%c", u8_ClassificationCode, u16_Input );
sqlite3_bind_text16( pst_SearchPrepareStmt, 2, sqlParam, -1,
SQLITE_STATIC );
}
Rgds,
Simon
2008/2/28 Mahalakshmi.m <[EMAIL PROTECTED]>:
>
>
> Hi,
>
>
>
> My table looks like:
>
>
>
> IdName
>
> 1 1aaa
>
> 2 01345
>
> 3 1asdf
>
>
>
>
>
> I want the statement to be like:
>
> "SELECT id, Name FROM MUSIC WHERE Name >= '1a' LIMIT 1;"
>
> But using prepare I could not able to get the desired statements.
>
> I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
> I want to bind it as 'a'.
>
>
>
> My Prepare statement is as follows:
>
>
>
> Unsigned char u8_ClassificationCode=1;
>
> Unsigned short u16_Input=0x0061;
>
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
> Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK)
>
> {
>
>return SQLITE_DB_ERROR;
>
> }
>
> else
>
> {
>
>sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
>sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
> *)u16_Input,-1,SQLITE_STATIC);
>
> }
>
>
>
> For the above the return status of sqlite3_prepare is success but not
> properly binded.
>
>
>
> Please help me to solve this.
>
>
>
> Thanks & Regards,
>
> Mahalakshmi.M
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF...THEN constructs
Sqlite does not have a built in procedural language like PL/SQL. For certain applications we just added Javascript to cover that requirement. It was straightforward using Spidermonkey and had the advantage of being the same language used by the AJAX applications backended by Sqlite so applications programmers had one less skill to master. Jason Salas wrote: > Hi Igor, > > Thanks for the insight. I'm used to doing stored procedures for web > apps, which conditionally execute statements based on state and/or the > presence of variables. Consider this construct, which I built recently > to populate a table with URL for a web spider bot I built: > > CREATE PROCEDURE AddLinkInfo > ( > @ProviderName VARCHAR(200), > @LinkPath VARCHAR(200), > @LinkText VARCHAR(200) > ) > AS > DECLARE @ProviderIDINT > > -- only store a link if it isn't already listed in the database > IF NOT EXISTS(SELECT LinkPath FROM SpiderBot WHERE LinkPath = @LinkPath) > BEGIN > -- is this a known provider? if not, add it into the DB and > then assign it's new ID > IF EXISTS(SELECT ContentProviderID FROM > SpiderBot_ContentProviders WHERE ProviderName = @ProviderName) > BEGIN > SET @ProviderID= (SELECT ContentProviderID FROM > SpiderBot_ContentProviders WHERE ProviderName = @ProviderName) > END > ELSE > BEGIN > INSERT INTO SpiderBot_ContentProviders VALUES > (@ProviderName) > SET @ProviderID = @@IDENTITY > END > > -- do the main content insertion > INSERT INTO SpiderBot (ContentProviderID,LinkPath,LinkText) > VALUES (@ProviderID,@LinkPath,@LinkText) > END > GO > > How would I got about re-writing something like this in SQLite? Thanks > again for your help. > > > Igor Tandetnik wrote: >> "Jason Salas" <[EMAIL PROTECTED]> wrote in message >> news:[EMAIL PROTECTED] >> >>> I'm used to doing lengthy T-SQL programming in SQL Server, so this is >>> kinda new to me. How does one replicate doing IF...THEN conditional >>> blocks in SQLite 3? >>> >> One typically doesn't. Instead, one implements complex logic in one's >> application that hosts SQLite. >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
Mahalakshmi.m <[EMAIL PROTECTED]> wrote: > I want the statement to be like: > > "SELECT id, Name FROM MUSIC WHERE Name >= '1a' LIMIT 1;" > > But using prepare I could not able to get the desired statements. > > I want to bind unsigned short as text. There are no parameters in your statement. What exactly do you plan to bind to? > Unsigned char u8_ClassificationCode=1; > > Unsigned short u16_Input=0x0061; > > if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC > WHERE Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= > SQLITE_OK) You seem to think sqlite3_prepare works like printf, what with you using %d and %c. It doesn't. %d and %c have no special meaning in a SQL statement. Your statement has no parameters to bind. Try something like this: wchar_t text[10]; wsprintf(text, L"%d%c", u8_ClassificationCode, u16_Input); sqlite3_prepare(gpst_SqliteInstance, "SELECT id, Name FROM MUSIC WHERE Name >= ? LIMIT 1;", -1, &pst_SearchPrepareStmt, 0); sqlite3_bind_text16(pst_SearchPrepareStmt, 1, text, -1, SQLITE_TRANSIENT); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Fred J. Stephens wrote: > For instance, how can I store a file in a table? > Not read the file and store the text, but the binary file itself? Fred, You can't do anything with the contents of a file until you read in into memory. To store a 1MB file in a database you need to decide if you will use the older complete blob API (i.e. sqlite3_bind_blob and sqlite3_column_blob) or the newer incremental I/O API routines sqlite3_blob_open, sqlite3_blob_write, and sqlite3_blob_close). The main difference is the size of the memory buffer needed to hold the data, and the number of times you call the routines. For a 1MB file you need a 1MB memory buffer to use the old API. You will read the entire file into the buffer and call the bind_blob function once to insert the data into the database. If you use the new API's you would first insert a 1MB zeroblob (i.e. 1M of zero data) using the sqlite3_bind_zeroblob function. Next you open the blob using sqlite3_open_blob. Then you could use a 10K buffer, and call the file read and write_blob functions 100 times in a loop to transfer the file into the database. Finally you close the blob. The newer APIs are more complicated to use in that you need to make more function calls to insert the blob, but they allow the application to use less memory, and they can handle data that is larger than what will fit in memory (i.e. it is the only way to insert a 100G file on a machine with only 2G of memory). HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 and 5.5.6 Amalgamation
I've poked around the SQLite website and found nothing really definitive so I am asking here. At one point, the FTS3 extension was listed on the download page, however, it is not any longer. Based on a few Google hits, I attempted an experiment to see if it was included in the amalgamation. The following line produces an error create virtual table foo using fts3(content, id primary key) Error finalizing prepared statement 1: SQL error or missing database. So, can I assume that I am missing something? Since FTS3 received some major coverage for a while, perhaps a very visible link to whatever has happened or needs be done would be in order? John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Keeping ROWID for INSERT OR REPLACE
Neville Franks wrote: > I want to insert a row if its key clm doesn't exist otherwise update > it. I can search for the row and then do either an insert or update > accordingly. However I was wondering whether the SQLite Conflict > Resolution: INSERT OR REPLACE would be more efficient (faster). The > problem is the REPLACE deletes the existing row and INSERT adds a new > one, loosing the ROWID value of the original row, which I need to > keep. > > So my question is should I just forget this and do it the: select -> > if not found insert otherwise update way or is there a way to maintain > the original rowid using INSERT OR REPLACE? > > If not what is the fastest way to check if a row exists, assuming the > search is on a single clm which is indexed. ex. > select myclm from mytable where myclm='abc'; > select count(*) from mytable where myclm='abc'; > add limit 1 to either of the above etc. > Neville, If you need to maintain the rowid (because it is linked to other tables) then the select and insert or update method is required. The best approach is to select the rowid of the row to be updated, so that it can be used directly in the update statement if required. If the row does not exist you will get a null value back and you must do an insert. If the column you are searching has a unique index then there can only be one result row, so there is no need to use a limit 1 clause. In pseudo code you do this: s = prepare(select rowid from mytable where myclm = 'abc') rc = step(s) if (rc == SQLITE_ROW) row = column_int(s, 1) execute(update mytable set ... where rowid = row) else execute(insert into mytable ...) Because SQLite has an index data optimization the first query will only scan the index on mytable, since it can get the required rowid value directly from the index. Also, since you already have the rowid for the row in the update case, the update will not need to reference the index to locate the row, it will go directly to the row in the table using the rowid. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prepare Statement
Hi,
My table looks like:
IdName
1 1aaa
2 01345
3 1asdf
I want the statement to be like:
"SELECT id, Name FROM MUSIC WHERE Name >= '1a' LIMIT 1;"
But using prepare I could not able to get the desired statements.
I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
I want to bind it as 'a'.
My Prepare statement is as follows:
Unsigned char u8_ClassificationCode=1;
Unsigned short u16_Input=0x0061;
if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK)
{
return SQLITE_DB_ERROR;
}
else
{
sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
*)u16_Input,-1,SQLITE_STATIC);
}
For the above the return status of sqlite3_prepare is success but not
properly binded.
Please help me to solve this.
Thanks & Regards,
Mahalakshmi.M
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
your code worked perfectly, thx once more! vl.pavlov wrote: > > thank u very much, > > i'll try :) > > > > Dennis Cote wrote: >> >> vl.pavlov wrote: >>> hello & thanks 4 reply >>> >>> ok, i think i understand, >>> i would like that you, if not prob., write the whole solution once with >>> index on words >>> >> >> Here it is again. >> >> create table words (word text primary key, number integer); >> >> insert or replace into words values(:word, >> coalesce((select number from words where word = :word) + 1, 1)); >> >> I have changed the field name above from the original count to number to >> avoid confusion with the count() function in SQL. >> >> The primary key constraint on the word column creates a unique index on >> the words with the word field in ascending order. That index is used to >> quickly locate a word in the table, or determine that the word is >> missing from the table. >> >> The :word is a parameter to the insert statement that you bind to the >> word you want to insert. If you are generating the SQL as text then >> simply replace that identifier with a literal string containing the word >> to be inserted. >> >> HTH >> Dennis Cote >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15731158.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
