Multiple Database Options

We are working on a new project and want to have the option to connect to a database of choice. I have a global property “gdb” and can set that to a database of choice. However when setting a database to one particular class, I am presented with errors when compiling for prepared statements for example.

How would I go about coding for multiple database options?

You would need to build your own class that abstracts the idiosyncrasies of each database and executes a common set of commands. For example prepared statements in SQLite need ? for place holders while other databases may need @1 or so forth. The next thing that comes into play is the functions of each database, such as date and formatting. Of course table data types is also a problem if you are creating tables in code.

I’m in the process of writing a program that uses a PostgreSQL database. When a connection to the server isn’t available it uses as local SQLite database and logs all the changes to be pushed to the main database once a connection becomes available again.

Hi Neil,

what kind of information did you keep in the log file?? and what do you do about time stamp for multi copy of the sqlite database from different users from different country???

I did that too, for sqlite, postgres and mysql. but it’s quite hard to do correctly … it takes time to debug every case.
and I did not do it for prepared statements, they are still for each database kind, I must find a common syntax to handle it for every database.
generally you will use 2 databases: one local and one network.

in my application, i can use the application as a single user (sqlite) or multiuser (cubesql) and check if a login.rsd exist or not. if does, mean it is multi user.

since it the same database file, i can use the same command. i have though about prepared statement which make it more difficult since the sqlite and cubesql is totally different for prepared statement.

[quote=353427:@Paul Oxley]We are working on a new project and want to have the option to connect to a database of choice. I have a global property “gdb” and can set that to a database of choice. However when setting a database to one particular class, I am presented with errors when compiling for prepared statements for example.

How would I go about coding for multiple database options?[/quote]

Use an interface.

Tutorial:
http://vimeo.com/seminarpro/interface

Sample project:
https://osswald.com/koblenz16/dbinterface.zip

also in the code i have to use 2 global property for the database, one for cubesql and one for sqlite and called the appropriate one when executing sql

ID, TableName, ChangeType(Insert,Delete, or Update), TimeStamp

This is an in house app for myself. We currently only cover one state so this isn’t an issue. No time zones to contend with at this point.

Thank you for your comments. I have recently come across SQLdelite @ https://github.com/1701software/SQLdeLite and so far seems to be what I am looking for

I was about to recommend SQLdeLite, but I see you found it. It does all the hard work for you behind the scenes. I use it extensively.