1. Forum
  2. Usenet
  3. COMP.OS.VMS

  • Accessing MS SQLServer from VMS in a JVM language

    From =?UTF-8?Q?Arne_Vajh=C3=B8j?=@21:1/5 to All on Mon Aug 26 20:44:32 2024
    This is surprisingly easy:

    1) enable SQLServer authentication (Windows authentication only will not
    work)
    2) download official Microsoft JDBC driver and transfer it to VMS
    3) make sure TCP port 1433 is open in various firewalls

    And voila - access from Java, Groovy, Kotlin, Scala and more
    exotic JVM languages.

    Demo with Groovy:

    $ type mssql.groovy
    import java.sql.*

    con = DriverManager.getConnection("jdbc:sqlserver://arnepc5;database=Test;integratedSecurity=false;encrypt=false;",
    "sa", "hemmeligt
    ")
    pstmt = con.prepareStatement("SELECT f1, f2 FROM t1")
    rs = pstmt.executeQuery()
    while(rs.next()) {
    f1 = rs.getInt(1)
    f2 = rs.getString(2)
    printf("%d %s\n", f1, f2)
    }
    con.close()
    $ groovy_cp = "mssql-jdbc-12_2_0_jre8.jar"
    $ groovy mssql.groovy
    1 A
    2 BB
    3 CCC

    And if JDBC is too low level then JPA can be used (Hibernate
    works fine as JPA provider on VMS - and it does come with
    SQLServer dialect).

    Easy.

    One of the finer points is that the Microsoft JDBC driver
    is type 4 for SQLServer authentication and therefore can run
    on VMS (it is type 2 for Windows autehntication, but that does
    not matter).

    So no need for jTDS JDBC driver.

    Arne

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Craig A. Berry@21:1/5 to All on Mon Aug 26 20:17:20 2024
    On 8/26/24 7:44 PM, Arne Vajhøj wrote:
    This is surprisingly easy:

    No surprise. It was easy with jTDS 15-20 years ago, but I think that
    driver is deprecated now.

    1) enable SQLServer authentication (Windows authentication only will not work)

    It might be possible using pure-Java Kerberos, but getting that working
    sounds like work:

    https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server?view=sql-server-ver16


    2) download official Microsoft JDBC driver and transfer it to VMS
    3) make sure TCP port 1433 is open in various firewalls

    And voila - access from Java, Groovy, Kotlin, Scala and more
    exotic JVM languages.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?Q?Arne_Vajh=C3=B8j?=@21:1/5 to Craig A. Berry on Mon Aug 26 21:33:26 2024
    On 8/26/2024 9:17 PM, Craig A. Berry wrote:
    On 8/26/24 7:44 PM, Arne Vajhøj wrote:
    This is surprisingly easy:

    No surprise.  It was easy with jTDS 15-20 years ago, but I think that
    driver is deprecated now.

    Looks like last release is from 2013.

    But it may still work fine. TDS is a very old protocol.

    Using the official Microsoft driver is could be important for some though.

    1) enable SQLServer authentication (Windows authentication only will
    not work)

    It might be possible using pure-Java Kerberos, but getting that working sounds like work:

    https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos- integrated-authentication-to-connect-to-sql-server?view=sql-server-ver16

    Yes.

    The driver comes with:

    mssql-jdbc_auth-12.2.0.x64.dll
    mssql-jdbc_auth-12.2.0.x86.dll

    which obviously will not work on VMS.

    Arne

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?Q?Arne_Vajh=C3=B8j?=@21:1/5 to All on Mon Aug 26 21:46:16 2024
    On 8/26/2024 9:33 PM, Arne Vajhøj wrote:
    On 8/26/2024 9:17 PM, Craig A. Berry wrote:
    On 8/26/24 7:44 PM, Arne Vajhøj wrote:
    This is surprisingly easy:

    No surprise.  It was easy with jTDS 15-20 years ago, but I think that
    driver is deprecated now.

    Looks like last release is from 2013.

    But it may still work fine. TDS is a very old protocol.

    It still works.

    $ type jtds.groovy
    import java.sql.*

    con = DriverManager.getConnection("jdbc:jtds:sqlserver://arnepc5/Test",
    "sa", "hemmeligt")
    pstmt = con.prepareStatement("SELECT f1, f2 FROM t1")
    rs = pstmt.executeQuery()
    while(rs.next()) {
    f1 = rs.getInt(1)
    f2 = rs.getString(2)
    printf("%d %s\n", f1, f2)
    }
    con.close()
    $ groovy_cp = "jtds-1_3_1.jar"
    $ groovy jtds.groovy
    1 A
    2 BB
    3 CCC

    Arne

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)