SQLifx for FlagShip and Informix engine Alejandro Fernandez Herrero afernand@lanet.com.ar v0.06, Aug 7 2000, Alejandro Fernandez Herrero This software is under the GNU General Public License Disclosure This program is distributed AS IS in the hope that it will be useful, but WITHOUT ANY WARRANTY, to the extent permitted by law; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Acknowdlegement I want to thank to Thomas Griffin, he was who start in me the possibility to develop this two interfaces (SQLmy and SQLifx). Also, Jonhatan Leffler who answer all my questions and give some advise about Informix. A great thanks to Paul Berger for his support and for multisoft GmbH for developing a great tool like FlagShip, really a very worth. 1. Introduction 1.1. What is SQLifx? SQLifx is an API consisting of four functions that may be statically linked to a FlagShip program. These functions allow you to communicate with an Informix database from within a FlagShip program. 1.2. Why use SQLifx? Although the FlagShip compiler includes a powerful and efficient database, there are many reasons why you may prefer to store your data external to FlagShip. For example, if you are porting a legacy application that previously used an SQL database, it may be easier to load the data into an Informix database and reuse the already tested SQL to access it rather than to convert the data and all queries to the FlagShip environment. You may also find it easier to access data from C or Perl programs if it is stored in an Informix database. Although it is not impossible to access FlagShip databases from C or other languages, the simple ESQL/C of Informix as well as its interactive dbaccess utility provide great ease and flexibilty of access. Finally, manipulating multiple tables of a relational database using a high-level language such as SQL is often conceptually simpler than writing explicit loops as required in the Clipper/FlagShip environment. Of course, there are tradeoffs to consider when using a high-level language such as SQL and an external database. A FlagShip program accessing data in a native FlagShip database will almost always outperform the corresponding FlagShip program accessing data in an external database. Nevertheless, Informix engine performs reasonably well when accessing even large data tables; and when used in conjunction with the FlagShip compiler, provides the programmer with a very potent database server as well as the tools to write efficient and attractive client applications. 1.3. What versions of FlagShip and Informix work? SQLifx version 0.06 was developed under FlagShip version 4.4 (ELF) and Informix-SE 7.24. The object file SQLifx.o was generated using esql command from Informix, dynamically linked with FlagShip librarys version 4.4 (ELF). Linux version 2.2.*. Although SQLifx was developed under Linux, due to all packet is under ESQL/C format, compiling SQLifx will run ok for other platform, but this has not been tested. 1.4. How is SQLifx delivered? The four functions making up SQLifx are encapsulated in an object file (SQLifx.o), generated from Makefile. You must link this file directly to your FlagShip program, the client library it was link with sqlifx.o, too. The files making up SQLifx are in a gzipped tar file called SQLifx0.06.tgz. Create a directory on your system. Copy SQLifx0.06.tgz into it. Extract the files with the command: tar -xvzf SQLifx0.06.tgz 1.5. How to get started. Make certain that you have the ELF version 4.4 of FlagShip and an Informix engine installed and functioning on your Linux machine. Run a few of the examples provided with the Informix distribution to verify that you are able to create and query a database. Then create a directory, say /home/sqlifx, and extract files from SQLifx package, as described above. The files are: README.txt the file you are now reading ChangeLog the things that has benn changed of fixed sqlifx.o object file containing four functions testx.prg a simple demo program to do a select. It will create a temporary xbase file with the columns as you pass in the query. testi.prg a simple demo program to insert a hardcoded row testd.prg a simple demo program to delete the row inserted with testi.prg Makefile the Makefile file to compile and install sqlifx.o file API TODO the things that I will do in a near future ChangeLog The changes from version to version create_db.sql create_tbl.sql populate_tbl.sql scripts to create db, table and populate it verify.sql the script to select the data in vat table, from outside db Environment Variable There are a few environment variable you could set up. MY_RETRIEVE_DIR should be a directory on your system where you have read/write permission. Make certain that you end the name of this directory with a slash. Temporary files consisting of tuples returned by select statements will be written in this directory. MY_ERROR_LOG will be your SQLifx error log. If errors are encountered, SQLifx functions will append messages to it before returning -1 to your FlagShip program. Please make certain you put MY_ERROR_LOG someplace where you have read/write permission. Of course, calling FS_SET() is only one of the ways that these environment variables may be set. But they must exist for SQLifx to function properly. MY_LEVEL_VERBOSITY if for some circumstance you want dump adicional information to error log put a value grater than 2 in this environment variable. It could be nice at first during the set up. First you must install sqlifx API, to do so enter: make install Due Makefile copy sqlifx.o to /usr/lib this operation ought to be with root privileges. Now, you should be able to compile, do: make testx .or FlagShip testx.prg /usr/lib/sqlifx.o -o testx and run the testx program. refere to 2.2 point, for a complete description 2. How SQLifx works. 2.1 Insight The four SQLifx functions consist of simple wrappers around some of the key functions in ESQL/C. The exception is the function ret_ifx(), which, in addition to parameters and return value, uses a temporary file to store data records returned to FlagShip from an Informix database . Any temporary file created by ret_ifx() will be written in the directory identified by the environment variable MY_RETRIEVE_DIR. There are various ways you may set this environment variable. For example, in ~/.login, in ~/.profile, in ~/.bash_profile, or via the FS_SET() function as shown in the test programs. Please make certain that the content of this variable ends with a slash, e.g. /home/SQLifx/, that the directory actually exists on your system, and that you have read/write permission in it. The name of the temporary file created by ret_ifx(), including its complete path, is returned in the third parameter to ret_ifx(). Please note that this is a pointer, so don't forget to begin it with a ``@'' when you call the function. The fourth parameter to ret_ifx(), also is a pointer, will contain an integer indicating how many fields comprise each record in the temporary file. These two pointer parameters must be initialized in your FlagShip program to the proper data type before ret_ifx() is called. You should assign the null string to parameter three and a zero to parameter four immediately before calling the function. See the sample programs for an example of this. The actual return value of ret_ifx() is an integer indicating how many records, i.e., lines, are found in the temporary file. ret_ifx() may return zero or -1, in which case no temporary file is written. -1 indicates an error. You should tail your MY_ERROR_LOG to see specifically what went wrong. The temporary file should be processed and deleted as soon as possible once ret_ifx() returns. If only a small amount of data is written to the temporary file, as is often the case, and little time passes between its creation and deletion, no actual disc activity will occur in connection with the temporary file. It all happens in buffers. Isn't Unix/Linux clever! so don't delay the processing. The data fields of all the retrieved records identified by the select statement, i.e., parameter one of ret_ifx(), will be written in the temporary file, with xbase format. 2.1.1 Xbase temporary file This file is a tipical FlagShip file (ended wit .dbf), this type of file is a bit more handy than textfile. You will use all the normal instruction, such as use, go, skeep, etc.. Really it is another file you are accostum with. The name, type, length and number of decimal, if any, will be the same as you define in the DB, or more exactly and equivalent type. Due the length of the field name does not exceed 10 characters in lenght, if the name in DB is more than 10 characters the name will be truncate. It is not my caprice. This version does not support BLOB's convertion. If BLOB are important plese let me know, I am preparing MEMO as BLOB. Note: The second argument will see in detail in point 3.3. Non-select SQL statements may be issued with the function exec_ifx(). 2.2 Our example I have prepared a complete example, there are a few step to do. Create the database: $ cat create_db.sql|dbaccess This will create the database vinage. Create the table vat as: $ cat create_tbl.sql|dbaccess This will create the table vat in your vinage database. Populate your table: $ cat populate_tbl.sql|dbaccess This will store all the information from vat.txt file to table vat into the db vinage. If you have trouble with this, maybe is the type of date. vat.txt have US date (mm/dd/yyyy), if you have configured the date in french edit vat.txt and change the last field. After this three step you have a database, a table and its data. To verify this do: $ cat verify.sql|dbaccess You will see a list of records records from the database. Now there are three flagship program. It must be compiled $ make testi $ make testd $ make testx The program testi have a hardcoded sentence to insert a record, run it twice and therefore inspect the error log. The program testd will delete the tuple inserted with the previos program, also is hardcoded. Again run twice and inspect the error log. The program testx, it will execute the query statement and put the result in a xbase temporary file and will be spreaded via dbedit() function. 3. SQLifx functions. 3.1. open_ifx() Connect to a My-SQL database. This function must be called before any other SQLifx function. PARAMETERS : one required, the name of the database. ADDITIONAL PARAMETERS: user : the name of the user you are entering in, default null password : the password for user, default null Important: The order of the parameters ought to be as listed. RETURN VALUE : integer : zero means success, negative one (-1) indicates an error. Tail MY_ERROR_LOG for the specific error message. EXAMPLE : LOCAL nRetVal := open_ifx ( "data" ) LOCAL nRetVal := open_ifx ( "data","informix","secret" ) Note: Of course the password you can obtain from keyboard and pass it via variable. 3.2. close_ifx() Disconnect from a My-SQL database. PARAMETERS : none. RETURN VALUE : integer : zero means success, negative one (-1) indicates an error. Tail MY_ERROR_LOG for the specific error message. EXAMPLE : LOCAL nRetVal := close_ifx() 3.3. ret_ifx() Retrieve (select) records from a My-SQL database. PARAMETERS : four are required. type_of_retrieve : It must be 1 or 0. Read Type of Retrieve below cSelect : SQL select statement @temp_file : a pointer to a variable that will contain the temporary file name where the retrieved records are stored. @num_fields : a pointer to a variable that will contain the number of fields making up each retrieved record. RETURN VALUE : A positive integer or zero indicating the number of records retrieved; or a negative one (-1) indicating an error. Tail MY_ERROR_LOG for the specific error message. If zero or -1 is returned, no temporary file is created. Type of Retrieve This is for compatibility with SQLmy. Here it doesn't matter. You can govern the type of fetch via FET_BUF_SIZE environment variable. With a fetch buffer, the client application performs the following tasks: 1. The database server sends as many rows as will fit in the fetch buffer to the application. If FET_BUF_SIZE environment variable is not set, the default buffer size is 4096. In your FlagShip programm you can set the FET_BUF_SIZE environment variable like: FS_SET("setenvir","FET_BUF_SIZE", "20000") This will set the buffer size to 20Kb. Or you can set it via the .profile file, setting the FET_BUF_SIZE when the user loggs into account. Why could I bother with the internal size of buffer? If you set or not set it the size of internal buffer, is a matter of performance, in both case the work it will be done. If you increase the size of the internal buffer, you reduce the overhead of refilling the buffer. The database server can buffer more data before it sends it to the application. A larger buffer can enhance performance when you select a large number of rows or when a single row is large. The greater the size of the buffer, the fewer number of times the application needs to wait while the database server retrieves a large number of rows or a few large rows. However, when you select only a few rows (unless the row is very large), the performance gain is not as apparent. EXAMPLE : FS_SET("setenvir","FET_BUF_SIZE", "20000") LOCAL cSelect := "select * from vat" LOCAL tfilename := "" LOCAL fieldcount := 0 LOCAL nRowCount := sql_ret(cSelect, 1, @temp_file, @num_fields) FS_SET("setenvir","FET_BUF_SIZE", "3000") This will retrieve into a temporary file all records in the vat table. Please note that parameters three and four are pointers and must have ``@'' in front of them. Their values are available after ret_ifx() returns. 3.4. exec_ifx() Execute a non-select SQL statement. PARAMETER : one required. cmd : SQL statement (not a SELECT statement) RETURN VALUE : integer : zero means success, negative one (-1) : indicates an error. Tail MY_ERROR_LOG for the specific error message. positive value : indicates the number of row affected. EXAMPLE : LOCAL cmd := "update vat set en_fermentacion = 1 where pileta = 100" LOCAL nRetVal := exec_ifx( cmd )