I wrote a program a while ago that I still use for my administration tasks using RealBasic and RealStudio and the database format is REALDatabaseoldFormat (.rbd)
I would like to migrate the data over to the latest database format in Xojo but also to a Microsoft SQL Server .mdf file (and the MSSQL Express db too) so I can access this via ASP.NET.
Is there a simple way to do this or will I have to export each table to XML or CSV for import into the new database?
What is the procedure to import XML or CSV tables into Xojo (and MSSQL) databases and how can you maintain relationship dependencies when doing so between two separate tables and foreign keys etc?
there’s no automatic migration
you’ll probably need to write a little app that opens the old format and then creates a new sqlite db from it by using database schema & field schema
then read all the data from the old db and insert it into the new one
you could just read the db and write a series of sql scripts BUT then they may need tweaks for each target db that you want to insert the data into
Norman: If that’s the case, what’s the easiest way of inserting bulk data into a MSSQL db?
Creating an SQL insert statement for each record in each table (one table at a time) or exporting the data from each table to csv or XML and then using a tool (if any?) to import it that way?
And how would you define separate table relationships with primary and foreign keys via either route?
There is nothing like a “version of rbd”. rbd is just a file extension. It has nothing to do with the content. You can change the file extension to whatever you want.
You probably can just replace all REALSQLDatabase occurrences in your code with SQLiteDatabase and run your application. As long as the database version is 3.x.
SELECT sqlite_version(); will return the version number. Like this:
// in memory database
Dim db As New SQLiteDatabase()
Call db.Connect()
Dim rst As RecordSet = db.SQLSelect(“SELECT sqlite_version() AS version;”)
MsgBox(rst.Field(“version”).StringValue) // 3.8.8
The same code with the last version of RB (2012 2.1) and REALSQLDatabase:
Dim db As New REALSQLDatabase
Call db.Connect()
Dim rst As RecordSet = db.SQLSelect(“SELECT sqlite_version() AS version;”)
MsgBox(rst.Field(“version”).StringValue) // 3.7.11
[quote=242055:@Denise Adams]Norman: If that’s the case, what’s the easiest way of inserting bulk data into a MSSQL db?
[/quote]
No idea - probably depends on what MS SQL db you’re using
SQL Server used to have a bulk copy interface / cmd line tool
No sure about Express
This “works” as long as you write the sql correctly
And since its just one giant script you can tweak it as needed
Via whatever create tab;e statements you need for the respective DB
If it truly is a REAL database (that predates using sqlite as the engine) then you probably also need to use an old version of REAL Studio (possibly no newer than 2008 / 2009) where you could open either format
I have old versions of RealBasic and RealStudio and am running WIndows 7 so accessing the db in the IDE and compiling shouldn’t be a problem. I’m just trying to figure out the best way of getting the old data into a MS SQL db etc. Whether to convert the old db to the newest Xojo db format and then somehow convert that to MS SQL or to go the export/import table route via SQL script or csv/xml etc.
Having it in SQLite DB format might be useful if you need to work with the data again in some other format. But for a one-time conversion like this maybe it doesn’t matter.
the first thing I do when I build a project with Data Base, is to create both Import and Export data functions.
If you do not have them (only Export is needed now), just add it !
When you will have the data in a text file format (.txt or .csv or ), copy / Paste the Select (and all other code used to create / read the data base) so you only have to adapt the code to SQLite (for example).
It may be a little excessive, but you also may wrote the data base design as diagrams, and use that to create the brand new application.
