Getting error in Event log while running executable with Datadirect Driver - Forum - DataDirect Connect - Progress Community

Getting error in Event log while running executable with Datadirect Driver

 Forum

Getting error in Event log while running executable with Datadirect Driver

This question is not answered

Actually our executable (.exe) is internally calling a stored procedure by connecting through Data-direct driver [Oracle Wire (DataDirect) ODBC driver].

While executing this EXE it failed and we are getting below error in Windows event log:

ODBC error message [XYZSoftware][ODBC Oracle Wire Protocol driver]Character, decimal and binary parameters cannot have a precision of zero. Error in parameter 1.SQL: {Call SampleStoredProcedure(?,?)}

Can someone please help here. We need to fix this ASAP.

All Replies
  • Some applications erroneously pass a precision of zero when binding parameters. Can you get a ODBC trace so we can determine if this is the case? If so there is a special connection option to work-around this problem.

    WorkArounds=1048576

  • SQL.LOG

    I took the ODBC trace while reproducing the error. Attaching here with.

    Also please let me know where exactly to add WorkArounds=1048576 in registry.

    I think I tried WorkArounds option and it didn't work.

  • knowledgebase.progress.com/.../4837

    It appears you are using the 32-bit Oracle driver. If so, the a String Value with the name WorkArounds and the value 1048576 would go in the Registry at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<your data source name> on 32-bit Windows and HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\<your data source name> on 64-bit Windows.

    Yes, a parameter is being bound with a precision of zero.

    id=23           88c-848 ENTER SQLBindParameter

    HSTMT               0x004CEF18

    UWORD                        1

    SWORD                        1 <SQL_PARAM_INPUT>

    SWORD                        1 <SQL_C_CHAR>

    SWORD                       12 <SQL_VARCHAR>

    SQLULEN                    0

    SWORD                        0

    PTR                0x7496B4FD

    SQLLEN                     0

    SQLLEN *            0x00000000

    ...

    id=23           88c-848 EXIT  SQLExecDirect  with return code -1 (SQL_ERROR)

    HSTMT               0x004CEF18

    UCHAR *             0x00900128 [      -3] "{call ABC_CREATE_SUMMARY(?,?)}\ 0"

    SDWORD                    -3

    DIAG [S1104] [Aspect Software][ODBC Oracle Wire Protocol driver]Character, decimal, and binary parameters cannot have a precision of zero.  Error in parameter 1. (0)

  • Here is a KB article on how to set the WorkArounds options:  http://ow.ly/Ar5eC

    There is also one that lists the various options that are available:  http://ow.ly/Ar5IT

  • Hi Hobson,

    Is this solution with Workarounds a final fix or a intermittent solution.

    Since we are having Data direct driver embedded in our standard product installer, how can we handle this.

    Also, please let me know what are the impacts of this registry change as far as performance/support is concerned.

    Thanks,

    Suniket

  • Suniket,

    This is the final fix, as a size for a character parameter of zero isn't valid.

    Your installer could add the WorkArounds entry to any data sources created or WorkArounds could be supplied by application when connecting.

    WorkArounds=1048576 has no affect on performance.

    Thanks,

    John

  • SQL1.LOG

    Thanks Jhon.

    I have added WorkArounds=1048576  to below location in registry.

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\DataSourceName]

    However I am getting below error under event logs:

    ODBC exception in thread , location .
    SQL state S1000.
    Native database error 6550.
    ODBC error SQL_ERROR.
    ODBC error message [Aspect Software][ODBC Oracle Wire Protocol driver][Oracle]ORA-06550: line 1, column 8:
    PLS-00306: wrong number or types of arguments in call to 'ABC_CREATE_SUMMARY'
    ORA-06550: line 1, column 8:
    PL/SQL: Statement ignoredSQL: {call ABC_CREATE_SUMMARY(?,?)}

    I am attaching ODBC trace log as well.

    Regards,

    Suniket

  • Suniket,

    Unfortunately the SQL1.LOG appears to be incomplete. You may need to enable flushing of the ODBC trace file.

    ...

    id=6            1b40-1900 EXIT  SQLExecute  with return code 0 (SQL_SUCCESS)

    HSTMT               0x010F2328

    However, I have a guess as to what might be going wrong. What parameters does ABC_CREATE_SUMMARY take? Does ABC_CREATE_SUMMARY have any REF CURSOR parameters? If so, you'll also need to add ProcedureRetResults=1 to your DSN. media.datadirect.com/.../help.html provides more information on this option.

    Thanks,

    John

  • SQL2.LOG

    Hi John,

    Please find updated trace.

    Actually ABC_CREATE_SUMMARY does not have any parameters. Its a very simple stored procedure without parameters.

    However I tried ProcedureRetResutlrs flag but didn't work.

    Thanks,

    Suniket

  • Suniket,

    I'm perplexed. If ABC_CREATE_SUMMARY has no parameters, then why is it being called with two parameters?

    {call ABC_CREATE_SUMMARY(?,?)}

    In the latest log I find calls to HSBC_CREATE_SUMMARY, which doesn't appear to exist.

    {call HSBC_CREATE_SUMMARY(?,?)}

    PLS-00201: identifier 'HSBC_CREATE_SUMMARY' must be declared

    Thanks,

    John

  • SQL3.LOG

    That is the strange thing.  Even though stored procedure is not having any parameter it shows 2 parameters.

    I tried by giving fake procedure name (HSBC_CREATE_SUMMARY, which actually not exist in my DB), it is looking for 2 parameters.

    Attaching latest ODBC trace log.

  • Sounds like the application might be assuming the procedure has two parameters without checking to see if that is correct first.  It would probably be a good idea for the application to call SQLProcedureColumns prior to the SQLBindParameter calls for the execution of the procedure.  Then the app would know how many parameters the procedure has, if they are input or output and what the data types are.  This will ensure the application binds the parameters correctly for that stored procedure.

  • SQL3.LOG

    Hi Jhon,

    I am attaching latest ODBC trace.

    - Suniket

  • Jhon,

    Is there any update on this one. I attached new ODBC trace.

    Somehow this registry workarounds is not working for me.

    Regards,

    Suniket

  • Suniket,

    As Bruce said, it looks like an application problem. The application is specifying and binding two parameters for a stored procedure that has no parameters.

    id=22           1a34-1850 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)

    HSTMT               0x022F2848

    UWORD                        1

    SWORD                        1 <SQL_PARAM_INPUT>

    SWORD                        1 <SQL_C_CHAR>

    SWORD                       12 <SQL_VARCHAR>

    SQLULEN                    0

    SWORD                        0

    PTR                0x6896B4FD

    SQLLEN                     0

    SQLLEN *            0x00000000

    ...

    id=22           1a34-1850 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)

    HSTMT               0x022F2848

    UWORD                        2

    SWORD                        4 <SQL_PARAM_OUTPUT>

    SWORD                      -18 <SQL_C_BOOKMARK>

    SWORD                        2 <SQL_NUMERIC>

    SQLULEN                   10

    SWORD                        0

    PTR                0x004FF68C

    SQLLEN                     4

    SQLLEN *            0x00000000

    {call ABC_CREATE_SUMMARY(?,?)}

    Thanks,

    John