Scratching my head why results are different between machines.

[Michael Gould]

I have several tables in a SQL Anywhere 10 database that I'm converting to
PostgreSQL 8.4.2.  In ASA we did not use any GUID columns but we are in
Postgres.


I have one table that is citystateinfo and the primary key is a UUID and it
is automatically generated using the contrib module oosp-uuid.  That works
fine.


We are also using the citext contrib module on most of our text columns so
that we can do case insensitive searches.  We now have this working
properly.


Here is a simple select that is used to get the UUID primary key for the
citystateinfo table to put into a terminaladdress table.


select "citystateinfoid" as KeyName from iss.citystateinfo where
"cityname"='JACKSONVILLE' and "statecode"='FL' and "zipcode"='32226'.


In PGAdmin this returns the correct uuid in the proper format.


In the program that we're using to convert this data (a commerical product
called DMC), when it is run on the tech support machine at the authors
company, it returns the proper UUID to all columns where there is a matching
row in the citystateinfo table.


On my machine the UUID that is returned is 16 bytes and I cannot make out
any relevant numbers from the UUID key in the citystateinfo table.  I've
tried this in a Windows XP machine and a Windows 7 64 bit. 


Now here is the weird thing.  I did a complete backup of my database and had
the author restore the data on his.  It works every time there.  He then did
a pg_dumpall and I reloaded my db with his script and while his runs all of
the queries fine, mine is still not returning the proper length UUID column.


I'm using the UTF ODBC driver 8.4 and Postgres 8.4.2.


Does anyone have any idea on what could possibly be going on? It's running
find on computers located in other locations but doesn't work properly on
either one of my machines.  It seems to me that it must be enviornmental. 
The author has both Windows 2008 server and Windows 7 64 bit.


Best Regards


Michael Gould

[Justin Graf]

Post by Michael Gould
On my machine the UUID that is returned is 16 bytes and I cannot make
out any relevant numbers from the UUID key in the citystateinfo
table. I've tried this in a Windows XP machine and a Windows 7 64 bit.
Now here is the weird thing. I did a complete backup of my database
and had the author restore the data on his. It works every time
there. He then did a pg_dumpall and I reloaded my db with his script
and while his runs all of the queries fine, mine is still not
returning the proper length UUID column.
I'm using the UTF ODBC driver 8.4 and Postgres 8.4.2.

You state the PgAdmin returns the correct result on your machine
connecting to the database that returns the wrong result using Data
Conversion App, RIGHT???

If so I'm betting it has to do with ODBC driver doing something odd or
the DATA Conversion App is doing something ODD.

What is the name of app being used to convert the data?

Which ODBC are you using ?
http://psqlodbc.projects.postgresql.org/
or
http://projects.commandprompt.com/public/odbcng/

Note: PgAdmin does not use ODBC interface .


All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

[Michael Gould]

Post by Justin Graf

Post by Michael Gould
On my machine the UUID that is returned is 16 bytes and I cannot make
out any relevant numbers from the UUID key in the citystateinfo
table. I've tried this in a Windows XP machine and a Windows 7 64 bit.
Now here is the weird thing. I did a complete backup of my database
and had the author restore the data on his. It works every time
there. He then did a pg_dumpall and I reloaded my db with his script
and while his runs all of the queries fine, mine is still not
returning the proper length UUID column.
I'm using the UTF ODBC driver 8.4 and Postgres 8.4.2.

You state the PgAdmin returns the correct result on your machine
connecting to the database that returns the wrong result using Data
Conversion App, RIGHT???

Any SQL client that can accept and process a query will bring it back
correctly. Also the author of DMC's can run it on his local PostGres server
restored using my backup from this afternoon and he gets the correct results
through DMC.

On my machine I get a UUID that looks like 8BA92F06-BCD6-49. Also I've
noticed that when I bring up the ODBC administrator, I do not see any entry
for the Postgres ODBC driver. I've tried to uninstall the driver, reboot
and reinstall with the same results.

Best Regards

Mike Gould

[Michael Gould]

One thing I've noticed is that on my machines, when I install the odbc
driver I get no error messages but when I look in the ODBC administrator I
do not see any entry for PostGres in the drivers list.

I do know that it somehow is working because the DMC conversion tool only
uses ODBC and builds it's data structures based on what it gets back from
querying the database.

Programs like WinSQL also work and I believe it uses ODBC.

In the past I believe I remember seeing the Postgres driver listed in the
ODBC admin tool under the drivers tab.

I'm running on a Windows 7 64 bit machine and I'm logged into our domain as
the domain administrator. Is there something else I need to do to install
the odbc driver under windows? I've even turned UAC off and it didn't seem
to help

Best Regards

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

[Justin Graf]

Post by Michael Gould
One thing I've noticed is that on my machines, when I install the odbc
driver I get no error messages but when I look in the ODBC administrator I
do not see any entry for PostGres in the drivers list.
I do know that it somehow is working because the DMC conversion tool only
uses ODBC and builds it's data structures based on what it gets back from
querying the database.
Programs like WinSQL also work and I believe it uses ODBC.
In the past I believe I remember seeing the Postgres driver listed in the
ODBC admin tool under the drivers tab.
I'm running on a Windows 7 64 bit machine and I'm logged into our domain as
the domain administrator. Is there something else I need to do to install
the odbc driver under windows? I've even turned UAC off and it didn't seem
to help

Can't help on the Windows 7 ODBC not appearing in the drivers list

If you can manage to configure a Data Source Name turn on the logging

then connect using this DSN in your DMC app just do a simple select

The log will get big quick so keep the selects small and use limit.
This will allow to see what ODBC driver is up to

open the C:\mylog_XXX and look at and you will find all the commands
sent the Postgresql and the results

then you will look for something like this. This will tell us what the
Select looks like and what being sent DMC app.

*[1444-387.642]Exec_with_parameters_resolved: copying statement params:
trans_status=1, len=51, stmt='Select user_password from mediawiki.mwuser
limit 10'*
[1444-387.645] stmt_with_params = 'Select user_password from
mediawiki.mwuser limit 10'
[1444-387.646]about to begin SC_execute
[1444-387.647] Sending SELECT statement on stmt=02C5D8C0,
cursor_name='SQL_CUR02C5D8C0' qflag=0,1
[1444-387.648]CC_send_query: conn=04313E00, query='Select user_password
from mediawiki.mwuser limit 10'
------snip--
*[1444-387.665]qresult: len=44,
buffer=':B:e2e28556:8ff13b68ebc64eccad0921d1571bf08c'*
[1444-387.666]qresult: len=0, buffer=''
*[1444-387.667]qresult: len=44,
buffer=':B:60a118ba:592cb633245cc330d48cc2e52c922f90'*
[1444-387.667]qresult: len=0, buffer=''
[1444-387.668]qresult: len=0, buffer=''
[1444-387.668]qresult: len=0, buffer=''
[1444-387.669]qresult: len=0, buffer=''
[1444-387.669]qresult: len=0, buffer=''
[1444-387.670]qresult: len=0, buffer=''






All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

[Michael Gould]

Is there anyone out there using Windows 7 64 bit with Postgres 8.4.2 and
the psqlodbc driver?  I've tried to install using the one that is available
with the standard windows installer.  I've also tried to install with the
one from EDB.   It appears that the installer finishes the install but when
I look for the drivers there are none in the odbc administrator.  I've
installed and uninstalled a couple of times.


Does anyone have any idea on what is going on here and how to fix the
problem?  I've logged in as the domain adminstrator and the system
administrator for our domain and I've turned UAC off also, but as of yet no
luck.


I know that they are at least partially installed because I can access our
database via two tools that use ODBC as it's access method.  It's just that
they are not showing up in the administrator and this makes me think that
I've got a problem. 


I'm trying to get a data conversion tool to work (was working when I was
using Windows XP) to convert data from SQL Anywhere into PostgreSQL 8.4.2. 
The problem is with UUID columns that are looked up and used in other
tables.  The UUID's are created properly in their parent tables, but the
value isn't correct when it is used as a lookup column.


The author of this product has taken a complete backup of my database and
restored it on his machine. When he runs the conversion routine it produces
the correct data.  The only difference between his machine and mine seems
to be how the odbc driver is installed.  He's using the one he downloaded
from EDB.  He sent it to me, I uninstalled the previous one I had, rebooted
and installed the one he sent me.  Same results, no driver shows up in the
odbc admin.


Best Regards


Michael Gould


 


 


"Justin Graf" <***@magwerks.com> wrote:
 

Post by Michael Gould
One thing I've noticed is that on my machines, when I install the odbc
driver I get no error messages but when I look in the ODBC administrator I
do not see any entry for PostGres in the drivers list.
I do know that it somehow is working because the DMC conversion tool only
uses ODBC and builds it's data structures based on what it gets back from
querying the database.
Programs like WinSQL also work and I believe it uses ODBC.
In the past I believe I remember seeing the Postgres driver listed in the
ODBC admin tool under the drivers tab.
I'm running on a Windows 7 64 bit machine and I'm logged into our domain as
the domain administrator. Is there something else I need to do to install
the odbc driver under windows? I've even turned UAC off and it didn't seem
to help
</pre>

Can't help on the Windows 7  ODBC  not  appearing in the drivers list
If you can manage to configure a Data Source Name  turn on the logging
then connect using this DSN in your DMC app just do a simple select
The log will get big quick so keep the selects small and use limit.  This
will allow to see what ODBC driver is up to
open the C:\mylog_XXX and look at and you will find all the commands sent
the Postgresql and the results 
then you will look for something like this.  This will tell us what the
Select looks like and what being sent DMC app. 
trans_status=1, len=51, stmt='Select user_password from mediawiki.mwuser
limit 10'</b>
[1444-387.645]   stmt_with_params = 'Select user_password from
mediawiki.mwuser limit 10'
[1444-387.646]about to begin SC_execute
[1444-387.647]       Sending SELECT statement on stmt=02C5D8C0,
cursor_name='SQL_CUR02C5D8C0' qflag=0,1
[1444-387.648]CC_send_query: conn=04313E00, query='Select user_password
from mediawiki.mwuser limit 10'
------snip--
<b>[1444-387.665]qresult: len=44,
buffer=':B:e2e28556:8ff13b68ebc64eccad0921d1571bf08c'</b>
[1444-387.666]qresult: len=0, buffer=''
<b>[1444-387.667]qresult: len=44,
buffer=':B:60a118ba:592cb633245cc330d48cc2e52c922f90'</b>
[1444-387.667]qresult: len=0, buffer=''
[1444-387.668]qresult: len=0, buffer=''
[1444-387.668]qresult: len=0, buffer=''
[1444-387.669]qresult: len=0, buffer=''
[1444-387.669]qresult: len=0, buffer=''
[1444-387.670]qresult: len=0, buffer=''
All legitimate Magwerks Corporation quotations are sent in a .PDF file
attachment with a unique ID number generated by our proprietary quotation
system. Quotations received via any other form of communication will not be
honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
legally privileged, confidential or other information proprietary to
Magwerks Corporation and is intended solely for the use of the individual
to whom it addresses. If the reader of this e-mail is not the intended
recipient or authorized agent, the reader is hereby notified that any
unauthorized viewing, dissemination, distribution or copying of this e-mail
is strictly prohibited. If you have received this e-mail in error, please
notify the sender by replying to this message and destroy all occurrences
of this e-mail immediately.
Thank you.

 



--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

[Craig Ringer]

Post by Michael Gould
Does anyone have any idea on what is going on here and how to fix the
problem? I've logged in as the domain adminstrator and the system
administrator for our domain and I've turned UAC off also, but as of yet
no luck.

I'm wondering if you should post to the pgsql-odbc list too, since some
of the ODBC folks don't follow the main list very closely.

Post by Michael Gould
I know that they are at least partially installed because I can access
our database via two tools that use ODBC as it's access method. It's
just that they are not showing up in the administrator and this makes me
think that I've got a problem.

If it's anything like it was in the win16 -> win32 transition, you'll
need to fire up the 32-bit ODBC administrator, as it could be different.

Try running "odbcad32.exe"

Post by Michael Gould
The author of this product has taken a complete backup of my database
and restored it on his machine. [where it works]

Is his machine running windows 7?

Is it 32-bit of 64-bit?

--
Craig Ringer

[Craig Ringer]

Argh - a follow-up re ODBC 32 and 64 bit-ness:

http://blog.danovich.com.au/2010/02/02/odbc-settings-on-64-bit-servers/

It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called "odbcad32.exe" but are in different (but not
distinctively named) locations.

http://support.microsoft.com/kb/942976/en-us

*bangs head against desk*

--
Craig Ringer

[Greg Sabino Mullane]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Post by Craig Ringer
It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called "odbcad32.exe" but are in different (but not
distinctively named) locations.
http://support.microsoft.com/kb/942976/en-us

Wow. If I even had the slightest regret about my move to abandon
Windows years ago, this would have pretty much squashed it.
Magnus et. al., I don't know how you do it. :)

- --
Greg Sabino Mullane ***@turnstep.com
PGP Key: 0x14964AC8 201003032043
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkuPEHoACgkQvJuQZxSWSsj0XQCg07IhnWKKEK1YRNevteSdGv/z
joMAoMJuGLwVsuybmumGgLkYe6yJyyGY
=9NGC
-----END PGP SIGNATURE-----

[John R Pierce]

Post by Greg Sabino Mullane
Wow. If I even had the slightest regret about my move to abandon
Windows years ago, this would have pretty much squashed it.
Magnus et. al., I don't know how you do it. :)

yeah, (l)unix systems/users/apps never get ./lib and ./lib64 mixed up,
oh no.

[Scott Marlowe]

Wow. If I even had the slightest regret about my move to abandon Windows
years ago, this would have pretty much squashed it. Magnus et. al., I don't
know how you do it. :)

yeah, (l)unix systems/users/apps never get ./lib and ./lib64 mixed up, oh
no.

My experience has been that when they get things like wrong (which is
rare) they fix it instead of declaring it the new standard.

[Scott Marlowe]

Post by Greg Sabino Mullane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Post by Craig Ringer
It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called "odbcad32.exe" but are in different (but not
distinctively named) locations.
http://support.microsoft.com/kb/942976/en-us

Wow. If I even had the slightest regret about my move to abandon
Windows years ago, this would have pretty much squashed it.
Magnus et. al., I don't know how you do it. :)

That page has to be an abandoned april fools joke, right? The 64 bit
version is named odbcad32.exe? That has to be a joke of some kind.

[Dave Page]

Post by Greg Sabino Mullane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Post by Craig Ringer
It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called "odbcad32.exe" but are in different (but not
distinctively named) locations.
http://support.microsoft.com/kb/942976/en-us

Wow. If I even had the slightest regret about my move to abandon
Windows years ago, this would have pretty much squashed it.
Magnus et. al., I don't know how you do it. :)

That page has to be an abandoned april fools joke, right?  The 64 bit
version is named odbcad32.exe?  That has to be a joke of some kind.

It's probably related to the fact that the platform API on both 32 and
64 bit platforms is known as win32 (with a more complete name of
'win32 for 64 bit Windows' on 64 bit).

My understanding is that this is because the naming originally came
from the transition from 16 to 32 bit which involved a much bigger API
change. The API on 64 bit is basically the same as on 32 bit, just
with the wider data types, thus it retains the name.

[Richard Huxton]

Post by Craig Ringer
http://blog.danovich.com.au/2010/02/02/odbc-settings-on-64-bit-servers/
It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called "odbcad32.exe" but are in different (but not
distinctively named) locations.
http://support.microsoft.com/kb/942976/en-us

Classy. Even better - according to the linked page, the 64 bit version
is in the "System32" folder - yippee!

" * The 32-bit version of the Odbcad32.exe file is located in the
%systemdrive%\Windows\SysWoW64 folder.
* The 64-bit version of the Odbcad32.exe file is located in the
%systemdrive%\Windows\System32 folder."

[Justin Graf]

Post by Richard Huxton

Post by Craig Ringer
http://support.microsoft.com/kb/942976/en-us

Classy. Even better - according to the linked page, the 64 bit version
is in the "System32" folder - yippee!
" * The 32-bit version of the Odbcad32.exe file is located in the
%systemdrive%\Windows\SysWoW64 folder.
* The 64-bit version of the Odbcad32.exe file is located in the
%systemdrive%\Windows\System32 folder."

Some of the naming conventions for Windows 64 suck. SysWoW64 = System
Windows on Windows 64 where does anyone get the idea this is where the
32bit apps are stored. Maintaining the System32 and using it to store
64 bit apps is Insane.

To pretty much anyone outside MS, a sane human would think 64 bit apps
in SysWoW64 and 32Bit apps in System32. :'(



All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

[Greg Stark]

Post by Justin Graf
To pretty much anyone outside MS, a sane human would think 64 bit apps
in SysWoW64 and 32Bit apps in System32. :'(

Ah, but you all are forgetting that the "32" here is to distinguish it
from the default odbc interface which as i recall was a *16* bit
interface. I'm not sure what exactly that means but as a result the
32-bit odbc configuration is entirely separate from the "regular" odbc
configuration. It's not the instruction set that the dll uses it's
which set of shared data structures it uses and which api it provides.

[Justin Graf]

Post by Greg Stark

Post by Justin Graf
To pretty much anyone outside MS, a sane human would think 64 bit apps
in SysWoW64 and 32Bit apps in System32. :'(

Ah, but you all are forgetting that the "32" here is to distinguish it
from the default odbc interface which as i recall was a *16* bit
interface. I'm not sure what exactly that means but as a result the
32-bit odbc configuration is entirely separate from the "regular" odbc
configuration. It's not the instruction set that the dll uses it's
which set of shared data structures it uses and which api it provides

ON the Way back machine we had System and System32. System32 came
about in Windows 3.1 if memory serves to separate where 16 and 32 bit
are placed. This time MS around is not doing that System32 is being
used for 64bit apps and SysWoW64 is where all the legacy 32 bits at
getting stuck.

32 bit apps in a directory with 64 in its name, and 64 bit apps in
directory thats intention was/is 32bit apps =-O




All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

[Scott Marlowe]

This reminds me of the joke, How many MS employees does it take to
change a lightbulb? None, they just redefine dark as light.