problems with id integer primary key autoincrement

[mikegann]

I created a SQLite table with an auto incrementing primary key column named id. (The only thing I know about sql is from the examples)

I read the complete table into an array, as shown below:

'check in a loop from 1 to number of rows
for i = 1 to #s rowcount()
#row = #s #nextrow()
db$(0,i) = #row customer$()
db$(1,i) = #row part$()
db$(2,i) = #row description$()
db$(3,i) = #row summary$()
db$(4,i) = #row responsible$()
db$(5,i) = #row date$()
db$(6,i) = #row status$()
db$(7,i) = #row id$()
print #row id$()
a$ = #row id$()
recordnum$(i) = #row id$()
print recordnum$(i)
next i

I can store the id in the array.
I can print the id directly using print #row id$()
if I assign this value to a string variable (a$) I can print it without an error.

But once I assign its value to an array element, I can't do anything with it. If I try to print the array element or work with it in any way, for example y = val(db$(7,i)) I get the following error:

Runtime Error in program 'CustomerLogRev1': print recordnum$(i)
Message not understood: #asIfString

Basically once you can store the primary key in an array, but then you can't do anything with it.

Any suggestions?

Thanks,

Mike

[Carl Gundel - admin]

Can you provide a very short example that demonstrates the bug?

-Carl

[kokenge]

Without seeing your schema, or how you inserted the data into the database, it's hard to tell what you have in the DB.

For some reason the retrieved recordnum$(i) from your DB is null.
Once the value of recordnum$(i) is null, and you try doing anything with it your program will blow and give you the Message not understood: #asIfString.

One way around it is to use the coalesce(id,'') in your selection statement and it will force any nulls to a blank.

[Carl Gundel - admin]

This should cease to be a problem in the next release.

-Carl

[mikegann]

Carl -

I will get a small example program together in the next day or two - work just got real busy.

Kokenge -

Here is how the table was created:

sqliteconnect #s, dbfile$
query$ = "create table CustomerList (customer text , part text, description text, summary text, responsible char(15),date char(15),status text, id integer primary key autoincrement)"

#s execute(query$)

The value retrieved from the database is not null. I can assign it to a normal variable and it works fine. If the value is null, it happens when it is saved to an array. Here is the work around I am using:

for i = 1 to #s rowcount()
#row = #s #nextrow()
db$(0,i) = #row customer$()
db$(1,i) = #row part$()
db$(2,i) = #row description$()
db$(3,i) = #row summary$()
db$(4,i) = #row responsible$()
db$(5,i) = #row date$()
db$(6,i) = #row status$()
db$(7,i) = #row id$()
a$ = #row id$()
recordnum$(i) = "#tableLink.";val(a$)
editlink$(i) = "#editLink.";val(a$)
actionlink$(i) = "#actionLink.";val(a$)
next i

These handles used in link statements were originally going to be created later when I actually create the html table using the id number stored in the the array. However since I can't use the value after it is saved to an array I just create the handle here. As you can see I can assign the id to a$ and then use val(a$) without any errors. So I am getting the id out of the database.

The problem only comes when I try to assign the id value to an array.

Best regards,

Mike

[kokenge]

Ok!
I created your database. Didn't want to actually create a file so did it in memory. Seems to work. Didn't create the #links, so simply took the # off.

Not sure if it solves your problem, but here is what I did.

sqliteconnect #s, ":memory:"
query$ = "create table CustomerList (customer text , part text, description text, summary text, responsible char(15),date char(15),status text, id integer primary key autoincrement)"
#s execute(query$)
query$ = "INSERT INTO CustomerList VALUES ('A','B','C','D','E','2008-10-10','F',null)"
#s execute(query$)
#s execute(query$)
#s execute(query$)
#s execute(query$)

query$ = "SELECT * from CustomerList"
#s execute(query$)
r = #s rowcount()
dim db$(7,r)
dim recordnum$(r)
dim editlink$(r)
dim actionlink$(r)
print r
for i = 1 to #s rowcount()
#row = #s #nextrow()
db$(0,i) = #row customer$()
db$(1,i) = #row part$()
db$(2,i) = #row description$()
db$(3,i) = #row summary$()
db$(4,i) = #row responsible$()
db$(5,i) = #row date$()
db$(6,i) = #row status$()
db$(7,i) = #row id$()
a$ = #row id$()
recordnum$(i) = "tableLink.";val(a$)
editlink$(i) = "editLink.";val(a$)
editlink$(i) = "actionLink.";val(a$)
print i;" ";recordnum$(i);" ";editlink$(i);" ";editlink$(i);" ";#row id$()
next i
wait


HTH

[mikegann]

Thanks for the sample program - I have added one line to show the problem I was having.

After storing the primary key (id) in an array, you can't work with that array value.

sqliteconnect #s, ":memory:"
query$ = "create table CustomerList (customer text , part text, description text, summary text, responsible char(15),date char(15),status text, id integer primary key autoincrement)"
#s execute(query$)
query$ = "INSERT INTO CustomerList VALUES ('A','B','C','D','E','2008-10-10','F',null)"
#s execute(query$)
#s execute(query$)
#s execute(query$)
#s execute(query$)

query$ = "SELECT * from CustomerList"
#s execute(query$)
r = #s rowcount()
dim db$(7,r)
dim recordnum$(r)
dim editlink$(r)
dim actionlink$(r)
print r
for i = 1 to #s rowcount()
#row = #s #nextrow()
db$(0,i) = #row customer$()
db$(1,i) = #row part$()
db$(2,i) = #row description$()
db$(3,i) = #row summary$()
db$(4,i) = #row responsible$()
db$(5,i) = #row date$()
db$(6,i) = #row status$()
db$(7,i) = #row id$()
a$ = #row id$()
recordnum$(i) = "tableLink.";val(a$)
editlink$(i) = "editLink.";val(a$)
editlink$(i) = "actionLink.";val(a$)
print i;" ";recordnum$(i);" ";editlink$(i);" ";editlink$(i);" ";#row id$()
next i

'******Here is the problem - you can't do anything once the id is stored in an array
print db$(7,i)
'****************

wait

[kokenge]

Ok - I see your problem.
Actually it don't matter what db$(?.?) you print. You get the error.
You can print the db$(?,?) array before you get data from the DB.

I also changed the program to get the data into non array values and you can print them out.

But for some reason. once something is retrieved from the db using #nextrow notation into an array it fails.

I changed using nextrow$() and this works:
for i = 1 to r
result$ = #s nextrow$(" |")
db$(0,i) = trim$(word$(result$,1,"|"))
db$(1,i) = trim$(word$(result$,2,"|"))
db$(2,i) = trim$(word$(result$,3,"|"))
db$(3,i) = trim$(word$(result$,4,"|"))
db$(4,i) = trim$(word$(result$,5,"|"))
db$(5,i) = trim$(word$(result$,6,"|"))
db$(6,i) = trim$(word$(result$,7,"|"))
db$(7,i) = trim$(word$(result$,8,"|"))
a$ = trim$(word$(result$,8,"|"))

You definitely have a bug of some kind.
You should probably post it as a bug..