ClarionLive for April 13 2012
Joe Tailleur presented on SQL, mainly PostgreSQL.
He started off with a discussion of label case in PostgreSQL. By default PostgreSQL will create all entities (tables, fields, indexes etc) in lower case, after which you can refer to those entities in any case you like. On the other hand you can force PostgreSQL to use mixed case by putting the labels in quotes, but if you do that then you'll have to refer to that entity by the exact same string with the exact same case.
PostgreSQL handles autonumbering with something called a sequence. You tell PostgreSQL to assign a value for a field, on insert, from a specified sequence (if you use the SERIAL keyword in the table creation the sequence is created automatically.
You can create the sequence after the fact, if you wish. If you are importing data you can still create the sequence first if wish, as PostgreSQL will only apply the sequence value if the field is zero. (But if you're importing data, after you're done you'll want to set the sequence value to something at least one higher than the highest value in the data table.
To enable autoincrementing with sequences:
- Set IsIdentity on the primary key field
- Add an EmulateAutoNumKey property with a value of True. (Tip: If you enter True as a string the dictionary editor will change it to a boolean, which is what you want anyway.) This actually isn't a requirement for autoincrementing per se, but Joe often does it. It causes the record to be created when you go into the form, which is useful if you have a child browse on the form and need the parent id for the child records.
- Set the autoincrementing code, something like this:
Joe ran into some insert problems. First he needed to update his sequence as he already had a record with a primary key of 1. Then when using EmulateAutoNumKey (which causes a record to be added) he got a duplicate key on an empty string field that had a unique constraint. While PostgreSQL will allow duplicate null values on unique constraints it will not allow duplicate empty strings. There was some discussion of how this error could crop up when two users might be adding records at the same time and the second insert attempt would fail. Issuing SetNull on the field should the job; perhaps having a null default value would also.
Encryption
If your database has an encryption function you can issue encryption calls via Prop:SQL. For instance, in PostgreSQL you can do this:
In this case to verify the password on login you'd have to get the MD5 hash of the password and then compare that with the saved hash.
Upgrading tables
Joe uses an encrypted TPS file to store SQL statements for things like table changes; he can then keep track of the current database version in the SQL database, and issue scheme changes and other modifications via PROP:SQL.
His connection string is also set from an encrypted TPS table. If he passes /S to the app then he brings up the connection configuration window; otherwise the stored connection settings are used.
If you do this you need to check for an error on a failure, and report the results of FileError(). Otherwise you could be running a series of upgrade scripts after one of the scripts has failed. Tom Hebenstreit had the following comment:
We have a similar utility, but always wrap the changes in a test to see if the object (new column, etc., ) exists. That way it is safe to run all scripts if a customer has munged up their versioning.
Rick Martin writes his scripts in such a way that they can be re-run safely.
Although the versions are per change, he stores the SQL version number on a per table basis. Some good discussion of upgrading strategies at the start of the second hour.
When Joe runs some SQL via Prop:SQL and there's an error he pastes the SQL command into the clipboard so he can run the script manually and find out the problem.
Browse resizing
Joe had some interesting browse resizing code that works better than the stock resizing code.
Other discussion
The webinar concluded with some general discussion of SQL issues including transactions, isolation levels and record locking, as well as a few questions from the participants.
At the end Arnold wanted to be convinced that he should use PostgreSQL over MS SQL. He's getting closer, but he hasn't quite made the leap yet.