Re: [sqlite] sqlite + .net cf

[Tom Lancaster]

On Fri, Aug 1, 2008 at 5:49 PM, Στράτος Νικολαΐδης
<[EMAIL PROTECTED]> wrote:
> Tom Lancaster wrote:
>>
>> i'm considering using sqlite as a replacement for sql server ce in a
>> mobile device that periodically syncs with a web server.
>
> It's the right thing to do. I did it and everything's working just fine!
>

Good to know.

>>
>> What I would like to know from others who might have experience is:
>>
>> would I need to restart my mobile application in order to see the new 
>> database.
>
> No, the only thing you have to do, is to disconnect from the database
> file, update it with the new one and reconnect.
>
>> I ask because sqlite is an embedded thing rather than a database
>> server; perhaps data is  cached in-memory.
>
> By disconnecting from the database, the file is closed, so the system
> flushes all cached data.
>

Great! That's what I needed to know.

Thanks.

Tom

> ___
> 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] More on 'Works only for short strings'

[David Nelson]

More on 'works only for small strings':

I replaced the sqlite3_exec code with sqlite3_prepare(),
sqlite3_step() and sqlite3_finalize() only to find that it produced
the same 'logic error' [SQLITE_ERROR] in the step() function.

Then, I changed the way I create the record (below) to where I
create an initial short record and then sequentially UPDATE each
column.  The UPDATE fails exactly when the total size of the
record exceeds 1024 bytes.  In the 'for' loop, it attempts 16 UPDATES
but fails after 8 of them, there being 128 bytes per column in the UPDATE.

The CL prog, sqlite3.exe displays a record of 1027 characters, the last 8
columns
being blank.  I have done C++ gymnastics to triangulate this problem and
everything
seems consistent with the assertion that I cannot create a record that
exceeds 1024 bytes, strange as that may seem.


Am now thinking there's something wrong with how sqlite gets compiled
(using MS Vers 6 Visual Studio)
The sqlite source shows:
** This amalgamation was generated on 2008-04-16 15:43:25 UTC.

BTW, some SQL document referenced a 'size' attribute of the database having
a property of 'tiny', 'medium', or 'large'.  I have no idea what this might
mean.

dave

The C++ code:
strSql.Format( "insert or replace into Event values( '%s', '%s', '%s', '%s',
'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
'%s', '%s', '%s' );", strKey, szSQLDateTime, strArgs + " /t " + szDateTime,
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" );

sqlite3_exec( m_pdbData, strSql, &Callback, this, &lpszErr );  // this works
ok

for( int i=0; i<16; i++ )  {
if( !m_strMotionData[i].IsEmpty() )  {
CString strQ;
strQ.Format( "UPDATE Event SET MotionData%d = '%s' WHERE Key =
'%s';", i+1, m_strMotionData[i], strKey );
if( sqlite3_exec( m_pdbData, strQ, &Callback, this, &lpszErr ) != 0
)  {  // fails when total record exceeds 1024 chars
 FireOnError( "update err" );
 FireOnError( m_strMotionData[i] ); // list data... shows 128
bytes of '0' chars
 }
 }
  }
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] More on 'Works only for short strings'

[Alexey Pechnikov]

Hello!

If you can't write C++ code then write C code. I did send C example some time 
ago and there are a lot of examples in internet. Try to write and compile C 
code and after that you'll can start to learn C++.

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

Re: [sqlite] More on 'Works only for short strings'

[David Nelson]

must be something i said that was confusing...  I've been developing
in C++ for over 20 years, programming in general for 45 years!

dave

On Sun, Aug 3, 2008 at 9:41 AM, Alexey Pechnikov <[EMAIL PROTECTED]> wrote:

> Hello!
>
> If you can't write C++ code then write C code. I did send C example some
> time
> ago and there are a lot of examples in internet. Try to write and compile C
> code and after that you'll can start to learn C++.
>
> Best regards, Alexey.
> ___
> 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] More on 'Works only for short strings'

[Alexey Pechnikov]

Hello!

В сообщении от Sunday 03 August 2008 17:57:33 David Nelson написал(а):
> must be something i said that was confusing...  I've been developing
> in C++ for over 20 years, programming in general for 45 years!

But why you wan't to try C code? Problem may be with MCF CString or your 
compilation; it's simple to replace CString to char* and identify the 
problem.

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

Re: [sqlite] More on 'Works only for short strings'

[David Nelson]

ok, good idea and easy to do, so i did it

converted CStrings to char szQ[32768]

unfortunately, same problem

On Sun, Aug 3, 2008 at 11:06 AM, Alexey Pechnikov <[EMAIL PROTECTED]>wrote:

> Hello!
>
> В сообщении от Sunday 03 August 2008 17:57:33 David Nelson написал(а):
> > must be something i said that was confusing...  I've been developing
> > in C++ for over 20 years, programming in general for 45 years!
>
> But why you wan't to try C code? Problem may be with MCF CString or your
> compilation; it's simple to replace CString to char* and identify the
> problem.
>
> Best regards, Alexey.
> ___
> 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] More on 'Works only for short strings'

[David Nelson]

for anyone still having patience with me, here are links to the
created database and a log4.txt file which is the result of sqlite.exe
cmd line doing a select *

this is a link to the database
http://www.savoysoft.com/EvtMgrRequestx05A

this is a link to the log file
http://www.savoysoft.com/log4.txt

Notice that each record grows in size until it gets to 1024 total.
Then, i get errors on the UPDATE.

dave

On Sun, Aug 3, 2008 at 11:36 AM, David Nelson <[EMAIL PROTECTED]> wrote:

> ok, good idea and easy to do, so i did it
>
> converted CStrings to char szQ[32768]
>
> unfortunately, same problem
>
>
> On Sun, Aug 3, 2008 at 11:06 AM, Alexey Pechnikov <[EMAIL PROTECTED]>wrote:
>
>> Hello!
>>
>> В сообщении от Sunday 03 August 2008 17:57:33 David Nelson написал(а):
>> > must be something i said that was confusing...  I've been developing
>> > in C++ for over 20 years, programming in general for 45 years!
>>
>> But why you wan't to try C code? Problem may be with MCF CString or your
>> compilation; it's simple to replace CString to char* and identify the
>> problem.
>>
>> Best regards, Alexey.
>> ___
>> 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] More on 'Works only for short strings'

[David Nelson]

for anyone still having patience with me, here are links to the
created database and a log4.txt file which is the result of sqlite.exe
cmd line doing a select *

this is a link to the database
http://www.savoysoft.com/EvtMgrRequestx05A

this is a link to the log file (corrected)
http://www.savoysoft.com/log4.txt

Notice that each record grows in size until it gets to 1024 total.
Then, i get errors on the UPDATE.

dave

On Sun, Aug 3, 2008 at 11:06 AM, Alexey Pechnikov <[EMAIL PROTECTED]>wrote:

> Hello!
>
> В сообщении от Sunday 03 August 2008 17:57:33 David Nelson написал(а):
> > must be something i said that was confusing...  I've been developing
> > in C++ for over 20 years, programming in general for 45 years!
>
> But why you wan't to try C code? Problem may be with MCF CString or your
> compilation; it's simple to replace CString to char* and identify the
> problem.
>
> Best regards, Alexey.
> ___
> 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] More on 'Works only for short strings'

[Hans-Juergen Taenzer]

David Nelson  ([EMAIL PROTECTED]) wrote:

> Am now thinking there's something wrong with how sqlite gets
> compiled (using MS Vers 6 Visual Studio)

Switch off all optimisations when compiling.
I more often had problems with MSVC in the past.

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

Re: [sqlite] More on 'Works only for short strings'

[Alexey Pechnikov]

Hello!

See code 
http://paste.pocoo.org/show/81137/

Compile as
gcc sqlite_test.c -o sqlite_test -lsqlite3

Create table log in database log.db as
$ sqlite3 log.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table log(value); 
sqlite> .q

Start program
./sqlite_test

And see result
$ sqlite3 log.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select length(value) from log;
3325

And "what I'm doing wrong"? :-)

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

Re: [sqlite] More on 'Works only for short strings'

[David Nelson]

PROBLEM SOLVED

Thank you Hans-Jürgen.

Your suggestion to disable msvc compiler optimizations seems to
have solved the problem... I can now create large records.  MSVC
optimizations apparently limited the size to 1024.

Sorry for all the trouble I caused.  Perhaps we learned something... for
sure, I did.

I can't thank you enough.

dave

On Sun, Aug 3, 2008 at 12:40 PM, Hans-Juergen Taenzer <
[EMAIL PROTECTED]> wrote:

> David Nelson  ([EMAIL PROTECTED]) wrote:
>
> > Am now thinking there's something wrong with how sqlite gets
> > compiled (using MS Vers 6 Visual Studio)
>
> Switch off all optimisations when compiling.
> I more often had problems with MSVC in the past.
>
> Hans-Jürgen
> ___
> 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] More on 'Works only for short strings'

[Teg]

Hello David,

Sunday, August 3, 2008, 3:29:10 PM, you wrote:

DN> PROBLEM SOLVED

DN> Thank you Hans-Jürgen.

DN> Your suggestion to disable msvc compiler optimizations seems to
DN> have solved the problem... I can now create large records.  MSVC
DN> optimizations apparently limited the size to 1024.

DN> Sorry for all the trouble I caused.  Perhaps we learned something... for
DN> sure, I did.

DN> I can't thank you enough.

DN> dave

DN> On Sun, Aug 3, 2008 at 12:40 PM, Hans-Juergen Taenzer <
DN> [EMAIL PROTECTED]> wrote:

>> David Nelson  ([EMAIL PROTECTED]) wrote:
>>
>> > Am now thinking there's something wrong with how sqlite gets
>> > compiled (using MS Vers 6 Visual Studio)
>>
>> Switch off all optimisations when compiling.
>> I more often had problems with MSVC in the past.
>>
>> Hans-Jürgen
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
DN> ___
DN> sqlite-users mailing list
DN> sqlite-users@sqlite.org
DN> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

My guess is this "fix" is just covering up a coding problem. I used
MSVC6 for years with 02 level optimization and never had an optimizer
problem what wasn't something I caused by assuming initializer values
or something else like that. "Whole Program" optimization though never
worked for me.

You can compile and link the optimized version with debug information
and step through it to see what's going on.

-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

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

[sqlite] Updating same tables in attached databases.

[A. H. Ongun]

I have two databases, an in memory main database consisting of a single table, 
and a disk based database consisting of multiple tables one of which is also 
the same table as the one in memory database.

Let's say in memory database table "foo" consists of records 1-4000.  The "foo" 
table in attached database "db2"  consists of records 3000-4000.

All writes are updates after initializing the in-memory database.  What I want 
to do is execute "UPDATE foo set val=xxx, where recordid = ".

Now, I wish to write to both the attached database, and in-memory database when 
recordid  is between 3000-4000.

Is there a way to do this without executing the update statement twice, once 
for foo, and also for db2.foo (which means conditional logic, testing for 
recordid range)?

Thanks in advance.



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