I have worked around it by using the OnCalculateField event to generate
the data. But what really concerns me is how many other problems will
I run into transferring from v4 to v5. This is about the 4th little problem
that takes me several hours to track down why it is causing an error.

I have been trying to switch over to v5 for 9 months now, as I have time.

By the way both of the fields SPO_ID and SPOD_SPO_ID are integers.

Thanks
Michael

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
Sent: Thursday, November 12, 2015 3:42 PM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Edit of a Query with a SubSelect causes Exception

>SQL.Strings:
> SELECT STOCKPO.*,
> (select sum(spod_ordqty * spod_cost) from stockpod
> where spod_spo_id = :ID) as POSUM
> FROM STOCKPO
> WHERE SPO_ID = :ID
>
>EditSQL.Strings;
> UPDATE STOCKPO STOCKPO SET
> STOCKPO.SPO_ID = :SPO_ID,
> STOCKPO.SPO_DATE = :SPO_DATE,
> STOCKPO.SPO_APVEN_ID = :SPO_APVEN_ID, > STOCKPO.SPO_SOURCE =

:SPO_SOURCE, > STOCKPO.SPO_NOTES = :SPO_NOTES, > STOCKPO.SPO_SHIPNAME =
:SPO_SHIPNAME, > STOCKPO.SPO_SHIPADDR1 = :SPO_SHIPADDR1, >
STOCKPO.SPO_SHIPADDR2 = :SPO_SHIPADDR2, > STOCKPO.SPO_SHIPCITY =
:SPO_SHIPCITY, > STOCKPO.SPO_SHIPSTATE = :SPO_SHIPSTATE, >
STOCKPO.SPO_SHIPZIP = :SPO_SHIPZIP, > STOCKPO.SPO_SHIPCOUNTRY =
:SPO_SHIPCOUNTRY, > STOCKPO.SPO_SHIPTOUS = :SPO_SHIPTOUS, >
STOCKPO.SPO_SHIPRESIDENTIAL = :SPO_SHIPRESIDENTIAL, >
STOCKPO.SPO_ORDEREDBY = :SPO_ORDEREDBY, > STOCKPO.SPO_TOTALCOST =
:SPO_TOTALCOST, > STOCKPO.SPO_INCOMING_RECVD = :SPO_INCOMING_RECVD, >
STOCKPO.SPO_RECVD_DATE = :SPO_RECVD_DATE, > STOCKPO.SPO_FC_ID =
:SPO_FC_ID, > STOCKPO.SPO_LOT = :SPO_LOT, > STOCKPO.SPO_APVEN_EMAIL =
:SPO_APVEN_EMAIL, > STOCKPO.SPO_APVEN_COMPANY = :SPO_APVEN_COMPANY, >
STOCKPO.SPO_APVEN_ADDR1 = :SPO_APVEN_ADDR1, > STOCKPO.SPO_APVEN_ADDR2 =
:SPO_APVEN_ADDR2, > STOCKPO.SPO_APVEN_CITY = :SPO_APVEN_CITY, >
STOCKPO.SPO_APVEN_STATE = :SPO_APVEN_STATE, > STOCKPO.SPO_APVEN_ZIP =
:SPO_APVEN_ZIP, > STOCKPO.SPO_APVEN_COUNTRY = :SPO_APVEN_COUNTRY, >
STOCKPO.SPO_APVEN_PHONE = :SPO_APVEN_PHONE, > STOCKPO.SPO_APVEN_FAX =
:SPO_APVEN_FAX, > STOCKPO.SPO_APVEN_TERMS = :SPO_APVEN_TERMS, >
STOCKPO.SPO_APVEN_CUSTOMERNUMBER = :SPO_APVEN_CUSTOMERNUMBER, >
STOCKPO.SPO_APVEN_CONTACT = :SPO_APVEN_CONTACT, > STOCKPO.SPO_APVEN_NOTES
= :SPO_APVEN_NOTES, > STOCKPO.SPO_FC_TERMS = :SPO_FC_TERMS, >
STOCKPO.SPO_TOTALCHARGES = :SPO_TOTALCHARGES, > STOCKPO.SPO_CODFEE =
:SPO_CODFEE, > STOCKPO.SPO_REMITCOD_NAME = :SPO_REMITCOD_NAME, >
STOCKPO.SPO_REMITCOD_ADDR1 = :SPO_REMITCOD_ADDR1, >
STOCKPO.SPO_REMITCOD_ADDR2 = :SPO_REMITCOD_ADDR2, >
STOCKPO.SPO_REMITCOD_CITY = :SPO_REMITCOD_CITY, >
STOCKPO.SPO_REMITCOD_STATE = :SPO_REMITCOD_STATE, >
STOCKPO.SPO_REMITCOD_ZIP = :SPO_REMITCOD_ZIP, >
STOCKPO.SPO_REMITCOD_COUNTRY = :SPO_REMITCOD_COUNTRY, >
STOCKPO.SPO_BOL_NOTES = :SPO_BOL_NOTES, > STOCKPO.SPO_ATTENTION =
:SPO_ATTENTION, > STOCKPO.SPO_INTERNALNOTES = :SPO_INTERNALNOTES, >
STOCKPO.SPO_CASHCERTCHECK = :SPO_CASHCERTCHECK, > STOCKPO.SPO_CUSCHECK =
:SPO_CUSCHECK, > STOCKPO.SPO_BROKER = :SPO_BROKER, > STOCKPO.SPO_STATUS
= :SPO_STATUS, > STOCKPO.SPO_PROCESSING_STATUS = :SPO_PROCESSING_STATUS >
WHERE SPO_ID = :OLD_SPO_ID > >when we do a post it gets the following
error

> Exception with message 'Invalid text in HexTextToBinary'. Process

Stopped

>
>If I comment out the subselect it doesn't have the error. It worked with

the subselect in IBObjects v4.

I agree that this sounds like an error, Michael, but why not try rewriting
the query to circumvent the error? I'm thinking of two
possibilities:

a) Link the subquery to the main query (having a non-related subquery is
rather unusual, making it more likely to not be part of Jasons test suite)
SELECT spo.*,
(select sum(spod_ordqty * spod_cost) from stockpod spod
where spod.spod_spo_id = spo.SPO_ID) as POSUM FROM STOCKPO spo
WHERE SPO_ID = :ID

b) Use a CTE
with TMP(POSUM) as
(select sum(spod_ordqty * spod_cost) from stockpod where spod_spo_id = :ID)
select STOCKPO.*, TMP.POSUM FROM STOCKPO CROSS JOIN TMP WHERE SPO_ID = :ID

Though, as I said, it sounds like an error, so it may be wise for Jason to
test and fix (if it really is an error) even if one of my suggestions work
for you. One possible error that wouldn't quite be IBOs fault, would be if
SPO_ID and SPOD_SPO_ID were defined differently in Firebird/InterBase (i.e.
different character sets or different types or length).

Set


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

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

___________________________________________________________________________
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 !
http://tracker.ibobjects.com - your portal to submit and monitor bug reports
http://community.ibobjects.com - your portal to purchase and upgrade
------------------------------------

Yahoo Groups Links