CODEWITHSUNDEEP
mysqldelphidllmysql-connectorlazarus
Mike Furlender
Mike Furlender

Reputation: 4019

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

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.

Upvotes: 2

Views: 1945

Answers (1)

Johan
Johan

Reputation: 76537

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.

Upvotes: 1

Related Questions