How create right JDBC connection to SAS MetaData Server?

drno-reg — Thu, 16 Jun 2016 07:49:59 GMT

Hellow.

I try to create JDBC connection to SAS MetaData Server and get error

java.sql.SQLException: Unable to establish a connection: Unable to return workspace. The client has connected to a SAS (9.2) Metadata Server (v1.0) when it intended to connect to a SAS Workspace Server.

Java code

<%
    Connection connection;
    Properties props;
    Statement statement;

  /* SAS datasets can be queried with a SQL statement itself */

    String queryString = "SELECT username FROM MySasLib.users1 t1";

    ResultSet result;
    String id;
    String name;
    int i=1;
    try {
        // CONNECT TO THE SERVER BY USING A CONNECTION PROPERTY LIST

        try
        {
            Class.forName("com.sas.rio.MVADriver");
        } catch (ClassNotFoundException e)
        {
            System.out.println(e.getMessage());
        }

        props = new Properties();
        props.setProperty("user", "user1");
        props.setProperty("password", "pwd1");
        props.setProperty("librefs", "mySasLib '/sas/dwh/user1/';");

   /* SAS libref and library name */

        //ACCESS DATA

             connection = DriverManager.getConnection("jdbc:sasiom://server.inside.my.ru:8000", props);

        statement = connection.createStatement();
        result = statement.executeQuery(queryString);
        ResultSetMetaData rsmd = result.getMetaData();
        int colCount = rsmd.getColumnCount();
        out.println("<TABLE border='1'>");
        out.println("<TR>");
        for (i = 1; i <= colCount; ++i) {
            out.println("<TH>" + rsmd.getColumnLabel(i) + "</TH>");
        }
        out.println("</TR>");
        String val = null;
        while (result.next()) {
            out.print("<TR>");
            for (i = 1; i <= colCount; ++i) {
                val = result.getString(i);
                if (result.wasNull()) {
                    val = nbsp;
                }
                out.print("<TD>" + val + "</TD>");
            }
            out.println("</TR>");
        }
        out.println("</TABLE>");
        statement.close();
        connection.close();
    } catch (Exception e) {
        out.println("error " + e);
    }
%>

Please help to solve this problem.

Re: How create right JDBC connection to SAS MetaData Server?

PaulHomes — Thu, 11 Aug 2016 02:02:35 GMT

I'm not sure if this is still an issue for you? It looks like you posted it in June but I only got my subscription email alert about it last night! 🙂

From your code, I'm assuming you are running your metadata server on port 8000? The SAS Metadata Server itself does not support JDBC. You need to connect to either a SAS Workspace Server or a SAS/SHARE server for a JDBC connection. However, the SAS Metadata Server can be used to find those servers. I'm assuming that is what you want to do? You'll find several JDBC examples for various different scenarios in the SAS 9.4 Integration Technologies: Java Client Developer's Guide, but since you mentioned using a metadata server I'd suggest you start with the example code in Connecting with Server Attributes Read from a SAS Metadata Server.

topic Re: How create right JDBC connection to SAS MetaData Server? in Administration and Deployment

How create right JDBC connection to SAS MetaData Server?

Re: How create right JDBC connection to SAS MetaData Server?