SQL working in Oracle but not H2 without explicit casts around bind variables

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL working in Oracle but not H2 without explicit casts around bind variables

Solwitz, Benjamin
I have a bunch of SQL in mapper XMLs which works fine in Oracle, but I get an "Unknown data type" error on the bind variables in H2. I was able to fix this error by adding explicit casts to the SQL around all of the bind variables, but I'm hoping there is an easier way?

Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "?"; SQL statement:
SELECT
            SEG.CURRENCY CURRENCY_CODE,
            COALESCE(SUM(I.INVOICE_AMOUNT), 0) AS INVOICE_AMOUNT,
            COALESCE(SUM(P.PAID_AMOUNT), 0) AS PAID_AMOUNT
        FROM CORE_OWNER.ACCOUNT A
        LEFT JOIN
        (
            SELECT
                G.PURCHASER_GROUP_ID AS GROUP_ID,
                G.PURCHASE_INVOICE_ID AS INVOICE_ID,
                G.CREATE_DATE
            FROM CORE_OWNER.GIFT_CERTIFICATE G
            UNION ALL
            SELECT
                A.GROUP_ID AS GROUP_ID,
                L.INVOICE_ID AS INVOICE_ID,
                L.CREATE_DATE
            FROM CORE_OWNER.ACCOUNT A
            JOIN CORE_OWNER.SUBSCRIPTION S on S.ACCOUNT_ID = A.id
            LEFT JOIN CORE_OWNER.LICENSE L ON L.SUBSCRIPTION_ID = S.ID
        ) AI ON AI.GROUP_ID = A.GROUP_ID
        LEFT JOIN CORE_OWNER.INVOICE INV ON INV.ID = AI.INVOICE_ID
        LEFT JOIN CORE_OWNER.SEGMENT SEG ON SEG.ID = INV.SEGMENT_ID
        LEFT JOIN
        (
            SELECT
            LI.INVOICE_ID AS INVOICE_ID,
            SUM(LI.AMOUNT) INVOICE_AMOUNT
            FROM CORE_OWNER.LINE_ITEM LI
            GROUP BY LI.INVOICE_ID
        ) I ON I.INVOICE_ID = AI.INVOICE_ID
        LEFT JOIN
        (
            SELECT
            AR.INVOICE_ID AS INVOICE_ID,
            SUM(AR.AMOUNT)      AS PAID_AMOUNT
            FROM CORE_OWNER.AR_BALANCE_APPLIES AR
            LEFT JOIN CORE_OWNER.CHARGE C ON C.ID = AR.CHARGE_ID
            WHERE C.CHARGE_STATUS_ID IN (4, 6, 9, 7, 8)
            GROUP BY AR.INVOICE_ID
        ) P ON P.INVOICE_ID = AI.INVOICE_ID
        WHERE A.ID            = ?
        AND AI.create_date > sysdate - ?
         
            AND AI.INVOICE_ID != ?
         
        GROUP BY SEG.CURRENCY [50004-196]

Here is the same SQL in the mapper xml:

 <select id="selectInvoicePayments" parameterType="map" resultMap="ArInvoiceAmountsResultMap">
        SELECT
            SEG.CURRENCY CURRENCY_CODE,
            COALESCE(SUM(I.INVOICE_AMOUNT), 0) AS INVOICE_AMOUNT,
            COALESCE(SUM(P.PAID_AMOUNT), 0) AS PAID_AMOUNT
        FROM CORE_OWNER.ACCOUNT A
        LEFT JOIN
        (
            SELECT
                G.PURCHASER_GROUP_ID AS GROUP_ID,
                G.PURCHASE_INVOICE_ID AS INVOICE_ID,
                G.CREATE_DATE
            FROM CORE_OWNER.GIFT_CERTIFICATE G
            UNION ALL
            SELECT
                A.GROUP_ID AS GROUP_ID,
                L.INVOICE_ID AS INVOICE_ID,
                L.CREATE_DATE
            FROM CORE_OWNER.ACCOUNT A
            JOIN CORE_OWNER.SUBSCRIPTION S on S.ACCOUNT_ID = A.id
            LEFT JOIN CORE_OWNER.LICENSE L ON L.SUBSCRIPTION_ID = S.ID
        ) AI ON AI.GROUP_ID = A.GROUP_ID
        LEFT JOIN CORE_OWNER.INVOICE INV ON INV.ID = AI.INVOICE_ID
        LEFT JOIN CORE_OWNER.SEGMENT SEG ON SEG.ID = INV.SEGMENT_ID
        LEFT JOIN
        (
            SELECT
            LI.INVOICE_ID AS INVOICE_ID,
            SUM(LI.AMOUNT) INVOICE_AMOUNT
            FROM CORE_OWNER.LINE_ITEM LI
            GROUP BY LI.INVOICE_ID
        ) I ON I.INVOICE_ID = AI.INVOICE_ID
        LEFT JOIN
        (
            SELECT
            AR.INVOICE_ID AS INVOICE_ID,
            SUM(AR.AMOUNT)      AS PAID_AMOUNT
            FROM CORE_OWNER.AR_BALANCE_APPLIES AR
            LEFT JOIN CORE_OWNER.CHARGE C ON C.ID = AR.CHARGE_ID
            WHERE C.CHARGE_STATUS_ID IN (4, 6, 9, 7, 8)
            GROUP BY AR.INVOICE_ID
        ) P ON P.INVOICE_ID = AI.INVOICE_ID
        WHERE A.ID            = #{accountId}
        AND AI.create_date &gt; sysdate - #{lookBackInDays}
        <if test="excludeInvoiceId != null">
            AND AI.INVOICE_ID != #{excludeInvoiceId}
        </if>
        GROUP BY SEG.CURRENCY
    </select>

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: SQL working in Oracle but not H2 without explicit casts around bind variables

Guy Rouillier-2
Check the MyBatis User Guide for jdbcType.  Instead of adding a cast to your SQL, add a jdbcType to your parameters, e.g., 

#{property,javaType=int,jdbcType=NUMERIC}

Most likely, this error is occurring in the presence of null values.

--
Guy Rouillier

------ Original Message ------
From: "Benjamin Solwitz" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 8/11/2017 9:45:33 AM
Subject: SQL working in Oracle but not H2 without explicit casts around bind variables

I have a bunch of SQL in mapper XMLs which works fine in Oracle, but I get an "Unknown data type" error on the bind variables in H2. I was able to fix this error by adding explicit casts to the SQL around all of the bind variables, but I'm hoping there is an easier way?

Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "?"; SQL statement:
SELECT
            SEG.CURRENCY CURRENCY_CODE,
            COALESCE(SUM(I.INVOICE_AMOUNT), 0) AS INVOICE_AMOUNT,
            COALESCE(SUM(P.PAID_AMOUNT), 0) AS PAID_AMOUNT
        FROM CORE_OWNER.ACCOUNT A
        LEFT JOIN
        (
            SELECT
                G.PURCHASER_GROUP_ID AS GROUP_ID,
                G.PURCHASE_INVOICE_ID AS INVOICE_ID,
                G.CREATE_DATE
            FROM CORE_OWNER.GIFT_CERTIFICATE G
            UNION ALL
            SELECT
                A.GROUP_ID AS GROUP_ID,
                L.INVOICE_ID AS INVOICE_ID,
                L.CREATE_DATE
            FROM CORE_OWNER.ACCOUNT A
            JOIN CORE_OWNER.SUBSCRIPTION S on S.ACCOUNT_ID = A.id
            LEFT JOIN CORE_OWNER.LICENSE L ON L.SUBSCRIPTION_ID = S.ID
        ) AI ON AI.GROUP_ID = A.GROUP_ID
        LEFT JOIN CORE_OWNER.INVOICE INV ON INV.ID = AI.INVOICE_ID
        LEFT JOIN CORE_OWNER.SEGMENT SEG ON SEG.ID = INV.SEGMENT_ID
        LEFT JOIN
        (
            SELECT
            LI.INVOICE_ID AS INVOICE_ID,
            SUM(LI.AMOUNT) INVOICE_AMOUNT
            FROM CORE_OWNER.LINE_ITEM LI
            GROUP BY LI.INVOICE_ID
        ) I ON I.INVOICE_ID = AI.INVOICE_ID
        LEFT JOIN
        (
            SELECT
            AR.INVOICE_ID AS INVOICE_ID,
            SUM(AR.AMOUNT)      AS PAID_AMOUNT
            FROM CORE_OWNER.AR_BALANCE_APPLIES AR
            LEFT JOIN CORE_OWNER.CHARGE C ON C.ID = AR.CHARGE_ID
            WHERE C.CHARGE_STATUS_ID IN (4, 6, 9, 7, 8)
            GROUP BY AR.INVOICE_ID
        ) P ON P.INVOICE_ID = AI.INVOICE_ID
        WHERE A.ID            = ?
        AND AI.create_date > sysdate - ?
         
            AND AI.INVOICE_ID != ?
         
        GROUP BY SEG.CURRENCY [50004-196]

Here is the same SQL in the mapper xml:

 <select id="selectInvoicePayments" parameterType="map" resultMap="ArInvoiceAmountsResultMap">
        SELECT
            SEG.CURRENCY CURRENCY_CODE,
            COALESCE(SUM(I.INVOICE_AMOUNT), 0) AS INVOICE_AMOUNT,
            COALESCE(SUM(P.PAID_AMOUNT), 0) AS PAID_AMOUNT
        FROM CORE_OWNER.ACCOUNT A
        LEFT JOIN
        (
            SELECT
                G.PURCHASER_GROUP_ID AS GROUP_ID,
                G.PURCHASE_INVOICE_ID AS INVOICE_ID,
                G.CREATE_DATE
            FROM CORE_OWNER.GIFT_CERTIFICATE G
            UNION ALL
            SELECT
                A.GROUP_ID AS GROUP_ID,
                L.INVOICE_ID AS INVOICE_ID,
                L.CREATE_DATE
            FROM CORE_OWNER.ACCOUNT A
            JOIN CORE_OWNER.SUBSCRIPTION S on S.ACCOUNT_ID = A.id
            LEFT JOIN CORE_OWNER.LICENSE L ON L.SUBSCRIPTION_ID = S.ID
        ) AI ON AI.GROUP_ID = A.GROUP_ID
        LEFT JOIN CORE_OWNER.INVOICE INV ON INV.ID = AI.INVOICE_ID
        LEFT JOIN CORE_OWNER.SEGMENT SEG ON SEG.ID = INV.SEGMENT_ID
        LEFT JOIN
        (
            SELECT
            LI.INVOICE_ID AS INVOICE_ID,
            SUM(LI.AMOUNT) INVOICE_AMOUNT
            FROM CORE_OWNER.LINE_ITEM LI
            GROUP BY LI.INVOICE_ID
        ) I ON I.INVOICE_ID = AI.INVOICE_ID
        LEFT JOIN
        (
            SELECT
            AR.INVOICE_ID AS INVOICE_ID,
            SUM(AR.AMOUNT)      AS PAID_AMOUNT
            FROM CORE_OWNER.AR_BALANCE_APPLIES AR
            LEFT JOIN CORE_OWNER.CHARGE C ON C.ID = AR.CHARGE_ID
            WHERE C.CHARGE_STATUS_ID IN (4, 6, 9, 7, 8)
            GROUP BY AR.INVOICE_ID
        ) P ON P.INVOICE_ID = AI.INVOICE_ID
        WHERE A.ID            = #{accountId}
        AND AI.create_date &gt; sysdate - #{lookBackInDays}
        <if test="excludeInvoiceId != null">
            AND AI.INVOICE_ID != #{excludeInvoiceId}
        </if>
        GROUP BY SEG.CURRENCY
    </select>

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

Virus-free. www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: SQL working in Oracle but not H2 without explicit casts around bind variables

Solwitz, Benjamin
I tried adding jdbcTypes but it didn't seem to help, good to know I was on the right track though. Will mess around with it some more, thanks! 

On Aug 11, 2017 7:49 PM, "Guy Rouillier" <[hidden email]> wrote:
Check the MyBatis User Guide for jdbcType.  Instead of adding a cast to your SQL, add a jdbcType to your parameters, e.g., 

#{property,javaType=int,jdbcType=NUMERIC}

Most likely, this error is occurring in the presence of null values.

--
Guy Rouillier

------ Original Message ------
From: "Benjamin Solwitz" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 8/11/2017 9:45:33 AM
Subject: SQL working in Oracle but not H2 without explicit casts around bind variables

I have a bunch of SQL in mapper XMLs which works fine in Oracle, but I get an "Unknown data type" error on the bind variables in H2. I was able to fix this error by adding explicit casts to the SQL around all of the bind variables, but I'm hoping there is an easier way?

Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "?"; SQL statement:
SELECT
            SEG.CURRENCY CURRENCY_CODE,
            COALESCE(SUM(I.INVOICE_AMOUNT), 0) AS INVOICE_AMOUNT,
            COALESCE(SUM(P.PAID_AMOUNT), 0) AS PAID_AMOUNT
        FROM CORE_OWNER.ACCOUNT A
        LEFT JOIN
        (
            SELECT
                G.PURCHASER_GROUP_ID AS GROUP_ID,
                G.PURCHASE_INVOICE_ID AS INVOICE_ID,
                G.CREATE_DATE
            FROM CORE_OWNER.GIFT_CERTIFICATE G
            UNION ALL
            SELECT
                A.GROUP_ID AS GROUP_ID,
                L.INVOICE_ID AS INVOICE_ID,
                L.CREATE_DATE
            FROM CORE_OWNER.ACCOUNT A
            JOIN CORE_OWNER.SUBSCRIPTION S on S.ACCOUNT_ID = A.id
            LEFT JOIN CORE_OWNER.LICENSE L ON L.SUBSCRIPTION_ID = S.ID
        ) AI ON AI.GROUP_ID = A.GROUP_ID
        LEFT JOIN CORE_OWNER.INVOICE INV ON INV.ID = AI.INVOICE_ID
        LEFT JOIN CORE_OWNER.SEGMENT SEG ON SEG.ID = INV.SEGMENT_ID
        LEFT JOIN
        (
            SELECT
            LI.INVOICE_ID AS INVOICE_ID,
            SUM(LI.AMOUNT) INVOICE_AMOUNT
            FROM CORE_OWNER.LINE_ITEM LI
            GROUP BY LI.INVOICE_ID
        ) I ON I.INVOICE_ID = AI.INVOICE_ID
        LEFT JOIN
        (
            SELECT
            AR.INVOICE_ID AS INVOICE_ID,
            SUM(AR.AMOUNT)      AS PAID_AMOUNT
            FROM CORE_OWNER.AR_BALANCE_APPLIES AR
            LEFT JOIN CORE_OWNER.CHARGE C ON C.ID = AR.CHARGE_ID
            WHERE C.CHARGE_STATUS_ID IN (4, 6, 9, 7, 8)
            GROUP BY AR.INVOICE_ID
        ) P ON P.INVOICE_ID = AI.INVOICE_ID
        WHERE A.ID            = ?
        AND AI.create_date > sysdate - ?
         
            AND AI.INVOICE_ID != ?
         
        GROUP BY SEG.CURRENCY [50004-196]

Here is the same SQL in the mapper xml:

 <select id="selectInvoicePayments" parameterType="map" resultMap="ArInvoiceAmountsResultMap">
        SELECT
            SEG.CURRENCY CURRENCY_CODE,
            COALESCE(SUM(I.INVOICE_AMOUNT), 0) AS INVOICE_AMOUNT,
            COALESCE(SUM(P.PAID_AMOUNT), 0) AS PAID_AMOUNT
        FROM CORE_OWNER.ACCOUNT A
        LEFT JOIN
        (
            SELECT
                G.PURCHASER_GROUP_ID AS GROUP_ID,
                G.PURCHASE_INVOICE_ID AS INVOICE_ID,
                G.CREATE_DATE
            FROM CORE_OWNER.GIFT_CERTIFICATE G
            UNION ALL
            SELECT
                A.GROUP_ID AS GROUP_ID,
                L.INVOICE_ID AS INVOICE_ID,
                L.CREATE_DATE
            FROM CORE_OWNER.ACCOUNT A
            JOIN CORE_OWNER.SUBSCRIPTION S on S.ACCOUNT_ID = A.id
            LEFT JOIN CORE_OWNER.LICENSE L ON L.SUBSCRIPTION_ID = S.ID
        ) AI ON AI.GROUP_ID = A.GROUP_ID
        LEFT JOIN CORE_OWNER.INVOICE INV ON INV.ID = AI.INVOICE_ID
        LEFT JOIN CORE_OWNER.SEGMENT SEG ON SEG.ID = INV.SEGMENT_ID
        LEFT JOIN
        (
            SELECT
            LI.INVOICE_ID AS INVOICE_ID,
            SUM(LI.AMOUNT) INVOICE_AMOUNT
            FROM CORE_OWNER.LINE_ITEM LI
            GROUP BY LI.INVOICE_ID
        ) I ON I.INVOICE_ID = AI.INVOICE_ID
        LEFT JOIN
        (
            SELECT
            AR.INVOICE_ID AS INVOICE_ID,
            SUM(AR.AMOUNT)      AS PAID_AMOUNT
            FROM CORE_OWNER.AR_BALANCE_APPLIES AR
            LEFT JOIN CORE_OWNER.CHARGE C ON C.ID = AR.CHARGE_ID
            WHERE C.CHARGE_STATUS_ID IN (4, 6, 9, 7, 8)
            GROUP BY AR.INVOICE_ID
        ) P ON P.INVOICE_ID = AI.INVOICE_ID
        WHERE A.ID            = #{accountId}
        AND AI.create_date &gt; sysdate - #{lookBackInDays}
        <if test="excludeInvoiceId != null">
            AND AI.INVOICE_ID != #{excludeInvoiceId}
        </if>
        GROUP BY SEG.CURRENCY
    </select>

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

Virus-free. www.avast.com

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/ba1fX8cbRWU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.