Обсуждение: Scratching my head why results are different between machines.
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
On 3/3/2010 3:40 PM, Michael Gould wrote: > 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 ourproprietary 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 informationproprietary 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 thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
"Justin Graf" <justin@magwerks.com> wrote: > On 3/3/2010 3:40 PM, Michael Gould wrote: >> 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
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
On 3/3/2010 5:16 PM, Michael Gould wrote:
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.
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.
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" <justin@magwerks.com> wrote:
On 3/3/2010 5:16 PM, Michael Gould wrote: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, Managing Partner
Intermodal Software Solutions, LLC
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
Michael Gould wrote: > 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. > 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" > 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
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > 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 greg@turnstep.com PGP Key: 0x14964AC8 201003032043 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkuPEHoACgkQvJuQZxSWSsj0XQCg07IhnWKKEK1YRNevteSdGv/z joMAoMJuGLwVsuybmumGgLkYe6yJyyGY =9NGC -----END PGP SIGNATURE-----
Greg Sabino Mullane wrote: > 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.
On Wed, Mar 3, 2010 at 6:45 PM, Greg Sabino Mullane <greg@turnstep.com> wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > >> 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.
On Wed, Mar 3, 2010 at 6:54 PM, John R Pierce <pierce@hogranch.com> wrote: > Greg Sabino Mullane wrote: >> >> 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.
On Thu, Mar 4, 2010 at 1:54 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Wed, Mar 3, 2010 at 6:45 PM, Greg Sabino Mullane <greg@turnstep.com> wrote: >> >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: RIPEMD160 >> >> >>> 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. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do
On 04/03/10 01:35, Craig Ringer wrote: > 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 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." -- Richard Huxton Archonet Ltd
On 3/4/2010 3:51 AM, Richard Huxton wrote: > On 04/03/10 01:35, Craig Ringer wrote: >> >> 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 ourproprietary 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 informationproprietary 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 thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
On Thu, Mar 4, 2010 at 2:14 PM, Justin Graf <justin@magwerks.com> wrote: > 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. -- greg
On 3/4/2010 10:00 AM, Greg Stark wrote: > On Thu, Mar 4, 2010 at 2:14 PM, Justin Graf<justin@magwerks.com> wrote: > >> 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 ourproprietary 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 informationproprietary 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 thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
This reminds me of the joke, How many MS employees does it take to change a lightbulb? None, they just redefine dark as light.