PostGres ODBC -- Multiple ordinal parameters ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

PostGres ODBC -- Multiple ordinal parameters ?

Joe Pence
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

Reply | Threaded
Open this post in threaded view
|

Re: PostGres ODBC -- Multiple ordinal parameters ?

Clemens Ladisch
Joe Pence wrote:
> 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 ODBC specification requires parameter markers to be question marks.

I do not know if ordinal parameter markers (a non-standard PG extension)
are supposed to be supported in the ODBC driver.


Regards,
Clemens