logo
Powered by QM on a Rpi server
Home
About OpenQM
Sales and Downloads
Help and Support
About
Login

KnowledgeBase 00026: Accessing an SQL database from QM

Last updated: 22 Jul 2016
Applies to: All versions
Search  
Top level index       Full Index Search Tips
Previous article     Next article

Introduction

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.


Prparing SQL.CLS

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:

   BASIC SQL.CLS 

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
    serveris the name of the MySQL server.
    useris the user name for the SQL connection.
    passwordis the password for the SQL connection.
    databaseis the name of the MySQL database to be accessed.


Performing SQL Operations

To execute an SQL command on the remote database, use the following syntax:

   ok = sql->execute(query, result) 
where
    queryis the command to be executed.
    resultis the variable to receive the command output.
    okis returned as true if the action is successful.


Example

   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) 

Related Articles

None.



Please tell us if this article was helpful
Very     Slightly     Not at all
Comments
Email (optional)