PostGres ODBC -- Multiple ordinal parameters ?
Joe Pence <[email protected]> Fri, 31 May 2019 22:49:33 -0400
| Newsgroups | gmane.comp.db.postgresql.odbc |
|---|---|
| Message-ID | <CAFm2tT=81VCkKaE85vGLzBheVm9+MF3Lgr6cF2hpP=RWSSVx1g@mail.gmail.com> |
--0000000000002af104058a3a2ee1
Content-Type: text/plain; charset="UTF-8"
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*
--0000000000002af104058a3a2ee1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div class=3D"gmail_quote">PostGres ODBC team,</div><div c=
lass=3D"gmail_quote"><br></div><div class=3D"gmail_quote">I'd like to s=
tart by thank you for the work that you've done on this project.=C2=A0<=
/div><div class=3D"gmail_quote"><br></div><div class=3D"gmail_quote">For wh=
at it's worth, I'd been tasked by my work with migrating a reportin=
g server with extensive SSRS projects with PostGres queries using the ODBC =
client.</div><div class=3D"gmail_quote"><br></div><div class=3D"gmail_quote=
">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 ordina=
l parameters.</div><div class=3D"gmail_quote"><br></div><div class=3D"gmail=
_quote">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 r=
outine (which I'd genericized for purposes of passing along):</div><div=
class=3D"gmail_quote"><br></div><div class=3D"gmail_quote"><p class=3D"Mso=
Normal" style=3D"background-image:initial;background-position:initial;backg=
round-size:initial;background-repeat:initial;background-origin:initial;back=
ground-clip:initial"><span style=3D"font-size:9pt;font-family:"Lucida =
Console";color:rgb(168,45,0)">$DB1ConnString</span><span style=3D"font=
-size:9pt;font-family:"Lucida Console"">=C2=A0<span style=3D"colo=
r:dimgray">=3D</span>=C2=A0<span style=3D"color:darkred">"Driver=3D{Po=
stgreSQL ANSI};Server=3D192.168.0.1;Database=3Ddbname;"</span><u></u><=
u></u></span></p><p class=3D"MsoNormal" style=3D"background-image:initial;b=
ackground-position:initial;background-size:initial;background-repeat:initia=
l;background-origin:initial;background-clip:initial"><span style=3D"font-si=
ze:9pt;font-family:"Lucida Console";color:rgb(168,45,0)">$DB1Conn=
</span><span style=3D"font-size:9pt;font-family:"Lucida Console""=
>=C2=A0<span style=3D"color:dimgray">=3D</span>=C2=A0<span style=3D"color:b=
lue">New-Object</span>=C2=A0<span style=3D"color:blueviolet">System.Data.Od=
bc.OdbcConnection</span><u></u><u></u></span></p><p class=3D"MsoNormal" sty=
le=3D"background-image:initial;background-position:initial;background-size:=
initial;background-repeat:initial;background-origin:initial;background-clip=
:initial"><span style=3D"font-size:9pt;font-family:"Lucida Console&quo=
t;;color:rgb(168,45,0)">$DB1Conn</span><span style=3D"font-size:9pt;font-fa=
mily:"Lucida Console";color:dimgray">.</span><span style=3D"font-=
size:9pt;font-family:"Lucida Console"">ConnectionString=C2=A0<spa=
n style=3D"color:dimgray">=3D</span>=C2=A0<span style=3D"color:rgb(168,45,0=
)">$DB1ConnString</span><u></u><u></u></span></p><p class=3D"MsoNormal" sty=
le=3D"background-image:initial;background-position:initial;background-size:=
initial;background-repeat:initial;background-origin:initial;background-clip=
:initial"><span style=3D"font-size:9pt;font-family:"Lucida Console&quo=
t;;color:rgb(168,45,0)">$DBCmd</span><span style=3D"font-size:9pt;font-fami=
ly:"Lucida Console"">=C2=A0<span style=3D"color:dimgray">=3D</spa=
n>=C2=A0<span style=3D"color:rgb(168,45,0)">$DB1Conn</span><span style=3D"c=
olor:dimgray">.</span>CreateCommand()<u></u><u></u></span></p><p class=3D"M=
soNormal" style=3D"background-image:initial;background-position:initial;bac=
kground-size:initial;background-repeat:initial;background-origin:initial;ba=
ckground-clip:initial"><span style=3D"font-size:9pt;font-family:"Lucid=
a Console";color:rgb(168,45,0)">$DBCmd</span><span style=3D"font-size:=
9pt;font-family:"Lucida Console";color:dimgray">.</span><span sty=
le=3D"font-size:9pt;font-family:"Lucida Console"">Parameters<span=
style=3D"color:dimgray">.</span>Add(<span style=3D"color:darkred">"@P=
aram"</span><span style=3D"color:dimgray">,</span>=C2=A0<span style=3D=
"color:dimgray">[</span><span style=3D"color:rgb(0,97,97)">System.Data.SqlD=
bType</span><span style=3D"color:dimgray">]</span><span style=3D"color:dark=
red">'VarChar'</span>)<u></u><u></u></span></p><p class=3D"MsoNorma=
l" style=3D"background-image:initial;background-position:initial;background=
-size:initial;background-repeat:initial;background-origin:initial;backgroun=
d-clip:initial"><span style=3D"font-size:9pt;font-family:"Lucida Conso=
le";color:rgb(168,45,0)">$DBCmd</span><span style=3D"font-size:9pt;fon=
t-family:"Lucida Console";color:dimgray">.</span><span style=3D"f=
ont-size:9pt;font-family:"Lucida Console"">Parameters<span style=
=3D"color:dimgray">[</span><span style=3D"color:darkred">"@Param"=
</span><span style=3D"color:dimgray">].</span>Value=C2=A0<span style=3D"col=
or:dimgray">=3D</span>=C2=A0<span style=3D"color:darkred">"fieldval&qu=
ot;</span><u></u><u></u></span></p><p class=3D"MsoNormal" style=3D"backgrou=
nd-image:initial;background-position:initial;background-size:initial;backgr=
ound-repeat:initial;background-origin:initial;background-clip:initial"><spa=
n style=3D"font-size:9pt;font-family:"Lucida Console";color:rgb(1=
68,45,0)">$DBCmd</span><span style=3D"font-size:9pt;font-family:"Lucid=
a Console";color:dimgray">.</span><span style=3D"font-size:9pt;font-fa=
mily:"Lucida Console"">CommandText=C2=A0<span style=3D"color:dimg=
ray">=3D</span>=C2=A0<span style=3D"color:darkred">"select count(*) fr=
om tablename where fieldname=3D`$1"</span><u></u><u></u></span></p><p =
class=3D"MsoNormal" style=3D"background-image:initial;background-position:i=
nitial;background-size:initial;background-repeat:initial;background-origin:=
initial;background-clip:initial"><span style=3D"font-size:9pt;font-family:&=
quot;Lucida Console";color:rgb(168,45,0)">$DBCmd</span><span style=3D"=
font-size:9pt;font-family:"Lucida Console";color:dimgray">.</span=
><span style=3D"font-size:9pt;font-family:"Lucida Console"">Conne=
ction<span style=3D"color:dimgray">.</span>Open()<u></u><u></u></span></p><=
p class=3D"MsoNormal" style=3D"background-image:initial;background-position=
:initial;background-size:initial;background-repeat:initial;background-origi=
n:initial;background-clip:initial"><span style=3D"font-size:9pt;font-family=
:"Lucida Console";color:rgb(168,45,0)">$DB1Results</span><span st=
yle=3D"font-size:9pt;font-family:"Lucida Console"">=C2=A0<span st=
yle=3D"color:dimgray">=3D</span>=C2=A0<span style=3D"color:rgb(168,45,0)">$=
DBCmd</span><span style=3D"color:dimgray">.</span>ExecuteReader()<u></u><u>=
</u></span></p><p class=3D"MsoNormal" style=3D"background-image:initial;bac=
kground-position:initial;background-size:initial;background-repeat:initial;=
background-origin:initial;background-clip:initial"><span style=3D"font-size=
:9pt;font-family:"Lucida Console";color:darkblue">while</span><sp=
an style=3D"font-size:9pt;font-family:"Lucida Console"">=C2=A0(<s=
pan style=3D"color:rgb(168,45,0)">$DB1Results</span><span style=3D"color:di=
mgray">.</span>Read()){<u></u><u></u></span></p><p class=3D"MsoNormal" styl=
e=3D"background-image:initial;background-position:initial;background-size:i=
nitial;background-repeat:initial;background-origin:initial;background-clip:=
initial"><span style=3D"font-size:9pt;font-family:"Lucida Console"=
;">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0<span style=3D"color:blue">wri=
te-host</span>=C2=A0<span style=3D"color:rgb(168,45,0)">$DB1Results</span><=
span style=3D"color:dimgray">.</span>GetString(<span style=3D"color:purple"=
>0</span>);<u></u><u></u></span></p><p class=3D"MsoNormal" style=3D"backgro=
und-image:initial;background-position:initial;background-size:initial;backg=
round-repeat:initial;background-origin:initial;background-clip:initial"><sp=
an style=3D"font-size:9pt;font-family:"Lucida Console"">}<u></u><=
u></u></span></p><p class=3D"MsoNormal" style=3D"background-image:initial;b=
ackground-position:initial;background-size:initial;background-repeat:initia=
l;background-origin:initial;background-clip:initial"><span style=3D"font-si=
ze:9pt;font-family:"Lucida Console";color:rgb(168,45,0)">$DBCmd</=
span><span style=3D"font-size:9pt;font-family:"Lucida Console";co=
lor:dimgray">.</span><span style=3D"font-size:9pt;font-family:"Lucida =
Console"">Connection<span style=3D"color:dimgray">.</span>Close()</spa=
n></p></div><div class=3D"gmail_quote"><br></div><div class=3D"gmail_quote"=
><br></div><div class=3D"gmail_quote">The new server we are using had the 1=
1.01.00 drivers and does not allow the same procedure to be pulled. It retu=
rns the following PostgreSQL error code:</div><div class=3D"gmail_quote"><d=
iv lang=3D"EN-US"><div class=3D"gmail-m_-719394927366617957m_-4532933562996=
847062WordSection1"><p class=3D"MsoNormal" style=3D"background-image:initia=
l;background-position:initial;background-size:initial;background-repeat:ini=
tial;background-origin:initial;background-clip:initial"><span style=3D"font=
-size:9pt;font-family:"Lucida Console";color:rgb(168,45,0)"><u></=
u>=C2=A0<u></u></span></p><p class=3D"MsoNormal" style=3D"background-image:=
initial;background-position:initial;background-size:initial;background-repe=
at:initial;background-origin:initial;background-clip:initial"><span style=
=3D"font-size:9pt;font-family:"Lucida Console";color:rgb(168,45,0=
)">"ERROR [42P02] Parameter $1 does not exist"<u></u><u></u></spa=
n></p><p class=3D"MsoNormal" style=3D"background-image:initial;background-p=
osition:initial;background-size:initial;background-repeat:initial;backgroun=
d-origin:initial;background-clip:initial"><span style=3D"color:rgb(168,45,0=
);font-family:"Lucida Console";font-size:9pt">=C2=A0</span><br></=
p><p class=3D"MsoNormal" style=3D"background-image:initial;background-posit=
ion:initial;background-size:initial;background-repeat:initial;background-or=
igin:initial;background-clip:initial">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.=C2=A0</p><p class=3D"MsoNormal=
" style=3D"background-image:initial;background-position:initial;background-=
size:initial;background-repeat:initial;background-origin:initial;background=
-clip:initial"><br></p><p class=3D"MsoNormal" style=3D"background-image:ini=
tial;background-position:initial;background-size:initial;background-repeat:=
initial;background-origin:initial;background-clip:initial">Thank you for yo=
ur time, and please feel free to let me know if there's anything else I=
can help with for my part.</p><p class=3D"MsoNormal" style=3D"background-i=
mage:initial;background-position:initial;background-size:initial;background=
-repeat:initial;background-origin:initial;background-clip:initial"><span st=
yle=3D"font-size:9pt;font-family:"Lucida Console";color:rgb(168,4=
5,0)"><u></u>=C2=A0<u></u></span></p><p class=3D"MsoNormal" style=3D"backgr=
ound-image:initial;background-position:initial;background-size:initial;back=
ground-repeat:initial;background-origin:initial;background-clip:initial">Re=
spectfully,=C2=A0</p><p class=3D"MsoNormal"><b><span style=3D"font-size:10p=
t;font-family:Arial,sans-serif;color:black">Joseph M. Pence</span></b></p><=
/div></div></div></div>
--0000000000002af104058a3a2ee1--