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)")
MsgBox("Database not created")
Thank you all in advance.
I would venture to say that if database exists you don’t want to destroy its contents.
Therefore look at this sql statement.
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.
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.
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
Then I would check out this link:
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.
CREATE TABLE Boxes(SRRef INTEGER PRIMARY KEY, nextcol TEXT, anothercol INTEGER)
Thank you all, guys.
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
db = New SQLite
Does anyone have any thoughts on how isolating your code for the specific database might be better achieved by saying:
db = new Database
db = new REALSQLdatabase
db = new MySQLCommunityServer
I think the base class is suppose to handle all derived databases.
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
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"
if db.Connect then
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.