Thanks for the info Helen.

There are on-hand numbers in the query - no problem there.



I've been bitten by the ambiguous field names in queries even in FB 1.03 and
I think I've got them all cleaned up. I don't see any problem in the query
in question other than maybe omitting the S. for 4 columns at the end.



I also don't have user names in the database itself. Something I've looked
at briefly, but it seems user maintenance is a pain from an application and
I prefer my users no getting into IBO Console or some similar tool to add
users. Maybe IBO 4.5 has something to help out here? I would love to have
the user ids/permissions a part of the database.



I guess it comes down to, is the following query valid (now and in the
future):

(is having a parameter part of the select columns ok?)



SELECT :USERID, T.COMPANY, T.DIVISION, T.ORDERNO, <etc.>

FROM MY TABLE T

WHERE

T.COMPANY = :COMPANY AND T.DIVISION = :DIVISION





Thanks again,

Kevin





_____

From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of Helen Borrie
Sent: Tuesday, April 19, 2005 8:00 AM
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Query no longer works after upgrading



At 07:19 AM 19/04/2005 -0700, you wrote:


It doesn't make sense to me, unless the params are getting scrambled. You
previously had a very old IBO that well pre-dated the changes Jason did
last year in 4.3Aa, to have IBO detect whether it was connecting to a
Firebird 1.5 server. This is because 1.5 fixed a very old InterBase/Fb 1.0
bug that scrambled params under some conditions. Now, IBO de-scrambles the
params only in those old versions. Maybe you have found a hole in that
fix...

However, I must say I can't see anything in your insert spec that looks
like on-hand numbers....

I wouldn't have thought you could make a parameter out of that select
<constant_value> syntax. Did you use it like that before? It's kind of
doubtful (to me) that the statement could stay prepared if you changed the
actual select statement (as you are attempting with this
syntax). Remember, parameters are not variables; and for DML statements
they should be WHERE criteria.

But *if* if so happens that the UserID is actually the database user name,
then you'd have a stable statement if you used the context variable
CURRENT_USER (or the predefined literal USER, if you are in dialect 1).

>I have not upgraded databases, currently using FB 1.03 but am gearing up to
>move to 1.5.

Then you'll need to fix up your joins and correlated
subqueries. Currently, in Fb 1.0, your subquery is ambiguous and the
engine throws a warning code (which IBO ignores). Fb 1.5 doesn't tolerate
ambiguous queries at all (it will thow exceptions on your query), and also
disallows mixing qualified and unqualified column references, even if the
unqualified column appears in only one of the tables.

I set out to give you the legal syntax for Fb 1.5 but realised I can't tell
which table some of those unqualified columns belong to, either. Anyway,
the clean layout should help to show you which columns you need to attend
to.

INSERT INTO PI_INQ (
USERID,
COMPANY,
DIVISION, INVLOC,
PRODID,
WRAPCODE,
SALESID,
YARDLOC,
INBORDERNO,
INBSUFFIX,
MILLNO,
SPIECES,
SFOOTAGE,
TOTCOST,
WOODCOST,
FRTCOST,
PROCESSCOST, INTEREST
)
SELECT
CURRENT_USER,
s.COMPANY,
s.DIVISION,
s.INVLOC,
S.PRODID,
S.WRAPCODE,
S.SALESID,
S.YARDLOC,
S.INBORDERNO,
S.INBSUFFIX,
0,
S.PIECES,
S.FOOTAGE,
( S.WOODCOST+S.FRTCOST+S.PROCESSCOST+S.INTEREST ),
/**/ WOODCOST,
/**/ FRTCOST,
/**/ PROCESSCOST,
/**/ INTEREST
FROM PI_SUM S
LEFT OUTER JOIN PROD_XREF X
ON S.PRODID = X.PRODID
WHERE
S.COMPANY = :COMPANY
AND S.DIVISION = :DIVISION
AND S.INVLOC = :INVLOC

Fb 2 gets even tighter. It won't tolerate mixing table qualifiers and
alias qualifiers in the same statement, either.

Helen




___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !




_____

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/IBObjects/

* To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com
<mailto:IBObjects-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



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