FMYSQL for FlagShip and MySQL Tore Van Grembergen torevg@albatros-logistics.be 27 november 2001 This software is under the GNU Lesser General Public License (LGPL), see the attached LICENSE file. Disclosure This program is distributed AS IS in the hope that it will be useful, but WITHOUT ANY WARRANTY without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. FMYSQL is an API consisting of 12 functions that may be linked to a FlagShip program. These functions allow you to communicate with a MySQL database from within a FlagShip program. MySQL is a very fast,reliable, easy to use, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL was originally developed to handle large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. MySQL today offers a rich and useful set of functions. (more info : www.mysql.com) FMYSQL was developed under FlagShip version Release 4.48.2460 (ELF) and MySQL version MySQL-Max 3.23.44 (ELF). You will need following packages installed: - MySQL Server rel. 3.23.41-17 or newer - MySQL Client rel. 3.23.41-17 or newer - MySQL Max rel. 3.23.41-17 or newer - MySQL Development headers and libraries rel. 3.23.41-17 or newer - FlagShip release 4.48.*460 or newer available via www.mysql.com and www.fship.com The API is based on the work done by Alejandro Fernandez Herrero afernand@linuxya.com. He wrote the SQLmy library. I changed the concept so that it can be easier adapted (most of the functions are written in plain FlagShip) Compile, link: simply type make :-) Run: ./mysqltest The best way to get going is taking a look at mysqltest.prg and change it to your database and adapt some of the queries. For the transactions you have to use one of the tabletypes of mysql that support transcations (currently bdb and innodb) Much credits to Alejandro Fernandez and Paul Berger (MultiSoft) for their assistance and answers to my questions. public functions fmysql MYDBOPEN(cDBNAME, cHOST, cUSER, cPASSWORD) ****************************************** attempts to establish a connection to a MySQL database engine running on host. MYDBOPEN(cDBNAME, cHOST, cUSER, cPASSWORD) must complete successfully before you can execute any of the other functions. Only 1 connection canbe open at the same time. MYDBCLOSE() *********** Closes a previously opened connection. MYDBCLOSE() also deallocates the connection handle pointed to by mysql MYEXEC(cSQL,aParams) ******************** executes a query that does not return any rows. function returns the affected rows by the command. parameters : cSQL = DELETE, INSERT, UPDATE, ... aParams := twodimensinal array containing the values of the parameters [1] : name of the parameter [2] : value of the parameter MYQTODBF(cSQL,aParams) ********************** executes a query that returns rows. sets the result in a dbf file. function returns the filename of the dbf file The file is made in the system temp directory parameters : cSQL = Select, show ... aParams := twodimensinal array containing the values of the parameters [1] : name of the parameter [2] : value of the parameter MYQTOTXT(cSQL,aParams) ********************** executes a query that returns rows. sets the result in a txt file. function returns the filename of the txt file The file is made in the system temp directory parameters : cSQL = Select, show ... aParams := twodimensinal array containing the values of the parameters [1] : name of the parameter [2] : value of the parameter MYQTOMEM(cSQL, aParams) *********************** executes a query that returns rows. sets the result in a array function returns the array, every element in the array is a string parameters : cSQL = Select, show ... aParams := twodimensinal array containing the values of the parameters [1] : name of the parameter [2] : value of the parameter MYQTODBMEM(cSQL,aParams) ************************ executes a query that returns rows. sets the result in a array function returns the array, every element in the array is typecasted parameters : cSQL = Select, show ... aParams := twodimensinal array containing the values of the parameters [1] : name of the parameter [2] : value of the parameter MYQTORS(cSQL,aParams) ********************* executes a query that returns a TMYRS object. This is an resultset with some methods on. a fieldvalue can be read by executing rs:fieldname parameters : cSQL = Select, show ... aParams := twodimensinal array containing the values of the parameters [1] : name of the parameter [2] : value of the parameter MYINSERTID() ************ Returns the ID generated for an AUTO_INCREMENT column by the previous query. Use this function after you have performed an INSERT query into a table that contains an AUTO_INCREMENT field. Note that MYINSERTID()returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call MYINSERTID() immediately after the query that generates the value. MYSTAT() ******** Returns a character string containing information similar to that provided by the mysqladmin status command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables MYBEGIN() ******** By default, MySQL runs in autocommit mode. This means that as soon as you execute an update, MySQL will store the update on disk If you want to switch from AUTOCOMMIT mode for one series of statements, you can use the MYBEGIN() function. After this you must use MYCOMMIT() to store your changes to disk or MYROLLBACK() if you want to ignore the changes you have made since the beginning of your transaction. MYCOMMIT() ********** use MYCOMMIT to store your changes since the beginning of your transaction to disk MYROLLBACK() ************ use MYROLLBACK() if you want to ignore the changes you have made since the beginning of your transaction. MYBREAK(cDescription) ********************* when encountered, terminates the sequence by branching the execution to the first statement following the corresponding RECOVER statement if one is specified, or the matching ENDSEQUENCE statement. If executed outside of the BEGIN..END structure, runtime errors occur. MYBREAK(cDescription) passes oError which is an error object, to the of the RECOVER USING clause. Normally this function should not be called directly. MYALERT(oError, aOptions,cColor) ******************************** Displays a simple modal dialog box with the errormessage build up by MYBREAK() The message is shown centered in the alert box. oError is an ERROR object. aOptions defines a list of possible responses to the dialog box. If not specified, a single "OK" option is presented. cColor defines the color specification. MYALERT() returns a numeric value indicating which option was chosen. If the ESC key is pressed, zero is returned. ********************************************************************************************* ********************************************************************************************* TMYRS : methods ********************************************************************************************* ********************************************************************************************* TMYRS:NoIvarGet(symIvarName) **************************** Should not be called directly ! If the instance or method is unknown, NoiVarGet(), NoiVarGet() or NoMethod() will be invoked. This provides the possiblity to do someting like this : ..... LOCAL rs rs := MYQTORS("select name, address, tel from employers") ? rs:name ? rs:address ? rs:tel .... TMYRS:FCOUNT() ************** Determines the number of fields in the current database file. TMYRS:RECCOUNT() **************** Retrieves the number of physical records in the current recordset TMYRS:RECNO() ************* Retrieves the current record number of the recordset. Returns: a numeric value representing the current record number within the recordset. TMYRS:SKIP(nskip) ***************** Moves the record pointer relative to the current pointer position. nskip specifies the number of records to move the record pointer from the current position. A positive value moves the pointer forward, while a negative value moves the pointer backwards. If nskip is not specified, 1 is assumed. TMYRS:EOF() *********** Reports an attempt to move past the end of the current database file. TMYRS:BOF() *********** Detects if there was an attempt to move past the beginning of the current database file. TMYRS:GOTO(nRecNo) ****************** Moves the database pointer to the specified record. nRecNo is the record to which the record pointer is to be positioned. If the nRecNo is out of range, the database pointer is positioned to the first or the last record. TMYRS:GOTOP() ************* Moves the database pointer to the first logical record. TMYRS:GOBOTTOM() **************** Moves the database pointer to the last logical record. TMYRS:FIELDINFO(nTypeInfo,uField) ********************************* Retrieves information about a field. Arguments: nTypeInfo specifies the required type of the field information, given as a constant (see "rddsys.fh") or a numeric value: Constant Value Ret Returns ------------------------------------------------------------------- DBS_NAME 1 retC name of the field DBS_TYPE 2 retC type of the field DBS_LEN 3 retN length of the field DBS_DEC 4 retN number of decimal places for the field uField is the ordinal position of the field in the record structure or the field name as a string. Specifying a numeric argument may perform slightly faster on databases with a large number of fields. Returns: | is the required field information, or NIL on error. TMYRS:DBSTRUCT() **************** Returns an array containing the structure of the current query. Returns a two-dimensional array containing the structure of the current query, whose length is equal to the number of fields in the query. Each element of the array is a subarray containing information for one field: Element Type Description Usage [n,DBS_NAME] C Field name 1..10 chars [n,DBS_TYPE] C Field type C,N,D,L,M [n,DBS_LEN] N Field length 1..65535 [n,DBS_DEC] N Deci places 0..18 (see "rddsys.fh") TMYRS:FIELDGET(uField) ********************** Retrieves the value of a field using the ordinal position of the field in query or the field name. Arguments: uField is the ordinal position of the field in the record structure or the field name. Specifying numeric argument may perform slightly faster on databases with a large number of fields. Returns the value of the specified field. If uField is out of range , the return value is NIL. TMYRS:FIELDLEN(uField) ********************** Determines the size of a selected database field. Arguments: uField is the ordinal field number, or the field name of the database in the current work area. Returns the length of the database field. Equivalent to the DBS_LEN subarray element of TMYRS:DBSTRUCT(). TMYRS:FIELDDEC(uField) ********************** Determines the number of decimals in a numeric database field. Arguments: uField is the ordinal field number, or the field name of the database in the current work area. Returns: is the number of decimal places (0..17) of numeric field, or zero on non-numeric fields. Equivalent to the DBS_DEC subarray element of DBSTRUCT().