PostgreSQL ODBC driver using SSRS with temp tables

[johnmatic]

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;

[evgeniym]

Hi John,
In order to use multiple commands please set Protocol = pv20 in settings of your ODBC Driver for PostgreSQL.

Regards,
Evgeniy

[johnmatic]

Hi Evgeniy,

Thank you so much! The pv20 worked perfect!

John

[evgeniym]

Hi John,
I’m glad that your issue is resolved and you can use all features of the software. Please feel free to contact us anytime in case you have any other questions.

Regards,
Evgeniy

[johnmatic]

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

[johnmatic]

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.

[evgeniym]

Hi John,

You may find information about how to set up SSH connection for ODBC Driver for PostgreSQL in our online documentation:

https://www.devart.com/odbc/postgresql/docs/ssh.htm

If you face any issues with setting up SSH connection according to instructions from documentation please feel free to contact us back.

Best regards,
Evgeniy