Error while invoking stored procedure from Experian Aperture Data Studio using custom JDBC driver

Shreya
Shreya Member
edited December 2023 in General

Hi,

I am using the Experian Aperture Data Studio (2.10.0.80) and have configured a custom JDBC Jar for adding Microsoft SQL Server Driver to query On premise SQL server(Microsoft SQL Server 2012 (SP4-GDR) (KB4532098) - 11.0.7493.4).

With this driver, I am able to perform DML SQL queries like Select, Insert and Update. However I am getting error while creating dataset when I am trying to execute stored procedure which contains any DML operation. The error from the log file is as below

2023-05-10 13:21:01,746 WARN c.e.w.a.d.s.e.SelectJDBCTablesPanel [qtp1057867030-2117] java.io.IOException: java.lang.NullPointerException: Cannot invoke "java.sql.ResultSetMetaData.getColumnCount()" because "meta" is null
2023-05-10 13:21:01,746 ERROR c.e.w.a.d.s.e.SelectJDBCTablesPanel [qtp1057867030-2117] Catching
java.io.IOException: java.lang.NullPointerException: Cannot invoke "java.sql.ResultSetMetaData.getColumnCount()" because "meta" is null

Can someone let me know how to resolve this issue?

Tagged:

Answers

  • Josh Boxer
    Josh Boxer Administrator

    Hi Shreya

    I think that the issue is that your stored procedure contains a CTE, i.e. rather than beginning with SELECT it begins WITH

    If that is the case then it should be resolved by upgrading Data Studio to something newer than v2.10.1 which contains an improvement to choose whether queries are executed in preview

  • Shreya
    Shreya Member

    Hi Josh,

    Thanks for your response.

    My end goal is to invoke the store procedure present on the On Premise SQL server from Experian. To do that I am trying to create a dataset using a JDBC external system configured for the custom driver(Microsoft SQL Server) with connection string as below

    jdbc:sqlserver://<HOST><NAMED_INSTANCE>:<PORT>;instanceName=MSSQLSERVER;allowPortWithNamedInstance=true;DatabaseName=<DBNAME>;LoginTimeout=15;UseServerSideUpdatableCursors=true;SelectMethod=cursor;encrypt=FALSE

    The stored procedure that I am trying to invoke does not contain any CTE and its something as below and I am passing the query exec sp_sample in the database query during dataset creation

    create procedure sp_sample

    as 

    BEGIN 

    Insert into dbo.Tbl1(ID) values(100);

    Update dbo.Tb1 set ID = 200; 

    Delete dbo.Tb1 where ID = 200;

    Select 'Success' as OperationStatus

    END

    Also noticed that if a stored procedure is like below, I am able to create the dataset successfully but not for the above one which is per my requisite.

    create procedure sp_sample

    as 

    BEGIN 

    Select 'Success' as OperationStatus

    END

    Please do let me know if there is any change needed either in jdbc connection string and/or in the execute procedure query or does it need some other changes altogether.

  • Shreya
    Shreya Member

    Hi @Josh Boxer ,

    I was able to resolve this issue by using the exec sp_sample from the PostSQL of export steps. My code is working now without any error

Categories