How to add binary data to mysql database using a commandline program.
lazarus / 16-Databases

----------------------------------------------------------------------
lccampbell · 2016-04-21 04:03

I have looked everywhere for documentation on how to use MYSQL using a
non-GUI program (commandline). There is a good example on how to read
a mysql database (testdb5.pas) but not how to insert data. FPC 3.0.0
32 bit compiler. Mysql version 5.7. mysql57dyn package (or any package
that will work). Ability to insert records containing the following
columns. 1 Autoincrement field, 2 68 bit binary fields, 2 integer
fields. One binary field is an indexed unique key field and I need to
be able to detect when a duplicate key insertion is attempted. It
shouldn't be that hard but all I could find was gui solutions.

----------------------------------------------------------------------
balazsszekely · 2016-04-21 04:39

The only difference is you have to create the components dynamically,
like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
project1; {$mode objfpc}{$H+} uses  {$IFDEF UNIX}{$IFDEF UseCThreads} 
cthreads,  {$ENDIF}{$ENDIF}  Classes, SysUtils, sqldb, db,
mysql50conn  { you can add units after this }; var  MySQL50Connection:
TMySQL50Connection;  SQLTransaction: TSQLTransaction; function
ConnectToDb(const AHostName, ADatabaseName, AUserName, APassword:
string): Boolean;begin  Result := True;  with MySQL50Connection do 
begin    HostName := AHostName;    DatabaseName := ADatabaseName;   
UserName := AUserName;    Password := APassword;    CharSet := 'UTF8';
//or something else    Transaction := SQLTransaction;  end;  try   
MySQL50Connection.Connected := True;  except    on E: Exception do   
begin      Writeln('Cannot connect to database: ' + E.Message);     
Result := False;    end;  end;end; function Insert(const AID: Integer;
const AName: String): Boolean;var  SQLQuery: TSQLQuery;begin  Result
:= False;  SQLQuery := TSQLQuery.Create(nil);  try   
 SQLQuery.DataBase := MySQL50Connection;     SQLQuery.SQLTransaction
:= SQLTransaction;     SQLQuery.SQL.Text := 'insert into TEST(ID,
NAME) values(:PID, :PName)';     SQLQuery.Params.BeginUpdate;   
 SQLQuery.ParamByName('PID').AsInteger := AID;   
 SQLQuery.ParamByName('PNAME').AsString := AName;   
 SQLQuery.Params.EndUpdate;     SQLQuery.ExecSQL;     Result := True; 
finally    SQLQuery.Free;  end;end; begin  MySQL50Connection :=
TMySQL50Connection.Create(nil);  SQLTransaction :=
TSQLTransaction.Create(nil);  try    if ConnectToDb('', '', '', '')
then //change values    begin      Insert(43, 'test');    end; 
finally    MySQL50Connection.Free;    SQLTransaction.Free;  end; 
Readln;end.
You can easily adapt the code to suit your needs.

----------------------------------------------------------------------
lccampbell · 2016-04-21 18:24

Thanks it looks like it will do the trick. I will try it out this
weekend.

----------------------------------------------------------------------
lccampbell · 2016-04-24 13:18

Never did get the binary part to work right. Using the above code and
converting my binary string to a string worked great. Thanks for the
tip.