Hi,

I need to update a first GDB on client.

Well, I did have program on clipper and used this sequence:

Backup of Total Database

Backup of table to update.

Update structure of table (In script file)

Populate table updated with datas in backup table.

Deleted the backup table to update. and repeat until finish tables.

Now I need to atualize the GDB on client and I don′t have Idea for
"automatic" this procedure.

Anybody have one Idea ? Please help-me.

Thank′s for Attenction.

RGS,

Alex



-----Mensagem original-----
De: ib-support@yahoogroups.com [mailto:ib-support@yahoogroups.com]
Enviada em: segunda-feira, 14 de abril de 2003 09:30
Para: ib-support@yahoogroups.com
Assunto: [ib-support] Digest Number 1424



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


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

There are 13 messages in this issue.

Topics in this digest:

1. Re: Timestamp granularity
From: Paul Reeves <paul@...>
2. about temp table
From: Zhibin Sun <seawolfxp@...>
3. Re: Problem with stored procedure FB1.0.2
From: "Johan Leroy" <johan.leroy@...>
4. Re: Problem with stored procedure FB1.0.2
From: "Martijn Tonies" <m.tonies@...>
5. Re: about temp table
From: Helen Borrie <helebor@...>
6. Re: Problem with stored procedure FB1.0.2
From: "Johan Leroy" <johan.leroy@...>
7. Re[2]: Problem with stored procedure FB1.0.2
From: Alexander Tabakov <saho@...>
8. Re: Problem with stored procedure FB1.0.2
From: Helen Borrie <helebor@...>
9. Re: Re[2]: Problem with stored procedure FB1.0.2
From: "Martijn Tonies" <m.tonies@...>
10. RE: Problem with stored procedure FB1.0.2
From: Dmitry Yemanov <dimitr@...>
11. Re: Problem with stored procedure FB1.0.2
From: "Johan Leroy" <johan.leroy@...>
12. Re: Re[2]: Problem with stored procedure FB1.0.2
From: "Johan Leroy" <johan.leroy@...>
13. Re: Problem with stored procedure FB1.0.2
From: "Johan Leroy" <johan.leroy@...>


________________________________________________________________________
________________________________________________________________________

Message: 1
Date: Mon, 14 Apr 2003 13:11:10 +0200
From: Paul Reeves <paul@...>
Subject: Re: Timestamp granularity

Lele Gaifax wrote:

>>>>>>Helen Borrie l'ha dit:
>
>
> Helen> If your server is on Windows, you can use the UDF from
> Helen> FBUDF.dll, GetExactTimeStamp, to get a timestamp with
> Helen> ten-thousandths of a second.
>
> BTW, is there any particular reason for which the FBUDF is available
> only under Win32?
>

Yes - porting problems.

These have been resolved for Fb 1.5.


Paul
--

Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird and InterBase




________________________________________________________________________
________________________________________________________________________

Message: 2
Date: Mon, 14 Apr 2003 19:40:26 +0800 (CST)
From: Zhibin Sun <seawolfxp@...>
Subject: about temp table

hi ib-supports:

I seems have to use a temp table to support a query.
But The FireBird1.02 does not support my 'Select ...
Into ... ' statement. How do I do it? or could anyone
provide me a temp table example.

Thanks in advance in this matter.

Sun

_________________________________________________________
Do You Yahoo!?
雅虎通网络KTV, 随时随地免费卡拉OK~~
http://rd.yahoo.com/mail_cn/tag/?http://cn.messenger.yahoo.com//chat/index.h
tml


________________________________________________________________________
________________________________________________________________________

Message: 3
Date: Mon, 14 Apr 2003 13:46:37 +0200
From: "Johan Leroy" <johan.leroy@...>
Subject: Re: Problem with stored procedure FB1.0.2

The problem is that when the sql statement (eg.)
SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO :StockQty;
is executed in the procedure and there is no result for the current ItemIdx,
it containts the result of the previous select where ther was a record for
Itemidx.
eg
Select ... ItemIdx = 5 -> result 10
Select ... itemidx = 1 -> (no record for it) return also 10

I must say that I only have this problem when using stored procedures. In
this case the procedure also manipulated a lot of tables.

regards

Johan


""Martijn Tonies"" <m.tonies@...> schreef in bericht
news:009f01c30276$f4f0bcc0$0a02a8c0@seal...

> Hi Johan,
>
> > One of my stored procedures is manipulating a lot of tables and need in

a

> > loop the data that is writen to the db in this same loop.
> > The problem I have is that the variables aren't update always when I

need

> to
> > retrive a null from the database.
>
> Retrieve a NULL where?
>
> > The current sollution I have is to set the var to 0 and then it works.
> Here
> > follows an example
> > StockQty = 0;
> > StockAdaptionQty = 0;
> > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx

INTO

> > :StockQty;
>
> You mean, if this SELECT doesn't return any rows? 'Cause then STOCKQTY

will

> stay empty (null).
>
> > SELECT Quantity FROM RESM_STOCK_ADAPTION_DETAILS WHERE
> > ItemIdx = :ItemIdx AND AdaptionIdx =:AdaptionIdx INTO
> :StockAdaptionQty;
> >
> > so if i dont set StockQty and StockAdaptionQty to 0 then I don't get the
> > correct data.
> >
> > Mayby this is a bug in FB or is there any other explination for it.
> >
> >
> > Regards
> >
> >
> > Johan
> >
> > PS: if needed I can send the complete proc source
>
> Better explain what you mean :)
>
>
> With regards,
>
> Martijn Tonies
> Database Workbench - the developer tool for InterBase & Firebird
> Upscene Productions
> http://www.upscene.com
>
> See you at the First European Firebird Conference in May in Fulda, Germany
> http://www.firebird-conference.com
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>

________________________________________________________________________
________________________________________________________________________

Message: 4
Date: Mon, 14 Apr 2003 13:52:26 +0200
From: "Martijn Tonies" <m.tonies@...>
Subject: Re: Problem with stored procedure FB1.0.2

Hi Johan,

> The problem is that when the sql statement (eg.)
> SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO :StockQty;
> is executed in the procedure and there is no result for the current

ItemIdx,

> it containts the result of the previous select where ther was a record for
> Itemidx.
> eg
> Select ... ItemIdx = 5 -> result 10
> Select ... itemidx = 1 -> (no record for it) return also 10

I'm pretty sure it doesn't return "10" - what makes you think it does?

--
Martijn

> I must say that I only have this problem when using stored procedures. In
> this case the procedure also manipulated a lot of tables.
>
> regards
>
> Johan
>
>
> ""Martijn Tonies"" <m.tonies@...> schreef in bericht
> news:009f01c30276$f4f0bcc0$0a02a8c0@seal...
> > Hi Johan,
> >
> > > One of my stored procedures is manipulating a lot of tables and need

in

> a
> > > loop the data that is writen to the db in this same loop.
> > > The problem I have is that the variables aren't update always when I
> need
> > to
> > > retrive a null from the database.
> >
> > Retrieve a NULL where?
> >
> > > The current sollution I have is to set the var to 0 and then it works.
> > Here
> > > follows an example
> > > StockQty = 0;
> > > StockAdaptionQty = 0;
> > > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx
> INTO
> > > :StockQty;
> >
> > You mean, if this SELECT doesn't return any rows? 'Cause then STOCKQTY
> will
> > stay empty (null).
> >
> > > SELECT Quantity FROM RESM_STOCK_ADAPTION_DETAILS WHERE
> > > ItemIdx = :ItemIdx AND AdaptionIdx =:AdaptionIdx INTO
> > :StockAdaptionQty;
> > >
> > > so if i dont set StockQty and StockAdaptionQty to 0 then I don't get

the

> > > correct data.
> > >
> > > Mayby this is a bug in FB or is there any other explination for it.
> > >
> > >
> > > Regards
> > >
> > >
> > > Johan
> > >
> > > PS: if needed I can send the complete proc source
> >
> > Better explain what you mean :)

________________________________________________________________________
________________________________________________________________________

Message: 5
Date: Mon, 14 Apr 2003 21:51:29 +1000
From: Helen Borrie <helebor@...>
Subject: Re: about temp table

At 07:40 PM 14/04/2003 +0800, you wrote:

>hi ib-supports:
>
>I seems have to use a temp table to support a query.
>But The FireBird1.02 does not support my 'Select ...
>Into ... ' statement. How do I do it? or could anyone
>provide me a temp table example.

In Firebird you have no need of a temp table, since you can generate an
output set directly from a stored procedure. See the Language Reference
for the FOR SELECT...INTO <output variables> DO...SUSPEND syntax. You
select from such procedures, as you would from a table.

Another option is to define a view and use that where you would need a temp
table in SQL Server...

heLen




________________________________________________________________________
________________________________________________________________________

Message: 6
Date: Mon, 14 Apr 2003 13:58:06 +0200
From: "Johan Leroy" <johan.leroy@...>
Subject: Re: Problem with stored procedure FB1.0.2

Because thats the result I get.
I'll send the complete procedure but it is verry long.
All values that I set manualy to 0 have this problem sometimes.
The biggest problem I have is when select/insert/update the table
RESM_STOCK_ADAPTION_DETAILS

////////////////////////////////////////////////
CREATE PROCEDURE PROC_RESM_CRE_DROP (
ACTIONIDX INTEGER,
USEMAXQTY SMALLINT,
MAXQTY INTEGER,
CHECKWITHSTOCK SMALLINT,
DROPDATE DATE)
RETURNS (
DROPQUANTITY INTEGER)
AS
declare variable OrderIdx INTEGER;
declare variable OrderLine SMALLINT;
declare variable DropIdx INTEGER;
declare variable InvoiceIdx INTEGER;
declare variable ItemDescription VARCHAR(100);
declare variable Quantity INTEGER;
declare variable UnitPrice NUMERIC(6,2);
declare variable Discount SMALLINT;
declare variable Vat SMALLINT;
declare variable InvoiceNbr VARCHAR(8);
declare variable Dummy VARCHAR(150);
declare variable Language VARCHAR(3);
declare variable PaymentReference VARCHAR(15);
declare variable CurrentYear SMALLINT;
declare variable AdaptionIdx integer; /* The stock adaptionidx */
declare variable AdaptionLine SMALLINT; /* The current Adaption Line */
declare variable MaxAdaptionLine SMALLINT; /* The last Adaption Line for
this Drop */
declare variable ItemIdx integer; /* The itemidx used for the stock
adaptionidx */
declare variable CustomerIdx INTEGER; /* This variable is used as link in
the table RESM_STOCK_ADAPTIONS */
declare variable StockQty INTEGER; /* This variable is used for
1: To check if there are any items in stock
2: To check if the current item is still in
stock*/
declare variable StockAdaptionQty INTEGER; /* Used for the stock of the
current item */
declare variable AnoughInStock SMALLINT;

declare variable CommCustomerIdx INTEGER;
BEGIN
/*Check if there are any orders to process */
SELECT COUNT(*) FROM RESM_ORDERS WHERE OrderStatus = 0 AND ActionIdx =
:ActionIdx INTO :DropQuantity;
if ((DropQuantity = 0) OR (DropQuantity IS NULL)) then
BEGIN
SUSPEND;
EXIT;
END
/* Check if there are any items if in stock if this options is
selected, if not we don't even create a drop*/
SELECT SUM(Stock) FROM RESM_STOCK WHERE ItemIdx IN (SELECT ItemIdx FROM
RESM_ACTION_ITEMS WHERE ActionIdx = :ActionIdx) INTO :StockQty;
if (StockQty < 1) then
begin
DropQuantity = -1;
SUSPEND;
EXIT;
end
/* Create a Drop */
DropIdx = GEN_ID(GEN_RESM_DROPS_IDX,1);
INSERT INTO RESM_DROPS (DropIdx,ActionIdx,DropDate) VALUES
(:DropIdx,:ActionIdx,:DropDate);
DropQuantity = 0;
/* Get The customerIdx */
SELECT CustomerIdx FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO
:CustomerIdx;
/* Create a stock adaption line */
AdaptionIdx = GEN_ID(GEN_RESM_STOCK_ADAPTION_IDX,1);
INSERT INTO RESM_STOCK_ADAPTIONS
(AdaptionIdx,CustomerIdx,AdaptionTypeIdx,ActionIdx)
VALUES(:AdaptionIdx,:CustomerIdx,'1',:ActionIdx);
MaxAdaptionLine = 0;
SELECT CustomerIdx FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO
:CommCustomerIdx;
/* Process the orders */
FOR SELECT OrderIdx,CustomerIdx FROM RESM_ORDERS WHERE OrderStatus = 0
AND ActionIdx = :ActionIdx ORDER BY CreateDate,OrderIdx INTO
:OrderIdx,:CustomerIdx
DO
BEGIN
if (dropQuantity >= MaxQty and USEMAXQTY = 1) then
begin
/* Update the stock */
FOR SELECT ItemIdx,Quantity FROM RESM_STOCK_ADAPTION_DETAILS
WHERE AdaptionIdx = :AdaptionIdx INTO :ItemIdx,:Quantity
DO
BEGIN
UPDATE RESM_STOCK SET STOCK = STOCK - :Quantity WHERE
ItemIdx = :ItemIdx;
END
UPDATE RESM_DROPS SET Quantity = :DropQuantity WHERE DropIdx =
:DropIdx;
SUSPEND;
exit;
end
AnoughInStock = 1;
/* Check if there are anough items in stock */
Quantity = 0;
FOR SELECT RESM_ACTION_ITEMS.ItemIdx,RESM_ORDER_LINES.Quantity
FROM RESM_ORDER_LINES INNER JOIN RESM_ACTION_ITEMS ON
(RESM_ACTION_ITEMS.ActionItemIdx = RESM_ORDER_LINES.ItemIdx)
WHERE RESM_ORDER_LINES.OrderIdx = :OrderIdx
INTO :ItemIdx,:Quantity DO
BEGIN
StockQty = 0;
StockAdaptionQty = 0;
SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO
:StockQty;
SELECT Quantity FROM RESM_STOCK_ADAPTION_DETAILS WHERE
ItemIdx = :ItemIdx AND AdaptionIdx =:AdaptionIdx INTO :StockAdaptionQty;
if((StockQty - StockAdaptionQty) < Quantity) then
AnoughInStock = 0;
END
if (AnoughInStock = 1) then
begin
SELECT LANGUAGE FROM
PROC_RESM_GET_CUST_DATA_BY_IDX(:CustomerIdx) INTO :Language;
/* Add A line to drop_lines */
INSERT INTO
RESM_DROP_LINES(DropIdx,OrderIdx)VALUES(:DropIdx,:OrderIdx);
/* Add an invoice */
InvoiceIdx = GEN_ID(GEN_RESM_INVOICE_IDX,1);
SELECT IdNumber FROM
PROC_RESM_GEN_ID_NUMBER(:CommCustomerIdx,0) INTO :InvoiceNbr;
CurrentYear = EXTRACT(YEAR FROM CURRENT_DATE);
/* SELECT ResmGenOrderNbr(:CurrentYear,:ActionIdx,:OrderIdx)
FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO :OrderNbr;*/
SELECT RESMGENORDERNBR(:CurrentYear,ActionIdx,:OrderIdx) FROM
RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO :PaymentReference;
INSERT INTO
RESM_INVOICES(InvoiceIdx,OrderIdx,InvoiceDate,Status,InvoiceNbr,Language,Cus
tomerIdx,PaymentReference) VALUES
(:InvoiceIdx,:OrderIdx,:DropDate,0,:InvoiceNbr,:Language,:CustomerIdx,:Payme
ntReference);
/* Add the invoice Lines */
FOR SELECT
RESM_ORDER_LINES.ORDERLINE,RESM_ORDER_LINES.DISCOUNT,RESM_ORDER_LINES.QUANTI
TY,RESM_ORDER_LINES.BASEPRICE,RESM_ITEMS.ITEMDESCRIPTION,RESM_ITEMS.VAT,RESM
_ITEMS.ItemIdx
FROM RESM_ORDER_LINES
INNER JOIN RESM_ACTION_ITEMS ON (RESM_ORDER_LINES.ITEMIDX =
RESM_ACTION_ITEMS.ACTIONITEMIDX)
INNER JOIN RESM_ITEMS ON (RESM_ACTION_ITEMS.ITEMIDX =
RESM_ITEMS.ITEMIDX)
WHERE OrderIdx = :OrderIdx ORDER BY OrderLine
INTO
:OrderLine,:Discount,:Quantity,:UnitPrice,:ItemDescription,:Vat,:ItemIdx
DO
BEGIN

/* Add an invoice line */
INSERT INTO
RESM_INVOICE_LINES(InvoiceIdx,InvoiceLine,Description,Quantity,UnitPrice,Dis
count,Vat)
VALUES
(:InvoiceIdx,:OrderLine,:ItemDescription,:Quantity,:UnitPrice,:Discount,:Vat
);
/* Update the stock adaption Line */
AdaptionLine = 0;
SELECT AdaptionLine FROM RESM_STOCK_ADAPTION_DETAILS WHERE
ItemIdx = :ItemIdx AND AdaptionIdx = :AdaptionIdx INTO :AdaptionLine;
if (AdaptionLine IS NULL or (AdaptionLine = 0)) then
BEGIN
MaxAdaptionLine = MaxAdaptionLine + 1;
INSERT INTO RESM_STOCK_ADAPTION_DETAILS
(AdaptionIdx,AdaptionLine,ItemIdx,Quantity)

VALUES(:AdaptionIdx,:MaxAdaptionLine,:ItemIdx,:Quantity);
END
else
begin
UPDATE RESM_STOCK_ADAPTION_DETAILS SET Quantity =
Quantity + :Quantity WHERE AdaptionIdx = :AdaptionIdx AND AdaptionLine =
:AdaptionLine;
end
END
DropQuantity = DropQuantity + 1;
/* Add the postalcosts line */
OrderLine = OrderLine + 1;
SELECT PostCosts FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx
INTO :UnitPrice;

INSERT INTO
RESM_INVOICE_LINES(InvoiceIdx,InvoiceLine,Description,Quantity,UnitPrice,Dis
count,Vat)
VALUES (:InvoiceIdx,:OrderLine,'Portkosten / Frais de
port',1,:UnitPrice,0,0);
/* Set the status of the current order to dropped */
UPDATE RESM_ORDERS SET OrderStatus = 1 WHERE OrderIdx =
:OrderIdx;
END
END
/* Update the stock */
FOR SELECT ItemIdx,Quantity FROM RESM_STOCK_ADAPTION_DETAILS WHERE
AdaptionIdx = :AdaptionIdx INTO :ItemIdx,:Quantity
DO
BEGIN
UPDATE RESM_STOCK SET STOCK = STOCK - :Quantity WHERE ItemIdx =
:ItemIdx;

END
UPDATE RESM_DROPS SET Quantity = :DropQuantity WHERE DropIdx =
:DropIdx;
SUSPEND;
END

//////////////////////////////////////////



regards

johan


""Martijn Tonies"" <m.tonies@...> schreef in bericht
news:00e201c3027c$515f82c0$0a02a8c0@seal...

> Hi Johan,
>
> > The problem is that when the sql statement (eg.)
> > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO

:StockQty;

> > is executed in the procedure and there is no result for the current
> ItemIdx,
> > it containts the result of the previous select where ther was a record

for

> > Itemidx.
> > eg
> > Select ... ItemIdx = 5 -> result 10
> > Select ... itemidx = 1 -> (no record for it) return also 10
>
> I'm pretty sure it doesn't return "10" - what makes you think it does?
>
> --
> Martijn
>
>
> > I must say that I only have this problem when using stored procedures.

In

> > this case the procedure also manipulated a lot of tables.
> >
> > regards
> >
> > Johan
> >
> >
> > ""Martijn Tonies"" <m.tonies@...> schreef in bericht
> > news:009f01c30276$f4f0bcc0$0a02a8c0@seal...
> > > Hi Johan,
> > >
> > > > One of my stored procedures is manipulating a lot of tables and need
> in
> > a
> > > > loop the data that is writen to the db in this same loop.
> > > > The problem I have is that the variables aren't update always when I
> > need
> > > to
> > > > retrive a null from the database.
> > >
> > > Retrieve a NULL where?
> > >
> > > > The current sollution I have is to set the var to 0 and then it

works.

> > > Here
> > > > follows an example
> > > > StockQty = 0;
> > > > StockAdaptionQty = 0;
> > > > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx
> > INTO
> > > > :StockQty;
> > >
> > > You mean, if this SELECT doesn't return any rows? 'Cause then STOCKQTY
> > will
> > > stay empty (null).
> > >
> > > > SELECT Quantity FROM RESM_STOCK_ADAPTION_DETAILS

WHERE

> > > > ItemIdx = :ItemIdx AND AdaptionIdx =:AdaptionIdx INTO
> > > :StockAdaptionQty;
> > > >
> > > > so if i dont set StockQty and StockAdaptionQty to 0 then I don't get
> the
> > > > correct data.
> > > >
> > > > Mayby this is a bug in FB or is there any other explination for it.
> > > >
> > > >
> > > > Regards
> > > >
> > > >
> > > > Johan
> > > >
> > > > PS: if needed I can send the complete proc source
> > >
> > > Better explain what you mean :)
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>

________________________________________________________________________
________________________________________________________________________

Message: 7
Date: Mon, 14 Apr 2003 15:01:21 +0300
From: Alexander Tabakov <saho@...>
Subject: Re[2]: Problem with stored procedure FB1.0.2

>I'm pretty sure it doesn't return "10" - what makes you think it does?

Believe me It does!

--
Best regards,
Alexander mailto:saho@...



________________________________________________________________________
________________________________________________________________________

Message: 8
Date: Mon, 14 Apr 2003 22:00:22 +1000
From: Helen Borrie <helebor@...>
Subject: Re: Problem with stored procedure FB1.0.2

At 01:46 PM 14/04/2003 +0200, you wrote:

>The problem is that when the sql statement (eg.)
> SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO :StockQty;
>is executed in the procedure and there is no result for the current

ItemIdx,

>it containts the result of the previous select where ther was a record for
>Itemidx.
>eg
> Select ... ItemIdx = 5 -> result 10
> Select ... itemidx = 1 -> (no record for it) return also 10
>
>I must say that I only have this problem when using stored procedures. In
>this case the procedure also manipulated a lot of tables.

This was the case in IB 5.x and IB 6. In IB 5.x, the safety net was to
ensure that each iteration of the loop began by initialising all of the
variables...

If you are seeing this in Firebird (where it is supposed to have been fixed
so that the engine re-initialises the variables) then we certainly would
appreciate a test case.

heLen




________________________________________________________________________
________________________________________________________________________

Message: 9
Date: Mon, 14 Apr 2003 14:04:13 +0200
From: "Martijn Tonies" <m.tonies@...>
Subject: Re: Re[2]: Problem with stored procedure FB1.0.2

> >I'm pretty sure it doesn't return "10" - what makes you think it does?
>
> Believe me It does!

I've heard about this when you don't initialize your variables,
but it should be fixed in Fb, right?


With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions
http://www.upscene.com

See you at the First European Firebird Conference in May in Fulda, Germany
http://www.firebird-conference.com


________________________________________________________________________
________________________________________________________________________

Message: 10
Date: Mon, 14 Apr 2003 16:12:23 +0400
From: Dmitry Yemanov <dimitr@...>
Subject: RE: Problem with stored procedure FB1.0.2

Johan and Martijn,

> > The problem is that when the sql statement (eg.)
> > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx
> INTO :StockQty;
> > is executed in the procedure and there is no result for the current
> ItemIdx,
> > it containts the result of the previous select where ther
> was a record for
> > Itemidx.
> > eg
> > Select ... ItemIdx = 5 -> result 10
> > Select ... itemidx = 1 -> (no record for it) return also 10
>
> I'm pretty sure it doesn't return "10" - what makes you think it does?

Johan is absolutely correct. If there's no record to fetch, then nothing is
assigned to the variable/parameter (StockQty), so it still contains an old
value. And it's not a bug, but as designed. The only way to handle it
properly is initializing variable/parameter before SELECT and checking its
value after it, i.e. exactly how Johan has done it.


Dmitry


________________________________________________________________________
________________________________________________________________________

Message: 11
Date: Mon, 14 Apr 2003 14:14:00 +0200
From: "Johan Leroy" <johan.leroy@...>
Subject: Re: Problem with stored procedure FB1.0.2

Thanks all for the help and thx Dimitry for the info that it is normal.

"Dmitry Yemanov" <dimitr@...> schreef in bericht
news:2EA47E65365FD511BF2100A0C9ACE53840ECAE@......

> Johan and Martijn,
>
> > > The problem is that when the sql statement (eg.)
> > > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx
> > INTO :StockQty;
> > > is executed in the procedure and there is no result for the current
> > ItemIdx,
> > > it containts the result of the previous select where ther
> > was a record for
> > > Itemidx.
> > > eg
> > > Select ... ItemIdx = 5 -> result 10
> > > Select ... itemidx = 1 -> (no record for it) return also 10
> >
> > I'm pretty sure it doesn't return "10" - what makes you think it does?
>
> Johan is absolutely correct. If there's no record to fetch, then nothing

is

> assigned to the variable/parameter (StockQty), so it still contains an old
> value. And it's not a bug, but as designed. The only way to handle it
> properly is initializing variable/parameter before SELECT and checking its
> value after it, i.e. exactly how Johan has done it.
>
>
> Dmitry
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>

________________________________________________________________________
________________________________________________________________________

Message: 12
Date: Mon, 14 Apr 2003 14:18:48 +0200
From: "Johan Leroy" <johan.leroy@...>
Subject: Re: Re[2]: Problem with stored procedure FB1.0.2

Martijn look at the respone of dimitry, he explains there that it is normal
and the only way to not having this problem is to initialise the vars before
the select is executed.

regards

johan
""Martijn Tonies"" <m.tonies@...> schreef in bericht
news:011701c3027d$f6f16a90$0a02a8c0@seal...

>
>
> > >I'm pretty sure it doesn't return "10" - what makes you think it does?
> >
> > Believe me It does!
>
> I've heard about this when you don't initialize your variables,
> but it should be fixed in Fb, right?
>
>
> With regards,
>
> Martijn Tonies
> Database Workbench - the developer tool for InterBase & Firebird
> Upscene Productions
> http://www.upscene.com
>
> See you at the First European Firebird Conference in May in Fulda, Germany
> http://www.firebird-conference.com
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>

________________________________________________________________________
________________________________________________________________________

Message: 13
Date: Mon, 14 Apr 2003 14:21:01 +0200
From: "Johan Leroy" <johan.leroy@...>
Subject: Re: Problem with stored procedure FB1.0.2

it's not that easy to give you a test case or I have to give you the
database and even backuped it's also about 6MB.

"Helen Borrie" <helebor@...> schreef in bericht
news:5.1.0.14.2.20030414215247.0354f7c0@......

> At 01:46 PM 14/04/2003 +0200, you wrote:
> >The problem is that when the sql statement (eg.)
> > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO

:StockQty;

> >is executed in the procedure and there is no result for the current

ItemIdx,

> >it containts the result of the previous select where ther was a record

for

> >Itemidx.
> >eg
> > Select ... ItemIdx = 5 -> result 10
> > Select ... itemidx = 1 -> (no record for it) return also 10
> >
> >I must say that I only have this problem when using stored procedures. In
> >this case the procedure also manipulated a lot of tables.
>
> This was the case in IB 5.x and IB 6. In IB 5.x, the safety net was to
> ensure that each iteration of the loop began by initialising all of the
> variables...
>
> If you are seeing this in Firebird (where it is supposed to have been

fixed

> so that the engine re-initialises the variables) then we certainly would
> appreciate a test case.
>
> heLen
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>

________________________________________________________________________
________________________________________________________________________



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/