Real SQL - Setting up a SQL DB and It Crashed (Newbie)

Databases
1

Hi All,

really, really new to databases. I have created a simple app that writes to two tables.

I have written the following after reading up around the subject, but my app keeps crashing. Any ideas?

I am running OSX Mavericks - No DB File is created on my desktop where i specified it to be. If i place the test SQL DB that i created onto my desktop all works fine ( i assume this is due to it already having tables and columns in it?)


// Creating the Database (Adding the Tables and the rows)
  
  //Check to make sure that the databaase is created
  
  Dim CreateTables as new REALSQLDatabase
  
  CreateTables.DatabaseFile = SpecialFolder.Desktop.Child("RouterDB.rsd")
  
  CreateTables.SQLExecute "Create Table Devices (DeviceName varchar, MAC varchar)"
  CreateTables.SQLExecute "Create Table RouterInfo (RouterID varchar, Address varchar)"
  
  CreateTables.Close
2

Xojo DB 101: Deprecations.

Don’t use REALSQLDatabase. Use SQLite instead.

3

http://documentation.xojo.com/index.php/Deprecations

4

Um using 2012 Release 2 - this shouldn’t be deprecated should it?

5

I only have the option to use New Real Studio SQL…

6

Ok, you should avoid it for future compatibility. I don’t see in your code the DB creation.

7

Examples:

Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
If Not db.Connect Then  // open existing DB
   //handle error here
End If
Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
If Not db.CreateDatabaseFile Then  // Create a new DB
   //handle error here
End If
8

Hi Rick,

should i be creating and SQLLite DB when mine saves as a RSD (real studio Database) ?

9

Was an example, as I never write REALSQLdbs. Adapt for your case. It’s the same. :wink:

10

I can use this

Still crashes - does this code look right?

11

Try this.

  Dim db As New REALSQLDatabase
  db.DatabaseFile = SpecialFolder.Desktop.Child("RouterDB.rsd")  // Put it where you have rights to read/write.
  If db.CreateDatabaseFile Then
    db.SQLExecute "Create Table Devices (DeviceName varchar, MAC varchar, RouterUp varchar, RouterDown varchar, DateAdded varchar)"
    db.SQLExecute "Create Table RouterInfo (RouterID varchar, Address varchar, Username varchar, Password varchar, URL varchar)"
    db.Close
  Else
    MsgBox("Database not created. Error: " + db.ErrorMessage)
  End If
12

Hey Rick,

That seems to create the RSD fine, however, no table or columns are being created… which i think is what is causing the app crash. I opened the newly created DB file and can confirm that there is no tables.

Is there something wrong with my SQL?

13

if i do this:


Dim db As New REALSQLDatabase
  db.DatabaseFile = SpecialFolder.Desktop.Child("RouterDB.rsd")  // Put it where you have rights to read/write.
  If db.CreateDatabaseFile Then
  Else
    MsgBox("Database not created. Error: " + db.ErrorMessage)
    
  End If
  
  Dim sql As String
  sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
  
  db.SQLExecute(sql)
  
  If db.Error Then
    MsgBox("DB Error: " + db.ErrorMessage)
  Else
    MsgBox("Team table created successfully.")
  End If

I get msg box Team table created successfully.

When i view the DB in Real Studio… there is no table…

I have this in the “OPEN” method on the First Form if you wanted to see it going. All you need to do is create a new project (desktop) and set it in there. It should create the DB file on desktop.

14

I think you need to add a “db.commit” after the db.SQLExecute.

15

Yes. He removed the close.

16

Thanks Rick and Zane!!! i feel like a right prat!!! I’ve used .commit everywhere in my code, but couldn’t see it right under my nose. after adding db.commit all is right again.

Thank you so much for all your help!

  // DB CREATE AND CHECK
  
  Dim db As New REALSQLDatabase
  Dim Table1 as String
  Dim Table2 as String
  
  Table1 = "CREATE TABLE Devices (DeviceName VARCHAR, MAC VARCHAR, RouterUp VARCHAR, RouterDown VARCHAR, DateAdded VARCHAR)"
  Table2 = "CREATE TABLE RouterInfo (RouterID VARCHAR, Address VARCHAR, Username VARCHAR, Password VARCHAR, URL VARCHAR)"
  
  db.DatabaseFile = SpecialFolder.Desktop.Child("RouterDB.rsd")  // Put it where you have rights to read/write.
  If db.CreateDatabaseFile Then
    db.SQLExecute (Table1)
    db.SQLExecute (Table2)
    db.Commit
  Else
    //MsgBox("Database not created. Error: " + db.ErrorMessage)
    
  End If