PostGres ODBC team,
I'd like to start by thank you for the work that you've done on this project.
For what it's worth, I'd been tasked by my work with migrating a reporting server with extensive SSRS projects with PostGres queries using the ODBC client.
I'm not sure if this is expected behavior or a bug, but I had noticed that there's an issue in pulling prepared queries with multiple ordinal parameters.
The old server we are using has the 9.03.03 drivers installed, and allows multiple ordinal parameters to be passed to a PostGres ODBC prepared statement ($1, $2, $3...) I confirmed this with the following PowerShell routine (which I'd genericized for purposes of passing along):
$DB1ConnString = "Driver={PostgreSQL ANSI};Server=192.168.0.1;Database=dbname;"
$DB1Conn = New-Object System.Data.Odbc.OdbcConnection
$DB1Conn.ConnectionString = $DB1ConnString
$DBCmd = $DB1Conn.CreateCommand()
$DBCmd.Parameters.Add("@Param", [System.Data.SqlDbType]'VarChar')
$DBCmd.Parameters["@Param"].Value = "fieldval"
$DBCmd.CommandText = "select count(*) from tablename where fieldname=`$1"
$DBCmd.Connection.Open()
$DB1Results = $DBCmd.ExecuteReader()
while ($DB1Results.Read()){
write-host $DB1Results.GetString(0);
}
$DBCmd.Connection.Close()
The new server we are using had the 11.01.00 drivers and does not allow the same procedure to be pulled. It returns the following PostgreSQL error code:
"ERROR [42P02] Parameter $1 does not exist"
I was able to get around the issue in our environment by downgrading the ODBC drivers, but I wanted to pass this along in case this was an undocumented bug.
Thank you for your time, and please feel free to let me know if there's anything else I can help with for my part.
Respectfully,
Joseph M. Pence