SQLite3 Copy row to different Table ? Help

[Shako]

This is what i currently got for opening a database :

    Status=sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
    Status=sqlite3_initialize();
    Status=sqlite3_open(":memory:",&SqlDatabaseHndl);
    Status=sqlite3_threadsafe();

                                                      
    strcpy(Command,"PRAGMA auto_vacuum = FULL;");
    StepQuery(Command,Dummy,0);
    strcpy(Command,"PRAGMA synchronous = 0;");
    StepQuery(Command,Dummy,0);
    strcpy(Command,"PRAGMA journal_mode = 0;");
    StepQuery(Command,Dummy,0);
    strcpy(Command,"PRAGMA page_size = 4096;");
    StepQuery(Command,Dummy,0);

[Shako]

I still cant figure out what is wrong.

I have attach a database that i created with this program.

Please have a look.

The weird thing that i cant figure out is that the table (Field) from PnlHndl 48 - 50 i cant read or update too but 51> i can.

Can some one please,please help me to figure out whats happening PLEASE?

Here is a very compressed function i used to create the database :

Do the follow for each Panel

{

Get some info from a text file

update Field set FieldInUse = 0;

insert into Field values( SQLPnlHndl,SQLVarName,SQLType,SQLFunction,SQLXPos,SQLYPos,SQLLength,SQLHeight,SQLReverseVideo,SQLVarText[0],SQLVarText[1],

SQLVarText[2],SQLVarText[3],SQLVarText[4],1,0,TabIndex);

update Field set FieldInUse = 1 where rowid = RowNum;

The Get the info that i just inserted using select statements by checking for FieldInUse =1 

}

Message Edited by Shako on 09-02-2009 12:18 PM

Message Edited by Shako on 09-02-2009 12:19 PM

[dummy_decoy]

well, that's strange. using sqlite3.exe, i can read or write anything in your database file without problem.

which version of sqlite3 are you using ? have you tested your sql syntax with the command line tool before pgogramming them ? are you sure your way of using the sqlite3 library is correct ?

[Shako]

I am using 3.6.17, i have not tested it with the command line tool but i did with a sqlite3 database manager, and even the manager cant update or read from those rows.

I think that i am using it correctly because the database is been populated.

Could you perhaps show me the correct way of using the library ?

It is just some of the Field Rows that i cant edit or read, i did mention that.

I read the infromation using the rowid most of the time,and i only read one coloumn cell at any given time. 

I am currently using the three step method which i need in order to retrieve infromation with out declaring global variables.

Message Edited by Shako on 09-02-2009 02:02 PM

[Shako]

I just dont a few tests and i can read and write but i just cant update those rows only.

[dummy_decoy]

looking again at your post: you are using ROWID as the primary key for your table. the SQL samples you posted contained:

"update Field set FieldInUse = 1 where rowid = RowNum;"

where does RowNum come from ? are you guessing row numbers ? then  you might easily encounter a problem as rowid are not always monotonic: failed inserts can lead to holes in the set of ids. 

also i would suggest doing the same but without the whole set of pragma (synchronous, autovacuum, etc.) you posted earlier: these pragma are good for efficiency but have a lot of impact on caching and delayed-writing.

[dummy_decoy]

ha, worse, i found a very possible explanation to your problem, which crosses my last post: read that

http://www.sqlabs.com/blog/2007/01/sqlite-and-unique-rowids-something-you.html

the combination of rowid without autoincrement and auto-vacuum seems to be a bad idea...

for more informations on autoincrement:

http://www.sqlite.org/autoinc.html

(also, i would have created the primary key column from the start for the sake of a good schema. but i was not totally convinced and i could not find any brutal argument to enforce this. the comments in the blog post above gives you plenty of reasons to always create your own primary key and not rely on rowids)

[Shako · Accepted solution]

Thanks for all the help but i found out where the problem was.

In sqlite.c the following statement was the problem:

/*
** Size of struct Mem not including the Mem.zMalloc member.
*/
#define MEMCELLSIZE (size_t)(&(((Mem *)0)->zMalloc))

The solution was to use offsetof(Structure,Member) :

offsetof(Mem,zMalloc);

Thanks again.