Create database and add columns?

Databases
1

Hi,
I have the following code which SHOULD create a database, containing 1 Table called Boxes, and a column called SRRef.

Could someone please advise me how I add additional columns.
Do I simply place a comma and then the name of the new column, followed by its type?

Dim db As REALSQLdatabase Dim f As FolderItem Dim result As Boolean f = New FolderItem("SR.db") db = New REALSQLdatabase db.DatabaseFile = f result = db.CreateDatabaseFile If db.Connect() Then db.SQLExecute("CREATE TABLE Boxes(SRRef INTEGER PRIMARY KEY)") db.Commit Else MsgBox("Database not created") End If

Thank you all in advance.

2

I would venture to say that if database exists you don’t want to destroy its contents.
Therefore look at this sql statement.

http://www.w3schools.com/sql/sql_alter.asp

If on the other hand you are not interested in tables contents then just create the table as you want it with the additional column.

3

Brian:
I am not worried about the contents, as this code is only run once in order to create a blank database.
I am just a bit unsure as to how to extend my code above in order to create an additional column.

4

You should use SQLiteDatabase rather than RealSQLDatabase.

Yes, to create additional columns separate them by commas.

Refer to the SQLite section of the Databases chapter in User Guide Book 3: Framework.

Also, the SQLite docs: http://sqlite.org/lang.html

5

Then I would check out this link:

http://www.w3schools.com/sql/sql_create_table.asp

It’s great place to learn about what these commands do.
I also use something called MySQLWorkBench which helps me create sql statements that I embed in my code.

6

CREATE TABLE Boxes(SRRef INTEGER PRIMARY KEY, nextcol TEXT, anothercol INTEGER)

7

Thank you all, guys.

Paul
What is the difference between RealSQLDatabase and SQLite? I thought they were the same?

Would I simply change the following code from this:

db = New REALSQLdatabase

To this:

db = New SQLite
8
  • RealSQLDatabase is deprecated, so it does not get updated to newer versions of SQLite.
  • RealSQLDatabase defaults to AutoCommit = False, so you have to commit everything regardless of whether you start a transaction.
  • SQLiteDatabase only needs Commits if you start a transaction. The AutoCommit property is not available.
  • RowIDs are not automatically added to SELECT statements, so if you use RecordSet.Update, you may have to modify the SQL.
9

Does anyone have any thoughts on how isolating your code for the specific database might be better achieved by saying:

db = new Database
vs
db = new REALSQLdatabase
db = new MySQLCommunityServer

I think the base class is suppose to handle all derived databases.

10

Make your db property “As Database”, but instantiate it with a specific database class:

if dbtype = "sqlite" then
   db = New SqliteDatabase
elseif dbtype = "mysql" then
   db = New MySQLCommunityServer
end

Common functions, like SqlSelect and SqlError will work fine. To use db-specific methods, test for each type and cast accordingly.

if db ISA SqliteDatabase then
   SqliteDatabase(db).DatabaseFile = GetFolderitem("somefile")
elseif db ISA MySQLCommunityServer then
   MySqlCommunityServer(db).Host = "localhost"
   MySqlCommunityServer(db).Username = "me"
   MySqlCommunityServer(db).Password = "secret"
end
if db.Connect then
    ...
11

I figured I’d bring it up so that people were aware of the base class and how it can be used to make as much of the database code as generic as possible. It’s not an uncommon scenario to change database engines in the life cycle of the software, only to find there is a lot of work that could have been generic that was made specific.