ODBC Connect without DSN. Can this be done?

[#1]

Is there any way to connect via ODBC without using an externally defined DSN? I an using Oracle and have a TNSnames entry set up. I know this can be easily done in .NET and other products.

My system reports the oracle driver as {Oracle in OraClient11g_home2} which I assume could vary from server to server.

But if I use

ODBC CONNECT TO [DRIVER={Oracle in OraClient11g_home2};DBQ=TheTNSName] (XUserId is THEUSERNAMEHASH, XPassword is THEPASSWORDHASH);

I get the following error

SQL##f - SqlState: IM010, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Data source name too long

If I use

ODBC CONNECT TO [TheExternalDSNName;DBQ=TheTNSName] (XUserId is THEUSERNAMEHASH, XPassword is THEUSERNAMEHASH);

it works fine. But, "TheExternalDSNName" is the name of an externally defined DSN entry on the machine.

Is there any way to do this? We really don't want to have to manage external DSN entries.

Thanks.

[petter]

The QlikView Connectors Database (formerly ODBC Connector Pack) is something that supports DSN-less connections, mind you that not all of them are necessarly the latest drivers - the Oracle one support 12C and 11.2 so you should be good to go. They have to be downloaded as a pack and installed after you log onto qlik.com as partner or customer and download the installation pack.

Notice that after installation you will have to select the QVOdbcConnectorPackage.exe in the Database drop-down list:

[#3]

Thanks Petter. I will try this out. Will this let me call an Oracle stored procedure, or must I still use a function?

Please see this post...

Calling Oracle Stored Procedure in Load Script Does Not Return Data‌

Thanks.

[petter]

It's really a new question - and I am not sure. I am not an Oracle expert. If you're trying to extract data row sets is what the stored procedure should return. Try to post a new question with the right title to draw attention... Thanks. Please mark this thread as answered.

[#5 · Accepted solution]

We were able to create a DSN-less connection with the QlikView Oracle connector, however, calling the function fails and doesn't return any fields. The solution was to drop back to the ODBC connector. That does allow us to call a function. We can't call the stored procedures directly, but if we use a function to call it then it does work.

So, if we have a stored procedure X that takes parameters A and B, and returns a SYSREFCURSOR

Then we can create function Y that takes the same parameters and returns a SYSREFCURSOR. Function Y calls stored procedure X and simply passes back the result.

The syntax we have to use in the LOAD statement is as follows:

LOAD

     FIELD1 as [Something],

     FIELD2 as [Something Else]

SQL     SELECT * FROM { ? = call Y( $(Var1), $(Var2) ) };

Var1 and Va2 would be the values that we're passing in for parameters A and B. You might need single quotes depending upon your variable types.

FIELD1 and FIELD2 would be names of some of the columns in the result set that is returned.

This method is reliable and performs well.