I have now been able to do simple sql statements from within UniVerse to access data in MySQL.
All good stuff.
As I really want to see how I can use MySQL as a multivalued database, I have decided to cheat a bit. MySQL 5.1 has some xml inbuilt functionality. It is possible to extract and update a value within a xml string stored in a MySQL field.
So, I upgraded my MySQL 4.5 to 5.1. This was a bit of a heart-ache. I eventually removed MySQL completely and installed the new version, as the upgrade repeatedly failed. VMWare was very handy with it’s snapshot feature here.
The two MySQL functions are ExtractValue and UpdateXML. It should be possible to convert a multivalue record into xml, using something as simple as using the field, value and subvalue positions as xml delimiters etc.
Field 1 Field 2 value 1value 2subvalue 1 of value 3
and using XPath (XML Path Language) to identify each element – field, value and subvalue.
To see an article on this new feature – MySQL 5.1’s New XML Functions.
This method is actually similar to the EXTRACT, INSERT and UPDATE dynamic array functions within UniBasic.