How can I avoid using mysql_real_connect() repeatedly for each query?

Question

I am querying my database through a connector I made in Lazarus/Freepascal. It uses the libmysql from MySQL 5.0. From what I can tell, the functions I can invoke are identical to those of the MYSQL C Connector.

The database is not for remote access; it is a pipe-accessible database that I am trying to optimize for local read using memory-only tables (no MyISAM or InnoDB). Speed is the primary focus.

The client application is written in a limited scripting language called MQL4 (for financial markets), and uses my library to repeatedly query the database and return the results for analysis.

The problem is: I am forced to destroy and re-create the connection to the database each time I send a new query.

If I do not destroy and re-create the connection I will get a read access violation because my connector library loses the value of *MYSQL assigned by mysql_real_connect().

I would pass this value through my client application, but unfortunately it is written in a language that can only handle LongInt, Double, and String datatypes.

I tried to pass the value through my client application cast to to LongInt, and then cast it back it *MYSQL in my library but that did not work.

Honestly I do not understand why my library looses the value for *MYSQL.

Here is my library code:

{$CALLING STDCALL}

library D1Query;

{$mode objfpc}{$H+}

uses
  cmem, Windows, SysUtils, mysql50;

var
  sock: PMYSQL;
  qmysql: st_mysql;

type
  VArray = array[0..100] of Double;
  PArray = ^VArray;

  procedure InitSQL; stdcall;
  begin
    sock := mysql_real_connect(PMysql(@qmysql), '.', 'root', 'password'
                     , 'data', 3306, 'mysql', CLIENT_MULTI_STATEMENTS);
    if sock = nil then begin
      OutputDebugString(PChar('  Couldn''t connect to MySQL.'));
      OutputDebugString(PChar(mysql_error(@qmysql)));
      halt(1);
    end;
  end;

  procedure DeInitSQL; stdcall;
  begin
    mysql_close(sock);
  end;

  function SQL_Query(QRY: PChar; output: PArray): integer; stdcall;
  var
    rowbuf: MYSQL_ROW;
    recbuf: PMYSQL_RES;
    i: integer;
    nfields: LongWord;
  begin
    if (mysql_query(sock, QRY) < 0) then begin
      OutputDebugString(PChar('  Query failed '));
      OutputDebugString(PChar('   ' + mysql_error(sock)));
    end;

    recbuf := mysql_store_result(sock);
    nfields :=  mysql_num_fields(recbuf);
    rowbuf := mysql_fetch_row(recbuf);

    if (rowbuf <> nil) then begin
      for i:=0 to nfields-1 do
          output^[i] := StrToFloatDef(rowbuf[i], -666);
    end;

    mysql_free_result(recbuf);
    Result := i;   
  end;

exports
  SQL_Query, InitSQL, DeInitSQL;

begin
end.

Answer 1

Mike, I'd recommend using ZEOS 6.6.6 to connect to MySQL.

That way you don't have to muck around in low level code, but you can just use the standard ZEOS controls: TZConnection TZQuery etc.

Here's a Howto for ZEOS in Lazarus: http://wiki.lazarus.freepascal.org/Zeos_tutorial
And you can download the ZEOS package
here: http://sourceforge.net/projects/zeoslib/files/Zeos%20Database%20Objects/zeosdbo-6.6.6-stable/ZEOSDBO-6.6.6-stable.zip/download
Download the zip file and install the package for lazarus.

Helpfiles can be found here: http://sourceforge.net/projects/zeoslib/files/Zeos%20Database%20Objects/zeosdbo-6.6.6-stable/

Good luck.