Conversion Error from data type sql_variant to uniqueidentifier

,
1

Hello experts,

I’ve created a jdbc adapter service for a stored procedure on the customer’s SQL Server database. Some of input and output variables of this stored procedure are of type “uniqueidentifier”.

When I run my service, it gives me this error:

com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service adapters:spPciMembCrud_service with connection adapters:sqlserverconn.
[ADA.1.316] Cannot execute the SQL statement “?= call PCIACCT.dbo.spPciMembCrud;1( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”. "
(S0003/257) Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query."
Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.

In my adapter service, I tried using JDBC Type of MSSQL_GUID for these columns and set the Input Field Type to either java.lang.String or GUID. But both configurations return the same error.

There are many input variables to this call, but I only need to supply some of these inputs. I am able to call this stored procedure in a database tool without issues.

Hope you can help.

Thanks!

2

Hi Markjason,

uniqueidentifier data type provides binary value looks like.

Please try to extract this as an object and then using built-in-services( for bytes transformation) to read that data.

Hope this helps.

Regards,
Firoz Nalband.

3

Thank you Firoz for the tip.

Upon further trying, it appears that when I execute the adapter service and does not part a String value for the GUID fields, the conversion error happens (say if I pass an empty string or null value).

The problem is, when the stored procedure is called, only certain parameters are used as input, the GUID columns although are declared INPUT variables are not passed as parameters for actual procedure calls, therefore I need to supply either an empty string or null. But it appears that webMethods still parse the value for these GUID columns and then it results to the error.

So if I input a string GUID value, it proceeds without error. If it’s an empty string or null value, it gets a conversion error. However, the GUID input parameters are not required so I will not always have a valid value to pass on to these params.

Any idea how to go around this?

Thanks!

4

Hi Markjason,

When I replied to you I thought the value is coming in output result set, so proposed that solution. But it looks like it is an input parameter.

Do you need this field in the input parameters of stored procedure if you aren’t going to pass any value to it while executing the SP. If not required, you can remove it.

Or else pass any dummy value which you can ignore in the stored procedure logic.

Try to execute the stored procedure on database server without passing any string to that value, and see how it reacts as well.

webMethods will try to parse the input variables to their correct format which is valid scenario, nothing much we can do about the same.

Regards,
Firoz N

5

Thanks Firoz! Appreciate your responses.

When I run the stored procedure on the database using the following script (note that I removed some of the fields as it is a long list), it returns successfully. In this procedure call, I did not have to specify values for the ‘uniqueidentifier’ fields, and the db engine just takes in a null value with the rest of the parameters.


DECLARE @RC int
DECLARE @CORPACCT char(11)
DECLARE @ACCT char(20)
DECLARE @CONTRACT char(3)
...
DECLARE @ROWGUID uniqueidentifier
DECLARE @SUB_CODE uniqueidentifier
DECLARE @NEWROWGUID uniqueidentifier
DECLARE @RESULTCODE int
DECLARE @RESULTMSG varchar(max)
DECLARE @QUERYMODE int
DECLARE @SEARCH varchar(50)

-- TODO: Set parameter values here.

EXECUTE @RC = [dbo].[myStoredProc] 
   @CORPACCT
  ,@ACCT = 'xxxxxxxxx'
  ,@CONTRACT = '15'
...
  ,@ROWGUID
  ,@SUB_CODE
  ,@NEWROWGUID OUTPUT
  ,@RESULTCODE OUTPUT
  ,@RESULTMSG OUTPUT
  ,@QUERYMODE='4'
  ,@SEARCH
GO

I may not be able to remove this parameter because in the customer’s application, they may or may not pass this GUID fields eventually.

Thanks again!

6

Hi Markjason,

As per the JDBC Adapter Guide, it doesn’t support user defined types. Also I couldn’t find this uniqueidentifier in the JDBC to JAVA data type mapping as supported.

**Adapter for JDBC does not support user-defined data types,**

For driver - SQL Server 2005 / Driver for JDBC Version 1.0.809.102, there is a limitation specified as - This driver returns invalid JDBC data type for MS SQL UNIQUEIDENTIFIER data type.

Please try to use Stored Procedure along and not with signature, and try to invoke without mapping the input parameter causing issue.

else use Custom SQL and invoke the SP with required parameters, as you said on database it worked fine.

Regards,
Firoz N

7

Thank you Firoz, I did not think of using Custom SQL to use. I can try that later on.

Also, I managed to get help from our Adapters team, and they had me configure the following file:

<Packages_Dir>/ WmJDBCAdapter/config/ MSSQLTypeMapping.xml

And change the JDBC code for MSSQL_GUID from -156 to -145, that resolved the issue as well. Saying that here for those who might encounter the same problem in the future.

Thank you very much for the help as well, Firoz! Much appreciated!

Markjason

1 Like
8

Hi Markjason,

Great to know that the issue is resolved.

Thanks for sharing the solution you found, that will definitely help others

Regards,
Firoz N

1 Like