No such table error on second run?

Databases
1

I have a desktop app. On first run it checks for a database, if not found, it creates one (REALSQLDatabase) Then creates a table called DBConnect, then adds a record set with host, user, pass and name for connecting to a postgre database on the net (in this example code and for testing it is just going to a localhost). It populates the database with default settings.

In Open Event of App:

PawnLocalDB = New REALSQLDatabase
PawnLocalDB.databaseFile = GetFolderItem(“Pawn.rsd”)
if PawnLocalDB.databaseFile.exists = true then
if PawnLocalDB.Connect() = false then
DisplayDatabaseError( false )// there was an error connecting to the database
Quit
return
end if
else
CreateDatabaseFile
end

In Method CreateDatabaseFile:

if PawnLocalDB.CreateDatabaseFile = false then
MsgBox “Database Error” + EndOfLine + EndOfLine + “There was an error when creating the database.”
Quit
end if
PawnLocalDB.SQLExecute “create table DBConnect (DBHost varchar, DBUser varchar, DBPass varchar, DBName varchar, DBNum integer NOT NULL PRIMARY KEY)”

dim rec as DatabaseRecord
rec = New DatabaseRecord
rec.Column(“DBHost”) = “localhost”
rec.Column(“DBUser”) = “Avro”
rec.Column(“DBPass”) = “”
rec.Column(“DBName”) = “Avro”
PawnLocalDB.InsertRecord “DBConnect”, rec

First run, it create the local REALSQLDatabase, apparently create the table called DBConnect and populates it with the default record set, because in the next part of the app, it gathers the data from that table and connects to the POSTGREDatabase. Connection is successful.

Second run, it sees the local REALSQLDatabase is there, connects to it, but returns an error stating, table DBConnect does not exist?

dim rs As RecordSet
rs = PawnLocalDB.SQLSelect(“SELECT * FROM DBConnect”)
if PawnLocalDB.error = true then
displayDatabaseError false
return
end

Any Ideas?

2

Are you sure it creates the file ?
Thats the first thing I’d check.
Second I would use the SQliteDatabase class - not REALSQLDatabase
The reason - the table creation needed to be committed in the REALSQLDatabase - which you didn’t so I’m fairly certain thats why it doesn’t exist.

The SqliteDatabase doesn’t work quite the same way (transactions are something YOU have to start & commit)
It doesn’t try to insert things into your sql to make things work - when you want to update rows using recordset editing you need to select the primary keys columns (REALSQLDatabase would try to do this automatically but it caused other problems)

3

Yay, thanks, it was the commit function i was missing.

Will try and migrate over to sqlite, sounds like the best way to go.

Cheers.

4

It just has less “magic” to it :slight_smile: