Thread: Query with hexadecimal characters
Hello! I am having a problem doing a query with hexadecimal characters through ODBC. The hexadecimal characters are inside a character filed. It would be something like: SELECT * FROM MYTABLE WHERE COLUMN1 < 'XXXXXX' but instead of the X's there are x'FF' characters... The query is accepted, but the result is not consistent... it returns an empty result set. Does anyone know if I'm missing something? Thanks in advance! Luciano __________________________________ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail
Luciano <ldelio@yahoo.com> writes:
> I am having a problem doing a query with hexadecimal
> characters through ODBC. The hexadecimal characters
> are inside a character filed. It would be something
> like:
> SELECT * FROM MYTABLE WHERE COLUMN1 < 'XXXXXX'
> but instead of the X's there are x'FF' characters...
> The query is accepted, but the result is not
> consistent... it returns an empty result set. Does
> anyone know if I'm missing something?
I suspect you are spitting byte sequences at the backend that are
illegal in the character set encoding you've selected. Check your
locale and database encoding setup. C/SQL_ASCII is the best bet
if you want to pretend that you can store any random byte sequence
in a character field.
Plan B, which would probably be better in the long run, is to store
that data in a bytea field instead, and send escaped characters
(ie \\377 rather than an actual FF byte).
regards, tom lane
Eric and Tom: thank you very much for your help, I will try your suggestions tomorrow at work. The sql statements are issued from a COBOL program, which can also issue the same statements to other databases (MySQL, MS SQLServer), so the first thing Im going to do is trying to change the encoding setup, which would be the most homogeneous solution that wouldn't interfere with the other databases, that currently handle well those strings with hexadecimal data. Thank u very much again! Luciano. --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Luciano <ldelio@yahoo.com> writes: > > I am having a problem doing a query with > hexadecimal > > characters through ODBC. The hexadecimal > characters > > are inside a character filed. It would be > something > > like: > > > SELECT * FROM MYTABLE WHERE COLUMN1 < 'XXXXXX' > > > but instead of the X's there are x'FF' > characters... > > The query is accepted, but the result is not > > consistent... it returns an empty result set. Does > > anyone know if I'm missing something? > > I suspect you are spitting byte sequences at the > backend that are > illegal in the character set encoding you've > selected. Check your > locale and database encoding setup. C/SQL_ASCII is > the best bet > if you want to pretend that you can store any random > byte sequence > in a character field. > > Plan B, which would probably be better in the long > run, is to store > that data in a bytea field instead, and send escaped > characters > (ie \\377 rather than an actual FF byte). > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > __________________________________ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250
> Eric and Tom: thank you very much for your help, I > will try your suggestions tomorrow at work. > The sql statements are issued from a COBOL program, > which can also issue the same statements to other > databases (MySQL, MS SQLServer), so the first thing Im > going to do is trying to change the encoding setup, > which would be the most homogeneous solution that > wouldn't interfere with the other databases, that > currently handle well those strings with hexadecimal > data. > > Thank u very much again! > Luciano. Just curious: what COBOL vendor/compiler are you using? Merlin
I am not sure you are aware but COBOL will cause
brain damage if used for an extended period of time
Jim
----- Original Message -----
From: "Merlin Moncure" <merlin.moncure@rcsonline.com>
To: "Luciano" <ldelio@yahoo.com>
Cc: <pgsql-odbc@postgresql.org>
Sent: Wednesday, January 12, 2005 9:33 AM
Subject: Re: [ODBC] Query with hexadecimal characters
> Eric and Tom: thank you very much for your help, I
> will try your suggestions tomorrow at work.
> The sql statements are issued from a COBOL program,
> which can also issue the same statements to other
> databases (MySQL, MS SQLServer), so the first thing Im
> going to do is trying to change the encoding setup,
> which would be the most homogeneous solution that
> wouldn't interfere with the other databases, that
> currently handle well those strings with hexadecimal
> data.
>
> Thank u very much again!
> Luciano.
Just curious: what COBOL vendor/compiler are you using?
Merlin
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
James M Doherty PGADMIN wrote: > I am not sure you are aware but COBOL will cause > brain damage if used for an extended period of time > Yep that is where all the Y2K stuff came from. People had been using Cobol for too long to actually program correctly ;) Don't even get me started on all the damage Java is doing to once young, inspired and productive programmers. Sincerely, Joshua D. Drake > Jim > ----- Original Message ----- From: "Merlin Moncure" > <merlin.moncure@rcsonline.com> > To: "Luciano" <ldelio@yahoo.com> > Cc: <pgsql-odbc@postgresql.org> > Sent: Wednesday, January 12, 2005 9:33 AM > Subject: Re: [ODBC] Query with hexadecimal characters > >> Eric and Tom: thank you very much for your help, I >> will try your suggestions tomorrow at work. >> The sql statements are issued from a COBOL program, >> which can also issue the same statements to other >> databases (MySQL, MS SQLServer), so the first thing Im >> going to do is trying to change the encoding setup, >> which would be the most homogeneous solution that >> wouldn't interfere with the other databases, that >> currently handle well those strings with hexadecimal >> data. >> >> Thank u very much again! >> Luciano. > > > Just curious: what COBOL vendor/compiler are you using? > > Merlin > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com
Attachment
At the moment we are using RM/Cobol, which is a Liant's product. Some time ago we also used Microfocus's Merant Cobol, which is quite much better, but extremely expensive... --- Merlin Moncure <merlin.moncure@rcsonline.com> wrote: > > Eric and Tom: thank you very much for your help, I > > will try your suggestions tomorrow at work. > > The sql statements are issued from a COBOL > program, > > which can also issue the same statements to other > > databases (MySQL, MS SQLServer), so the first > thing Im > > going to do is trying to change the encoding > setup, > > which would be the most homogeneous solution that > > wouldn't interfere with the other databases, that > > currently handle well those strings with > hexadecimal > > data. > > > > Thank u very much again! > > Luciano. > > Just curious: what COBOL vendor/compiler are you > using? > > Merlin > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > __________________________________ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com