RealSQLDatabase Question

Databases
1

Deprecated RealSQLDatabase is being replaced with SQLiteDatabase.
This is a major Deprecated item to loose and be backwards compatible
with out a lot of RS program rework.

RealSQLDatabase now works in XoJo 2013r1 but does anyone know
if it will stop working in the near future?

My program reads old databases with many tables that do not have
PRIMARY KEYS in all the tables and the new SQLiteDatabase will not
work properly with them so I really need to keep RealSQLDatabase
engine or is the RS2012r2.1 the end of the line for me?
If so I will not renew XoJo again as the aggravation is not worth the
money that XoJo cost.

2

Database command statement.

This is the current sql SELECT statment but I need it to get the RowID as well and using an asterisk(wild card)
does not get the RowID.

SQL = “SELECT * FROM SMTPsettings”

My question is:
Is the SQLite SELECT statement below legal ? Will it get the RowID and all other fields of data from SMTPsettings table
or will I have to spell out all the fields that I want explicitly ?

SQL = “SELECT Rowid, * FROM SMTPsettings”

3

Your old tables do have a primary key - it was just automated. With try using SELECT rowid, * and see what you get.

4

If they don’t have a primary key, why not just add one. Here’s a reference site for starters.

The above site doesn’t indicate how to create a primary key via the ALTER method, so went searching and came across this site :. It should give you some clues on how to proceed.

5

I can not modify a customers database, only can read it.

6

It will probably be a long time before they can remove RealSQLDatabase from the language. Don’t sweat it.

That said,

is correct. It will get all the fields (except rowid) PLUS rowid.

7

That will work with RealSQLDatabase and SQLiteDatabase to deliver the RowId and all explicitly defined fields.

As Wayne wrote, all of your databases do have a Primary Key. It is the RowId if you haven’t specified one. If you have specified one and what you specified is an Integer then you can also reference that Primary Key as RowId or whatever name you gave it.

Moving from RealSQLDatabase to SQLiteDatabase may require some code changes but you won’t need to change any of your databases.

That said, specifying a Primary Key for each table is a good idea and making it an Integer is even better. It’s better because it will behave like an implicit RowId Primary Key with one exception, it will remain the same after a Vacuum is done on the database. For your databases without an explicitly specified Integer Primary Key, the RowId will remain unique within each table but can change per record after a Vacuum is executed on the database. If you don’t reference the RowId for relationships then this is a non-issue.

8

If you are only reading the database, I don’t think the lack of a specified primary key is going to a problem. SQLiteDatabase requires a primary key when using RecordSet.Update.

As for when RealSQLDatabase will be removed from Xojo, our policy is that it could be removed 1 year after it was deprecated. But that is the absolute earliest. We’ve had deprecated stuff that’s been around for years. Although, keep in mind that we won’t be updating RealSQLDatabase to any newer versions of SQLite any longer.

9

Thanks for the information people. For now that makes my life easier.
I will in the future make damn sure to include a primary key in all tables
if used or not in any new programs made with XoJo.

Again, thanks

10

I haven’t downloaded Xojo, so my question is, this is only for people not using SQLExecute?

11

Right. RealSQLDatabase auto-added the rowid when you used SQLSelect so that RecordSet.Edit and RecordSet.Update would work even if your SQL did not specify a primary key.

If you always used SQLExecute to execute your own UPDATE commands then this change is not a concern.