Devart Forums

Hi Devart Forum,

I know that I've asked a few questions lately, but I'm having a problem that looks to me like a bug.

I have Visual Studio reporting services (2019) connected via the ODBC driver with protocol pv20 and IPBoth.

I'm running a really simple postgreSQL query that creates a temp table and inserts the results. I've paired this right back, and in this example I have 27 records of just one field, a text member number.

If I finish the query with a group by, the query works. I have a far more complex query I'm trying to write, and when I group by on this, it works too.

select
member_no
from TEMPTbl
group by member_no
;

If I remove the grouping, to see all the results, the query fails. I've tried everything, like select *, select all *

select
member_no
from TEMPTbl
;

This is the error message in Visual Studio when I attempt to execute the query.

Error Source: DevartODBCPostgreSQL.dll
Error Message: ERROR [HY000][Devart][ODBC][PostgreSQL]
Syntax error at end of input at character 23

Below is the whole query, of course member_contribution is my table. I've included character 23 in the error message because no matter how many times I change this, it is always at character 23. Both queries work perfect in pgAdmin4.

drop table if exists TEMPTbl;
CREATE TEMP TABLE TEMPTbl
(
member_no text
)
;
insert into TEMPtbl
select
member_no
from
member_contribution
where payment_received_date between '2021-08-04' and '2021-08-04'
;
select
member_no
from TEMPTbl
;

Any help would be appreciated as I can't use this solution without being able to see the detailed query results.

Hi Devart Forum,

I'm using the ODBC driver for PostgreSQL on an AWS hosted database using SSH security via Jumpcloud. (Don't blame me, not my design.)

The configuration used for the ODBC driver is as follows:

Advanced Settings:
IP Version: IPV4
Protocol: pv20
ODBC behavior: Default

Security: SSH Settings
I've set the host name, port 22, my user name and password and the client private key. I've given a file location on the storage path. Other fields are left blank.

The test connection produces the error message "No pg.hba.conf entry for host (with an IP address, the user, and database name,) SSL off." I'm not sure why the reference to SSL in the error message when the connection is SSH.

I can successfully connect to the database via SSH on pgAdmin4 to write SQL outside of the ODBC, which I need for SSRS.

I'm just not sure where the debug issue lies? Any help would be most appreciated.

John

Encase someone ends up here, I successfully configured the SSH. The company uses Jumpcloud and I found the need was to leave the server public key blank but key in the path.

Hi Evgeniy,

I am getting so close. We have moved my prototype from DEV to UAT and here we unfortunately have SSH security. I am getting the message "Host key not verified." I have given the public and private files different names. Any help very welcome.

Private_id_rsa
id_rsa.pub

Hi Evgeniy,

Thank you so much! The pv20 worked perfect!

John

I've successfully configured the Devart ODBC driver to connect a data source in SSRS.

It works fine with a straightforward select query and returns the data as expected. I was moving down the complexity scale and I tried to get a temp table to work. I cut the query back to its bare essentials and it continues to fail. I tried removing the drop table statement and adding a # to the temp table, as I've done with MSSQL to no improvement.

I am receiving this error message in SSRS:

ERROR [HY000] [Devart][ODBC][PostgreSQL]cannot insert multiple commands into a prepared statement

This is the basic query. My fear is that the driver doesn't support temp tables in postgreSQL. Has anyone found a way to use temp tables via the ODBC driver in SSRS? All help would be welcome.

--Date query
drop table if exists daterange;
CREATE TEMP TABLE daterange(
runDate date
)
;
insert into daterange
SELECT
CURRENT_DATE
;
select * from daterange;