[Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection

[Howard Lee Harkness]

On Thu, Jun 19, 2008 at 8:22 AM, Joost van der Sluis <joost at cnoc.nl> wrote:

> Op zaterdag 14-06-2008 om 16:06 uur [tijdzone -0500], schreef Howard Lee
> Harkness:
>
> > However, it doesn't seem to matter what I put in for nbDelete. I
> > always get the error "There are no fields found to generate the
> > where-clause" if I do ApplyUpdates for nbDelete. If I put nothing in
> > there, the field appears to be deleted, but it is still in the table,
> > and comes back to the grid when I press "refresh".
>
> Does your table has a primary key? And do you have set the
> 'UsePrimaryKeyAsKey' property to true?


Yes, and yes. See the code that I posted at the beginning of the thread --
if you see a problem with the code , please tell me what I'm doing wrong, or
not doing right. There is definitely something wrong with it, because it
doesn't work. If I left something out, tell me and I can just zip up the
whole project and email it to you.

>
>
> sqldb tries to create an delete-query for you, but to do that it has to
> indentify the record that should be deleted. Normally it uses the
> primary key for that. If there is no primary key, you have to tell it
> which fields should be included as a 'key'. (ie: be a part of the
> where-statement in the delete query: 'delect from table where ...)


Things would be a lot easier for me if only I could find a working example
of a small database program using MySQL and Lazarus. I've found a few
examples, but I have been unable to make any of them work. Currently, I'm
about to download the latest Lazarus/FPC versions to see if something
magically works, but it seems to me that I really ought be able to get a
trivial program like the one I posted at the beginning of the thread to work
with minimal effort.

Here again is the code that I posted in the first part of the thread:

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) -- YES! there is a primary key!
);

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            // YES! UsePrimaryKeyAsKey is set to
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
 end
end

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+}

interface

uses
 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.

BTW, I did get rid of the exception on close by adding an event handler for
formclose that closes the database. And I was able to get an insert to work
by adding an event handler to the navigator component that did
SQLQuery1.Appyupdates in response to nbPost button click. However, delete
and edit still do not work.
-- 
Howard Lee Harkness
www.celtic-fiddler.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20080621/5ca7f698/attachment-0007.html>