Error on Query with Subselects: Invalid Pointer

[rh-phoenix]

Using newest MySQL 5.0.24a Server and dbexpress driver Version 2.60.8.

First I noticed the Fields in Subselects of the following Query under Server 5.0 are TFMTBCDFields, with MySQL Server 4.x these are normal Float Fields...

On executing the following Query, an Error with "Invalid Pointer" accurs, and the Application terminates with Runtime error.
If I take out the Sub-Selects, everything works fine.

Strange thing is, the error isn't popping up each time.

This is the query how the server executes it.
All direct values shown are normally params I set via ClientDataSet.

Code: Select all

SELECT  A.ARTNR, A.PROGRAMM, A.BEZEICH, A.VK_AB_BRUT, A.AUSF, A.VK_NETTO A_VK_NETTO, A.VK_SON_NET A_VK_SON_NET,
        A.SAMMELART, A.BILDNR, A.LI_NR, A.LI_ARTNR, A.LI_PROGR, A.LIEF_STAT, A.ARTIKELEAN, L.KURZ, AL.S_KENNUNG,
        AL.VK_ABHOL, AL.VK_LIEFER, AL.VK_NETTO AL_VK_NETT0, AL.VK_SON_NET AL_VK_SON_NET, AK.FARBE,
        (SELECT SUM(Z_MENGE - STORN_ME - ME_RESERV) FROM BESTPOS
          WHERE POSART BETWEEN 1 AND 2
          AND KDFALL = 0
          AND STATUS BETWEEN 6 AND 30
          AND BESTELLKZ = 2 AND ARTIKELNR = A.ARTNR) AS BESTFREI,
        (SELECT SUM(LA_FREI) FROM LAGART WHERE ARTNR = A.ARTNR AND (LAGER = 'RLAG' OR LAGER = 'RAUS') ) AS LAGERFREI
FROM ARTIKEL A
LEFT JOIN LIEF L ON A.LI_NR = L.NR_INT
LEFT JOIN AKTLAUF AL ON AL.ARTNR = A.ARTNR
                      AND AL.FILIALE = 1
                      AND AL.DATUM >= (SELECT MAX(DATUM) FROM AKTLAUF WHERE FILIALE = 1 AND ARTNR = A.ARTNR)
LEFT JOIN AKTION AK ON AK.S_KENNUNG = AL.S_KENNUNG
LIMIT 0, 24

If you have problem to reproduce, I could give additional source of structure and data.

[Antaeus]

Please supply us with script to create and fill tables. You can send it to evgeniyD*crlab*com.

[rh-phoenix]

I've done that, thanks in advance for looking into.

[Antaeus]

Thank you for sample. We have checked this issue. This is a bug of dbExpress and NormalizeBcd function. NormalizeBcd accidentally clears buffer size of the field in TCustomSQLDataSet.GetFieldData method. The result is the same with standard dbExpress driver for MySQL. As a solution to this problem we can suggest you to cast result of SELECT subquery explicitly:

Code: Select all

SELECT  A.ARTNR, A.PROGRAMM, A.BEZEICH, A.VK_AB_BRUT, A.AUSF, A.VK_NETTO A_VK_NETTO, A.VK_SON_NET 
A_VK_SON_NET, 
        A.SAMMELART, A.BILDNR, A.LI_NR, A.LI_ARTNR, A.LI_PROGR, A.LIEF_STAT, A.ARTIKELEAN, L.KURZ, AL.S_KENNUNG, 
        AL.VK_ABHOL, AL.VK_LIEFER, AL.VK_NETTO AL_VK_NETT0, AL.VK_SON_NET AL_VK_SON_NET, AK.FARBE, 
        CAST((SELECT SUM(Z_MENGE - STORN_ME - ME_RESERV) FROM BESTPOS 
          WHERE POSART BETWEEN 1 AND 2 
          AND KDFALL = 0 
          AND STATUS BETWEEN 6 AND 30 
          AND BESTELLKZ = 2 AND ARTIKELNR = A.ARTNR) AS DECIMAL(12,2)) AS BESTFREI, 
        (SELECT SUM(LA_FREI) FROM LAGART WHERE ARTNR = A.ARTNR AND (LAGER = 'RLAG' OR LAGER = 'RAUS') ) AS 
LAGERFREI 
FROM ARTIKEL A 
LEFT JOIN LIEF L ON A.LI_NR = L.NR_INT 
LEFT JOIN AKTLAUF AL ON AL.ARTNR = A.ARTNR 
                      AND AL.FILIALE = 1 
                      AND AL.DATUM >= (SELECT MAX(DATUM) FROM AKTLAUF WHERE FILIALE = 1 AND ARTNR = A.ARTNR) 
LEFT JOIN AKTION AK ON AK.S_KENNUNG = AL.S_KENNUNG 
LIMIT 0, 24

[rh-phoenix]

thank you for the fast reply.
There ist only one Problem: This CAST doesn't work on MYSQL server 4.x as it was introduced in Version 5.0.8.
Is there another way to solve this issue, perhaps modify the NormalizeBcd function?

I'm trying to cast as string instead.

[Antaeus]

> Is there another way to solve this issue, perhaps modify the NormalizeBcd function?
This is standard function of Delphi. It is implemented in FMTBcd.pas unit. We are unable to fix anything in Delphi's units.

> I'm trying to cast as string instead.
Casting to CHAR[n] or BINARY is also good solution.

[rh-phoenix]

CAST is a way to solve this, but we would have to change a lot of our code in many applications.

TCustomSQLDataSet.GetFieldData had another problem, as we had exceptions when scrolling through data with sum() fields.

We changed
TempBuffer := AllocMem(SizeOf(TBCD));
to
TempBuffer := AllocMem(SizeOf(TBCD)+4);

And, of course accordingly
if TempBuffer nil then FreeMem(TempBuffer, SizeOf(TBCD)+4);

That seemed to fix the errors, because some Versions of MySQL seem to give back a length of 36.

Also we found a newer Version of the NormalizeBcd Function from Borland wich we now use in our projects.

But the Datatype in MySQL 4 is still fldFloat, why is it fldFMTBCD in MySQL 5???

But then the data of that fields under MySQL 4.x is just 0, if any data present.

[Antaeus]

> But the Datatype in MySQL 4 is still fldFloat, why is it fldFMTBCD in MySQL 5???
This is server issue. DbxMda uses the information of the field types sent by server.

> But then the data of that fields under MySQL 4.x is just 0, if any data present.
Do you get correct result if you retrieve these values from the server casting to strings or using another tool?

[rh-phoenix]

When I cast the Query, the values are ok.
Also using Querybrowser everything works.

Also when Server returns NIL, it is processed normaly and stays correct NIL, only the values returned are 0.

I tried to change the DataType by hand from Float to FmtBCD, so it would work with both server versions, but I still get no values returned.

[Antaeus]

Please simplify your SELECT statement as it possible and reduce size of test data to maximum possible extent. Create a complete small sample to demonstrate this problem. Send this project to evgeniyD*crlab*com.
Also supply us following information:
- Exact version of Delphi, C++ Builder or Kylix
- Exact version of MySQL server.