Parameters not working
lazarus / 16-Databases

----------------------------------------------------------------------
BezNalogov · 2009-08-13 14:02

I use postgres as db.

First I tried to set to the sqlquery a parameter (called
NATIONALITYID) via the IDE and then via the property inspector.

The query is set this way (in the form OnCreate event):
SQLExtensions.SQL.Text :=  'SELECT * FROM is_extensions WHERE
jurisdiction_id = :NATIONALITYID OR jurisdiction_id=0 ORDER BY
jurisdiction_id,extension_id';

Then I execute this:

SQLExtensions.Params.ParamByName('NATIONALITYID').AsString :=
DsContacts.DataSet.FieldByName('nationality_id').AsString;
SQLExtensions.ExecSQL;

It will give an exception error that the parameter doesn't exist.

Then I tried to create this parameter, so I put in the OnCreate event
of the form (before the sql query is set):

SQLExtensions.Params.CreateParam(ftString,'NATIONALITYID',ptInput);

Run again, still I get the same exception error.

With my third attempt I put the line to create the parameter above
where the parameter will also be set, so the code there is now:

SQLExtensions.Params.CreateParam(ftString,'NATIONALITYID',ptInput);
SQLExtensions.Params.ParamByName('NATIONALITYID').AsString :=
DsContacts.DataSet.FieldByName('nationality_id').AsString;
SQLExtensions.ExecSQL;

And again it gives an exception error, except that this time it's
'Unknown field type for parameter NATIONALITYID'

What is wrong here? A bug or don't I understand how to use parameters?

I use: Lazarus 0.9.27 r21126 FPC 2.2.4 x86_64-linux-gtk 2 (beta)

----------------------------------------------------------------------
DougNettleton · 2009-08-14 21:36

Hi:

I offer the following, in answer to this and your other postings about
DBLookupComboBox.  Most of my experience has been with Lazarus under
Windows XP / Vista, but I'm interested in Linux too - compile
everywhere

I use FireBird as a db, and I have a test database with the following
tables:
1) Provcode - a Province / State table
2) Country - a Country table

They can be created with the following SQL commands:

CREATE DOMAIN LOGICAL AS Char(1)
 DEFAULT 'N'
 NOT NULL
 check (value in ('Y', 'N'));
COMMIT;
CREATE TABLE COUNTRY(
  CODE Char(3) NOT NULL,
  NAME Varchar(28),
  DELFLAG LOGICAL DEFAULT 'N',
  PRIMARY KEY (CODE)
);
COMMIT;
CREATE TABLE PROVCODE
(
  CODE Char(2) NOT NULL,
  DESCRIP Varchar(28),
  COUNTRY Char(3),
  PRINTDESC LOGICAL DEFAULT 'N',
  DELFLAG LOGICAL DEFAULT 'N',
  PRIMARY KEY (CODE)
);
COMMIT;

And partially populated with:

INSERT INTO COUNTRY (CODE, NAME, DELFLAG) VALUES ('CAN', 'Canada',
'N');
INSERT INTO COUNTRY (CODE, NAME, DELFLAG) VALUES ('USA', 'United
States of America', 'N');

INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('AB', 'Alberta', 'CAN', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('AK', 'Alaska', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('AL', 'Alabama', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('AR', 'Arkansas', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('AZ', 'Arizona', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('BC', 'British Columbia', 'CAN', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('CA', 'California', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('CO', 'Colarado', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('CT', 'Connecticut', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('DC', 'District of Columbia', 'USA', 'N', 'N');
INSERT INTO PROVCODE (CODE, DESCRIP, COUNTRY, PRINTDESC, DELFLAG)
VALUES ('DE', 'Delaware', 'USA', 'N', 'N');

If you have such a database and you create a new Project as follows:

Add DatabaseConnection and configure for your db.

Add a SQLTransaction - linked to the database.

Add three pairs of SQLQuery / Datasource as follows:

SQLQuery1.SQL set to:

select * from country
order by code

DataSource1.DataSet set to SQLQuery1
DataSource1.AutoEdit set to False

SQLQuery2.SQL set to:

select * from provcode
where country = :code
order by code

SQLQuery2.DataSource set to DataSource1

DataSource2.DataSet set to SQLQuery2
DataSource2.AutoEdit set to True

SQLQuery3.SQL set to:

select * from country
order by code

DataSource3.DataSet set to SQLQuery3
DataSource3.AutoEdit set to False

Add two DBGrids as follows:

DBGrid1.DataSource set to DataSource1
DBGrid2.DataSource set to DataSource2

Add a DBLookupComboBox as follows:

DBLookupComboBox1.DataSource set to DataSource2
DBLookupComboBox1.DataField set to COUNTRY
DBLookupComboBox1.ListSource set to DataSource3
DBLookupComboBox1.ListField set to NAME
DBLookupComboBox1.KeyField set to CODE

Add a Button and in the button's OnClick event add:

  ShowMessage(SQLQuery2.FieldByName('COUNTRY').Value);
  SQLQuery2.Post;
  SQLQuery2.ApplyUpdates;
  SQLTransaction1.CommitRetaining;
  SQLQuery2.Close;
  SQLQuery2.Open;

As you move through the country table - grid1, grid2 displays the
Provinces / States for the country - so parameterised queries seem to
be working fine.

If using the DBLookupComboBox, you switch to a different country,
DBGrid2 will indicate that the SQLQuery2 dataset has gone into edit
mode and you will see the result of the "selection" in the grid.

If you then press the button a message will display with the "new"
country value of the Province / State dataset and DBGrid2 will refresh
with the changed Province / State removed.  Navigating DBGrid1 to the
"New" country will show the Province / State that disappeared from the
DBGrid2 when you pressed the button.

I was using Lazarus version 0.9.27 beta dated: 2009-08-12 with FPC
version 2.2.5 i386-win32.
The above worked equally well with RxDBLookupCombo component from the
Lazarus components repository revision 933.

As I said I'm interested in Lazarus under Linux so I launched Sun
VirtualBox and fired up Ubuntu 9.04, I had previously installed Code
Typhon but since it was a little out of date, I downloaded and
installed the latest version 0.9.66.

The Lazarus Help About box says the following:
Version#: 0.9.27 beta
Date: 2009-08-14
FPC Version: 2.3.1
SVN Version: Unknown
i386-linux-gtk 2 (beta)

The first disturbing thing was that I got an error when trying to run
(F9) that kind of indicated that the datasets were not being open
correctly.

Adding a OnCreate event for the form with following lines:

IBConnection1.Connected := True;
SQLQuery1.Open;
SQLQuery3.Open;
SQLQuery2.Open;

Overcame that initial problem, but then I noticed when I "changed the
country" with the DBLookupComboBox, it was losing the last character
from the country code and the province / state being no longer matched
any country code - perhaps having a field "code char(2)" on one table
and a field "Code char(3)" on the other table caused the problem.

At this point, I decided to try and use the zeoslib components - which
I usually use.

So I deleted the IBConnection1, SQLTransaction1, SQLQuery1, SQLQuery2,
and SQLQuery3 and added Zconnection1, ZQuery1, ZQuery2 and ZQuery3 and
changed the Button OnClick event to:

  ShowMessage(ZQuery2.FieldByName('COUNTRY').Value);
  ZQuery2.Post;
  ZQuery2.Close;
  ZQuery2.Open;

and changed the Form OnCreate event to:

  ZCOnnection1.Connected := True;
  ZQuery1.Open;
  ZQuery3.Open;
  ZQuery2.Open;

But later, discovered that it worked just fine if I commented these
out assuming they were set "Active" in the IDE.

If you try the equivalent of the foregoing with postgresql, you may
have greater success with Zeoslib.  I've been using it because I find
I have to write less code and it has an "experimental" method
"RefreshCurrent" which is great in multi-user enviironemts for
Firebird.  Unfortunately, the version of Zeoslib 6.6.5-stable in the
current Code Typhon doesn't appear to have this
method.

You can also replace:

  ZQuery2.Close;
  ZQuery2.Open;

with

  ZQuery2.Refresh;

Whereas SQLQuery2.Refresh doesn't appear to do anything even under
Windows.

Good luck with your problem(s),

Doug

P.S. It may be the case that postgresql doesn't support parameterised
queries, but if that's the case someone would probably jump in by now.

P.P.S. AFAIK, "select" sql statements are "opened" not "executed"