LUFDOC : mysqltips

Notes

Some simple examples of MySql database usage.

Small Demo

Using MySQL

Using MySQL with Powtils can be done in several ways.

Some choose to call the database procedures directly, using the database DLL (library) or API. More documentation on the direct api's is available.

Others choose to use third party database units that wrap several databases into the same system. These are bulkier end up make the program larger and less database specific, but can be good if one is switching between multiple databases.

Thirdy party wrappers

Examples are ZeosLib, Pascal Data Objects (PDO), and the FPC FCL-DB (not documented as of 2008).

SQL Injection

See how to prevent SQL injection by preparing your SQL statements and binding parameters into SQL templates/parameterized queries ahead of time.

Hosting Account Tips

If you wish to run programs from your home or office computer that connect to a remote database elsewhere on another server, then you will have to add your IP address to a "whitelist". CPanel accounts have this whitelist setting in the "mysql databases" area. If you run the programs on the server itself, you do not generally have to whitelist any IP address.

Some hosts (custom control panels) don't even have a mysql whitelist feature.

To find out your ip address use sites like this or this

Using MySQL API Directly

Some examples below follow on using the direct MySQL API (with the idea that you are using a standard CPANEL web hosting account).

Examples

See /download/mysql-test.zip

Or build a simple program like so:

program SampleConnect; {$mode objfpc} {$h+}

uses
  pwinit,   // only required in 1.7.x
  pwmain,   // "pwu" prefix in 1.6.x
  mysql4;   // note: mysql DLL should be in local dir or system dir
  
const
  DB: Pchar = 'cpaneluser_yourdb';
  User: Pchar = 'cpanelusername_dbuser';
  pw: Pchar = 'yourpassword';

var 
  hostip: pchar = 'localhost'; // your Mysql database IP 
  sock : PMYSQL;
  qmysql: TMYSQL;
  rowbuf: TMYSQL_ROW;
  recbuf: PMYSQL_RES;

{ simple connection routine }
procedure OpenDb;
begin
  mysql_init(@qmysql);
  sock:=  mysql_real_connect(@qmysql, hostip, user, pw, DB, 0, nil, 0);
  if sock=Nil then halt(1);
  if mysql_select_db(sock, DB) < 0 then halt(1);
end;

procedure RunQuery1;
begin
  if mysql_query(sock, pchar('SELECT * FROM test') ) < 0 then Halt(1);
  recbuf:= mysql_store_result(sock);
  if RecBuf = nil then begin
    out('<p>Query returned nil result.');
    mysql_close(sock);
    halt(1);
  end;
  rowbuf:= mysql_fetch_row(recbuf);
  while (rowbuf <> nil) do begin
    // output your rowbuf array here 
    // outln(rowbuf...)
    rowbuf:= mysql_fetch_row(recbuf); // keep fetching until nil in WHILE loop
  end;
  mysql_free_result(recbuf);
end;

begin
  OpenDb;
  RunQuery1;
  mysql_close(sock);
end.

Example 2

program SampleConnect; {$mode objfpc} {$h+}

uses
  pwinit,   // only required in 1.7.x
  pwmain,   // "pwu" prefix in 1.6.x
  mysql4;   // note: mysql DLL should be in local dir or system dir
  

const
  DB: Pchar = 'cpanelusername_yourdbname';
  User: Pchar = 'cpanelusername_dbuser';
  pw: Pchar = 'yourpassword';

var 
  hostip: pchar = 'localhost'; // your Mysql database IP or                
  sock : PMYSQL;
  qmysql: TMYSQL;
  rowbuf: TMYSQL_ROW;
  recbuf: PMYSQL_RES;

procedure OpenDb;
begin
  // connect to database
  sock:=  mysql_real_connect(@qmysql, hostip, user, pw, DB, 0, nil, 0);
  if sock=Nil then halt(1);
  if mysql_select_db(sock, DB) < 0 then halt(1);
end;

{ initialize database, optionally set some mysql OPTIONS }
procedure InitDb;
begin
  // initialize
  mysql_init(@qmysql);
  //BELOW ARE OPTIONAL INIT SETTINGS BEFORE YOU RUN QUERIES
  // set default charset in this connection to UTF-8
  // mysql_options(@qmysql, MYSQL_SET_CHARSET_NAME, 'utf8');
  // See http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html for more details.
end;

procedure RunQuery1;
begin
  if mysql_query(sock, pchar('SELECT * FROM test') ) < 0 then Halt(1);
  recbuf:= mysql_store_result(sock);
  if RecBuf = nil then begin
    out('<p>Query returned nil result.');
    mysql_close(sock);
    halt(1);
  end;
  out('<p>Number of records returned  : ',mysql_num_rows (recbuf));
  out('<p>Number of fields per record : ',mysql_num_fields(recbuf));
  { while loop that breaks when finished receiving data from DB}
  rowbuf:= mysql_fetch_row(recbuf);
  while (rowbuf <> nil) do begin
    // output your rowbuf array items here
    // outln(rowbuf...)
    rowbuf:= mysql_fetch_row(recbuf); // keep fetching until nil in WHILE loop
  end;
  mysql_free_result(recbuf);
end;

procedure CloseDb;
begin
  out('<p>Closing connection to database.');
  mysql_close(sock);
end;

begin
  InitDb;
  OpenDb;
  RunQuery1;
  CloseDb;
end.