|Powered by QM on a Rpi server|
KnowledgeBase 00026: Accessing an SQL database from QM
This article describes use of the SQL.CLS class module that is provided in source form in the BP file of the QMSYS account. This module allows a QM application to submit SQL commands to MySQL or via the sqsh interface to Sybase.
The first step to use of the SQL.CLS class module is to compile and catalogue the QMBasic code. All that is required is to execute the following command in the QMSYS account:
The source code contains a $CATALOGUE directive that will catalogue this globally with the name !SQL.CLS. Prior to QM release 2.12-0 the object code was placed into the private catalogue of the account in which it was compiled.
Establishing a Connection
Instantiation of the object created by this class module also establishes the connection to the SQL server. The required syntax for connection to MySQL is:
sql = object("!sql.cls", "mysql", server, user, password, database)where
Performing SQL Operations
To execute an SQL command on the remote database, use the following syntax:
ok = sql->execute(query, result)where
sql = object("!sql.cls", "mysql", "sqlsrvr", "jsmith", "xxx", "LIVE") query = "INSERT INTO supplier(pmid,vendor,status,pub)" query := " VALUES ('" : pmid : "','" : vendor : "','" : status : "','" : pub : "')" query := " ON DUPLICATE KEY UPDATE vendor='" : vendor : "',status='" : status : "';" ok = sql->execute(query, result)