Xojo: Migration to Valentina DB

Users, who have experienced in usage of Xojo and some database, e.g. SQLite or mySQL/PostgreSQL, usually ask how to switch to Valentina DB easily?

This page should help to understand major points in this migration.

When you use standard Database Access of Xojo/REALBasic you operate with the following classes of Xojo:

• Database with many sub-classes for each db.
• RecordSet
• DatabaseField

and yet some helper classes:

• PreparedSQLStatement with many sub-classes for each db.
• DatabaseRecord

To work with some SQL database you should do these steps:

1. Open Database or Connect to Server and open database from Server.
2. Send SQL commands to this database using SqlExecute() or SqlSelect() methods.
3. SqlExecute() allows you to change database schema and records using SQL commands.
4. SqlSelect() allows you to do searches on database and get the results stored in the RecordSet.
5. RecordSet allows you to iterate records, read field values, sometimes update/delete records.
6. You can access some field of recordSet by its name or index to get instance of DatabaseField.
7. DatabaseField have properties and methods to read/write values of different types.

So in general you have the following usage of classes:

Database.SqlExecute( SQL_COMMAND )
Database.SqlSelect( SQL_COMMAND ) -> RecordSet -> DatabaseField      

Valentina plugin supports all above classes and methods. So you can work with Valentina DB using them and nothing else. You will use SQL to access all features of Valentina DB.

Easy?

But, we suggest to take a look of the native Valentina API, especially if you start new application that will use Valentina DB.

We have develop in the V4RB plugin own set of classes, independent from Database classes of Xojo. They provide much more classes and methods, which allow you to control each aspect of Valentina Database.

But to start with Valentina DB, it is a good idea to see how Valentina Classes are similar to RBDB classes. So let`s compare

• Database class - VDatabase class
• RecordSet class - VCursor class
• DatabaseField class - VField class

• PreparedSQLStatement class - nothing in Valentina
• DatabaseRecord class - nothing in Valentina

Database class has the same major methods: SqlExecute(), SqlSelect and even one more SqlQuery().

Using SqlSelect() you can get VCursor class.

Using VCursor you can access fields by name or index and get VField class, which like RBDB class have many properties and methods to read/write Values.

Using these classes of Valentina API, you can do all the same job. For example, to create a new table you will do SqlExecute( “CREATE TABLE…” ). And you can ignore all other possibilities of Valentina API on the start.

With time, when developer becomes used to our API, he starts to learn other classes and methods and techniques.

You can wonder, why there is no PreparedSQLStatement class.

This is because we have implemented (similar to mySQL) pool of parameterized queries, and Valentina automatically finds prepared tree in that pool.

You can just do SqlExecute( query, binds ), where query looks as “…VALUES(:1,:2,:3)” or “…VALUES(?,?,?)”.

1. Well, it`s been already written above, first of all to provide the best control on Valentina DB.
2. Our classes allow to work with DB in different ways: SQL way, API way, Class way (like ORM).
3. Our classes provide much better OOP, e.g. look how many sub-classes of VField we have.
4. This allows to work with Valentina DB without Database License of Xojo.
5. You can note, that Xojo itself provides sub-classes of Database for each DB. And these sub-classes have quite different set of properties and methods. You do not have “100% the same code for all databases” anyway.

Lets look at VCursor vs RecordSet.

RecordSet class is one for many databases supported by Xojo. They are forced to find something working everywhere, and this is almost impossible task. In the description of this class you can find many here it works in this way, but here it works in another. But okay, lets look on features totally missing in the RecordSet.

• Jump to Nth record - Valentina gives you cursors with random access.
• AddRecord() - You can add a new record using VCursor.