Lazarus-ZEOS-FireBird-parameter problem
lazarus / 16-Databases

----------------------------------------------------------------------
iru · 2010-04-09 11:58

Gentlefolk,

Using Lazarus 9.28.2, ZEOS 6.6.6, FireBird 2.1.5, Windows XP SP-3.

Using DBGRID-DataSource-(ZQuery-ZUpdateSQL)-ZConnection.
  ZQuery.CachedUpdates = False.

Connect to the database OK, database data displayed in DBGRID.

Problem is on Update/Insert and parameters.

The SQL in ZQuery.SQL is "select * FROM TABLE02 WHERE UID = :UID"
The SQL in ZUpdateSQL.InsertSQL is

  "INSERT INTO TABLE02
    (FIELD01)
  VALUES
    (:FIELD01)"

Where FIELD01 and UID are valid fields in TABLE02, UID has the
"unique" constraint.

When changing rows in DBGrid the code failes with an exception about
violation of unique key constraint.

The SQL that causes this is

 "INSERT INTO TABLE02
    (FIELD01)
  VALUES
    (?)"

Much trawling through the source code (ZSQLStrings, etc) suggests that
the the problem is that the code around line 380 in ZQLStrings where
an attempt is made to find a match on a parameter:

          ParamIndex := FindParam(ParamName);
          if ParamIndex < 0 then
            ParamIndex := FParams.Add(ParamName);

On return from FindParam the result is -1 indicating no match on
"UID".
Unfortunately I cannot debug at any lower to check what it is
attempting to match.

Alot of trawling around the internet on the subject shows lots of
examples/tutorials/whatever on accessing databases, very little on
insert/update or similar errors (?) that I am encountering.

I must be missing something somewhere in the DBGrid-DataSource-ZQuery-
ZUpdateSQL chain.
Trawling around the internet shows examples/tutorials on simple
queries, nothing much on insert/update, nothing much on problems with
parameters.

Any help/suggestions/examples/tutorials on the correct use of
parameters when performing an insert/update. Any help at all......

Ian

----------------------------------------------------------------------
Zoran · 2010-04-09 17:02

Would you upload a small (not working) application which would best
show what you are trying to achieve?

Let's assume that the database would be c:\database1.fdb with one
table. Upload the database backup and source of the smallest possible
application (for example with an edit and a button which should insert
a value entered in edit or something like that). I'll see then if I
can make it work.

----------------------------------------------------------------------
iru · 2010-04-09 23:23

Thank you for the response.

Attached is a zip of "unit1" of the test project and the database
structure/contents. The directory "forumdata" contains a file with the
metasql for the database structure and contents.
Only sent the unit1 as I could not comptess the project under the
forum upload limits.

The FB database has one table, two integer fields.
The test program is one form with all "Z" components. The
Zmonitor.Memo shows the SQL, note the "msg: select * FROM TABLE02
WHERE UID = ?" in the .memo1 display.

There is no button for post, etc, in fact there is no "user" code in
the test program except for the .memo1.clear in the formcreate.

Put the cursor on the grid, enter numeric data in both columns the use
the down arrow. This will initiate a "post" and an exception will
occur.

Thanks for looking at this, it has cost me a lot of time and a lot of
frustration.

Ian

----------------------------------------------------------------------
Zoran · 2010-04-10 05:34

First, you should remove WHERE UID = :UID from the ZQuery1.SQL. It
does not make any sence and you do not provide any value in code to
substitude this parameter in select.
So, the ZQuery1.SQL should be just "select * from TABLE02".

Now, when you removed this, let's take a look at your ZUdateSQL1
component. You have a problem in insert statement, as you don't
provide a value for UID field here.
If you change the insert statement to include UID, everything will
work:
Code: [Select]INSERT INTO TABLE02
  (UID, FIELD01)
VALUES
  (:UID, :FIELD01)

The problem should be solved with this. However, you will achieve the
same functionality by simply removing ZUpdateSQL1! That's because all
three statements -- insert, modify and delete are nothing else but
expected default. Everything will work exactly the same if you remove
UpdateSQL component. Try!

In insert maybe you expected the server to provide the value for UID
automatically.
Note that this field is not autoincrement (in Firebird it means that
you did not define a generator and a triger which would provide this
functionality), so you cannot expect the server to provide a value
automatically. Read this: http://www.firebirdfaq.org/faq29
If you use FlameRobin, you can do it quickly this way: In the main
tree view, expand the TABLE02, then right-click on UID and chose
ShowProperties. In FieldProperties dialog check both "create new
generator" and "create trigger".

Anyway, the parameter :UID in ZQuery1 select statement really did not
make any sence.

I hope I helped you.

----------------------------------------------------------------------
iru · 2010-04-10 19:48

Thank you for the response.

The parameter on the select is some residue from my pottering, I had
the same problems with or without it.

The SQL on the zupdate insert/delete/update/etc is an indication of my
troubles of getting my head around how parameters and the zquery-
zupdatesql functioned.
All compounded by my miss(?)-interpretation of the "?" in the sql
statements displayed in the zmonitor log.

Rather than 

  msg: INSERT INTO TABLE02 (FIELD01, UID) VALUES (?, ?)

I was expecting a parameter name or a value  rather than "?, ?" and
from wandering around code and some examples took "?" as an error or
incomplete SQL and went off chasing in strange directions.......

OK on the generator for the UID, will include it as I progress from
test to further development.

Tidied up the test setup SQL, all works OK.

Thanks once again. Your response/s were rapid and most helpful.

Regards, Ian.