Subject Re: [IBO] TIBOTable and field names that require quoting.
Author mspencewasunavailable
--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 10:43 PM 27/09/2006, you wrote:
>
> > > 2. The other half of the problem is that parameter names are
> > > actually Delphi identifiers. For that reason, you cannot have
> >quotes
> > > or spaces in the identifiers.
> > >
> >
> >This is news to me. Did you mean SQL identifiers?
>
> No, I meant Delphi identifiers. You can't for example, define a
> variable, a constant or an object with the name My Dog, or "My
> Dog". Likewise, a parameter has to have a legal Delphi identifier
> for its name. (A named parameter is a Delphi thing, not a Fb/IB
> thing. Hence why you see a ? in the statement that goes to the
API,
> with the parameter name commented)
>
> HB
>

The parameter name (TParam.Name) is just a string.

Here's a working example. Sorry it's so long but I wanted a real
one. I've adjusted the parameter name to illustrate my point:

This gets put into a TIBOQuery.

qry.SQL.Add('INSERT INTO WOANSWER (ID, "Last Name", "First
Name", "Street",' +
' "Town", "State", "Zip", "Phone", "Bus Phone",' +
' "Ext", "Lawn Sq Footage", "Interior Sq Footage",' +
' "Print Order", "Description", "Serv ID", "Price",' +
' "Price Over Max", "Cost", "Code", "Year Countdown",' +
' "Countdown", "Max Per Year", "Completed",' +
' "Proj Hours", "Avg Hours", "YTD Hours", "Start Date",' +
' "Frequency", "Crew", "Zone", "Route",' +
' "Last Performed", "Hold", "Proj Qty Used",' +
' "Inventory Label", "YTD Used", "Proj Cost",' +
' "Skip Weekends", "Price Type", "Qty Type", "Next Date",' +
' "WO Message", "Tax Code", SESSION_ID) ' +
'SELECT C.ID,C."Last Name",C."First Name",C."Street",' +
' C."Town",C."State",C."Zip",C."Phone",C."Bus Phone", ' +
' C."Ext",C."Lawn Sq Footage",C."Interior Sq Footage",' +
' S."Print Order", S."Description",S."Serv ID",
S."Price", ' +
' S."Price Over Max", S."Cost", S."Code", S."Year
Countdown", '
+
' S."Countdown", S."Max Per Year", S."Completed",' +
' S."Proj Hours", S."Avg Hours", S."YTD Hours", S."Start
Date", '
+
' S."Frequency", S."Crew", S."Zone", S."Route", ' +
' S."Last Performed", S."Hold", S."Proj Qty Used", ' +
' S."Inventory Label", S."YTD Used", S."Proj Cost", ' +
' S."Skip Weekends", S."Price Type", S."Qty Type",
S."Next Date",'
+
======> Parameter in next line
' S."WO Message", S."Tax Code", :"This is a session ID" '
+
'FROM CUST C, SCHED S, CUSTG CG ' +
' WHERE (S."Acc ID" = C.ID) ' +
' AND (C."Cat" = CG.ID) ' +
' AND (CG."Def Status" = ''A'') ' +
' AND (S."Countdown" > 0) ' + ' AND (S."Hold"
= ''N'') ';

Here we fill in the parameter from a field in another object.
TIBOQuery is kind enough to create the named parameter when it
parses the SQL:

qry.ParamByName('This is a session ID').AsInteger
:= StartupDM.SessionID;




And here's the trace, with the appropriate 6 records being found and
inserted.


27 Sep 2006 09:46:45:676 [ INFO] /*---
PREPARE STATEMENT
TR_HANDLE = 27681908
STMT_HANDLE = 27681692

INSERT INTO WOANSWER (ID, "Last Name", "First Name",
"Street", "Town", "State", "Zip", "Phone", "Bus
Phone",
"Ext", "Lawn Sq Footage", "Interior Sq Footage",
"Print Order", "Description", "Serv ID",
"Price", "Price Over Max", "Cost", "Code",
"Year Countdown", "Countdown", "Max Per Year",
"Completed", "Proj Hours", "Avg Hours", "YTD
Hours",
"Start Date", "Frequency", "Crew", "Zone", "Route",
"Last Performed", "Hold", "Proj Qty Used",
"Inventory Label", "YTD Used", "Proj Cost",
"Skip Weekends", "Price Type", "Qty Type",
"Next Date", "WO Message", "Tax Code", SESSION_ID)
SELECT C.ID,C."Last Name",C."First Name",C."Street",
C."Town",C."State",C."Zip",C."Phone",C."Bus Phone",
C."Ext",C."Lawn Sq Footage",C."Interior Sq Footage",
S."Print Order", S."Description",S."Serv ID",
S."Price",
S."Price Over Max", S."Cost", S."Code", S."Year
Countdown",
S."Countdown", S."Max Per Year", S."Completed",
S."Proj Hours", S."Avg Hours", S."YTD Hours",
S."Start Date",
S."Frequency", S."Crew", S."Zone", S."Route",
S."Last Performed", S."Hold", S."Proj Qty Used",
S."Inventory Label", S."YTD Used", S."Proj
Cost",
S."Skip Weekends", S."Price Type", S."Qty Type",
S."Next Date", S."WO Message", S."Tax Code",
====> Parameter
? /* "This is a session ID" */
FROM CUST C, SCHED S, CUSTG CG
WHERE (S."Acc ID" = C.ID)
AND (C."Cat" = CG.ID)
AND (CG."Def Status" = 'A')
AND (S."Countdown" > 0)
AND (S."Hold" = 'N')

PLAN JOIN (S NATURAL,C INDEX
(PK_Cust),CG INDEX (PK_Custg))

FIELDS = [ Version 1 SQLd 0 SQLn
64 ]
----*/
27 Sep 2006 09:46:45:738 [ INFO] /*---
EXECUTE STATEMENT
TR_HANDLE = 27681908
STMT_HANDLE = 27681692
PARAMS = [ Version 1 SQLd 1 SQLn 1
["This is a session ID"] = 64 ]

SELECT COUNT: 6
INSERT COUNT: 6

SECONDS = 0.015
----*/