Hi, Adam

Yes. I'm using FB 1.5 higher, I'm thinking it could be a bug in FB1.5, since I'm using the following generated SQL without problem, but as long as I assign it to a string, and calling for execute statement thisstring into then got the error.

select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY), sum(id.AMOUNT) from INVOICE i inner join INVOICE_DETAIL id on id.INVOICE_ID = i.ID and id.PART_ID is null and id.ADJUSTINVENTORY <> 'B' inner join INVENTORY iv on iv.ID = id.INVENTORY_ID inner join COUNTRY c on c.ID = iv.COUNTRY_ID inner join MATERIAL m on m.ID = iv.MATERIAL_ID inner join TARIFF t on t.ID = m.TARIFF_ID where i.POSTED = 'Y' and i.INVOICE_NO > 0 group by m.DESCRIPTION, t.CODE, c.CODE

Runing this SQL in SQL editor without any problem, and it does return correct dataset. BUT execute statement it with NO success!


William

Adam <s3057043@...> wrote: William,

Perhaps you have included the wrong procedures etc.

In your other prost you include the SP source for
REPORT_TARIFFJOURNAL but your test refers to the stored procedure
REPORT_TARIFF.

I assume this is a mistake and replace for REPORT_TARIFFJOURNAL, but
it works fine in iSQL. I also tried the generated query directly and
it works without errors in iSQL. Obviously it returns no data but
there is no error message either.

Execute statement is new in Firebird 1.5. You are using Firebird 1.5
or higher right?

Adam




--- In firebird-support@yahoogroups.com, Wei Yu <william_yuwei@y...>
wrote:

>
> Hi, Adam
>
> I have emailed all the related DDL (include the store procedures).
>
> Thanks
>
> William
>
> Adam <s3057043@y...> wrote: --- In firebird-

support@yahoogroups.com, "william_yuwei"

> <william_yuwei@y...> wrote:
> >
> > Hi, All
> >
> > I have the following code in one of my store procedure
> > REPORT_TARIFF(divisionid, excludeinvoices):
> >
> > sql_text = 'select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY),
> > sum(id.AMOUNT) '
> > || 'from INVOICE i ' ||
> > 'inner join INVOICE_DETAIL id on id.INVOICE_ID =

i.ID and

> > ' ||
> > 'id.PART_ID is null and id.ADJUSTINVENTORY

<> ''B'' '||

> > 'inner join INVENTORY iv on iv.ID =

id.INVENTORY_ID ' ||

> > 'inner join COUNTRY c on c.ID = iv.COUNTRY_ID ' ||
> > 'inner join MATERIAL m on m.ID = iv.MATERIAL_ID ' ||
> > 'inner join TARIFF t on t.ID = m.TARIFF_ID ';
> >
> > if (divisionid > 0) then
> > sql_text = sql_text ||
> > 'where i.DIVISION_ID = ' || divisionid || ' and ' ||
> > 'i.POSTED = ''Y'' and i.INVOICE_NO > '

|| :last_tariff_invoiceno;

> > else
> > sql_text = sql_text ||
> > 'where i.POSTED = ''Y'' and i.INVOICE_NO > ' ||
> > :last_tariff_invoiceno;
> >
> >
> > if (excludeinvoices <> '') then
> > sql_text = sql_text ||
> > ' and i.INVOICE_NO not in (' || excludeinvoices || ') ';
> >
> > sql_text = sql_text ||
> > ' group by m.DESCRIPTION, t.CODE, c.CODE';
> >
> > /*
> > for select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY), sum

(id.AMOUNT)

> > from INVOICE i
> > inner join INVOICE_DETAIL id on id.INVOICE_ID = i.ID and
> > id.PART_ID is null and id.ADJUSTINVENTORY <> 'B'
> > inner join INVENTORY iv on iv.ID = id.INVENTORY_ID
> > inner join COUNTRY c on c.ID = iv.COUNTRY_ID
> > inner join MATERIAL m on m.ID = iv.MATERIAL_ID
> > inner join TARIFF t on t.ID = m.TARIFF_ID
> > where ((:divisionid <= 0) or (i.DIVISION_ID

= :divisionid)) and

> > i.POSTED = 'Y' and i.INVOICE_NO
> :last_tariff_invoiceno
> > group by m.DESCRIPTION, t.CODE, c.CODE
> > */
> >
> > for execute statement sql_text
> > into :DESCRIPTION, :TARIFF_CODE, :COUNTRY_CODE, :QTY,
> > :INVOICE_AMOUNT
> > do
> > begin
> > suspend;
> > end
> >
> > 1)What is strange is that, when I debug this store procedure

under

> > IBExpert, everything work just fine, but when I try to run the

store

> > procedure in the SQL editor, select * from report_tariff(-

1, ''), I

> > got: Unsuccessful execute caused by an unavailable resource.

Variable

> > type (position 3) in EXECUTE STATMENT 'select m.DESCRIPTION,

t.CODE,

> > c.CODE, sum(id.QYT),' INTO does not match return.
> >
>
> IBExpert is a simulator not a true debugger. There is no true

debugger

> for Firebird stored procedures. That means it may behave

differently

> in some cases, and in some cases, may behave "better" than

Firebird does.

>
> > 2)When I using my application against "select * from
> > report_tariffjournal(-1, '')" I got error like: unexpected end of
> > command, Dyanamic SQL Error, Error Code: 249(100000) Invalid

token SQL

> > Error code = -104"
> >
> > What am I doing wrong?
> >
>
> I had a problem before attempting to combine parameters with

execute

> statement. Also make sure your variable is long enough to handle

the

> entire query.
>
>
> Adam
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
> ---------------------------------
> YAHOO! GROUPS LINKS
>
>
> Visit your group "firebird-support" on the web.
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of

Service.

>
>
> ---------------------------------
>
>
>
>
>
>
> William, Yu
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> [Non-text portions of this message have been removed]
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




---------------------------------
YAHOO! GROUPS LINKS


Visit your group "firebird-support" on the web.

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


---------------------------------






William, Yu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]