Re: [sqlite] Question about using SQLite3 on Windows Mobile 6+

[Anthony Papillion]

Thank you sir!
Indeed, I am writing it in .NET.

Anthony

- Original Message - 
From: "Roosevelt Anderson" 
To: "General Discussion of SQLite Database" 
Sent: Sunday, March 07, 2010 8:10 PM
Subject: Re: [sqlite] Question about using SQLite3 on Windows Mobile 6+


> I'm guessing you are writing this in .NET. If so, you need to use the
> sqlite ADO.NET provider found here:
>
> http://sqlite.phxsoftware.com/
>
> On Sun, Mar 7, 2010 at 7:33 PM, Anthony Papillion  
> wrote:
>> Hello Everyone,
>>
>> I'm totally new to SQLite and am trying to use it in a new Windows Mobile
>> application. I've downloaded the SQLite3.dll and went to my project and
>> tried to add a reference to the dll. For some reason, when I try to add 
>> the
>> reference, I am told "A reference to sqlite3.dll could not be added" with 
>> no
>> further information.
>>
>> I've been searching Google for a while and can't find a resolution so I'm
>> hoping someone can help me out.
>>
>> Thanks a lot!
>> Anthony Papillion
>> OpenEMR HQ, Inc.
>> www.openemrhq.com
>> ___
>> 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] Question about using SQLite3 on Windows Mobile 6+

[Roosevelt Anderson]

I'm guessing you are writing this in .NET. If so, you need to use the
sqlite ADO.NET provider found here:

http://sqlite.phxsoftware.com/

On Sun, Mar 7, 2010 at 7:33 PM, Anthony Papillion  wrote:
> Hello Everyone,
>
> I'm totally new to SQLite and am trying to use it in a new Windows Mobile
> application. I've downloaded the SQLite3.dll and went to my project and
> tried to add a reference to the dll. For some reason, when I try to add the
> reference, I am told "A reference to sqlite3.dll could not be added" with no
> further information.
>
> I've been searching Google for a while and can't find a resolution so I'm
> hoping someone can help me out.
>
> Thanks a lot!
> Anthony Papillion
> OpenEMR HQ, Inc.
> www.openemrhq.com
> ___
> 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] Question about using SQLite3 on Windows Mobile 6+

[Anthony Papillion]

Hello Everyone,

I'm totally new to SQLite and am trying to use it in a new Windows Mobile 
application. I've downloaded the SQLite3.dll and went to my project and 
tried to add a reference to the dll. For some reason, when I try to add the 
reference, I am told "A reference to sqlite3.dll could not be added" with no 
further information.

I've been searching Google for a while and can't find a resolution so I'm 
hoping someone can help me out.

Thanks a lot!
Anthony Papillion
OpenEMR HQ, Inc.
www.openemrhq.com 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feasability of a Range function

[Darren Duncan]

Jean-Christophe Deschamps wrote:
>> Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i 
>> <= 5"?
>>
>> Then it would also work for ordered types that aren't ordinal, such as 
>> rationals
>> and strings and blobs and dates etc, and it would work for very large 
>> ranges,
>> since there's no conceptual need to generate all the individual values.
>>
>> Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, 
>> 1^..^5,
>> where a ^ means exclude that endpoint and its absence means include.
>>
>> This is more flexible than SQL's BETWEEN, which I believe only covers 
>> one of
>> those 4 options.
> 
> That's getting into generic interval support.  I find this interesting 
> even if its really much more ambitious than my simple-minded (and 
> highly optional) initial need/question.
> 
> I might dig further in this direction someday.  Good point.
> 
> Thanks Darren.

Getting into, yes.

Generally speaking, any time someone is talking about a range in terms of 2 
endpoint values, there are 2 distinct things they want:

1.  Test if a value is between those 2 endpoints, in which case we have an 
interval, and the endpoints can be any ordered type.  Alternate ways to spell 
that generally is a pair of binary order-comparison tests.

Or you support having your interval as a value in and of itself, but that would 
be overkill in a system that doesn't already support collection-typed values. 
That is, I would not recommend SQLite goes to support actual interval/range 
types, but supporting the .. etc as simply a shorthand syntax for existing 
comparison ops it already supports, I would say is reasonable to support, 
essentially an expansion of BETWEEN.

2.  Generate a list of values, in which case you need an ordinal type, or a 
closure to explicitly generate the next list element from a prior one.  Such as 
how one may generically define a "sequence generator".

FYI, my Muldis D language for RDBMSs, and Perl 6, as well as other languages, 
have actual interval types, so you can say "foo in bar" if you want.

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

Re: [sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

>Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i 
><= 5"?
>
>Then it would also work for ordered types that aren't ordinal, such as 
>rationals
>and strings and blobs and dates etc, and it would work for very large 
>ranges,
>since there's no conceptual need to generate all the individual values.
>
>Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, 
>1^..^5,
>where a ^ means exclude that endpoint and its absence means include.
>
>This is more flexible than SQL's BETWEEN, which I believe only covers 
>one of
>those 4 options.

That's getting into generic interval support.  I find this interesting 
even if its really much more ambitious than my simple-minded (and 
highly optional) initial need/question.

I might dig further in this direction someday.  Good point.

Thanks Darren.



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

[sqlite] SQLITE3 Recompiling for FTS3

[hector]

For Windows:

I followed the best of my ability recompiling the most current
SQLITE3.DLL to get the FTS3 incorporated.  I added the compiler directive

SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS

and was able to create a SQLITE3.DLL but it faults when a example test
C/C++ program runs.

No problem compiling with FTS3 disable.  But the DLLs are smaller than
the official sqlite.org website v3.6.22 precompiled binaries For Windows.

My compile:  417,792 sqlite3.dll
Official:511,383 sqlite3.dll

The difference I can only see if the official has a dependency on  std
C++ rtl msvcrt.dllwhere my compiled has no dependency on msvcrt.dll

I am not sure if I have the MS VS2005 project file correct, but I also
recompiled under VC6 too.   All I did to create the project was to add
all the *.C/*.H files to a project and added the defined (for release)

WIN32
NDEBUG
_WINDOWS
_USRDLL
SQLITE3_EXPORTS;
SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_COLUMN_METADATA

I guess, I ideally I would like to able to reproduce the same official
size and then see how to get the FTS3 logic added.

Tips?

Thanks in Advance

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

Re: [sqlite] Feasability of a Range function

[Darren Duncan]

Jean-Christophe Deschamps wrote:
> I'm trying to determine if a Range(from, to) function can be made as an 
> extension function.
> 
> Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, 
> 4, 5) for use in constructs similar to
>   select some_scalar_function(i) where i in range(1, 5);
> without having to build a table holding integers 1..5
> 
> I don't see how that's possible, but maybe someone with better internal 
> knowledge can advise.

Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i <= 5"?

Then it would also work for ordered types that aren't ordinal, such as 
rationals 
and strings and blobs and dates etc, and it would work for very large ranges, 
since there's no conceptual need to generate all the individual values.

Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, 1^..^5, 
where a ^ means exclude that endpoint and its absence means include.

This is more flexible than SQL's BETWEEN, which I believe only covers one of 
those 4 options.

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

Re: [sqlite] how to execute an ATTACH DATABASE?

[Sam Carleton]

There is another condition I didn't see originally:  You have to
attach to the other database BEFORE starting the transaction.

P.S.  Who ever is responsible for putting in helpful error messages,
God bless you!


On Sun, Mar 7, 2010 at 4:34 PM, Sam Carleton  wrote:
> On Sun, Mar 7, 2010 at 4:16 PM, Jean-Christophe Deschamps  
> wrote:
>>
>>>ATTACH DATABASE ?1 as sysDB
>>
>> AFAIK you can't use parameter binding for anything else than litteral
>> values.
>>
>> It makes sense since it would be impossible for the parser and
>> optimizer to evaluate and produce run-time code for a statement without
>> knowing beforehand which database or column the statement actually
>> refers to.
>
> Thank you, I have a sneaking suspicion that was the issue.  I was just
> hoping that I could get away with it, fore I use parameter binding as
> much as a sprintf, as I do to prevent SQL injection.  Should the DB
> path be in singe quotes, double quotes, or no quotes at all?
>
> Sam
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding TRANSACTION

[Sam Carleton]

I figured it out, I was NOT calling sqlite3_finalize(stmt) in the
initial call to setDefaultIsInSlideShow() which was read only, so the
table still had a read lock on it, so the read/write always failed.

On Sun, Mar 7, 2010 at 3:36 PM, Sam Carleton  wrote:
> My use of SQLite is within a Apache module and Axis2/C web services.
> Every request invokes a unique call to sqlite3_open_v2, most of the
> time there are multiple calls to sqlite3_open_v2 in one request. For
> example the module part, which sends configuration to the web servers
> calls sqlite3_open_v2 as read only to initialize the web service, it
> closes the connection prior to the web service being called.  Next the
> web service will open up the database in either read/write or read,
> depending on what it is doing.
>
>
>
> I am using my first transaction, which I think it working fine, except
> I am getting some strange behavior with a call that is made
> immediately after the transaction.
>
> (This client is a test harness designed exclusively to test web
> services, no other clients are running and only in instance of the
> Apache process is running.  I am able to change the order quickly and
> easily, and this order is not indicative to the order things will
> normally be called in.)
>
>
> 1: Client calls the web server method setDefaultIsInSlideShow () as to
> have it return the current state, aka read only.
> 2: The client calls the method enableSlideShow() which creates and
> commits the transaction all in one call, code to follow.
> 3: The client calls setDefaultIsInSlideShow() again, this time to set
> the value.  It is this time the sqlite3_step() returns SQLITE_BUSY,
> EVERY time.
>
> When I move #3 into the #1 spot, it works fine.  What is really
> strange is even when I removed the BEGIN TRANSACTION/COMMIT
> TRANSACTION, I continued to get the error when #3 is #3.  So at this
> point I really don't know what the issue might be.  Oh, another
> strange fact, the two databases are DIFFERENT files.  The transaction
> happens in the event database, the other call acts on the system
> database.  Since I am totally stumped, I am posting the code as
> complete as I can make it:  It is broken up into a number of different
> functions in my code, but I will try to consolidate it as much as
> possible while not leaving out details:
>
> /** #2: enableSlideShow */
>
> #define SQL_SET_ISINSLIDESHOW_CUSTOMER \
> "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId = ?1"
>
> #define SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER
> "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId <> ?1"
>
> enableSlideShow()
> {
>        // Make note this is a different database file then the call below
>        int rc = sqlite3_open_v2(pCfg->pszEventDB, ,
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, zVfs);
>        rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
>
>        rc = UpdateSlideShowTable(env, db,
> SQL_CMD(SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER), nCustomerId,
> AXIS2_FALSE);
>        if( rc == SQLITE_OK) rc = UpdateSlideShowTable(env, db,
> SQL_CMD(SQL_SET_ISINSLIDESHOW_CUSTOMER), nCustomerId, AXIS2_TRUE);
>
>        if( rc == SQLITE_OK)
>                rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);
>        else
>                rc = sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL, 
> NULL);
>
>        sqlite3_close(db);
> }
>
>
>
> static int UpdateSlideShowTable(const axutil_env_t *env, sqlite3 *db,
> const char *zSql, int nByte, int nCustomerId, axis2_bool_t bEnable)
> {
>        adb_imageDTO_t* pImageDTO = NULL;
>        sqlite3_stmt *stmt = NULL;
>        const char* pzTail = NULL;
>
>        int rc = sqlite3_prepare_v2(db, zSql, nByte, , );
>        if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, nCustomerId);
>        if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 2, bEnable ==
> AXIS2_FALSE ? 0 : 1);
>        if( rc == SQLITE_OK) rc = sqlite3_step(stmt);
>
>        sqlite3_finalize(stmt);
>        if(rc == SQLITE_DONE)
>                return SQLITE_OK;
>        return rc;
> }
>
> /** #3: setDefaultIsInSlideShow() that is a reader/writer */
>
> #define SQL_INSERT_DEFAULTISINSLIDESHOW \
>        "UPDATE DBLookup SET ItemValue = ?1 WHERE Category = 'SystemSettings'
> AND ItemName = 'DefaultIsInSlideShow'"
>
> setDefaultIsInSlideShow()
> {
>        // Make know that this is a different database file then the call above
>        int rc = sqlite3_open_v2(pCfg->pszSystemDBFile, ,
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL);
>        sqlite3_stmt *stmt = NULL;
>        const char* pzTail = NULL;
>
>        rc = sqlite3_prepare_v2(db, SQL_CMD(SQL_INSERT_DEFAULTISINSLIDESHOW),
> , );
>        if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, bValue ==
> AXIS2_FALSE ? 0 : 1);
>        if( rc == SQLITE_OK) rc = sqlite3_step(stmt); // *** This returns
> SQLITE_BUSY every time it is called AFTER the transaction above,
> whether or not the 

Re: [sqlite] Problem with SQLite in BCB 4

[a1rex]

Well, sorry to hear that.


I tested the function:


vector  CSGDb::query(char* query, sqlite3 *database)
{
sqlite3_stmt *statement;
vector results;

if(sqlite3_prepare_v2(database, query, -1, , 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{
std::string  val;
char * ptr = 
(char*)sqlite3_column_text(statement, col);
if(ptr)
{
val = ptr;
}
else val = "";

values.push_back(val); 
}
results.push_back(values);
}
else
{
break;   
}
}

sqlite3_finalize(statement);
}
  
return results;
}

  
and it works for me fine.

Could you print the value of val before it is pushed to the values vector?  Of 
course the problem is not Sqlite problem but the proper usage of STL.


- Original Message 
From: Chimerian 
To: sqlite-users@sqlite.org
Sent: Sun, March 7, 2010 4:23:30 PM
Subject: Re: [sqlite] Problem with SQLite in BCB 4

Unfortunately it still doesn't work. I have error in line values.push_back(val);
I tried to run program on Windows XP - I have this same error.

Links to error screens:
http://chimerian.net/error1.jpg
http://chimerian.net/error2.jpg


  __
Looking for the perfect gift? Give the gift of Flickr! 

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

Re: [sqlite] how to execute an ATTACH DATABASE?

[Sam Carleton]

On Sun, Mar 7, 2010 at 4:16 PM, Jean-Christophe Deschamps  
wrote:
>
>>ATTACH DATABASE ?1 as sysDB
>
> AFAIK you can't use parameter binding for anything else than litteral
> values.
>
> It makes sense since it would be impossible for the parser and
> optimizer to evaluate and produce run-time code for a statement without
> knowing beforehand which database or column the statement actually
> refers to.

Thank you, I have a sneaking suspicion that was the issue.  I was just
hoping that I could get away with it, fore I use parameter binding as
much as a sprintf, as I do to prevent SQL injection.  Should the DB
path be in singe quotes, double quotes, or no quotes at all?

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

Re: [sqlite] Problem with SQLite in BCB 4

[Chimerian]

Unfortunately it still doesn't work. I have error in line values.push_back(val);
I tried to run program on Windows XP - I have this same error.

Links to error screens:
http://chimerian.net/error1.jpg
http://chimerian.net/error2.jpg


if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{
std::string  val;
char * ptr = 
(char*)sqlite3_column_text(statement, col);
if(ptr)
{
val = ptr;
}
else val = "";

// now below is a error
values.push_back(val);
}
results.push_back(values);
}
else
{
break;
}



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

Re: [sqlite] how to execute an ATTACH DATABASE?

[Jean-Christophe Deschamps]

>ATTACH DATABASE ?1 as sysDB

AFAIK you can't use parameter binding for anything else than litteral 
values.

It makes sense since it would be impossible for the parser and 
optimizer to evaluate and produce run-time code for a statement without 
knowing beforehand which database or column the statement actually 
refers to. 



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

[sqlite] how to execute an ATTACH DATABASE?

[Sam Carleton]

I just posted a question a few minutes ago and it just dawned on me
that the order I am calling things in that post is be ideal, so much
that I would like to combine both web services calls.  I want the
transaction that enableSlideShow() creates to also execute the SQL
which is called in setDefaultIsInSlideShow().  Since the two calls are
in different databases, I believe this would fall into the "Multi-file
Commit" concept.  I understand the concept from
http://www.sqlite.org/atomiccommit.html, but how does one actually go
about doing a Multi-file Commit?  I just tried to do an ATTACH
DATABASE, but the step is failing:

#define SQL_ATTACH_SYSDB  "ATTACH DATABASE ?1 AS sysdb"

ATTACH DATABASE ?1 as sysDB

static int AttachToSysDb(const axutil_env_t *env, sqlite3 *db,
photo_parata_cfg_t * pCfg)
{
adb_imageDTO_t* pImageDTO = NULL;
sqlite3_stmt *stmt = NULL;
const char* pzTail = NULL;

int rc = sqlite3_prepare_v2(db, SQL_CMD(SQL_ATTACH_SYSDB), , 
);

if( rc == SQLITE_OK) rc = sqlite3_bind_text( stmt, 1,
pCfg->pszSystemDBFile, -1, SQLITE_STATIC);
if( rc == SQLITE_OK) rc = sqlite3_step(stmt);

sqlite3_finalize(stmt);

if(rc == SQLITE_DONE)
return SQLITE_OK;

return rc;
}

Sam

Here is the other post, if you want some context:

On Sun, Mar 7, 2010 at 3:36 PM, Sam Carleton  wrote:
> My use of SQLite is within a Apache module and Axis2/C web services.
> Every request invokes a unique call to sqlite3_open_v2, most of the
> time there are multiple calls to sqlite3_open_v2 in one request. For
> example the module part, which sends configuration to the web servers
> calls sqlite3_open_v2 as read only to initialize the web service, it
> closes the connection prior to the web service being called.  Next the
> web service will open up the database in either read/write or read,
> depending on what it is doing.
>
>
>
> I am using my first transaction, which I think it working fine, except
> I am getting some strange behavior with a call that is made
> immediately after the transaction.
>
> (This client is a test harness designed exclusively to test web
> services, no other clients are running and only in instance of the
> Apache process is running.  I am able to change the order quickly and
> easily, and this order is not indicative to the order things will
> normally be called in.)
>
>
> 1: Client calls the web server method setDefaultIsInSlideShow () as to
> have it return the current state, aka read only.
> 2: The client calls the method enableSlideShow() which creates and
> commits the transaction all in one call, code to follow.
> 3: The client calls setDefaultIsInSlideShow() again, this time to set
> the value.  It is this time the sqlite3_step() returns SQLITE_BUSY,
> EVERY time.
>
> When I move #3 into the #1 spot, it works fine.  What is really
> strange is even when I removed the BEGIN TRANSACTION/COMMIT
> TRANSACTION, I continued to get the error when #3 is #3.  So at this
> point I really don't know what the issue might be.  Oh, another
> strange fact, the two databases are DIFFERENT files.  The transaction
> happens in the event database, the other call acts on the system
> database.  Since I am totally stumped, I am posting the code as
> complete as I can make it:  It is broken up into a number of different
> functions in my code, but I will try to consolidate it as much as
> possible while not leaving out details:
>
> /** #2: enableSlideShow */
>
> #define SQL_SET_ISINSLIDESHOW_CUSTOMER \
> "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId = ?1"
>
> #define SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER
> "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId <> ?1"
>
> enableSlideShow()
> {
>        // Make note this is a different database file then the call below
>        int rc = sqlite3_open_v2(pCfg->pszEventDB, ,
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, zVfs);
>        rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
>
>        rc = UpdateSlideShowTable(env, db,
> SQL_CMD(SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER), nCustomerId,
> AXIS2_FALSE);
>        if( rc == SQLITE_OK) rc = UpdateSlideShowTable(env, db,
> SQL_CMD(SQL_SET_ISINSLIDESHOW_CUSTOMER), nCustomerId, AXIS2_TRUE);
>
>        if( rc == SQLITE_OK)
>                rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);
>        else
>                rc = sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL, 
> NULL);
>
>        sqlite3_close(db);
> }
>
>
>
> static int UpdateSlideShowTable(const axutil_env_t *env, sqlite3 *db,
> const char *zSql, int nByte, int nCustomerId, axis2_bool_t bEnable)
> {
>        adb_imageDTO_t* pImageDTO = NULL;
>        sqlite3_stmt *stmt = NULL;
>        const char* pzTail = NULL;
>
>        int rc = sqlite3_prepare_v2(db, zSql, nByte, , );
>        if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, nCustomerId);
>        if( rc == SQLITE_OK) rc = sqlite3_bind_int( 

Re: [sqlite] Understanding TRANSACTION's

[Sam Carleton]

Please ignore this post, I just followed up with another post with a
lot more info, I am still having the problem, though.

Sam

On Sun, Mar 7, 2010 at 10:27 AM, Sam Carleton
 wrote:
> My use of SQLite is within a Apache module and Axis2/C web services.  At a
> minimum, every request invokes a unique call to sqlite3_open_v2, most of the
> time there are multiple calls to sqlite3_open_v2, one for each specific
> task.  An example would be the module part, which sends configuration to the
> web servers calls sqlite3_open_v2 in readonly to set things up, then it
> closes the connection.  Later the web service will open up the database in
> either read/write or read, depending on what it is doing.
> So far all the inserts/updates have not needed to be wrapped in a
> transaction.  I have finally run into a case where a set of updates do need
> to be wrapped in a tranaction, and things are not working correctly.  Here
> is what I am doing:
> 1: sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
> 2: Calling a function that prepare and execute: UPDATE Customer SET
> IsInSlideShow = 0 WHERE CustomerId <> ?1
> 3: Calling same method that prepare and execute: UPDATE Customer SET
> IsInSlideShow = 1 WHERE CustomerId = ?1
> 4a: if all is successful: sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL,
> NULL);
> 4b: if something fails: sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL,
> NULL);
> This seems to work fine.  The problem comes in the next web service call
> that tries to update the database.  The call to sqlite3_step() always
> returns SQLITE_BUSY when called right after the above web service, but runs
> fine when called before the above web service.  I am guessing that I am
> doing something wrong in the above transaction.  Any thoughts?
> Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding TRANSACTION

[Sam Carleton]

My use of SQLite is within a Apache module and Axis2/C web services.
Every request invokes a unique call to sqlite3_open_v2, most of the
time there are multiple calls to sqlite3_open_v2 in one request. For
example the module part, which sends configuration to the web servers
calls sqlite3_open_v2 as read only to initialize the web service, it
closes the connection prior to the web service being called.  Next the
web service will open up the database in either read/write or read,
depending on what it is doing.



I am using my first transaction, which I think it working fine, except
I am getting some strange behavior with a call that is made
immediately after the transaction.

(This client is a test harness designed exclusively to test web
services, no other clients are running and only in instance of the
Apache process is running.  I am able to change the order quickly and
easily, and this order is not indicative to the order things will
normally be called in.)


1: Client calls the web server method setDefaultIsInSlideShow () as to
have it return the current state, aka read only.
2: The client calls the method enableSlideShow() which creates and
commits the transaction all in one call, code to follow.
3: The client calls setDefaultIsInSlideShow() again, this time to set
the value.  It is this time the sqlite3_step() returns SQLITE_BUSY,
EVERY time.

When I move #3 into the #1 spot, it works fine.  What is really
strange is even when I removed the BEGIN TRANSACTION/COMMIT
TRANSACTION, I continued to get the error when #3 is #3.  So at this
point I really don't know what the issue might be.  Oh, another
strange fact, the two databases are DIFFERENT files.  The transaction
happens in the event database, the other call acts on the system
database.  Since I am totally stumped, I am posting the code as
complete as I can make it:  It is broken up into a number of different
functions in my code, but I will try to consolidate it as much as
possible while not leaving out details:

/** #2: enableSlideShow */

#define SQL_SET_ISINSLIDESHOW_CUSTOMER \
"UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId = ?1"

#define SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER
"UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId <> ?1"

enableSlideShow()
{
// Make note this is a different database file then the call below
int rc = sqlite3_open_v2(pCfg->pszEventDB, ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, zVfs);
rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);

rc = UpdateSlideShowTable(env, db,
SQL_CMD(SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER), nCustomerId,
AXIS2_FALSE);
if( rc == SQLITE_OK) rc = UpdateSlideShowTable(env, db,
SQL_CMD(SQL_SET_ISINSLIDESHOW_CUSTOMER), nCustomerId, AXIS2_TRUE);

if( rc == SQLITE_OK)
rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);
else
rc = sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL, NULL);

sqlite3_close(db);
}



static int UpdateSlideShowTable(const axutil_env_t *env, sqlite3 *db,
const char *zSql, int nByte, int nCustomerId, axis2_bool_t bEnable)
{
adb_imageDTO_t* pImageDTO = NULL;
sqlite3_stmt *stmt = NULL;
const char* pzTail = NULL;

int rc = sqlite3_prepare_v2(db, zSql, nByte, , );
if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, nCustomerId);
if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 2, bEnable ==
AXIS2_FALSE ? 0 : 1);
if( rc == SQLITE_OK) rc = sqlite3_step(stmt);

sqlite3_finalize(stmt);
if(rc == SQLITE_DONE)
return SQLITE_OK;
return rc;
}

/** #3: setDefaultIsInSlideShow() that is a reader/writer */

#define SQL_INSERT_DEFAULTISINSLIDESHOW \
"UPDATE DBLookup SET ItemValue = ?1 WHERE Category = 'SystemSettings'
AND ItemName = 'DefaultIsInSlideShow'"

setDefaultIsInSlideShow()
{
// Make know that this is a different database file then the call above
int rc = sqlite3_open_v2(pCfg->pszSystemDBFile, ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL);
sqlite3_stmt *stmt = NULL;
const char* pzTail = NULL;

rc = sqlite3_prepare_v2(db, SQL_CMD(SQL_INSERT_DEFAULTISINSLIDESHOW),
, );
if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, bValue ==
AXIS2_FALSE ? 0 : 1);
if( rc == SQLITE_OK) rc = sqlite3_step(stmt); // *** This returns
SQLITE_BUSY every time it is called AFTER the transaction above,
whether or not the transaction statements are present.

sqlite3_finalize(stmt);
sqlite3_close(db);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding TRANSACTION's

[Sam Carleton]

My use of SQLite is within a Apache module and Axis2/C web services.  At a
minimum, every request invokes a unique call to sqlite3_open_v2, most of the
time there are multiple calls to sqlite3_open_v2, one for each specific
task.  An example would be the module part, which sends configuration to the
web servers calls sqlite3_open_v2 in readonly to set things up, then it
closes the connection.  Later the web service will open up the database in
either read/write or read, depending on what it is doing.

So far all the inserts/updates have not needed to be wrapped in a
transaction.  I have finally run into a case where a set of updates do need
to be wrapped in a tranaction, and things are not working correctly.  Here
is what I am doing:

1: sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
2: Calling a function that prepare and execute: UPDATE Customer SET
IsInSlideShow = 0 WHERE CustomerId <> ?1
3: Calling same method that prepare and execute: UPDATE Customer SET
IsInSlideShow = 1 WHERE CustomerId = ?1

4a: if all is successful: sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL,
NULL);
4b: if something fails: sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL,
NULL);

This seems to work fine.  The problem comes in the next web service call
that tries to update the database.  The call to sqlite3_step() always
returns SQLITE_BUSY when called right after the above web service, but runs
fine when called before the above web service.  I am guessing that I am
doing something wrong in the above transaction.  Any thoughts?

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

Re: [sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

>Ah. You want table-valued functions, like this:
>
>http://msdn.microsoft.com/en-us/library/ms191165.aspx

Thanks Igor, that's what I had in mind.


>In any case, SQLite doesn't support table-valued functions. The 
>closest thing to it is a virtual table:

OK, got it, but this is a bit of heavy engine for such a marginal use.
I'll do the simple way for now.




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

Re: [sqlite] sqlite compile error

[noel frankinet]

Gary Zigmann a écrit :

Hello,

Since sqlite is a library, could you show us your main program, in order 
to help you ?
Best wishes

Noël Frankinet
>  Good Afternoon,
>
>  Today I downloaded sqlite-amalgamation-3_6_22.zip from the  
>  sqlite.org website because I was looking for a SQL database to  
>  replace MS Access on my machine. I compiled sqlite3.c using a c  
>  compiler and came up with this error:
>
> [Linker error] undefined reference to `winm...@16'
>
>  Can you help me?
>
> Thanks,
>
>   
>> Gary Zigmann, MBA
>> Clinical Data Analyst
>> Gifford Medical Center
>> gzigm...@giffordmed.org
>>
>> This information is confidential. If you are not the intended recipient, 
>> delete this e-mail and any attachments without forwarding it or retaining a 
>> copy. To try to obtain permission to use this transmission or to notify the 
>> sender of the error, call (802) 728-2135 or e-mail gzigm...@giffordmed.org.
>>
>>
>> 
> ___
> 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] Feasability of a Range function

[Igor Tandetnik]

Jean-Christophe Deschamps wrote:
>> Why not just
>> 
>> select some_scalar_function(i) where i between 1 and 5;
> 
> That's because we then get
> No such column: i.

Ah. You want table-valued functions, like this:

http://msdn.microsoft.com/en-us/library/ms191165.aspx

Those should appear where a table may - in your example, in a FROM clause. 
Having one in a WHERE clause makes no sense. WHERE clause never introduces a 
new identifier.

In any case, SQLite doesn't support table-valued functions. The closest thing 
to it is a virtual table:

http://www.sqlite.org/cvstrac/wiki?p=VirtualTables

-- 
Igor Tandetnik

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

Re: [sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

>Why not just
>
>select some_scalar_function(i) where i between 1 and 5;

That's because we then get
No such column: i.

That was not very important.  I would have the use for such possibility 
but I can live without.  My question was just curiosity about whether 
something along the line could work, without any materialized table 
holding successive integers in the needed range.



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

Re: [sqlite] Feasability of a Range function

[Igor Tandetnik]

Jean-Christophe Deschamps wrote:
> I'm trying to determine if a Range(from, to) function can be made as an
> extension function.
> 
> Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3,
> 4, 5) for use in constructs similar to
>  select some_scalar_function(i) where i in range(1, 5);
> without having to build a table holding integers 1..5

I don't see how SQLite would accept that as a valid syntax, no matter how you 
define the function.

However, I don't understand the point of the exercise. Why not just

select some_scalar_function(i) where i between 1 and 5;

-- 
Igor Tandetnik

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

[sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

I'm trying to determine if a Range(from, to) function can be made as an 
extension function.

Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, 
4, 5) for use in constructs similar to
  select some_scalar_function(i) where i in range(1, 5);
without having to build a table holding integers 1..5

I don't see how that's possible, but maybe someone with better internal 
knowledge can advise.

Thank you.



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

Re: [sqlite] Crash after add column

[a1rex]

- Original Message 

>From: VasiliyF4 
>After I try to ADD a column by run the querry "ALTER TABLE x
>ADD y NUMERIC" from my application, I can't use the data base any more. If I
>try to get or save any data at my DB it cause crash of the application.

Of course your old INSERT statements will not work anymore, since INSERT 
statement requires knowledge of all columns in the table.


  __
Looking for the perfect gift? Give the gift of Flickr! 

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

Re: [sqlite] Crash after add column

[a1rex]

>I try to ADD a column by run the querry "ALTER TABLE x
>ADD y NUMERIC" from my application, I can't use the data base any more.

Try this: "ALTER TABLE main.x ADD y NUMERIC" and verify by external tool that 
column x has been added.


  __
Connect with friends from any web browser - no download required. Try the new 
Yahoo! Canada Messenger for the Web BETA at 
http://ca.messenger.yahoo.com/webmessengerpromo.php
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Running SQlite commands from perl 5.8.0

[Golan Yaron]

Hi,
Have anyone managed to automate SQLite commands via perl 5.8.0?
I've tried to install the DBD::SQLite module but it is supportd from 5.8.1 only.

If any of you have any idea, I'd appreciate it.
YG



"This e-mail message may contain confidential, commercial or privileged 
information that constitutes proprietary information of Comverse Technology or 
its subsidiaries. If you are not the intended recipient of this message, you 
are hereby notified that any review, use or distribution of this information is 
absolutely prohibited and we request that you delete all copies and contact us 
by e-mailing to: secur...@comverse.com. Thank You."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash after add column

[A.]

On Sat, 2010-03-06 at 23:39 -0800, VasiliyF4 wrote:
> I Use C/Linux. After I try to ADD a column by run the querry "ALTER TABLE x
> ADD y NUMERIC" from my application, I can't use the data base any more. If I
> try to get or save any data at my DB it cause crash of the application.

Could you provide us with some reproducible steps?

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