Strange issue with getting sdb.Rowset for postgres

English
, , ,
#1

Hello
I have a very strange issue when trying to get a rowset from postgresql in different ways:
1:

Dim pgCon as Object

Sub Main
     makePGConnection
     sql =  "SELECT ""id"", ""url"" FROM ""journeycache"".""urls"" ""urls"" ORDER BY ""id"" desc"
rem this fails
     rs1 = getRowSetForward(pgCon, sql)
     closeDbObject(rs1)
closeDbObject(locCon)
End Sub

sub makePGConnection()
Dim sURL$
Dim myDict As Variant
Dim oParms As Variant
	sURL$ ="sdbc:postgresql:host=localhost  port=5412 dbname=postgres user=postgres password=postgres"
	pgCon = connectionManager(sURL)
End Sub

Function connectionManager(sURL As String) As Object
Dim oManager As object
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	connectionManager= oManager.getConnection(sURL)
End Function

public Function getRowSetForward(con As object,sql As String) As Object
	set getRowSetForward = initiateRowSet(con,sql)
	makeRowSetType getRowSetForward,com.sun.star.sdbc.ResultSetType.FORWARD_ONLY,com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY
End Function
Function initiateRowSet(con As object,sql As String) As Object
	set initiateRowSet= CreateUNOService("com.sun.star.sdb.RowSet")
	With initiateRowSet
		.activeconnection = con
		.CommandType = com.sun.star.sdb.CommandType.COMMAND
		.Command =sql
	End with
End Function

Public sub makeRowSetType(rs As Object,rsType As integer,rsConcurreny As Integer)
Dim exc As Variant
	rs.ResultSetType= rsType
	rs.ResultSetConcurrency=rsConcurreny
	rs.execute()
End sub

The result is:
image

However when making a connection to base file which connects to postgres like this:

Dim pgCon as Object
Sub Main
     openLocal(converttourl("e:\Documents\LOCALHOST.odb"))
     sql =  "SELECT ""id"", ""url"" FROM ""journeycache"".""urls"" ""urls"" ORDER BY ""id"" desc"
     rs2 = getRowSetForward(pgCon, sql)
     closeDbObject(rs2)
     closeDbObject(locCon)
End Sub
Public Sub openLocal(db$)
		pgCon= getFBConnection(db)
End Sub
Private Function getFBConnection(context$)
Dim DatabaseContext As Object
	DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
	DataSource = DatabaseContext.getByName(context)
	getFBConnection= DataSource.getConnection("","")
End Function

public Function getRowSetForward(con As object,sql As String) As Object
	set getRowSetForward = initiateRowSet(con,sql)
	makeRowSetType getRowSetForward,com.sun.star.sdbc.ResultSetType.FORWARD_ONLY,com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY
End Function
Function initiateRowSet(con As object,sql As String) As Object
	set initiateRowSet= CreateUNOService("com.sun.star.sdb.RowSet")
	With initiateRowSet
		.activeconnection = con
		.CommandType = com.sun.star.sdb.CommandType.COMMAND
		.Command =sql
	End with
End Function

Public sub makeRowSetType(rs As Object,rsType As integer,rsConcurreny As Integer)
Dim exc As Variant
	rs.ResultSetType= rsType
	rs.ResultSetConcurrency=rsConcurreny
	rs.execute()
End sub

The connection setting for e:\Documents\LOCALHOST.odb is

host=localhost port=5412 dbname=postgres user=postgres password=postgres

image

The result of this test is that the sdb.Rowset is created successfully with no errors.

Current setting is

Version: 7.6.6.3 (X86_64) / LibreOffice Community
Build ID: d97b2716a9a4a2ce1391dee1765565ea469b0ae7
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_US); UI: en-US
Calc: CL threaded

What could be the issue? Thank you.

#2

50 views and no answer, I guess your (very detailled) post would required a bit of synthesis :wink: like what is the actual diff between your 2 code samples, and at what line the error actually occurs ?

#3

The error occurs in the first example of the code, where I am making a direct native SDBC connection to postgresql at the line

	rs.execute()

in the makeRowSetType subroutine:

Public sub makeRowSetType(rs As Object,rsType As integer,rsConcurreny As Integer)
Dim exc As Variant
	rs.ResultSetType= rsType
	rs.ResultSetConcurrency=rsConcurreny
	rs.execute()
End sub

It gets the error message:

Basic runtime error.
An exception occurred
Type: com.sun.star.beans.UnknownPropertyException
Message: -1

There is no difference in the two examples of the code with the exception of how I make a connection to the database. In the first example I go directly to the Postgresql database.

In the second example I make a connection to a base file, which itself is connected to the same database. And it successfully returns back a Rowset.

#4

are you sure your URL is correct ?
does the connection only fails on RowSet ?

#5

The connection is successful in both ways. The code fails on rs.execute() for the direct connection.

#6

tried the same thing, making a rowset from a direct connection to postgres this on LO 24/linux and i got the following error message:

image

Version: 24.2.2.2 (X86_64) / LibreOffice Community
Build ID: 427a8baee0312a7693737440f205d2e411d50bad
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

This did not result in an error when i made a connection to postgres via connecting to a base file

#7 
"sdbc:postgresql:host=localhost  port=5412 dbname=postgres user=postgres password=postgres"

does not look like a valid URL because of the spaces.

1 Like
#8

I will try the alternatives but the connectiin string I am currently using works and makes a succesful connection to the PostGresql database.

#9

I tried the following connection string:

sdbc:postgresql://postgres:postgres@localhost:5412/postgres

and I got the following error message:

BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException Message: Error in database URL 'sdbc:postgresql://postgres:postgres@localhost:5412/postgres': missing "=" after "//postgres:postgres@localhost:5412/postgres" in connection info string .

I then switched to:

postgresql://postgres:postgres@localhost:5412/postgres

Please note the lack of the SDBC: prefix/

And I got the following error message:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: No connection could be established.
#10

I did some more checking and found that the connection string:

postgresql://postgres:postgres@localhost:5412/postgres

when passed as a parameter to:

Function connectionManager(sURL As String) As Object
Dim oManager As object
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	connectionManager= oManager.getConnection(sURL)
End Function

does NOT result in an error, however the value of the connection is NULL. So the connection string

sdbc:postgresql:host=localhost port=5412 dbname=postgres user=postgres password=postgres

Is a valid connection string, it returns back a valid connection, but I can not get a rowset from this.

Connecting to a Base file which itself is connected to the PostGresQL database using the previously mentioned connection string results in a working connection that allows for making a successful rowset.

1 Like
#11

if no one more knowledgeable on the topic pops up here, next investigations would (unfortunately) be in bugzilla, like tdf#124531
and opengrok

1 Like
#12

I looked up the connection URL of my local MariaDB test database. It is
sdbc:mysql:mysqlc:LOCALHOST:3306/test The slash separates the database name from protocol:host:port
Database URLs may or may not follow the standards, but the syntax for additional URL arguments goes like 'protocol:host/path?first=1&second=2&third=3`. The chain of name=value arguments starts with ?, and subsequent arguments are separated by &.

May be that sdbc drivers can not be used without any database document? I don’t know.

1 Like
#13

Hello,
Happily, a JDBC connection to PostGresql does not cause any error:

Sub Main
     makeJdbcPGConnection()
     rs1 = getRowSetForward(pgCon, sql)
     closeDbObject(rs1)
     closeDbObject(pgCon)
End Sub
sub makeJdbcPGConnection()
Dim sURL$
Dim myDict As Variant
Dim oParms As Variant
	sURL$ ="jdbc:postgresql://localhost:5432/postgres"
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	myDict = CreateScriptService("Dictionary")
	With myDict
		.Add("user","postgres")
		.Add("password","postgres")
		.Add("JavaDriverClass","org.postgresql.Driver")
		props = .ConvertToPropertyValues()
		myDict = .Dispose()
	End with
	pgCon = connectionWithInfoManager(sURL,props)
End Sub
Function connectionWithInfoManager(sURL As String, param as Variant) As Object
Dim oManager As Object
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	connectionWithInfoManager= oManager.getConnectionWithInfo(sURL,param)
End Function
public Function getRowSetForward(con As object,sql As String) As Object
	set getRowSetForward = initiateRowSet(con,sql)
	makeRowSetType getRowSetForward,com.sun.star.sdbc.ResultSetType.FORWARD_ONLY,com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY
End Function
Function initiateRowSet(con As object,sql As String) As Object
	set initiateRowSet= CreateUNOService("com.sun.star.sdb.RowSet")
	With initiateRowSet
		.activeconnection = con
		.CommandType = com.sun.star.sdb.CommandType.COMMAND
		.Command =sql
	End with
End Function

Public sub makeRowSetType(rs As Object,rsType As integer,rsConcurreny As Integer)
Dim exc As Variant
	rs.ResultSetType= rsType
	rs.ResultSetConcurrency=rsConcurreny
	rs.execute()
End Sub

Public Sub closeDbObject(dbObj As object)
	dbObj.close()
	dbObj=Nothing
End Sub

I suspect ODBC will have a problem

1 Like
#14

So I managed to test making a rowset via PG’s odbc driver and I was able to do so without a problem. So it seems the only problem is with making a rowset via direct SDBC connection to the PostGresql database. all other connection types are okay.

#15

I did one final check before deciding that the point is made. It is established that making a rowset from a direct connection to postgres results in an error. My final check is whether a resultset will cause an error and the good news is that there is no prolem with that. My work is done

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.