I'm having some trouble understanding the operation of the TSQLQuery. I'm using MySQL 5, TMySQL50Connection, and TSQLQuery.
Several problems. First of all, it's not clear to me how the transaction component is to be hooked up. There is a property for that in both the connection component and the query component -- do these need to be separate components, or can they be the same transaction?
I'm also getting some errors on updates. I have been unable to locate any documentation or examples that relate to the relative simple task I wish to accomplish, so I figure the best way to get the information is to put together a really simple example, and see if I can find somebody to tell me what it wrong with it. Once I get it figured out, I'll be happy to write up the missing documentation and submit it to wherever it needs to go.
To start with, here's a sample database setup:
CREATE TABLE IF NOT EXISTS ZIPCODE
(
ZIPID INT NOT NULL AUTO_INCREMENT,
ZIP VARCHAR(5) NOT NULL,
ZIPEXT VARCHAR(4),
CITY VARCHAR(40),
STATE VARCHAR(2),
PRIMARY KEY(ZIPID)
);
INSERT INTO ZIPCODE (ZIP,ZIPEXT,CITY,STATE) VALUES ('01814',NULL,'Lawrence','MA')
,('13502',NULL,'Utica','NY')
,('22030',NULL,'Fairfax','VA')
,('27030',NULL,'Mt. Airy','NC')
,('27103',NULL,'Winston-Salem','NC')
,('27104',NULL,'Winston-salem','NC')
,('27106',NULL,'Winston-Salem','NC')
I started up Lazarus, created an application, and put a DBGrid, DBNavigator, a MySQL50Connection, an SQLTransaction, an SQLQuery, and a DataSource components on it. Here is the Form File:
object Form1: TForm1
Left = 307
Height = 300
Top = 180
Width = 602
HorzScrollBar.Page = 601
VertScrollBar.Page = 299
ActiveControl = DBGrid1
Caption = 'Form1'
ClientHeight = 300
ClientWidth = 602
Position = poDesktopCenter
object DBGrid1: TDBGrid
Left = 24
Height = 201
Top = 32
Width = 560
DataSource = Datasource1
FixedColor = clBtnFace
FixedHotColor = cl3DLight
Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize, dgColumnMove, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit]
OptionsExtra = [dgeAutoColumns, dgeCheckboxColumn] ParentColor = False
TabOrder = 0
TabStop = True
end
object DBNavigator1: TDBNavigator
Left = 152
Height = 25
Top = 248
Width = 241
BevelOuter = bvNone
ClientHeight = 25
ClientWidth = 241
DataSource = Datasource1
end
object MySQL50Connection1: TMySQL50Connection
Connected = True
Streamedconnected = True
DatabaseName = 'test'
Password = 'test'
Transaction = SQLTransaction1
UserName = 'root'
left = 24
top = 9
end
object SQLQuery1: TSQLQuery
Active = True
Database = MySQL50Connection1
Transaction = SQLTransaction1
SQL.Strings = (
'select * from zipcode'
'order by zip'
)
Params =
UpdateMode = upWhereChanged
UsePrimaryKeyAsKey = True
ParseSQL = True
left = 24
top = 44
end
object SQLTransaction1: TSQLTransaction
Database = MySQL50Connection1
left = 59
top = 9
end
object Datasource1: TDatasource
DataSet = SQLQuery1
left = 59
top = 44
endend
I did not add any code at all to the main PAS file, so the project and source are as follows:
program zipcode;
{$mode objfpc}{$H+}
uses
{$IFDEF UNIX}{$IFDEF UseCThreads}
cthreads,
{$ENDIF}{$ENDIF}
Interfaces, // this includes the LCL widgetset
Forms
{ you can add units after this }, zipcode, SQLDBLaz;
begin
Application.Initialize;
Application.CreateForm(TForm1, Form1);
Application.Run;
end.+++++
unit Zipcode;
{$mode objfpc}{$H+}
interfaceuses
Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
mysql50conn, sqldb, db, DBGrids, DbCtrls;type
{ TForm1 }
TForm1 = class(TForm)
Datasource1: TDatasource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
MySQL50Connection1: TMySQL50Connection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
initialization
{$I zipcode.lrs}
end.Ok, if I run this as-is, I get some interesting errors. First thing I noticed is that when I click on "add" in the DBNavigator, the auto-increment field is not added properly; it's left blank.
Next, when I exit the program, I get a mysterious error message that tells me that I attempted to use a type name as an expression, as reported by procedure DatabaseError in unit db. I get a database error from just loading the program into Lazarus, and then closing Lazarus.
I'm suspecting the fixes for these problems will go a long way towards giving me an understanding of when and how to hook up the transaction components, and just what the parseSQL property is used for. And I'm suspecting that the fixes are very close to being trivial, but I've already spent too much time trying to help by letting me know what I am leaving out and/or doing wrong.
TIA