SQLmy for FlagShip and My-SQL Alejandro Fernandez Herrero afernandez@qubitsa.com.ar v0.08, 3 March 2001, 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. 1. Introduction 1.1. What is SQLmy? SQLmy is an API consisting of four functions that may be dinamyc linked to a FlagShip program. These functions allow you to communicate with a My-SQL database from within a FlagShip program. 1.2. Why use SQLmy? 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 a My-SQL 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 a My-SQL database. Although it is not impossible to access FlagShip databases from C or other languages, the simple C API of My-SQL as well as its interactive mysql 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, My-SQL 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 My-SQL work? SQLmy version 0.08 was developed under FlagShip version 4.4 (ELF) and My-SQL version 3.23.27beta (ELF). The object file SQLmy.o generated from the Makefile file, will work only with ELF version 4.4 of the FlagShip compiler on Linux. It may work with ELF versions of My-SQL earlier than 3.23.27b running on Linux, although this has not been tested. 1.4. How is SQLmy delivered? The six functions making up SQLmy are encapsulated in an object file (sqlmy.o), generated from Makefile. You must link this file directly to your FlagShip program, the client library it was link with sqlmy.o, too. The files making up SQLmy are in a gzipped tar file called SQLmy0.08.tgz. Create a directory on your system. Copy SQLmy0.08.tgz into it. Extract the files with the command: tar -xvzf SQLmy0.08.tgz 1.5. How to get started. Make certain that you have the ELF version 4.4 of FlagShip and a recent ELF version of My-SQL installed and functioning on your Linux machine. Run a few of the examples provided with the My-SQL distribution to verify that you are able to create and query a database. Make certain your linker can find the library mysqlclient (generally under /usr/local/mysql/lib/mysql). Then create a directory, say /home/sqlmy, and extract files in the SQLmy package in it as described above. The files are: README.txt the file you are now reading ChangeLog the things that has benn changed of fixed SQLmy.c source file containing six functions tests.prg a simple demo program to do a select. Works for text temporary file. testx.prg from version 0.06 the result may be stored in an xbase file (.dbf), so the treatment is more flexible. This program is exactly as tests.prg, but take into account xbase temporary file. 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 testm.prg a simple demo program to do a select. The result goes to memory. testp.prg a simple demo program to check if database connection is alive. Makefile the Makefile file to compile and install sqlmy.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 My-SQL error log. If errors are encountered, SQLmy 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 SQLmy 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 create the sqlmy API, to do so enter: make Due Makefile copy sqlmy.o to /usr/lib this operation ought to be with root privileges. This assume you have all mysql stuff under /usr/local/mysql, if not edit the Makefile and modify as appropiate. This will create mysql.o in your current directory and under /usr/lib Now exist two form of temporary file, text separeted by pipe, or xbase (.dbf) file. If you want to work with text temporary file, edit Makefile file and uncomment the line #TEXT=-DTMP_FILE_TXT. If you leave this untouch, this API will generate the result in xbase format. You should now be able to compile FlagShip testx.prg sqlmy.o -o testx or FlagShip testx.prg /usr/lib/sqlmy.o -o testx and to run the testx program. refere to 2.3 point, for a complete description 2. How SQLmy works. 2.1 Insight The six SQLmy functions consist of simple wrappers around some of the key functions in mysqlclient.a, the My-SQL C language interface. With one exception, the communication with a My-SQL database via passed parameters and return value as documented below. The one exception is the function ret_my(), which, in addition to parameters and return value, uses a temporary file to store data records returned to FlagShip from a My-SQL database (see TODO list). Any temporary file created by ret_my() 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/SQLmy/, 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_my(), including its complete path, is returned in the third parameter to ret_my(). 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_my(), 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_my() 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_my() is an integer indicating how many records, i.e., lines, are found in the temporary file. ret_my() may return zero or a negative value, in which case no temporary file is written. A negative value indicates an error. You should tail your MY_ERROR_LOG to see specifically what went wrong. From version 0.08 the negative value is an error code depending on what it cause the invconvenience. You can manage this error code in your program. The temporary file should be processed and deleted as soon as possible once ret_my() 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_my(), will be written in the temporary file. Each record consists of a single line, i.e., each record ends with the NEWLINE character. Each field ends with a bar. Null fields are empty and all data is returned as a character string in the same format as the mysql utility provided in the My-SQL distribution. From vesion 0.08 there are two additional functions, ret_mem_my() is like ret_my(), but instead to deposit the data in a temporary file, it uses an array. And the other one is ping_my() to check if the conection with the database server still is alive. 2.1.1 Temporary File There are two type of temporary file: text file and xbase file. To prepare the API's to generate temporary text file, edit the Makefile and uncomment the line: #TEXT=-DTMP_FILE_TXT Conversely, if you want to work with xbase file keep this line untouch. Obviously this step must be done before you compile. (do make) 2.1.1.1 Text temporary file This file is a typical ascci text file, each field delimited by a pipe or vertical bar. A typical temporary file containing two records might look like this: 10|125.65|malbeck|good aroma| 3ra zona|1| 11|150.00|semillon|tight color| 3ra zona|1| Normally, the contents of a text temporary file will be read into a i two-dimensional array for further processing within the FlagShip program. Then the file will be deleted. An example of how to do this is provided in the tests programs. 2.1.1.2 Xbase temporary file This file a a tipical FlagShip file (ended wit .dbf), this type of file it 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_my(). 2.2 How ret_mem_my() works? Instead of deposit the data in a temporary file I use an array of data. Really is a data structure, I name it aRecord. Its graphical representation is: Information structure (aRecord) ===================== -------------------------------- | 1 | 2 | 3 | 4 | ....|nFields | -------------------------------- | v --------------------------------------- |name_Field_1|type_Field_1| | --------------------------------------- | v -------------------------------------------------- | value_1_of_field_1 |..| value_nRows_of_field_1| -------------------------------------------------- aRecord is an array in FlagShip land, but come from C land. I mean a C function build this array. If we crete aRecord from FlagShip land directly. We should do: aRecord := { {name_1,type_1,{value_1, value_2,..,value_nRows}}, {name_2,type_2,{value_1,value_2,..,value_nRows}},.., {name_nFields,type_nFields,{value_1, value_2,.., value_nRows}} } The name of the first field is: aRecord[1,1] The type of the first field is: aRecord[1,2] The first value of the first field is : aRecord[1,3,1] The third value of the second field is : aRecord[2,3,3] As you can see, the second index of the array remain the same (3), when you recover a value from the array aRecord from the FlagShip land. The first index refer to field, and the third is a value itself. 2.2.1 Which method to use, file base or memory base? Well, as a rule of thumb I can say all it depends. Is it oversimplified this answer? No. First of all, an array in FlagShip cannot take more than 65535 elements, looking at the aRecord structure the number of elements are function of its fields and rows: elements = fields + fields * 3 + rows * fields Suppose you are recovering 6 fields, the maximun number of rows can be: rows = (65535 - 6 - 18) / 6 = 10918 This is the upper limit, but there are another limit. It isn't the same if the six fields are two character in length or forty character in length. This limit is impose by the memory in the client side. Don't worry both situation are controled by the function. As a matter of fact, I recommend returning data to memory only for data isn't really long (both the number of records or the length of data itself), such as tables of parameters or query with low number of records. 2.3 Return errors code From version 0.08 the return code isn't alway -1, instead is a negative value stating in -10. Below is the table of error code: Mnemonic name Error Description code ERR_WRONG_NUM_ARGUMENTS -11 You call the function with invalid type or number of arguments ERR_OPEN_TWICE -12 You want to open the database twice ERR_CANNOT_CREATE_STRUCTURE -13 You cannot create the structure due to permissions in the specified directory ERR_CANNOT_CONNECT -14 You cannot connet, check all the arguments sent to function ERR_NO_DB_OPEN -15 You want to execute a SQL statement but no database has been opened ERR_CONNECTION_LOST -16 The connection is lost, verify anything involve in a connection (server, net, etc.) ERR_CANNOT_EXEC_SELECT -17 The select statement return an error code, verify the name of table, fields, etc. ERR_CANNOT_RECOVERY_RESULT -18 Internall error or insuficient memory space ERR_CANNOT_OPEN_TMP_FILE -19 Insuficient privileges to open ERR_CANNOT_WRITE_TMP_FILE -20 Same as above ERR_EXECUTING_NO_SELECT -21 Error executing a non select statament, like an update or delete ERR_CANNOT_FETCH_ROWS -22 Internal error ERR_CANNOT_ALLOCATE_MEM -23 The functions cannot allocate memory ERR_DATA_NOT_SUPPORTED -24 Type of data not supported yet (BLOB) ERR_INVALID_SCHEMA -25 Internal error 2.4 Our example I have prepared a complete example, there are a few step to do. Create the database: $ create_db.sql This script will create the database vinage. Create the table vat as: $ cat create_tbl.sql|mysql -p This will create the table vat in your vinage database, it will prompt you for a password, enter your true password for your current account. Populate your table: $ cat populate_tbl.sql|mysql -p This will store all the information from vat.txt file to table vat into the db vinage. After this three step you have a database, a table and its data. To verify this do: $ cat verify.sql|mysql -p You will see a list of records whos fields are headed by the name of the table's field. Now there are four flagship program. It must be compiled $ Flagship tests.prg sqlmy.o -o tests (only works with text temporary file) $ Flagship testi.prg sqlmy.o -o testi $ Flagship testd.prg sqlmy.o -o testd $ Flagship testx.prg sqlmy.o -o testx (only works with xbase temp file) $ Flagship testm.prg sqlmy.o -o testm $ Flagship tests.prg sqlmy.o -o tests $ Flagship testp.prg sqlmy.o -o testp The program tests, it will select all the row in the table vat that are in fermentacion (fermentacion=1). Only use this if you choose to work with text temporary file. The program testi have a hardcoded sentence, run 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 read the xbase temporary file and will be spreaded via dbedit() function. 3. SQLmy functions. 3.1. open_my() Connect to a My-SQL database. This function must be called before any other SQLmy function. PARAMETERS : one required, the name of the database. ADDITIONAL PARAMETERS: host : the name of the host you are access, default localhost user : the name of the user you are entering in, default null password : the password for user, default null port : the number of port you are defined for access to My-SQL demon (tcp_port), default 3306 unixsock : if you want bother with it read the My-SQL manual, default null flags : same as before, default zero Important: The order of the parameters ought to be as listed. RETURN VALUE : zero means success ERR_OPEN_TWICE ERR_CANNOT_CREATE_STRUCTURE ERR_CANNOT_CONNECT Tail MY_ERROR_LOG EXAMPLE : LOCAL nRetVal := open_my ( "data" ) LOCAL nRetVal := open_my ( "data","server.com","mysql","mysql",5050 ) 3.2. close_my() Disconnect from a My-SQL database. PARAMETERS : none. RETURN VALUE : zero means success ERR_NO_DB_OPEN EXAMPLE : LOCAL nRetVal := close_my() 3.3. ret_my() 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 value indicate number of records retrieved Zero indicate success, but no error found ERR_WRONG_NUM_ARGUMENTS ERR_CANNOT_EXEC_SELECT ERR_CANNOT_RECOVERY_RESULT ERR_CANNOT_OPEN_TMP_FILE ERR_CANNOT_ALLOCATE_MEM ERR_CANNOT_ALLOCATE_MEM Tail MY_ERROR_LOG for the specific error message. Type of Retrieve There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once, this is the type_of_retrieve with value one (1). This, acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval, this is type_of_retrieve with value zero (0). The different is, with option 1, the function will fetch all rows to memory and then will write to temporary file. With the second way you fetch one row and write to temporary file one row at a time. Rule of thumb The two retrieval mechanisms are complementary. Client programs should choose the approach that is most appropriate for their requirements. In practice, clients tend apply value 1, but only if he/she have enough memory to gather all the rows or the select affect a few rows. If you are not in this situation use value 0 instead. Remember that in either case you obtain a temporary file with the row you requested, it's only for the benefit of the system performance to choose value 1 or 0. Of course if you choose 1, you may run out of memory, but don't panic, you will receive a message. After the function is executed the memory is freed. EXAMPLE : LOCAL cSelect := "select * from vat" LOCAL tfilename := "" LOCAL fieldcount := 0 LOCAL nRowCount := sql_ret(cSelect, 1, @temp_file, @num_fields) 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_my() returns. 3.4. ret_mem_my() Retrieve (select) records from a My-SQL database, but the result goes to a memory array. PARAMETERS : four are required. cSelect : SQL select statement RETURN VALUE : The address of a aRecord structure array ERR_WRONG_NUM_ARGUMENTS ERR_CANNOT_EXEC_SELECT ERR_CANNOT_RECOVERY_RESULT ERR_CANNOT_OPEN_TMP_FILE ERR_CANNOT_ALLOCATE_MEM ERR_CANNOT_ALLOCATE_MEM Tail MY_ERROR_LOG for the specific error message. Example: local aRecord ret := open_my("vinage","localhost","mysql","mysql") if ret == -1 ? "I can't open the database, look into error log, if set" return endif *-------------Execute query ---------------------- LOCAL cSelect:="select * from vat" aRecord := ret_mem_my(cSelect) if aRecord == -1 ? "I can't execute query, look into error log, if set" return endif close_my() nFields=len(aRecord) nRows=len(aRecord[1,3]) for i=1 to nFields for j=1 to nRows ? aRecord[i,3,j] next next 3.4. exec_my() Execute a non-select SQL statement. PARAMETER : one required. cmd : SQL statement RETURN VALUE : integer : zero means success, negative one (-1) indicates an error. Tail MY_ERROR_LOG for the specific error message. EXAMPLE : LOCAL cmd := "update vat set en_fermentacion = 1 where pileta = 100" LOCAL nRetVal := exec_my( cmd ) 3.5. ping_my() Execute a query to a MySQL database server to know if the connection still is alive. PARAMETER : none RETURN VALUE : integer : one means success, negative one (-1) indicates an error. Tail MY_ERROR_LOG for the specific error message. EXAMPLE : if (!ping_my()) ? "The database connection is lost" return endif LOCAL nRetVal := exec_my( cmd ) 4. Feedback This routines are yours, so please let me know if you have any suggestions. Don't let me alone. Only if you participate this stuff will be more useful and handy. Also code contribution are wellcome as well. And at last if this routines were usuful for you tell me. I will record your experiencies, so the other persons can read it. Thanks a lot. Alejandro