Werner Kuhnle | 3 Sep 11:00 2010

Problem with simple SELECT over ODBC

Hello,
 
I am completely new to PostgreSQL and wanted to evaluate PostgreSQL as a substitute for MS SQL.
Therefore I installed PG 9 RC and ODBC driver 8.04.02 (ANSI)
The program (EXE) calling the ODBC driver (Windows 32bit written in VC++) is using ANSI strings, not Unicode.
 
When I try the following simple SELECT :
 
SELECT Kurzzeichen,Name_Feld,EURO_Umr_Faktor,NKStellen,RowTimestamp,RowUserID,RowDDMFields,RowID FROM Waehrungen ORDER BY Kurzzeichen
 
from within pgAdminIII then it works well.
 
When I use exactly the same query with MS SQL and the SQL Server Native Client 10.0 ODBC Driver
it also works well.
 
But when exactly the same SELECT is issued to PG over the PG ODBC driver then I get :

SQLRetcode=-1
SqlState=42601, NativeError=7, MessageText=ERROR; syntax error at »,«;
 
Playing around some time with ENCODING=WIN1252 instead of UTF-8 and trying the UNICODE odbc driver instead of the ANSI driver gives the following results:
 
With the UNICODE driver the message does not occur, but the data returned is completely corrupted.
 
With ENCODING=WIN1252 the message is slightly different:
SQLRetcode=-1
SqlState=42601, NativeError=7, MessageText=ERROR; syntax error at »,«;
 
What's wrong here ?
 
Thanks in advance for any help/hint.
 
Greetings
 
Werner
 

 
Tom Lane | 3 Sep 16:35 2010
Picon

Re: Problem with simple SELECT over ODBC

"Werner Kuhnle" <wek <at> kuhnle.com> writes:
> But when exactly the same SELECT is issued to PG over the PG ODBC driver then I get :

> SQLRetcode=-1
> SqlState=42601, NativeError=7, MessageText=ERROR; syntax error at »,«;

> Playing around some time with ENCODING=WIN1252 instead of UTF-8 and trying the UNICODE odbc driver
instead of the ANSI driver gives the following results:

> With the UNICODE driver the message does not occur, but the data returned is completely corrupted.

> With ENCODING=WIN1252 the message is slightly different:
> SQLRetcode=-1
> SqlState=42601, NativeError=7, MessageText=ERROR; syntax error at »,«;

> What's wrong here ?

No idea, but the first thing I'd try is looking into the server's log to
see what it thought the received query was.  Anything that generates an
error would be logged by default.

			regards, tom lane

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Werner Kuhnle | 3 Sep 16:52 2010

Re-2: Problem with simple SELECT over ODBC


Hi Tom,

thanks for your reply.

the log contains :
"
2010-09-03 13:40:04 CEST FEHLER: Syntaxfehler bei »,« bei Zeichen 1
2010-09-03 13:40:04 CEST ANWEISUNG: , "ctid" SELECT
Kurzzeichen,Name_Feld,EURO_Umr_Faktor,NKStellen,Ro wTimestamp,RowUserID,RowDDMFields,RowID
FROM Waehrungen ORDER BY Kurzzeichen
"
but I don't know what the ', "ctid" ' means and why it is in the log; my sql quere does not contain this; it
definitely begins with "SELECT"

Regards

Werner

To: tgl <at> sss.pgh.pa.us
Cc: pgsql-odbc <at> postgresql.org

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Tom Lane | 3 Sep 17:23 2010
Picon

Re: Re-2: Problem with simple SELECT over ODBC

"Werner Kuhnle" <wek <at> kuhnle.com> writes:
> the log contains :
> "
> 2010-09-03 13:40:04 CEST FEHLER: Syntaxfehler bei »,« bei Zeichen 1
> 2010-09-03 13:40:04 CEST ANWEISUNG: , "ctid" SELECT
Kurzzeichen,Name_Feld,EURO_Umr_Faktor,NKStellen,Ro wTimestamp,RowUserID,RowDDMFields,RowID
FROM Waehrungen ORDER BY Kurzzeichen
> "
> but I don't know what the ', "ctid" ' means and why it is in the log; my sql quere does not contain this; it
definitely begins with "SELECT"

Ah-hah.  The ODBC driver is inserting that because it would like to see
the ctid system column in the result.  It's evidently getting confused
and inserting it in the wrong place.  Anybody on the list recognize this
misbehavior?

			regards, tom lane

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Hiroshi Inoue | 3 Sep 23:53 2010
Picon

Re: Problem with simple SELECT over ODBC

(2010/09/03 18:00), Werner Kuhnle wrote:
>
> Hello,
> I am completely new to PostgreSQL and wanted to evaluate PostgreSQL as a
> substitute for MS SQL.
> Therefore I installed PG 9 RC and ODBC driver 8.04.02 (ANSI)
> The program (EXE) calling the ODBC driver (Windows 32bit written in
> VC++) is using ANSI strings, not Unicode.
> When I try the following simple SELECT :
> SELECT
> Kurzzeichen,Name_Feld,EURO_Umr_Faktor,NKStellen,RowTimestamp,RowUserID,RowDDMFields,RowID
> FROM Waehrungen ORDER BY Kurzzeichen
> from within pgAdminIII then it works well.
> When I use exactly the same query with MS SQL and the SQL Server Native
> Client 10.0 ODBC Driver
> it also works well.
> But when exactly the same SELECT is issued to PG over the PG ODBC driver
> then I get :
>
> SQLRetcode=-1
> SqlState=42601, NativeError=7, MessageText=ERROR; syntax error at »,«;

Could you send me directly the Mylog output?

> Playing around some time with ENCODING=WIN1252 instead of UTF-8 and
> trying the UNICODE odbc driver instead of the ANSI driver gives the
> following results:
> With the UNICODE driver the message does not occur, but the data
> returned is completely corrupted.

What do you mean by "corrupted"?
The Unicode driver returns the results in UTF16 strings.

regards,
Hiroshi Inoue

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Hiroshi Inoue | 5 Sep 23:26 2010
Picon

Re: Re-2: Problem with simple SELECT over ODBC

Hi Werner,

(2010/09/04 23:18), Werner Kuhnle wrote:
> Hi Hiroshi,
>
>>
>> Could you send me directly the Mylog output?
>>
> What do you mean with "Mylog output" ?
> The log entries in pg_log\postgresql*.log are:
> "
> 2010-09-03 13:40:04 CEST FEHLER:  Syntaxfehler bei »,« bei Zeichen 1
> 2010-09-03 13:40:04 CEST ANWEISUNG:  , "ctid" SELECT
Kurzzeichen,Name_Feld,EURO_Umr_Faktor,NKStellen,RowTimestamp,RowUserID,RowDDMFields,RowID
FROM Waehrungen ORDER BY Kurzzeichen
> "
> but I don't know what the ', "ctid" ' means and why it is in the log; my sql query does not contain this; it
definitely begins with "SELECT"

OK could you please try the drivers on testing for 8.4.0202 at
  http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
?

regards,
Hiroshi Inoue

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Kilburn Abrahams | 7 Sep 13:50 2010
Picon

Build problems with unixodbc 2.3

  Hi

I am getting build erros with psqlodbc 08.04.200 and unixodbc 2.3.0. 
This is on gentoo 64 bit.

./configure  --with-libpq=/usr/include/postgresql --disable-pthreads 
--enable-unicode --enable-openssl --with-unixodbc
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether to enable maintainer-specific portions of Makefiles... no
checking for odbc_config... /usr/bin/odbc_config
configure: using /usr/include -L/usr/lib64 -lodbc
checking for style of include used by make... GNU
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ANSI C... none needed
checking dependency style of gcc... gcc3
checking for pg_config... /usr/bin/pg_config
checking for gcc... (cached) gcc
checking whether we are using the GNU C compiler... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for gcc option to accept ANSI C... (cached) none needed
checking dependency style of gcc... (cached) gcc3
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for a sed that does not truncate output... /bin/sed
checking for egrep... grep -E
checking for ld used by gcc... /usr/x86_64-pc-linux-gnu/bin/ld
checking if the linker (/usr/x86_64-pc-linux-gnu/bin/ld) is GNU ld... yes
checking for /usr/x86_64-pc-linux-gnu/bin/ld option to reload object 
files... -r
checking for BSD-compatible nm... /usr/bin/nm -B
checking whether ln -s works... yes
checking how to recognise dependent libraries... pass_all
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking dlfcn.h usability... yes
checking dlfcn.h presence... yes
checking for dlfcn.h... yes
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking dependency style of g++... gcc3
checking how to run the C++ preprocessor... g++ -E
checking for g77... no
checking for f77... no
checking for xlf... no
checking for frt... no
checking for pgf77... no
checking for fort77... no
checking for fl32... no
checking for af77... no
checking for f90... no
checking for xlf90... no
checking for pgf90... no
checking for epcf90... no
checking for f95... no
checking for fort... no
checking for xlf95... no
checking for ifc... no
checking for efc... no
checking for pgf95... no
checking for lf95... no
checking for gfortran... gfortran
checking whether we are using the GNU Fortran 77 compiler... yes
checking whether gfortran accepts -g... yes
checking the maximum length of command line arguments... 32768
checking command to parse /usr/bin/nm -B output from gcc object... ok
checking for objdir... .libs
checking for ar... ar
checking for ranlib... ranlib
checking for strip... strip
checking if gcc static flag  works... yes
checking if gcc supports -fno-rtti -fno-exceptions... no
checking for gcc option to produce PIC... -fPIC
checking if gcc PIC flag -fPIC works... yes
checking if gcc supports -c -o file.o... yes
checking whether the gcc linker (/usr/x86_64-pc-linux-gnu/bin/ld -m 
elf_x86_64) supports shared libraries... yes
checking whether -lc should be explicitly linked in... no
checking dynamic linker characteristics... cat: ld.so.conf.d/*.conf: No 
such file or directory
GNU/Linux ld.so
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking for shl_load... no
checking for shl_load in -ldld... no
checking for dlopen... yes
checking whether a program can dlopen itself... yes
checking whether a statically linked program can dlopen itself... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... no
configure: creating libtool
appending configuration tag "CXX" to libtool
checking for ld used by g++... /usr/x86_64-pc-linux-gnu/bin/ld -m 
elf_x86_64
checking if the linker (/usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64) 
is GNU ld... yes
checking whether the g++ linker (/usr/x86_64-pc-linux-gnu/bin/ld -m 
elf_x86_64) supports shared libraries... yes
checking for g++ option to produce PIC... -fPIC
checking if g++ PIC flag -fPIC works... yes
checking if g++ supports -c -o file.o... yes
checking whether the g++ linker (/usr/x86_64-pc-linux-gnu/bin/ld -m 
elf_x86_64) supports shared libraries... yes
checking dynamic linker characteristics... cat: ld.so.conf.d/*.conf: No 
such file or directory
GNU/Linux ld.so
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking for shl_load... (cached) no
checking for shl_load in -ldld... (cached) no
checking for dlopen... (cached) yes
checking whether a program can dlopen itself... (cached) yes
checking whether a statically linked program can dlopen itself... 
(cached) yes
appending configuration tag "F77" to libtool
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... no
checking for gfortran option to produce PIC... -fPIC
checking if gfortran PIC flag -fPIC works... yes
checking if gfortran supports -c -o file.o... yes
checking whether the gfortran linker (/usr/x86_64-pc-linux-gnu/bin/ld -m 
elf_x86_64) supports shared libraries... yes
checking dynamic linker characteristics... cat: ld.so.conf.d/*.conf: No 
such file or directory
GNU/Linux ld.so
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking for library containing SQLGetPrivateProfileString... -lodbcinst
checking for PQconnectdb in -lpq... yes
checking for SSL_read in -lssl... yes
checking locale.h usability... yes
checking locale.h presence... yes
checking for locale.h... yes
checking sys/un.h usability... yes
checking sys/un.h presence... yes
checking for sys/un.h... yes
checking sys/time.h usability... yes
checking sys/time.h presence... yes
checking for sys/time.h... yes
checking libpq-fe.h usability... yes
checking libpq-fe.h presence... yes
checking for libpq-fe.h... yes
checking openssl/ssl.h usability... yes
checking openssl/ssl.h presence... yes
checking for openssl/ssl.h... yes
checking whether time.h and sys/time.h may both be included... yes
checking for long... yes
checking size of long... 8
checking for void *... yes
checking size of void *... 8
checking for long long... yes
checking for signed char... yes
checking for ssize_t... yes
checking for size_t... yes
checking whether struct tm is in sys/time.h or time.h... time.h
checking for struct addrinfo... yes
checking for an ANSI C-conforming const... yes
checking whether strerror_r is declared... yes
checking for strerror_r... yes
checking whether strerror_r returns char *... no
checking for strtoul... yes
checking for strtoll... yes
checking for iswascii... no
checking for lt_dlopen in -lltdl... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating config.h
config.status: executing depfiles commands

make error:

odbcapi.c: In function 'SQLTablePrivileges':
odbcapi.c:1594: warning: pointer targets in passing argument 2 of 
'make_lstring_ifneeded' differ in signedness
misc.h:133: note: expected 'const char *' but argument is of type 
'SQLCHAR *'
odbcapi.c:1596: warning: pointer targets in assignment differ in signedness
odbcapi.c:1599: warning: pointer targets in passing argument 2 of 
'make_lstring_ifneeded' differ in signedness
misc.h:133: note: expected 'const char *' but argument is of type 
'SQLCHAR *'
odbcapi.c:1601: warning: pointer targets in assignment differ in signedness
odbcapi.c:1604: warning: pointer targets in passing argument 2 of 
'make_lstring_ifneeded' differ in signedness
misc.h:133: note: expected 'const char *' but argument is of type 
'SQLCHAR *'
odbcapi.c:1606: warning: pointer targets in assignment differ in signedness
make[1]: *** [odbcapi.lo] Error 1
make[1]: Leaving directory `/root/psqlodbc-08.04.0200'
make: *** [all] Error 2

Any suggestions would appreciated.

Thanks
Kilburn

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Tom Lane | 7 Sep 16:50 2010
Picon

Re: Build problems with unixodbc 2.3

Kilburn Abrahams <kilburna <at> gmail.com> writes:
> I am getting build erros with psqlodbc 08.04.200 and unixodbc 2.3.0. 

> odbcapi.c: In function 'SQLTablePrivileges':
> odbcapi.c:1594: warning: pointer targets in passing argument 2 of 
> 'make_lstring_ifneeded' differ in signedness
> misc.h:133: note: expected 'const char *' but argument is of type 
> 'SQLCHAR *'
> odbcapi.c:1596: warning: pointer targets in assignment differ in signedness
> odbcapi.c:1599: warning: pointer targets in passing argument 2 of 
> 'make_lstring_ifneeded' differ in signedness
> misc.h:133: note: expected 'const char *' but argument is of type 
> 'SQLCHAR *'
> odbcapi.c:1601: warning: pointer targets in assignment differ in signedness
> odbcapi.c:1604: warning: pointer targets in passing argument 2 of 
> 'make_lstring_ifneeded' differ in signedness
> misc.h:133: note: expected 'const char *' but argument is of type 
> 'SQLCHAR *'
> odbcapi.c:1606: warning: pointer targets in assignment differ in signedness
> make[1]: *** [odbcapi.lo] Error 1

You seem to have edited out the actual error :-(.  What you showed is
just warnings, and harmless ones at that.

			regards, tom lane

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Werner Kuhnle | 8 Sep 13:27 2010

Re-4: Problem with simple SELECT over ODBC

Hi Hiroshi,

> OK could you please try the drivers on testing for 8.4.0202 at
>   http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
> ?
> 

Yes, with this version of the drivers (psqlodbc30a.zip 8.4.0202) it seems to work correctly now !

Thanks for your help !

Regards

Werner

To: inoue <at> tpf.co.jp
    pgsql-odbc <at> postgresql.org

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Robert Klaus | 14 Sep 03:11 2010

sspi & odbc

Hello,

 

Question, should it be possible to use sspi authentication in conjunction with odbc?  The sspi is working using psql from a remote (windows) client, but when I try to use an odbc connection, it fails indicating ‘ERROR [08001] authentication method 9 is not supported’.

 

I am running version 8.4 of the server, and 8.04.02 of the Postgresql Unicode ODBC Driver.

 

Thanks for any assistance or insights

 

-Rob

 


Gmane