select long varchar returns null

[SvKN0]

Hi I have a problem with getting a long varchar field. Select from table with 32k varchar field returns null. Experimentally i have found that substr(varCharStr, 0, 8191) and shorter works well. Is it some limitation or a problem? Node 10.9.0, idb-connector 1.1.10

[jasonclake]

I can duplicate SvKN0's null results issue with the following:

const { dbconn, dbstmt } = require("idb-connector");

function runsqlp(sql, conn) {
    return new Promise((resolve) => {
        let statement = new dbstmt(conn);
        statement.exec(sql, (result, error) => {
            if (error) {
                statement.close();
                console.log(JSON.stringify(error));
                return resolve(error);

            }
            statement.close();
            resolve(result);
        });
    });
}

async function test() {
    console.log("```debug output");
    const connection = new dbconn();
    connection.debug(true);
    connection.conn("*LOCAL");
    console.log("---------------------------------------------");
    result = await runsqlp(
        `SELECT  
        --This works
        cast('B' || repeat('X',8189) || 'E' as char(8191)) as string8191
        --Fails as OP says
        ,cast('B' || repeat('X',8190) || 'E' as char(8192)) as string8192
    from sysibm.sysdummy1`,
        connection
    );
    console.log("```debug output \n");
    console.log(`\`\`\` result output \n ${JSON.stringify(result,null," ")} \n\`\`\``);
}

test();

Debug

SQLConnect(0): conn obj [180345530] handler [2]
---------------------------------------------
SQLExecDirect(0): SELECT
        --This works
        cast('B' || repeat('X',8189) || 'E' as char(8191)) as string8191
        --Fails as OP says
        ,cast('B' || repeat('X',8190) || 'E' as char(8192)) as string8192
    from sysibm.sysdummy1
SQLNUMRESULTSCOLS(0) Column Count = 2
SQLDescribeCol(0)       index[0]        sqlType[1]      colScale[0]     colPrecise[8191]
SQLDescribeCol(0)       index[1]        sqlType[1]      colScale[0]     colPrecise[8192]
bindColData is < 0
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
{}

Result

 {}

[dmabupt]

The actual size of the char buffer is 4 times of the chars length plus 1 (to avoid overflowing for wide charset) -> https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbstmt.cc#L1957

So 8192 chars needs 32769 bytes (32KB + 1 byte). This seems to be the limit of varchar.

• If a DECIMAL or NUMERIC data type is specified, precision must be from 1 through 63 and the scale must be between 0 and the precision.
• If DECFLOAT is specified, the precision must be omitted or must be 16 or 34.
• If CHARACTER or BINARY is specified, the length must be from 1 through 32766 for a parameter or for a column that does not allow null values or from 1 through 32765 for a column that allows null values.
• If VARCHAR or VARBINARY is specified, the length must be from 1 through 32740 for a parameter or for a column that does not allow null values or from 1 through 32739 for a column that allows null values.
• If the FOR MIXED DATA clause or a mixed CCSID is specified, the length cannot be less than 4.
• If GRAPHIC or NCHAR is specified, the length must be from 1 through 16383 for a parameter or for a column that does not allow null values and from 1 through 16382 for a column that allows null values.  The length specified is the number of DBCS characters.
• If VARGRAPHIC or NVARCHAR is specified, the length must be from 1 through 16370 for a parameter or for a column that does not allow null values or from 1 through 16369 for a column that allows null values. The length specified is the number of DBCS characters.
• If BLOB or CLOB is specified, the length must be from 1 through 2G (2048 M or 2,097,152 K or 2,147,483,647 bytes). If DBCLOB or NCLOB is specified, the length must be from 1 through 1073741823.
• If TIMESTAMP is specified, the precision must be from 0 to 12.
• If DATALINK is specified, the length must be from 1 through 32717.
• If VARCHAR, VARGRAPHIC, NVARCHAR, VARBINARY, CLOB, BLOB, DBCLOB, NCLOB, or DATALINK is specified, the ALLOCATE attribute must be less than or equal to both the length attribute specified and 32766.

Commit 7f04b3ea2130781b1921d0b39852185c7ad8f48a add support to bind to CLOB for long text. And the SQL should work with following changes --

    `SELECT  
        cast('B' || repeat('X',8189) || 'E' as char(8191)) as string8191
        -- change char to clob
        ,cast('B' || repeat('X',8190) || 'E' as clob(8192)) as string8192
    from sysibm.sysdummy1`,

[kadler]

Is this the case of CLI saying that the buffer is too big? I ran in to that in Python as well: https://github.com/kadler/python-ibmdb/commit/4f40750be1bc68b3fd6b258a5a21b78d98a254d9

I also fixed it in CLI to allow > 32k buffers to be used. The PTFs are: 7.4: SI69778 7.3: SI69779 7.2: SI69780

The PTFs will be in the next DB PTF group.

[dmabupt]

@kadler Yes, If we bind the buffer to type SQL_C_CHAR, the maximum size is 32k. But if I bind it to SQL_C_CLOB, there is no problem.

I also tried the provided SQL query in ACS, it showed the detailed message I pasted above.

These PTFs are very helpful for people who has long (>8k) varchar data stored in there Db2 tables. (not that easy to change it to clob)

May I ask if there is a new limit when these PTF applied?

[SvKN0]

Updated idb-connector to 1.2.0 version. Unfortunately magic with clob doesn't work for me. Only cast(VarChar as clob(8191)) and shorter works well but 8192+ returns null. I converted a varchar field to clob(32k) in my file and tried to just select it without convertion. Get null as well.

[dmabupt]

Hello @SvKN0 , idb-connector v1.2.0 does not contains this changes. It will be landed in idb-connector v1.2.1.

[SvKN0]

Now it works. Thank you very much for fix! But... =) SQL through idb-con returns some trash at the ends of the clob lines. Running it through some sql editor doesn't reproduce the problem.

[dmabupt]

Hello @SvKN0 , I have run the example code provided by jasonclake above. But I can not find any trash at the ends. Would you tell me how to recreate it?

-bash-4.4$ node longchar.js 
debug output
SQLConnect(0): conn obj [18034d4f0] handler [2]
---------------------------------------------
SQLExecDirect(0): SELECT  
        --This works
        cast('B' || repeat('X',8189) || 'E' as char(8191)) as string8191
        --Fails as OP says
        ,cast('B' || repeat('X',8190) || 'E' as clob(8192)) as string8192
    from sysibm.sysdummy1
SQLNUMRESULTSCOLS(0) Column Count = 2
SQLDescribeCol(0)       index[0]        sqlType[1]      colScale[0]     colPrecise[8191]
SQLDescribeCol(0)       index[1]        sqlType[14]     colScale[0]     colPrecise[8192]
null
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)

result output 
 [
 {
  "STRING8191": "BX…XE",
  "STRING8192": "BX…XE"
 }
] 
-bash-4.4$

[SvKN0]

Hello, @dmabupt. To reproduce it just change the SQL statment in this example code to SELECT cast('BxxxE' as clob(32000)) as c from sysibm.sysdummy1 Run it several times and get next results: 1st "BxxxE4о " 2nd "BxxxE4б б " 3rd "BxxxE3y " ...

[dmabupt]

@SvKN0 I can recreate the issue now. The accurate length of CLOB data should be retrieved in advance. Commit 6c9dfac resolved this. But a new CLOB issue #85 is reported. So we may not release a new version until we fix that.

[dmabupt]

Hello @SvKN0 , The new version v1.2.2 is released now. You may upgrade it and verify again.

[SvKN0]

Hi, @dmabupt. Unfortunately bug is still there. Now it works with latin and with something like '£'. I can reproduce the bug with the test string contains just one unicode symbol 'ё' between 'B' and 'E'. Now result is trimmed. Debug looks as follow: SQLExecDirect(0): SELECT cast('Bп E' as clob(32400)) as clb from sysibm.sysdummy1 SQLNUMRESULTSCOLS(0) Column Count = 1 SQLDescribeCol(0) index[0] sqlType[14] colScale[0] colPrecise[32400] SQLFreeStmt: stmth 3 [SQL_DROP] SQLFreeStmt(0) [{ "CLB": "Bп " }]

[kadler]

@dmabupt

May I ask if there is a new limit when these PTF applied?

There is no limit except for what can be stored in a signed integer (2GiB).

[kadler]

Commit 6c9dfac resolved this.

Using LOB locators is fraught with problems. SQLGetSubString is absolutely not designed to work with multi-byte encodings and will truncate randomly and not give you correct offsets.

[krisbaehr]

What is the latest on this? I'm running into an issue with the listagg() function. In this specific script, I only need 5,000 so I can work around it.

listagg(cast(coalesce(TDCMMT, '') as varchar(32717)), '') within group ( order by TDCMLN asc) as COMMENT_TEXT

[jsoques]

I might have a related problem. Doing a fetchAll from a table that contains a field defined as VARCHAR of length 16000, I get null value. If I do a CAST(myfield AS CHAR(4096)) it will bring data. Works for now as a temporary solution. I would like to know if there is a better solution. Thanks.

[dmabupt]

Hello @SvKN0 , @jsoques and @krisbaehr . Due to the system limit, idb-connector can not get an accurate length of CLOB data by now. Would you try to apply the PTFs from @kadler ? With the PTF I can query CHAR data > 32k.

Is this the case of CLI saying that the buffer is too big? I ran in to that in Python as well: kadler/python-ibmdb@4f40750

I also fixed it in CLI to allow > 32k buffers to be used. The PTFs are: 7.4: SI69778 7.3: SI69779 7.2: SI69780

The PTFs will be in the next DB PTF group.

[jsoques]

The cast is working for me now. Thanks.

[github-actions[bot]]

:wave: Hi! This issue has been marked stale due to inactivity. If no further activity occurs, it will automatically be closed.