Getting error in Event log while running executable with Dat

Posted by suniketpatel on 13-Aug-2014 07:33

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.

DataDirect Connect - Forum

All Replies

Posted by jhobson on 14-Aug-2014 07:05

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

Posted by suniketpatel on 18-Aug-2014 06:20

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.

Posted by jhobson on 18-Aug-2014 06:41

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)

Posted by Bruce Rudd on 18-Aug-2014 07:50

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

Posted by suniketpatel on 19-Aug-2014 02:06

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

Posted by jhobson on 19-Aug-2014 12:12

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

Posted by suniketpatel on 20-Aug-2014 04:00

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

Posted by jhobson on 20-Aug-2014 06:01

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

Posted by suniketpatel on 20-Aug-2014 07:00

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

Posted by jhobson on 20-Aug-2014 07:16

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

Posted by suniketpatel on 20-Aug-2014 07:27

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.

Posted by Bruce Rudd on 20-Aug-2014 07:55

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.

Posted by suniketpatel on 20-Aug-2014 08:45

Hi Jhon,

I am attaching latest ODBC trace.

- Suniket

Posted by suniketpatel on 21-Aug-2014 04:11

Jhon,

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

Somehow this registry workarounds is not working for me.

Regards,

Suniket

Posted by jhobson on 21-Aug-2014 05:53

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

Posted by Bruce Rudd on 21-Aug-2014 09:03

Keep in mind that the workaround John suggested is only for the situation where their are parameters that need to be bound but the application is incorrectly specifying a length of 0.  It would not apply to this situation where it appears the procedure has no parameters but the application is binding some anyway.

This thread is closed