At 04:27 PM 25-04-01 -0400, you wrote:

> >I am new to Interbase & IBObjects. I am using IBObjects with >Delphi 4.0
>with update 3.
>
> >I have used the TIB_StoredProc to populate my tables and >everything is OK.
>I have populated the tables fine. I figured that >I would use stored
>procedures to retrieve data from the tables >to my Delphi program for
>further processing.
>
> >I am receiving this error:
>
> >Project w2process.exe raised exception class >EIB_StatementError with
>message
> >'Fieldname st_no not found'. Use step or run to continue.
>
> >st_no is defined as an integer default 0 nulls allowed. I am not >sure how
>to correct this problem. Any help would be >appreciated.
>
>I thank the people, who responded to the question above. You
>asked if this was SQL Select and submit the code.
>
>ALTER PROCEDURE "INFO_EMPR"
>( "PDATE" DATE)
>RETURNS
>( "EMPR_TAX_YEAR" CHAR(4),
> "EMPR_EIN" CHAR(9),
> "EMPR_NAME" VARCHAR(50),
> "EMPR_ADDR" VARCHAR(40),
> "EMPR_CITY" VARCHAR(25),
> "EMPR_STATE" CHAR(2),
> "EMPR_ZIP_EXT" VARCHAR(5),
> "EMPR_ZIP" CHAR(5),
> "NAME_CODE" CHAR(1),
> "TYPE_CODE" CHAR(1),
> "OTHER_EIN" CHAR(9),
> "TOT_EMPLOYEES" CHAR(7),
> "TOT_WAGES" CHAR(13),
> "TOT_IT_WITHHELD" CHAR(13),
> "FILE_ID" CHAR(12),
> "TRANS_EIN" CHAR(9),
> "DATE_PROCESSED" DATE,
> "DATE_SENT_VAX" DATE,
> "ST_NO" INTEGER,
> "END_NO" INTEGER
>)
>AS
>BEGIN
> for
> select EMPR_TAX_YEAR, EMPR_EIN, EMPR_NAME, EMPR_ADDR, EMPR_CITY,
>EMPR_STATE, EMPR_ZIP_EXT,
> EMPR_ZIP, NAME_CODE, TYPE_CODE, OTHER_EIN, TOT_EMPLOYEES,
>TOT_WAGES, TOT_IT_WITHHELD,
> FILE_ID, TRANS_EIN, DATE_PROCESSED, DATE_SENT_VAX, SNUMBER,
>ENUMBER
> from EMPLOYER where DATE_SENT_VAX = '12/31/1899' for update
> into
> :empr_tax_year, :empr_ein, :empr_name, :empr_addr,
>:empr_city, :empr_state,
> :empr_zip_ext, :empr_zip, :name_code, :type_code,
>:other_ein, :tot_employees,
> :tot_wages, :tot_it_withheld, :file_id, :trans_ein,
>:date_processed,
> :date_sent_vax, :st_no, :end_no
> as cursor getdata
> do
> begin
> update EMPLOYER set date_sent_vax = :pdate where
> (FILE_ID = :file_id and EMPR_EIN = :empr_ein and DATE_PROCESSED =
>:date_processed and
> (SNUMBER = :st_no and ENUMBER = :end_no));
> suspend;
> end
>END ^
>
>Something that I just noticed is that all of the return parameters with the
>exception of st_no and end_no are the same as field names in the select
>portion of the SQL statement. Can someone tell me if I change st_no to
>SNUMBER and end_no to ENUMBER would this correct the problem that I have.

Okay...(deep breath)..there are quite a few issues here. This, by the way, is actually off-topic for the IBO list - did you realise that there is an ib-support@yahoogroups.com list for interbase/firebird support questions?

Well...

First of all, the error is arising because you have defined two sets of variables with names that evaluate differently. You have double-quoted the names of your output arguments (parameters) which forces them to be case-sensitive. The variable names for the ..SELECT INTO... predicate are case-insensitive. I think you are getting an error amongst a bunch of errors arising from ambiguities about just which variable is being referred to.

OK, now, let's say that you amended the declaration of your output arguments and removed the double quotes. You would then have a full set of case-insensitive variable names to which you could refer...there is no problem using the same names for parameters/variables and the corresponding column names, as long as you precede the variable name with ":" when you are reading its value. You could bump into confusion when using the variable name on the left-hand side of the assignment statement if you are not careful about absolute identifiers.

However...

the larger problem is that you are using your stored procedure to attempt to both select a dataset and update the same rows within a simultaneous transaction. If you visualise what the SP is trying to do, you'll realise that it can't be done this way. What could the engine return in the dataset, with all those uncommitted updates hanging around?

There is no (logical) problem with using a selectable procedure to perform updates on a *different* table (even so, it's not a practice one would recommend because it introduces extra inter-dependencies in your metadata).

If your intention here is to write a routine that performs some DML and shows the results to the user, approach it by means of an executable SP that completes in one transaction context and a select which displays the results in a subsequent one.

If, OTOH, you want to show the user a read-only view of the data as they would be as a result of the DML (but not actually perform the DML), then correct the syntax of this proc and remove the DML, replacing it with computed values on the output columns affected.

hth

Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________