dbexecute insert (Documentation Bugs and Problems)

by DickStomp ⌂ @, Europe Netherlands Sassenheim, Sunday, June 08, 2014, 20:52 (1024 days ago)

dbexecute "create table dicktest1 (id integer, nummer text, value decimal);"
for n = 1 to 3
dbexecute "insert into dicktest1 (id, nummer, value) values (n, string(n), n);"
next n

gives an error: n unknown as column

--
it must be possible...

dbexecute insert

by Jim ⌂ @, Russell, KY, Monday, June 09, 2014, 00:58 (1023 days ago) @ DickStomp

Your problem is that the BASIC256 variable 'n' is not understood by SQLite. You need to build your SQL statement using string concatenation. Try this:

dbexecute "create table dicktest1 (id integer, nummer text, value decimal);"
for n = 1 to 3
stmt$ = "insert into dicktest1 (id, nummer, value) values (" + n + ", '" + n + "', " + n + ");"
print stmt$
dbexecute stmt$
next n

You could do it all in one statement but sometimes I like to see the SQL statement before it executes.

Happy programming.

Jim

dbexecute insert

by DickStomp ⌂ @, Europe Netherlands Sassenheim, Monday, June 09, 2014, 06:06 (1023 days ago) @ Jim

Thank you Jim, that works fine!

Now the "read back":

dbopenset "select * from dicktest1 order by nummer;"
while dbrow()
print "?";
print dbint(0) + dbstring(1) + dbfloat(2)
end while
print "einde"

Only "einde" is shown... Because there are no "?" shown, I think the table is not filled.

Dick

--
it must be possible...

dbexecute insert

by Jim ⌂ @, Russell, KY, Monday, June 09, 2014, 12:52 (1023 days ago) @ DickStomp

I totally missed one other statement that you need to execute BEFORE you can use SQL statements and that is the DBOPEN statement. You need to tell SQLite where the data will actually be stored.

dbopen "testdatabase.db"
dbexecute "create table dicktest1 (id integer, nummer text, value decimal);"
for n = 1 to 3
stmt$ = "insert into dicktest1 (id, nummer, value) values (" + n + ", '" + n + "', " + n + ");"
print stmt$
dbexecute stmt$
next n

and

dbopen "testdatabase.db"
dbopenset "select * from dicktest1 order by nummer;"
while dbrow()
print "?";
print dbint(0) + dbstring(1) + dbfloat(2)
end while
print "einde"

When you run the code you will see a file (where you saved the program) called "testdatabase.db" that actually contains the database tables and rows.

Jim

dbexecute insert

by DickStomp ⌂ @, Europe Netherlands Sassenheim, Monday, June 09, 2014, 19:10 (1023 days ago) @ Jim

Jim, thank you very much!
Finally results a good working program SQLTEST.KBS:
***************************************************
print "database open"
dbopen "dbtest.db"

print "vorige dicktest1 opruimen"
onerror errortrap
dbexecute "drop table dicktest1;"
offerror

print "nieuwe dicktest1 aanmaken"
dbexecute "create table dicktest1 (id integer, nummer text, value decimal);"

print "tabel vullen"
for n = 1 to 3
print " + n + "
stmt$ = "insert into dicktest1 (id, nummer, value) values (" + n + ", '" + n + "', " + n + ");"
dbexecute stmt$
print stmt$
next n

print "sluit tabel"
dbcloseset

print "teruglezen = read back"

print "open dicktest1"
onerror opvanger
dbopenset "select * from dicktest1 order by nummer;"
print "lezen tot eind set"
while dbrow()
print "?";
print dbint(0) + dbstring(1) + dbfloat(2)
end while
offerror
print "eind set"

print "sluit tabel"
dbcloseset

print "sluit database"
dbclose

print "eind programma"
end

errortrap:
# 't is goed, ga gewoon verder =
# it's ok, carry on
return

# Dit is de vriendelijke fout 'opvanger'
opvanger:
print "Fout opgevangen - op lijn " + lasterrorline + " - foutnummer " + lasterror + " boodschap: " + lasterrormessage + " (" + lasterrorextra + ")"
return

--
it must be possible...

RSS Feed of thread
powered by my little forum