MySQL Documentation Appendix D (Porting mSQL applications to MySQL)

First, Run the shell script msql2mysql over your source. This needs the binary program replace, which is distributed with MySQL. This will translate mSQL function names to MySQL. It will NOT fix parameter types or change the number of parameters in MySQL functions that need additional parameters.

Other Hints:

• MySQL uses a data structure called MYSQL as a connection type. (mSQL uses an int)
• mysql_connect takes a pointer to a MYSQL struct as a parameter. You can either define one globally or use malloc to get one.
• mysql_connect takes 2 extra parameters. These are user and password. These may be set to NULL,NULL for default use.
• mysql_error takes MYSQL as a parameter ; Simply add it.

MySQL comes with ported versions of the mSQL test programs insert_test.c and select_test.c. It's a good idea to compare these with their mSQL equivalents. This should give you a good start on understanding what you will need to do to port a mSQL application to MySQL. It will also give you some hints on what you need to do to write a MySQL application.

There are significant differences. Amongst them are...

• A message buffer may contain many result rows.
• On the server side the message buffers are dynamically enlarged as necessary for big questions from the client.
• All packages are numbered to catch out of sync packages.
• All columns are sent in ASCII, the length of columns and length of rows are sent in packed binary coding (1,2 or 3 bytes).
• MySQL can read in the result unbuffered.
• If a single write/read takes more than 30 seconds then the server closes the connection.
• If nothing has happened on a connection for 8 hours the server closes the connection.

- CREATE TABLE:
  MySQL: Has the following field option types:
         UNSIGNED, ZEROFILL,
         Has DEFAULT on not null fields.
  mSQL:


- To create indexes:
  MySQL:  All indexes have to be given at CREATE TABLE.
  mSQL:   Indexes must be created by a separate CREATE INDEX clause.
          (Does mSQL use a new key file for each index?)
          Indexes may be dropped with DROP INDEX

- To get a unique identifier at insert:
  MySQL: Use 'auto_increment' as a column type specifier.
         The used index may be retrieved after update by the API
         function mysql_insert_id().
  mSQL:  Create a SEQUENCE on a table and use the __seq function
         to get a unique index.

- Group functions:
  MySQL: count(), avg(), min(), max() and sum().  min() and max() may
         take string arguments. count(*) is optimized to return directly
         if no other column is used.
  mSQL:  none

- searching case-independent:
  MySQL: LIKE is always case independent. If possible MySQL uses indexes
         if the like argument doesn't start with a wild-card.
  mSQL:  Use CLIKE.

- searching with regular expressions.
  MySQL: Use REGEXP or RLIKE
  mSQL:  Use RLIKE.

- What is the difference in the WHERE statement:
  MySQL has braces, Soon it will have full function support in the WHERE.
  (This is scheduled for version 3.21.X)

- Qualifying column names:
  MySQL: If a column name is unique you don't have to use the full qualifier.
  mSQL:  When using more than one table in a select you must use full
         table qualifiers.

- Aliasing
  MySQL: Has table and column aliasing.
  mSQL:  Has table aliasing.

- Insert/update with calculations.
  MySQL: Full calculation support. In an insert you may use values of preceding
         fields.
  mSQL:  Only insert and update with constants.

- Which formulas may be used in the select statement.
  MySQL: To many to print here. Check the manual.
  mSQL:  none

- HAVING
  MySQL: Supported, but can only use calculation on the selected fields.
         To select on a calculated value one must use a column alias.
         Ex: SELECT COUNT(*) AS id_count,id FROM groups GROUP BY id HAVING
             id_count > 10
  mSQL:  Not supported

- Importing and Exporting data
  MySQL: MySQL has functionality built into the server for the import 
         of data by way of LOAD DATA INFILE.  Having this built into the
         server makes data loads significantly faster.
  mSQL:  External program.