search

JuliaDatabases / JDBC.jl

Julia interface to Java database drivers
Other
38 stars 19 forks source link

Passing username and password as args? #57

Open ThibTrip opened 4 years ago

ThibTrip commented 4 years ago

Problem description

Hello,

to my greater despair I have to work with a Pervasive SQL database. It seems the username and password are not passed to the java class correctly somehow.

The documentation for the Pervasive SQL JDBC connector states that you have to pass username and password as args (see below). This is not possible here since the only methods are getConnection(url::AbstractString) and getConnection(url::AbstractString, props::Dict).

Connection conn = DriverManager.getConnection("jdbc:pervasive://SERV:1583/DEMODATA",
    loginString, 
    passwordString);

If I try with a Dict it fails (see code and traceback below). Perhaps the problem lies somewhere else but I know that the username, password, url and jars are all correct because everything works on the same machine in DBeaver, so I don't see what else could be wrong.

Code

using JavaCall
using JDBC
# the jars are in same folder as the script
JavaCall.addClassPath("jpscs.jar")
JavaCall.addClassPath("pvjdbc2.jar")
JavaCall.addClassPath("pvjdbc2x.jar")
JavaCall.init()
classforname("com.pervasive.jdbc.v2.Driver")

url = "jdbc:pervasive://SERVER:PORT/DATABASE?transport=tcp"
user = "USER"
password = "PASSWORD"

conn = DriverManager.getConnection(url, 
    Dict("username" => user,"password" => password))
Exception in thread "main" java.sql.SQLException: [LNA][PSQL][SQL Engine][Data Record Manager]Invalid user authorization specification.
    at com.pervasive.jdbc.lna.LNAObject.getErrors(LNAObject.java:227)
    at com.pervasive.jdbc.lna.LNAConnection.getErrors(LNAConnection.java:673)
    at com.pervasive.jdbc.lna.LNAObject.checkError(LNAObject.java:270)
    at com.pervasive.jdbc.lna.LNAEnvironment.connect(LNAEnvironment.java:205)
    at com.pervasive.jdbc.v2.Connection.<init>(Connection.java:141)
    at com.pervasive.jdbc.v2.Driver.connect(Driver.java:57)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
JavaCall.JavaCallError("Error calling Java: java.sql.SQLException: [LNA][PSQL][SQL Engine][Data Record Manager]Invalid user authorization specification.")

Stacktrace:
 [1] geterror(::Bool) at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:294
 [2] geterror at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:274 [inlined]
 [3] _jcall(::JavaMetaClass{Symbol("java.sql.DriverManager")}, ::Ptr{Nothing}, ::Ptr{Nothing}, ::Type, ::Tuple{DataType,DataType}, ::String, ::Vararg{Any,N} where N) at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:247
 [4] jcall(::Type{JavaObject{Symbol("java.sql.DriverManager")}}, ::String, ::Type, ::Tuple{DataType,DataType}, ::String, ::Vararg{Any,N} where N) at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:143
 [5] getConnection(::String, ::Dict{String,String}) at /home/tibaldo/.julia/packages/JDBC/2ruzk/src/JDBC.jl:23
 [6] top-level scope at In[1]:13

Platform/Softwares

Notes

I would like to point out that I am a total julia newbie and I'd like to thank @aviks for his code snippet which I use here.

aviks commented 4 years ago

Maintaining this package makes me learn about all kinds of strange databases that I knew nothing about.

If you look at the source of the package, you should be able to see how to wrap the getConnection method with url/user/pwd.

I'll can help more when I get back to a computer.

On Tue, 3 Mar 2020, 15:26 ThibTrip, notifications@github.com wrote:

Problem description

Hello,

to my greater despair I have to work with a Pervasive SQL database. It seems the username and password are not passed to the java class correctly somehow.

The documentation for the Pervasive SQL JDBC connector https://docs.actian.com/psql/psqlv13/index.html#page/jdbc/jdbctasks.htm states that you have to pass username and password as args (see below). This is not possible here since the only methods are getConnection(url::AbstractString) and getConnection(url::AbstractString, props::Dict).

Connection conn = DriverManager.getConnection("jdbc:pervasive://SERV:1583/DEMODATA", loginString, passwordString);

If I try with a Dict it fails (see code and traceback below). Perhaps the problem lies somewhere else but I know that the username, password, url and jars are all correct because everything works on the same machine in DBeaver, so I don't see what else could be wrong. Code

using JavaCallusing JDBC# the jars are in same folder as the script JavaCall.addClassPath("jpscs.jar") JavaCall.addClassPath("pvjdbc2.jar") JavaCall.addClassPath("pvjdbc2x.jar") JavaCall.init()classforname("com.pervasive.jdbc.v2.Driver")

url = "jdbc:pervasive://SERVER:PORT/DATABASE?transport=tcp" user = "USER" password = "PASSWORD"

conn = DriverManager.getConnection(url, Dict("username" => user,"password" => password))

Exception in thread "main" java.sql.SQLException: [LNA][PSQL][SQL Engine][Data Record Manager]Invalid user authorization specification. at com.pervasive.jdbc.lna.LNAObject.getErrors(LNAObject.java:227) at com.pervasive.jdbc.lna.LNAConnection.getErrors(LNAConnection.java:673) at com.pervasive.jdbc.lna.LNAObject.checkError(LNAObject.java:270) at com.pervasive.jdbc.lna.LNAEnvironment.connect(LNAEnvironment.java:205) at com.pervasive.jdbc.v2.Connection.(Connection.java:141) at com.pervasive.jdbc.v2.Driver.connect(Driver.java:57) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189) JavaCall.JavaCallError("Error calling Java: java.sql.SQLException: [LNA][PSQL][SQL Engine][Data Record Manager]Invalid user authorization specification.")

Stacktrace: [1] geterror(::Bool) at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:294 [2] geterror at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:274 [inlined] [3] _jcall(::JavaMetaClass{Symbol("java.sql.DriverManager")}, ::Ptr{Nothing}, ::Ptr{Nothing}, ::Type, ::Tuple{DataType,DataType}, ::String, ::Vararg{Any,N} where N) at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:247 [4] jcall(::Type{JavaObject{Symbol("java.sql.DriverManager")}}, ::String, ::Type, ::Tuple{DataType,DataType}, ::String, ::Vararg{Any,N} where N) at /home/tibaldo/.julia/packages/JavaCall/0wJQZ/src/core.jl:143 [5] getConnection(::String, ::Dict{String,String}) at /home/tibaldo/.julia/packages/JDBC/2ruzk/src/JDBC.jl:23

Platform/Softwares

  • OS: Pop OS (Ubuntu Clone)
  • 32 bits database (not sure if this play a role)
  • julia 1.3.1.
  • JDBC v0.5.0
  • JavaCall v0.7.3

Notes

I would like to point out that I am a total julia newbie and I'd like to thank @aviks https://github.com/aviks for his code snippet https://github.com/JuliaDatabases/JDBC.jl/issues/24#issuecomment-327499260 which I use here.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/JuliaDatabases/JDBC.jl/issues/57?email_source=notifications&email_token=AAC4QJUKHGNE53IDHM3KAATRFUOQ3A5CNFSM4LAM5DQKYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4ISB6AXQ, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAC4QJSMKQXN6EMJ5NMTQALRFUOQ3ANCNFSM4LAM5DQA .

ThibTrip commented 4 years ago

Thanks a lot for this quick response @aviks! In a very unexpected turn of events I managed to have it working in Python (what I initially wanted). After trying to do it in Java as well the different error messages gave me some ideas :man_facepalming: .

I tried what you proposed with url/user/pwd and that led me to another error which I already had when working with Python and I was never able to fix it: "The Btrieve file directory is invalid(Btrieve Error 35)".

As much as I'd like to help other people I am not sure who is going to use Pervasive SQL with Julia :laughing: (I mean there are not many search results even in Python and since Julia is quite new...). The least I can do however is to post a gist of how I managed to do it with Python.

Perhaps we can close the issue and if someone else comes up with this problem then this person can reopen it? I'll let you decide.

walter-weinmann commented 3 years ago

I am a Julia novice, but I managed to get Oracle.jl working. I am now trying JDBC.jl, but I have the following problem with the database connection:

Based on:

cnxn = JDBC.Connection("jdbc:derby:test/juliatest") # create connection

I have tried:

JDBC.Connection(connection_string,Dict("username"=>connection_user,"password"=>connection_password))

But I got:

ERROR: LoadError: fatal error: program abort =====> JDBC.Connection() error: 'MethodError(JDBC.Connection, ("jdbc:oracle:thin:@//localhost:1521/orclpdb1?oracle.net.disableOob=true", Dict("password" => "regit", "user" => "scott")), 0x0000000000007401)' <=====
Stacktrace:
 [1] error(s::String)
   @ Base .\error.jl:33
 [2] create_connections(benchmark_number_partitions::Int64, config::Dict{String, Any})
   @ Main.OraBenchJdbc D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:54
 [3] run_benchmark(config::Dict{String, Any})
   @ Main.OraBenchJdbc D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:424
 [4] main()
   @ Main.OraBenchJdbc D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:355
 [5] top-level scope
   @ D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:856
in expression starting at D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:7

caused by: MethodError: no method matching JDBC.Connection(::String, ::Dict{String, String})
Closest candidates are:
  JDBC.Connection(::AbstractString; props, connectorpath) at C:\Users\walte\.julia\packages\JDBC\2ruzk\src\interface.jl:44
Stacktrace:
 [1] create_connections(benchmark_number_partitions::Int64, config::Dict{String, Any})
   @ Main.OraBenchJdbc D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:47
 [2] run_benchmark(config::Dict{String, Any})
   @ Main.OraBenchJdbc D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:424
 [3] main()
   @ Main.OraBenchJdbc D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:355
 [4] top-level scope
   @ D:\SoftDevelopment\Projects\ora_bench\lang\julia\OraBenchJdbc.jl:856
icarosadero commented 7 months ago

I think the props parameter in the getConnection function just passes arbitrary arguments, so the right keys for username and password will depend on the driver that you're using. On Trino, for example, you have to pass "user" instead of "username":

conn = DriverManager.getConnection("jdbc:trino://<url>/<database>", Dict("user"=><username>, "password"=><password>))