Developers

Omnis Technical Note TNSQ0012 September 2006

Creating your own SQL session for MySQL

For Omnis Studio 4.1 or above.
By Dr Michael Hufschmidt

With Omnis Studio 4.1 it is possible to use the free version of MySQL with the native MySQL DAM, but there is a slight issue. When you open a session to a MySQL database in the SQL browser, using the native MySQL DAM, you will get the error message "Access denied for user ... to database 'mysql'".

The session was correctly defined and should talk to the database "test" rather than "mysql" (see the red frame in the picture).

The new user "myUser" with the password "xxsecret" had been correctly entered before (here is a snippet of the command lines from MySQL):

GRANT ALL ON test.* TO 'myUser'@'%'IDENTIFIED BY 'xxsecret';FLUSH PRIVILEGES;

On the command line the user is able to log in:

mysql -h 192.168.0.4 -u myUser -pxxsecret test

However, the login with Omnis Studio does not work.

So what is the reason for this? The SQL browser first tries to logon to database "mysql" which exists on every MySQL server. Afterwards the MySQL command 'use test' is used to switch to the requested database. But the access to the system database "mysql" is only allowed for the user "root".

The Solution: If the MySQL server is running on a test system you can give the user "myUser" at least a SELECT access to the database "mysql". To do this root can enter the following command in the MySQL command line:

GRANT SELECT ON mysql.* TO 'myUser';FLUSH PRIVILEGES;

Afterwards you can open the session in the SQL browser and can then access the database "test".

However, if your database server is running on a productive system this approach will not be allowed for security reasons because in the system database "mysql" also users, passwords, access rights, etc. are stored.
So as an alternative you don't open the session with the SQL browser but via Omnis code, which is best placed in the Startup_Task of your Omnis library. To do this, you should declare a session object in the Startup_Task (a task variable called tSessionObj with type Object and subtype MYSQLSESS, selected from the dropdown list of SQL DAM objects)

and create a $logon() method as follows:

Calculate host as '192.168.0.4'
Calculate user as 'myUser'
Calculate password as 'xxsecret'
Calculate database as 'test'
Calulate session as 'MySQL_Session'
Do tSessionObj.$database.$assign(database)
Do tSessionObj$logon(host,user,password,session)

Note that it is important to assign the database to the session object before the $logon() call, hence the order of the commands:

Do tSessionObj.$database.$assign(database)
Do tSessionObj.$logon(host,user,password,session)

Then in the $construct method of the Startup_Task you need to call the $logon method with

Do method $logon()

The session will appear in the SQL browser when you open the library and you can use it as required.