I’m trying to call a stored procedure with wM 6.1.
This stored procedure has many field, and one of them is a JDBC type : BINARY(12).
My stored procedure call does work, but i don’t manage to insert a good value for the BINARY.
In SQL Advantage (a Sybase Client for windows), I can see the values in the data base for the BINARY(12). It’s like : 0x5f000001000000012b7b700f for example.
In wM, I tried to force wM to use String type for the input of the stored procedure (VARCHAR). It does work, but, the result in Sybase is 0x35463030303030… It the ASCII representation for my string (as you can see here : http://www.lookuptables.com/)
If I try to use a byte array for the input of the stored procedure (BINARY), I don’t know what to map. If I tried to create a Java service to convert a string to bytes (string.getBytes()), the stores procedure return me that the input is null.
Does anyone of you have the same problem, or how do you call (or insert) BINARY ?
Had the same problem, never got it to work with call in WmDB. Instead we use the execSQL with a statement like exec sp_name followed by the parameters.
Many thanx Bent.
My sp name is : create_account. It has 17 parameters. The first parameter is a BINARY(12). Others are SMALLINT or VARCHAR.
I tried to call it with a execSQL as you said.
The SQL request is in WmDB :
exec create_account ?,?,?,?,…
The input JDBC type for the first parameter is VARCHAR. So, I map a String into it, but the sp convert the string (in ASCII).
I tried the input JDBC at BINARY, but I don’t know what to map into it.
The problem is the same like yesterday.
Any ideas ?
In my particular case the stored procedure requires an timestamp as input in order to check if the record has been updated since the data was retrieved.
The timestamp when retrieved by the select sp is missing the starting 0x, so I first add that to the timestamp.
Then I create the whole string exec sp_name param1, param2 etc. same way as if you would write a direct sp call from an application like Rapid SQL.
I then use this string as the input for $dbSQL in execSQL. I do not specify param names or types.
I never managed to get the timestamp working via the call as the value seems to be changed before hitting SyBase or via execSQL using params, but with this method I did get it working.
FYI - this approach worked for me on both 4.6 and also now on 6.1.
Many many thanx !
>I then use this string as the input for $dbSQL in execSQL. I do not >specify param names or types.
That’s it! I use DynamicSQL, and it works very well. The solution is to not specify JDBC type.
Thanks you very much indeed.