[sqlite] corrupt db vacuums clean on 3.2.7 but not 3.5.4 or 3.5.6

2008-03-11 Thread Josh Gibbs
Hi there,

We are having some problem with DB corruption occurring
using 3.5.4.  I don't know the source of the corruption, however
after extensive testing and updating to 3.5.6 in the hope of getting
some resolution to our problems I accidentally ran an older build
of the command line tool and found that it was able to vacuum a
corrupt DB back to a working state.

Once the DB had been vacuumed with 3.2.7, it was then possible
to vacuum it with 3.5.6.

What's the chance of someone having a look at the DB that we
have with the problem and assisting with a patch so we can stay
on the 3.5.x code track?

Thanks, Josh

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Two Phase commit using sqlite (Ken)

2008-03-11 Thread Shailesh Madhukar Birari
Hello,
Ken you are partially correct on what I am trying to implement.
There are two hosts A & B, each having its on copy of databases dbA and dbB.

A commit of a transaction means that it committed on both dbA and dbB. At
any given point of time, both the databases should be exact replica of each
other.
If it cannot be done, is there a way that I can rollback committed operation
in sqlite? Since in my case, it might happen that a commit on B succeeded
but a commit of A failed and then I want to rollback the committed operation
on B.

Or is there a better way to achieve this functionality using sqlite.

Kindly let me know,

Regards
Shailesh

Date: Mon, 10 Mar 2008 08:15:30 -0700 (PDT)
From: Ken <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Two Phase commit using sqlite
To: General Discussion of SQLite Database 
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset=iso-8859-1

Hello Shailesh,

Seems like you may be trying to put a square peg into a round hole.

Sqlite is an embedded database. Meaning it is not "distributed" as such
unless you build the distribution and 2 phase commit logic yourself.

Let me just clarify for a moment, by distributed you mean.
  a. 2 databases and 2 host platforms
  b. A trasnsaction that originates on host A but uses host b
  c. A transaction that upon commit on host a implies a commit on host b.
  d. A transaction that upon error of commit logic, host B will enter its
transaction as "in doubt".
  e. A transaction that is rolled back on host A will also rollback on host
b.


However, Sqlite does have the capability to have two databases. You can
attach a database and run a transaction/rolllback. But I'm not certain that
it could recover from a failure when the databases reside on seperate
filesystems (nfs/san/nas etc) and the DB is utilized by Host B.

Consider this: host A houses A.db and host b houses B.db.
   B.db is accessible to both host a and host b (stored on a shared file
system, but host A.db is stored locally )

  A process on Host A opens A.db and Attaches B.db, opens a transaction and
modifies data such that writes occur to A.db and B.db (ie journal files are
created and modified data is written out).
Then Lets say Host B becomes unavailble.
Then a Process on Host B attempts to utilize B.db I think you'll get a
failure of corruption of some form at this stage since B.db is involved in a
transaction, but the journal file is not available for a recovery.

Hope that helps.
Ken



Shailesh Madhukar Birari <[EMAIL PROTECTED]> wrote: Hello,
I want to implement a distributed database using sqlite. Can some one give
me information of whether SQLite supports two phase commit?

Thanks,
Shailesh.
___
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] SQL Blobs - what am i doing wrong here?(code inside)

2008-03-11 Thread Dennis Cote
C S wrote:
> hi all - i am trying once again to insert an image(an
> array of shorts) into a blob. 
> 
> i created a table that is described as such:
> 
> CREATE TABLE Images{
> imageID integer primary key,
> imageData text not null);
> 
> #1 - is text ok here for a blob? maybe not - this may
> be my entire problem. 
> 

I would recommend using a column type of BLOB for blob data just to 
avoid any confusion.

CREATE TABLE Images (
 imageID integer primary key,
 imageData blob not null);


> anyway instead of using data in an image i just made
> an array of unsigned shorts and filled it. a size of
> 10. 
> 
> so here is what i am doing in the code. imageArray
> holds the unsigned shorts(there are 10 of them)
> 
> char* blob = reinterpret_cast(imageArray);
> 

Generally you should use a void* instead of a char* for blobs.

void* blob = reinterpret_cast(imageArray);

This pointer isn't needed if your imageArray is a true array of unsigned 
shorts rather than a pointer to some dynamically allocated memory.

unsigned short imageArray[10] = {1,2,3,4,5,6,7,8,9,10};


> string myString = "insert into Images(ImageID,
> imageData) values(?, 'blob')";

Note, this 'blob' is a string literal and has no relation at all to the 
blob variable you defined above. You need to use a ? for the second 
parameter as well so that you can bind a value to it later.

string myString = "insert into Images(ImageID, imageData) values(?, ?)";

> 
> //then i want to try to prepare the statement:
> 
> int status = sqlite3_prepare_v2(db, myString.c_str(),
> -1, , NULL);
> if( (status != SQLITE_OK) || (statement == NULL))
>   cout << "Error preparing SQL Statement" << endl;
> 

There is no need to check statement here. SQLite will return an error 
code other than SQLITE_OK if it fails.

> 
> //now i would like to bind the blob:
> 
> status = sqlite3_bind_blob(statement, 1, imageArray,
> 10 * sizeof(unsigned short), SQLITE_TRANSIENT);
> 

The blob will be the second parameter to the statement. This parameter 
can be static as long as the image data will be stable until the 
statement is executed by sqlite3_step().

If you want to use the blob pointer above you should do this:

status = sqlite3_bind_blob(statement, 2, blob,
 10 * sizeof(unsigned short), SQLITE_STATIC);

If imageArray is a true array as shown above you can instead do this:

status = sqlite3_bind_blob(statement, 2, imageArray,
 sizeof(imageArray), SQLITE_STATIC);

You have not bound a value to the first parameter, the imageId column, 
so it will have a null value when the statement executes. This is OK 
since the column is declared as "integer primary key" and SQLite will 
assign a unique ID value.

> 
> //execute statement for each row??
> while( (status = sqlite3_step(statement)) ==
> SQLITE_ROW);
> 

There is no need for a while loop here. An insert statement can only 
step once, and sqlite3_step() will return SQLITE_DONE or some other 
error code.

> //free the prepared statement
> status = sqlite3_finalize(statement);
> if(status != SQLITE_OK)
>   cout << "Error deleting prepared SQL statement" <<
> endl;
> =
> 
> i actually get the last status check output, saying
> that there was an error deleting the statement. when i
> looked that up it says there was a problem with the
> prepare statement being successful or nothing happens
> at all when clearly i did and clearly the status was
> ok too since i didnt get an error message there. 
> 

I'm not sure why the finalize would fail, unless perhaps your prepare 
also failed, and you didn't have a valid statement pointer to pass to 
sqlite3_finalize().

> can anyone help me out as to what might be going on
> here? thanks so much in advance 
> 

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] PHP Code That Can Store and Retrieve Images

2008-03-11 Thread RB Smissaert
Funny you ask that as just 2 days ago I posted a little project
on RAC to do exactly this. In my case it has to be called from
VBA or VB. Unfortunately and surprisingly no takers yet.

RBS


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert L Cochran
Sent: 11 March 2008 21:15
To: General Discussion of SQLite Database
Subject: [sqlite] PHP Code That Can Store and Retrieve Images

Is there open source PHP code (PHP 5.x compatible) that can store and
retrieve images from an SQLite 3.5.6 database?

For SQLite version 3.5.x, I need to use the PHP PDO functions if I am
using PHP 5.2.5, right?

I want to show a group of people about 45 photos which I would like to
store on an SQLite database and then retrieve.

Thanks

Bob Cochran
___
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] SQL Blobs - what am i doing wrong here?(code inside)

2008-03-11 Thread Igor Tandetnik
C S <[EMAIL PROTECTED]> wrote:
> so here is what i am doing in the code. imageArray
> holds the unsigned shorts(there are 10 of them)
>
> char* blob = reinterpret_cast(imageArray);
>
> string myString = "insert into Images(ImageID,
> imageData) values(?, 'blob')";

This statement inserts some value (to be bound later) into ImageID 
column, and a string literal 'blob' into imageData column. Is that 
really what you want? From your problem description, I expected 
something like

insert into Images(imageData) values(?);

> int status = sqlite3_prepare_v2(db, myString.c_str(),
> -1, , NULL);
>
> status = sqlite3_bind_blob(statement, 1, imageArray,
> 10 * sizeof(unsigned short), SQLITE_TRANSIENT);

You are binding the blob to a parameter that the statement will try to 
place into ImageID column. You cannot insert a BLOB into a field 
declared INTEGER PRIMARY KEY.

> //execute statement for each row??

How do you mean? You are running an INSERT statement, not a SELECT 
statement. For each row of what?

> while( (status = sqlite3_step(statement)) ==
> SQLITE_ROW);

Luckily, sqlite3_step will never return SQLITE_ROW when executing INSERT 
(or UPDATE or DELETE) statement.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Using WHERE within a BLOB

2008-03-11 Thread Ben Harper
The short answer is no. If you want to use the database for the
purposes for which it was created, then you have to play by its rules-
which in this case is to create fields for each of those categories.

On Tue, Mar 11, 2008 at 10:38 PM, Derek Developer
<[EMAIL PROTECTED]> wrote:
> Is it possible to search a sequence of bytes within a BLOB?
> Lets say i have a BLOB that contains 4 text fields 32 characters long ie 
> firstname, lastname, street, city.
>
> Can I form a query that looks something like:
> SELECT Bytes33to64 FROM MyTable WHERE Bytes33to64 LIKE 'Jones'
>
> instead of
> SELECT lastname FROM MyTable WHERE lastname LIKE 'Jones'
>
> Is there anyway to do this?
>
> This would allow me to store a single UDT as a BLOB and treat it like a 
> series of columns in a table.
>
>
> -
> Looking for last minute shopping deals?  Find them fast with Yahoo! Search.
> ___
> 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] PHP Code That Can Store and Retrieve Images

2008-03-11 Thread Robert L Cochran
Is there open source PHP code (PHP 5.x compatible) that can store and
retrieve images from an SQLite 3.5.6 database?

For SQLite version 3.5.x, I need to use the PHP PDO functions if I am
using PHP 5.2.5, right?

I want to show a group of people about 45 photos which I would like to
store on an SQLite database and then retrieve.

Thanks

Bob Cochran
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Does an sqlite3_get_table() results set get updated.

2008-03-11 Thread Scott Hess
On Tue, Mar 11, 2008 at 1:30 PM, Neville Franks <[EMAIL PROTECTED]> wrote:
> Wednesday, March 12, 2008, 3:50:46 AM, you wrote:
>  SH> On Mon, Mar 10, 2008 at 6:15 PM, Neville Franks <[EMAIL PROTECTED]> 
> wrote:
>  >>  I'm displaying tree's which are built from SQL queries and I ideally
>  >>  want the tree control to reflect updates to the underlying tables
>  >>  without have to do queries all over again and rebuild the trees from
>  >>  scratch.
>
>  SH> You need to ponder your model a bit longer.  Unless you build it
>  SH> yourself, there exists no thread of control which would allow SQLite
>  SH> to monitor the table for changes and notify you if any happen!
>  SH> Instead, _you_ need to handle this kind of synchronization issue.
>
>  I've not used Triggers, but would have thought they could be very
>  helpful in reflecting table changes in our application views of same.

Triggers can make changes to the database, but, again, there is no
thread of control you can rely on.  There is no server coordinating
things.  Each thread of control runs triggers when it does updates,
which means that if some other connection makes a change, that
connection runs the triggers, and your connection does not, so your
connection will not get an asynchronous notification of the changes.
YOU have to provide that kind of asynchronous stuff.

-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Using WHERE within a BLOB

2008-03-11 Thread Derek Developer
Is it possible to search a sequence of bytes within a BLOB?
Lets say i have a BLOB that contains 4 text fields 32 characters long ie 
firstname, lastname, street, city.

Can I form a query that looks something like:
SELECT Bytes33to64 FROM MyTable WHERE Bytes33to64 LIKE 'Jones'

instead of
SELECT lastname FROM MyTable WHERE lastname LIKE 'Jones'

Is there anyway to do this?

This would allow me to store a single UDT as a BLOB and treat it like a series 
of columns in a table.

   
-
Looking for last minute shopping deals?  Find them fast with Yahoo! Search.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Does an sqlite3_get_table() results set get updated.

2008-03-11 Thread Neville Franks
Tuesday, March 11, 2008, 12:34:02 PM, you wrote:

JS> Neville Franks wrote:
>> Hi John,
>> 
>> Tuesday, March 11, 2008, 11:51:57 AM, you wrote:
>> 
>> JS> Neville Franks wrote:
>> 
Hi John,

Tuesday, March 11, 2008, 10:18:30 AM, you wrote:

JS> Neville Franks wrote:


>>Tuesday, March 11, 2008, 8:48:05 AM, you wrote:
>>
>>JAK> On Tue, Mar 11, 2008 at 08:37:27AM +1100, Neville
>>Franks scratched on the wall:
>>
>>
>>
If I use sqlite3_get_table() and update/insert/delete one of the tables
in the original query, does the results set get updated. ie. If I
iterate through the original results from sqlite3_get_table() will I
see the changes, or do I need to run the sqlite3_get_table() query
anew?

If I do see the changes does this also apply to an sqlite3_get_table()
query that was on a VIEW?
>>
>>
>>JAK>   From :
>>
>>JAK> The sqlite3_get_table() interface is implemented as a wrapper 
>>around
>>JAK> sqlite3_exec(). The sqlite3_get_table() routine does not have 
>>access
>>JAK> to any internal data structures of SQLite. It uses only the 
>>public
>>JAK> interface defined here. As a consequence, errors that occur in 
>>the
>>JAK> wrapper layer outside of the internal sqlite3_exec() call are not
>>JAK> reflected in subsequent calls to sqlite3_errcode() or
>>JAK> sqlite3_errmsg().
>>
>>JAK>   In other words, "No."  You need to run it again.
>>
>>JAK>-j
>>
>>Jay,
>>Thanks, I assumed that would be the case. Dynamically updating GUI's
>>on SQL DB updates appears to be challenging.
>>

JS> Are you using Windows and the WIN API?  If so you can just set up
JS> callbacks to do it.

I am using C++ and Windows. Do you mean use "triggers"?

>> 
>> JS> No.  If you have the data in some form of windows control like a
>> JS> listview when you get a notify messge telling you it is changed you can
>> JS> fire a callback to perform an Sqlite update.  It is a bit tedious to
>> JS> program.  It is quite a while since I wrote such a program so I can only
>> JS> give you a big picture.
>> 
>> JS> By making the edit phase a transaction you can give the user the option
>> JS> of commiting changes or rolling back.
>> 
>> 
>> Thanks, but that isn't what I'm trying to do.
>> 
>> I'm displaying tree's which are built from SQL queries and I ideally
>> want the tree control to reflect updates to the underlying tables
>> without have to do queries all over again and rebuild the trees from
>> scratch.
>> 
JS> In that case a trigger activating a user function which updates the tree
JS> would be a way of doing that.  Depends on number of user, processes etc.
JS>   We use an XML output from the DB to do that is a very ditributed way.

I'm creating XML as well. I've written my own tree control that
interfaces to a data source, without knowing nor caring what the data
source is. It doesn't store any data itself. I was hoping I could use
SQLite as a data source, but that won't work.

I'll start by recreating the XML whenever the underlying SQL changes
and if that proves too slow, I'll look at in situ updates to the XML
DOM. There can be 10's of thousands of nodes in the trees. I'll have
to run some tests with large trees to determine how long it takes to
create same.

-- 
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] Testing the rowid algorithm. Was: Generating new rowid alg o

2008-03-11 Thread drh
Nicolas Williams <[EMAIL PROTECTED]> wrote:
> 
> Slightly OT: The current SQLite3 test code base makes use of internal
> interfaces, which means you can't necessarily test the bits that you
> want to install.  It'd be nice to be able to test the bits actually
> installed. 

See http://www.sqlite.org/cvstrac/wiki?p=ToDo and especially
the 3rd item, second bullet.  We are working on this.  It will
take time.

--
D. Richard Hipp <[EMAIL PROTECTED]>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite and (high) concurrency

2008-03-11 Thread John Stanton
It looks as if you are not syncing correctly.  Do you check for 
SQLITE_BUSY?  If you are using transactions are you certain that you COMMIT?

Are you using mutexes for synchronization or using the Sqlite BUSY checks?

Tore Austraatt wrote:
> Thanks, but I'm afraid this don't add up. 
> I have tested this in numerous examples. Concurrent INSERT's
> disappears into thin air, they leave no trace what so ever. 
> It seems very strange if some of you haven't experienced simular
> problems...?
> 
> John, PRAGMA sync = whatever does not remedy this either.
> Ken, locks are handled without any problem at all...
>  
> Tore.
> ___
> 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] Testing the rowid algorithm. Was: Generating new rowid alg o

2008-03-11 Thread Nicolas Williams
On Tue, Mar 11, 2008 at 02:54:32PM -0400, John Elrick wrote:
> [EMAIL PROTECTED] wrote:
> > Our solution to this problem is that we allow the test script
> > to monkey with the state of the Pseudo-Random Number Generator
> > (PRNG) that SQLite uses to generate random rowids.  On a test
> > build, we have special APIs that will save the current state
> > of the PRNG and that will restore the state of the PRNG to its
> > most recently saved state.
> >   
> 
> There is a fairly common pattern where the random number generator is 
> passed into the system under test as an abstraction.  That permits one 
> to substitute any type of mock generator one would like, including one 
> accessing an array of specifically picked numbers.  Easier to do in 
> languages which either support interfaces or dynamic typing, although I 
> guess you could do it in C as a callback function.

Slightly OT: The current SQLite3 test code base makes use of internal
interfaces, which means you can't necessarily test the bits that you
want to install.  It'd be nice to be able to test the bits actually
installed.  No, I don't have useful suggestions yet as to how to achieve
that.  And yes, the SQLite3 testsuite is impressive.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Testing the rowid algorithm. Was: Generating new rowid alg o

2008-03-11 Thread John Elrick
[EMAIL PROTECTED] wrote:
> "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
>   
>> "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote in
>> message news:[EMAIL PROTECTED]
>> 
>>> So is it possible
>>> that SQLITE will give an error when attempting to insert a record even
>>> if there are free ROWID's?
>>>   
>> Yes, in theory. If you have close to 2^63-1 rows, probing at random for 
>> a ROWID to reuse may just happen to keep hitting occupied ROWIDs. If I 
>> remember correctly, after a certain number of unsuccessful attemps 
>> SQLite gives up and returns an error.
>> 
>
> You remember correctly.  Up to 100 random rowids are attempted.
> If no unused rowid is found, SQLITE_FULL is returned.  
>
> As an aside, I note that this logic presents some interesting
> problems for testing.  How does one verify that random rowid
> algorithm works and that it really does stop after 100 tries
> and return SQLITE_FULL?  How does one construct a test that 
> collides for 100 randomly chosen rowids?  We can force the 
> random-rowid logic to run simply by inserting a row with a 
> rowid of 9223372036854775807. But how can we get collisions 
> to happen 100 times in a row without inserting 
> 18446744073709551616 distinct rows?
>
> Our solution to this problem is that we allow the test script
> to monkey with the state of the Pseudo-Random Number Generator
> (PRNG) that SQLite uses to generate random rowids.  On a test
> build, we have special APIs that will save the current state
> of the PRNG and that will restore the state of the PRNG to its
> most recently saved state.
>   

There is a fairly common pattern where the random number generator is 
passed into the system under test as an abstraction.  That permits one 
to substitute any type of mock generator one would like, including one 
accessing an array of specifically picked numbers.  Easier to do in 
languages which either support interfaces or dynamic typing, although I 
guess you could do it in C as a callback function.


John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite and (high) concurrency

2008-03-11 Thread Ken
Tore,

A few things:
1. Remove the  "or IGNORE" from your insert.
2. You only need to test for BUSY on the BEGIN IMMEDIATE command.
3. Transactions (begin immediate/commit) are beneficial for multi row 
operations. Like inserting a group of records then commit. Your not going to 
see any gain if you just have a singleton.

Some pseudo code:
  do
   begin immediate
  until ! busy || threshold exceeded (your call).

  INSERT (no need to test for busy you have the lock).
  if ( !SQLITE_OK || !SQLITE_DONE) ... call error handler. rollback. etc.
  
  COmmit.
  if ( !SQLITE_OK ) call error handler. rollback. etc.


hth,
Ken

Tore Austraatt <[EMAIL PROTECTED]> wrote: Ken,
sorry, it didn't make a difference, including _LOCKED in my test.
None of the dropped records are involved in lock situations.

an example...
_exec("BEGIN IMMEDIATE..."
sqlite3_mprintf("INSERT OR IGNORE INTO ..."...
_prepare
if _OK
_step
if _DONE
 _exec("COMMIT"...
else if _BUSY || LOCKED
   _exec ("ROLLBACK"...

Thanks !
Tore.
___
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] Does an sqlite3_get_table() results set get updated.

2008-03-11 Thread Scott Hess
On Mon, Mar 10, 2008 at 6:15 PM, Neville Franks <[EMAIL PROTECTED]> wrote:
>  I'm displaying tree's which are built from SQL queries and I ideally
>  want the tree control to reflect updates to the underlying tables
>  without have to do queries all over again and rebuild the trees from
>  scratch.

You need to ponder your model a bit longer.  Unless you build it
yourself, there exists no thread of control which would allow SQLite
to monitor the table for changes and notify you if any happen!
Instead, _you_ need to handle this kind of synchronization issue.

-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite and (high) concurrency

2008-03-11 Thread Dan

On Mar 11, 2008, at 10:57 PM, Tore Austraatt wrote:

> Ken,
> sorry, it didn't make a difference, including _LOCKED in my test.
> None of the dropped records are involved in lock situations.
>
> an example...
> _exec("BEGIN IMMEDIATE..."
> sqlite3_mprintf("INSERT OR IGNORE INTO ..."...
  ^

Maybe that's got something to do with it. Knock out the OR IGNORE
and see what happens.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Database Journal File

2008-03-11 Thread Ovidiu Anghelidi
Hi Ken,

Thanks a lot for the information. 

Sincerely,
Ovidiu

--- Ken <[EMAIL PROTECTED]> wrote:

> The journal file is created when you begin a
> transaction and has nothing to do with in memory.
> (memory db's do not have journal  files).
> 
> When your application commits the journal file is
> delete! This is the atomicity part of ACID. 
> 
> Verify upon exit that you are performing a commit.
> If your not committing at exit then a journal file
> will be left.
> 
> HTH,
> Ken
> 
> 
> 
> Ovidiu Anghelidi <[EMAIL PROTECTED]> wrote: Hi
> there,
> 
> I have a small issue and I was hoping that someone
> might be able to point me in the right direction. 
> 
> I am trying to build a C/C++ application using the
> amalgamation version without having the
> file.db-journal created. I am using version 3.5.3.
> My understanding is that in order to do that I need
> to
> execute the PRAGMA temp_store = MEMORY; as soon as I
> open the database,
> and also to change in the SQLite C amalgamation
> file,
> the value: 
> #ifndef TEMP_STORE
>  #define TEMP_STORE 2 
> #endif
> 
> The application compiles fine and is working but the
> db-journal file is still being created.
> 
> If that is not correct and the above commands have a
> different role, then here is what I am trying to
> accomplish.
> 
> We are running a scientific application on both
> Windows and Linux machines and we are writing
> equation
> results in database files.
> The problem is that the file.db-journal is kept
> "alive" on the disk for a short while, after
> transactions have been commited and the database
> connection is closed; that is causing us some issues
> with another module. I tried the above commands but 
> the journal file is still being created. I also
> tried
> waiting for 60 seconds after the database was closed
> but to no avail. 
> 
> Any help on that would be greatly appreciated. 
> 
> Thank you.
> 
> Best regards,
> Ovidiu Anghelidi
> 
> 
> 
>  
>

> Looking for last minute shopping deals?  
> Find them fast with Yahoo! Search. 
>
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
> ___
> 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
> 



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] garbage mail messages

2008-03-11 Thread Dan

On Mar 11, 2008, at 3:02 PM, [EMAIL PROTECTED] wrote:

>
>
>OK Dan, you have the solution.  The count was including the
> terminating NULL char.  Making it not include the NULL char fixed
> the problem.
>
>Another question:  For an empty result, should I return 0 or -1?
> And should the string be NULL or "" ?  Bear in mind that its an empty
> result - not a NULL result.

   sqlite3_result_text(pContext, "", 0, SQLITE_STATIC)

will work. You could also pass -1 instead of 0 (-1 means use
strlen() or its utf-16 equivalent for result_text16()) to
discover the number of bytes in the string argument.

Passing NULL as the second parameter would not work. That would
result in the SQL user function returning an SQL NULL, not a
zero length string.

Dan.



>
>Thanks a million
> -brett
>
>Quoting Dan Kennedy >
>
>>
>>   I'm trying to get the concat operator to work with my
> user-defined
>> function.  This works fine:
>>
>>   SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM  Employees
>>
>>   But this doesn't work:
>>
>>   SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time)
>> FROM Sessions
>>
>>   I get only the formatted date - missing the formatted time.
>> FORMAT_DATE is my own user-defined function that returns text data
>> type.
> When you call sqlite3_result_text() to return the result, does your
> result string include a nul-terminator character? If so, that byte
> should not be included in the "number of bytes" parameter passed
> to result_text(). i.e. if you were doing:
>
> sqlite3_result_text(pContext, "abc", 4, ...)
> you might get the result you are describing.
> Dan.
>
> 
> This message was sent using IMP, the Internet Messaging Program.
>
> ___
> 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] SQL Newbie problem I guess...

2008-03-11 Thread Jonas Sandman
Thanks for the input.

The 'Folder' vs 'Folders' problem was merely a spelling mistake here,
not in the code.

My problem was that '%' was being escaped in my code. Putting a '%%'
fixed the problem.

Regards,
Jonas

On Tue, Mar 11, 2008 at 5:37 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> Jonas Sandman wrote:
>  >
>  > I must be missing something obvious here...
>  >
>  > I have created my database like this:
>  >
>  > CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER,
>  > rootid INTEGER, path VARCHAR(255))";
>  >
>  > The database is filled with files and folders..
>  >
>  > folderid parentid rootid path
>  > 1 0   1   C:\MP3\Albums\
>  > 2 1   1   C:\MP3\Albums\Abba - Definitive Collection\
>  > 3 2   1   C:\MP3\Albums\Abba - Definitive Collection\cd1\
>  > 4 2   1   C:\MP3\Albums\Abba - Definitive Collection\cd2\
>  >
>  > Then I want to delete 'C:\MP3\Albums' folder and its sub-folders:
>  >
>  > I figured this should work:
>  > DELETE FROM Folder WHERE folderid IN (SELECT folderid FROM Folder
>  > WHERE path LIKE :PATH || '%');
>  >
>  > Where :PATH is 'C:\MP3\Albums\'
>  >
>  > If I run the sub-query by itself it returns the resultset from above
>  > but when I run it like above, sqlite3_changes(..) returns '1' and only
>  > the row with folderid=1 is deleted.
>  >
>  > What am I doing wrong?
>
>  Well, your problem may be that the table name in the subquery and the
>  delete statement, "Folder", is different than the name in the create
>  table statement, "Folders". If I use the correct table name it runs as
>  expected.
>
>  The more interesting thing I discovered when testing this is that having
>  the wrong table name in the subquery causes the sqlite3.exe command
>  shell to terminate.
>
>  The following SQL script terminates when it executes the delete statement.
>
>
>  CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER,
>  rootid INTEGER, path VARCHAR(255));
>
>  insert into Folders values(1,0,1,'C:\MP3\Albums\');
>  insert into Folders values(2,1,1,'C:\MP3\Albums\Abba - Definitive
>  Collection\');
>  insert into Folders values(3,2,1,'C:\MP3\Albums\Abba - Definitive
>  Collection\cd1\');
>  insert into Folders values(4,2,1,'C:\MP3\Albums\Abba - Definitive
>  Collection\cd2\');
>
>  SELECT folderid FROM Folders WHERE path LIKE 'C:\MP3\Albums\' || '%';
>
>  DELETE FROM Folders WHERE folderid IN
>  (SELECT folderid FROM Folder WHERE path LIKE 'C:\MP3\Albums\' || '%');
>
>  Dennis Cote
>
>
> ___
>  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] SQL Newbie problem I guess...

2008-03-11 Thread Dennis Cote
Jonas Sandman wrote:
> 
> I must be missing something obvious here...
> 
> I have created my database like this:
> 
> CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER,
> rootid INTEGER, path VARCHAR(255))";
> 
> The database is filled with files and folders..
> 
> folderid parentid rootid path
> 1 0   1   C:\MP3\Albums\
> 2 1   1   C:\MP3\Albums\Abba - Definitive Collection\
> 3 2   1   C:\MP3\Albums\Abba - Definitive Collection\cd1\
> 4 2   1   C:\MP3\Albums\Abba - Definitive Collection\cd2\
> 
> Then I want to delete 'C:\MP3\Albums' folder and its sub-folders:
> 
> I figured this should work:
> DELETE FROM Folder WHERE folderid IN (SELECT folderid FROM Folder
> WHERE path LIKE :PATH || '%');
> 
> Where :PATH is 'C:\MP3\Albums\'
> 
> If I run the sub-query by itself it returns the resultset from above
> but when I run it like above, sqlite3_changes(..) returns '1' and only
> the row with folderid=1 is deleted.
> 
> What am I doing wrong?

Well, your problem may be that the table name in the subquery and the 
delete statement, "Folder", is different than the name in the create 
table statement, "Folders". If I use the correct table name it runs as 
expected.

The more interesting thing I discovered when testing this is that having 
the wrong table name in the subquery causes the sqlite3.exe command 
shell to terminate.

The following SQL script terminates when it executes the delete statement.

CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER, 
rootid INTEGER, path VARCHAR(255));

insert into Folders values(1,0,1,'C:\MP3\Albums\');
insert into Folders values(2,1,1,'C:\MP3\Albums\Abba - Definitive 
Collection\');
insert into Folders values(3,2,1,'C:\MP3\Albums\Abba - Definitive 
Collection\cd1\');
insert into Folders values(4,2,1,'C:\MP3\Albums\Abba - Definitive 
Collection\cd2\');

SELECT folderid FROM Folders WHERE path LIKE 'C:\MP3\Albums\' || '%';

DELETE FROM Folders WHERE folderid IN
(SELECT folderid FROM Folder WHERE path LIKE 'C:\MP3\Albums\' || '%');

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Testing the rowid algorithm. Was: Generating new rowid algo

2008-03-11 Thread drh
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
> > So is it possible
> > that SQLITE will give an error when attempting to insert a record even
> > if there are free ROWID's?
> 
> Yes, in theory. If you have close to 2^63-1 rows, probing at random for 
> a ROWID to reuse may just happen to keep hitting occupied ROWIDs. If I 
> remember correctly, after a certain number of unsuccessful attemps 
> SQLite gives up and returns an error.

You remember correctly.  Up to 100 random rowids are attempted.
If no unused rowid is found, SQLITE_FULL is returned.  

As an aside, I note that this logic presents some interesting
problems for testing.  How does one verify that random rowid
algorithm works and that it really does stop after 100 tries
and return SQLITE_FULL?  How does one construct a test that 
collides for 100 randomly chosen rowids?  We can force the 
random-rowid logic to run simply by inserting a row with a 
rowid of 9223372036854775807. But how can we get collisions 
to happen 100 times in a row without inserting 
18446744073709551616 distinct rows?

Our solution to this problem is that we allow the test script
to monkey with the state of the Pseudo-Random Number Generator
(PRNG) that SQLite uses to generate random rowids.  On a test
build, we have special APIs that will save the current state
of the PRNG and that will restore the state of the PRNG to its
most recently saved state.

So the test runs like this:

   *  Create a table and insert rowid=9223372036854775807
   *  Save the current state of the PRNG
   *  Loop 101 times:
   +  Restore the state of the PRNG
   +  Insert a new row with a randomly chosen rowid

Sure enough - the loop fails with an SQLITE_FULL error on the
last iteration.

--
D. Richard Hipp <[EMAIL PROTECTED]>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite and (high) concurrency

2008-03-11 Thread Tore Austraatt
Ken,
sorry, it didn't make a difference, including _LOCKED in my test.
None of the dropped records are involved in lock situations.

an example...
_exec("BEGIN IMMEDIATE..."
sqlite3_mprintf("INSERT OR IGNORE INTO ..."...
_prepare
if _OK
_step
if _DONE
 _exec("COMMIT"...
else if _BUSY || LOCKED
   _exec ("ROLLBACK"...

Thanks !
Tore.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Generating new rowid algo

2008-03-11 Thread Dennis Cote
Igor Tandetnik wrote:
> "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> So is it possible
>> that SQLITE will give an error when attempting to insert a record even
>> if there are free ROWID's?
> 
> Yes, in theory. If you have close to 2^63-1 rows, probing at random for 
> a ROWID to reuse may just happen to keep hitting occupied ROWIDs. If I 
> remember correctly, after a certain number of unsuccessful attemps 
> SQLite gives up and returns an error.
> 
> Of course, it is highly unlikely that you will ever have anywhere close 
> to 2^63 rows. Storage requirements alone are prohibitive. The database 
> will likely become unusable long before that.
> 
> In fact, the only realistic way to take the highest possible ROWID, and 
> thus force SQLite into "pick at random" mode, is to explicitly specify 
> the large ROWID when inserting a record. So just don't do that.
> 

There is another possible error when inserting even if there are free 
rowids. That is the SQLITE_FULL error that can be generated if the 
AUTOINC keyword is used. See http://www.sqlite.org/autoinc.html for full 
details.

This keyword may be the solution to the OPs ocncerns as well.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite and (high) concurrency

2008-03-11 Thread Tore Austraatt
Ken,

A. multi process
B. receive all return codes, eg. rc = sqlite3_..(); and handle 
accordingly.
C. Yes. sqlite3_exec(db, "BEGIN IMMEDIATE", NULL,NULL,NULL); and if _step 
OK, ...exec("COMMIT");
D. Good question. In the latest testing, only two processes "compete", but 
at a rather slow rate, 1 sec interv.

It seems that I forgot to test onn _LOCKED (only _BUSY) before retry 
running a test "as we speak" . I'll keep you posted.

Thanks very much!
regards,
Tore

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite and (high) concurrency

2008-03-11 Thread Ken
Tore,

a couple of questions:
A. How are your concurrent inserts implemented,  in  a multi threaded 
application or multi process?
B. What type of error handling do you have in place for failures ?
C. Do you use transactions? If so what how are they initiated? 
D. What do you mean by "high concurrency"? 

In my view there is really no such thing as "high concurrency" just 
concurrency. You need to take care of the error codes from sqlite that indicate 
failure and 1. retry until success or you can just give up. 

Instead of just saying it don't work, could you provide an example? 

I have a concurrent applicaiton and It does work, Not once has sqlite dropped a 
record, it does return SQLITE_BUSY and in some cases SQLITE_LOCKED.

Sqlite is not a concurrent write database. Meaning that you may not have two 
threads or even two processes concurrently writing to the database at the same 
time! Sqlite will return a sqlite_busy error.

Check out the following:
http://sqlite.org/lockingv3.html

HTH,
Ken


Tore Austraatt <[EMAIL PROTECTED]> wrote: Thanks, but I'm afraid this don't add 
up. 
I have tested this in numerous examples. Concurrent INSERT's
disappears into thin air, they leave no trace what so ever. 
It seems very strange if some of you haven't experienced simular
problems...?

John, PRAGMA sync = whatever does not remedy this either.
Ken, locks are handled without any problem at all...
 
Tore.
___
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] SQLite and (high) concurrency

2008-03-11 Thread Tore Austraatt
Thanks, but I'm afraid this don't add up. 
I have tested this in numerous examples. Concurrent INSERT's
disappears into thin air, they leave no trace what so ever. 
It seems very strange if some of you haven't experienced simular
problems...?

John, PRAGMA sync = whatever does not remedy this either.
Ken, locks are handled without any problem at all...
 
Tore.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Generating new rowid algo

2008-03-11 Thread Igor Tandetnik
"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> So is it possible
> that SQLITE will give an error when attempting to insert a record even
> if there are free ROWID's?

Yes, in theory. If you have close to 2^63-1 rows, probing at random for 
a ROWID to reuse may just happen to keep hitting occupied ROWIDs. If I 
remember correctly, after a certain number of unsuccessful attemps 
SQLite gives up and returns an error.

Of course, it is highly unlikely that you will ever have anywhere close 
to 2^63 rows. Storage requirements alone are prohibitive. The database 
will likely become unusable long before that.

In fact, the only realistic way to take the highest possible ROWID, and 
thus force SQLite into "pick at random" mode, is to explicitly specify 
the large ROWID when inserting a record. So just don't do that.

> Please consider this scenario in the worst case. I agree that the
> number
> of ROWID's is so large, but still there is a possibility it might be
> filled.

If you insert a new record every millisecond, it will take some 300 
million years to exhaust ROWIDs. What kind of software do you develop 
that you feel this might come up as even a remote possibility?

> I basically want to understand the algorithm used by SQLITE in
> picking a new ROWID in such scenario.

Which part of "at random" do you find unclear?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQL Newbie problem I guess...

2008-03-11 Thread BareFeet
Hi Jonas,

It seems to me that you only need the path in your query:

DELETE FROM Folders WHERE path LIKE 'C:\MP3\Albums\%';

Alternatively you could keep parentid and just the appended path, ie:

folderid  parentid path
1 0C:\MP3\Albums\
2 1Abba - Definitive Collection\
3 2cd1\
4 2cd2\

But the query to recursively delete would be more difficult.

Tom
BareFeet

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sqlite3_get_table only get 16 rows

2008-03-11 Thread James
Sorry, I have already solved!

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of James
Sent: Tuesday, March 11, 2008 4:34 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3_get_table only get 16 rows

Hi,

  I use command-line to query the table:

# sqlite3 listtable

 sqlite> select Name,Value from TblDeviceInfo;

 AdditionalHardwareVersion

AdditionalSoftwareVersion

Description

DeviceLog

DeviceStatus

EnabledOptions

FirstUseDate

HardwareVersion01B

Manufacturer   III

ManufacturerOUI001A2A

ModelName

ProductClass   Speedstrea

ProvisioningCode   000.000.00

SerialNumber   A1

SoftwareVersion1.09.000

UpTime



  I write a program:

 
===

  #include 

#include 

 

int main(int argc, char **argv){

  sqlite3 *db;

  char *zErrMsg = 0;

  int rc;

  char **result;

  int nrow, ncol, i;

 

  rc = sqlite3_open("listtable", );

  if( rc ){

fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));

sqlite3_close(db);

exit(1);

  }

 

  rc = sqlite3_get_table(db, select Name,Value from TblDeviceInfo", ,
, , );

  if( rc!=SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

sqlite3_free_table(result);

return -1;

  }

 

  fprintf(stderr, "Row = %d, Col = %d\n", nrow, ncol);

  for (i = 0 ; i < nrow ; ++i)

fprintf(stderr,"%s\n", result[i]);

 

  sqlite3_free_table(result);

  sqlite3_close(db);

 

  return 0;

===

The output is

Row = 16, Col = 2

Name

Value

AdditionalHardwareVersion



AdditionalSoftwareVersion

 

Description

 

DeviceLog

 

DeviceStatus

 

EnabledOptions

 

FirstUseDate

 

I don't know why I just get the 16 rows of data. I expect to get the 34 rows
of data.

What should I do?

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] sqlite3_get_table only get 16 rows

2008-03-11 Thread James
Hi,

  I use command-line to query the table:

# sqlite3 listtable

 sqlite> select Name,Value from TblDeviceInfo;

 AdditionalHardwareVersion

AdditionalSoftwareVersion

Description

DeviceLog

DeviceStatus

EnabledOptions

FirstUseDate

HardwareVersion01B

Manufacturer   III

ManufacturerOUI001A2A

ModelName

ProductClass   Speedstrea

ProvisioningCode   000.000.00

SerialNumber   A1

SoftwareVersion1.09.000

UpTime



  I write a program:

 
===

  #include 

#include 

 

int main(int argc, char **argv){

  sqlite3 *db;

  char *zErrMsg = 0;

  int rc;

  char **result;

  int nrow, ncol, i;

 

  rc = sqlite3_open("listtable", );

  if( rc ){

fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));

sqlite3_close(db);

exit(1);

  }

 

  rc = sqlite3_get_table(db, select Name,Value from TblDeviceInfo", ,
, , );

  if( rc!=SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

sqlite3_free_table(result);

return -1;

  }

 

  fprintf(stderr, "Row = %d, Col = %d\n", nrow, ncol);

  for (i = 0 ; i < nrow ; ++i)

fprintf(stderr,"%s\n", result[i]);

 

  sqlite3_free_table(result);

  sqlite3_close(db);

 

  return 0;

===

The output is

Row = 16, Col = 2

Name

Value

AdditionalHardwareVersion



AdditionalSoftwareVersion

 

Description

 

DeviceLog

 

DeviceStatus

 

EnabledOptions

 

FirstUseDate

 

I don't know why I just get the 16 rows of data. I expect to get the 34 rows
of data.

What should I do?

Thank you.

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] garbage mail messages

2008-03-11 Thread brettg


   OK Dan, you have the solution.  The count was including the
terminating NULL char.  Making it not include the NULL char fixed
the problem.

   Another question:  For an empty result, should I return 0 or -1? 
And should the string be NULL or "" ?  Bear in mind that its an empty
result - not a NULL result.

   Thanks a million
-brett

   Quoting Dan Kennedy >

   >
>   I'm trying to get the concat operator to work with my
user-defined
> function.  This works fine:
>
>   SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM  Employees
>
>   But this doesn't work:
>
>   SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time)
> FROM Sessions
>
>   I get only the formatted date - missing the formatted time.
> FORMAT_DATE is my own user-defined function that returns text data
> type.
When you call sqlite3_result_text() to return the result, does your
result string include a nul-terminator character? If so, that byte
should not be included in the "number of bytes" parameter passed
to result_text(). i.e. if you were doing:

sqlite3_result_text(pContext, "abc", 4, ...)
you might get the result you are describing.
Dan.


This message was sent using IMP, the Internet Messaging Program.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users