Start a new topic

SAS database support

I have (more or less) successfully configured SAS connection in DbVisualizer, using official SAS 9.4 drivers.

DbVisualizer connects and lists schemas and tables.

However I see that there's something wrong in SQL statements that DbVisualizer issues to SAS (see second screenshot).

I think it would be nice to support SAS out of the box.



SAS-DB.png
(183 KB)

Dear Luca, everything works fine.
Many THX for your Help.

Sorry, probably it's just a problem of writing the correct schema (i.e. library). 


"WORK" is the default library. If you write a select and you do not specify the library (i.e. select * from BA_RISIKO_DIM ), the table will be searched in WORK library. You should write select * from LIBRARY.BA_RISIKO_DIM


Can you post your select?

Me again.
Now I can see my Tables and the Content. I can execute Filters but the Select Statement doesn't work.


2018-12-14 13:15:44.052 FINE 213 [ExecutorRunner-pool-4-thread-18 - J.execute] Executing...

2018-12-14 13:15:44.052 FINE 213 [ExecutorRunner-pool-4-thread-18 - J.setCurrentCatalog] DbConnection='Database Connection' Catalog='null' Schema='null' NewCatalog='null'

2018-12-14 13:15:44.052 FINE 213 [ExecutorRunner-pool-4-thread-18 - J.setCurrentSchema] DbConnection='Database Connection' Catalog='null' Schema='null' NewSchema='DM_BA'

2018-12-14 13:15:44.052 FINE 213 [pool-5-thread-15 - C.?] DefaultEditor81: MVAConnection.createStatement()

2018-12-14 13:15:44.052 FINE 213 [pool-5-thread-15 - B.?] DefaultEditor81: MVAStatement.execute("select * from BA_RISIKO_DIM")

2018-12-14 13:15:44.067 FINE 213 [pool-5-thread-15 - B.?] DefaultEditor81: EXCEPTION -> com.sas.rio.MVASQLException: Unable to execute query: SQL-Passthru-Ausdruck enthielt folgende Fehler: ERROR: Datei WORK.BA_RISIKO_DIM.DATA existiert nicht.

com.sas.rio.MVASQLException: Unable to execute query: SQL-Passthru-Ausdruck enthielt folgende Fehler: ERROR: Datei WORK.BA_RISIKO_DIM.DATA existiert nicht.

 at com.sas.rio.RIOUtil.executeQuery(RIOUtil.java:446)

 at com.sas.rio.MVAStatement.execute2(MVAStatement.java:658)

 at com.sas.rio.MVAStatement.execute(MVAStatement.java:591)

 at sun.reflect.GeneratedMethodAccessor78.invoke(Unknown Source)

 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

 at java.lang.reflect.Method.invoke(Unknown Source)

 at com.onseven.dbvis.h.B.B.?(Z:194)

 at com.onseven.dbvis.h.B.F$A.call(Z:1369)

 at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)

 at java.util.concurrent.FutureTask.run(Unknown Source)

 at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

 at java.lang.Thread.run(Unknown Source)

Caused by: com.sas.iom.SASIOMDefs.GenericError: SQL-Passthru-Ausdruck enthielt folgende Fehler: ERROR: Datei WORK.BA_RISIKO_DIM.DATA existiert nicht.

 at com.sas.iom.SASIOMDefs.GenericErrorHelper.read(GenericErrorHelper.java:22)

 at com.sas.iom.SASIOMDefs.GenericErrorHelper.extract(GenericErrorHelper.java:47)

 at com.sas.iom.SAS._portable_stub_IDBMSConnection.ExecuteQuery(_portable_stub_IDBMSConnection.java:75)

 at com.sas.rio.RIOUtil.executeQuery(RIOUtil.java:435)

 ... 12 more


Any Idea?

Hi Luca,
thx a lot for your comment.

The Connection does work now, but I cannot get any Data from my SAS System because I fetch an Error.

image


It seams, the select Statement wants to create an WORK Table on Workspace Server and cannot give back the result or has no permission to do that stuff.

Can you help again?


My DbVis Version is 9.2.8 (Full Version)

Thanks a lot

Hello Stefan. I have downloaded drivers from SAS website, then I have manually created the connection in DbVisualizer Tools -> Driver Manager (see attachment). I have also downloaded log4j.jar from Maven Central Repository.


I also had to modify the "Delimited identifiers" in connection properties (second attachment).


Hope that helps you.

sas-2.png
(104 KB)
sas-1.png
(66.2 KB)

Dear Luca, can you tell me, how to add SAS drivers for DbVisualizer and connect to my SAS Server, please.
That's an amazing Idea.

THX

Great.


Please enable Debug DbVisualizer in Tools->Debug Window and the Debug tab and then re-run the operation that doesn't show any data. Then use Help->Contact Support in DbVisualizer to send the log file to us. Refer to this link in the new request: https://support.dbvis.com/discussions/topics/1000086288


Regards


Roger


Thank for your support, I have solved the 10.0.1 issue. SAS driver requires log4j.jar, which is present in 9.0.8 but not in 10.0.1. I have added this library, and now I can use the 10.0.1. DbVisualizer cannot still show tables, but apart for this everything works.

With 10.0.1 situation is worse: DbVisualizer does not even connect.

I will send logs for both situations.

Thank you again.



Thank you for your fast reply. I'l see.

Luca,


Great to hear you got connected.


Please note that SAS is not a supported database nor do we have any short-term plans to support it as there is low  demand for it.


I see you are using DbVisualizer Free 9.0.8 which is quite old. I first recommend that you upgrade to the latest 10.0.1 version. http://www.dbvis.com/download/


For the actual error(s), please enable Debug DbVisualizer in Tools->Debug Window and the Debug tab and then re-run the operation that causes the issue. Then use Help->Contact Support in DbVisualizer to send the log file to us. 


Regards


Roger

Login or Signup to post a comment