Thread: Problem with Postgres/ODBC Driver.

Problem with Postgres/ODBC Driver.

From
Robert Carbonari
Date:
Hello,
I wrote a script which copies information from an Outlook public folder into
a Postgres database and it didn't work until I made the following changes to
it and to the table under Postgres:
I had to change the names of nearly all the fields so that there were no
more capitals and spaces between fields.  I had originally given real names
the fields to reflect those given to a parallel table under MS SQL server
v.7. I had names like "Project Manager Name" which I entered into the table
with statements like:
CREATE TABLE table_name ("Project Manager Name" text, ...);
I had to alter these names using ALTER TABLE and not use double-quotes, so
that every field was automatically lower-case.
Next, my script has commands like the following (you can ignore all but the
underlined line if you want):
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = 1
'DSN USING POSTGRES/ODBC DRIVER FOR WIN32.
Const DSN = "PostgresContacts"
Call Main()
WScript.Quit (0)
Sub Main'SET OUTLOOK OBJECTS TO REFER TO ITEMS IN PUBLIC FOLDERSet objOutlook = Wscript.CreateObject
("Outlook.Application")SetobjNameSpace = objOutlook.GetNameSpace("MAPI")Set objPublicFolders =
objNameSpace.Folders("PublicFolders")Set objAllPublicFolders = objPublicFolders.Folders("All Public
 
Folders")Set ContactItems = objAllPublicFolders..Folders("Some Contacts")varSQL =  "Select * From
PMStoreContacts"varSQL_Del =  "Delete  From PMStoreContacts "'CREATE ADO RECORDSET OBJECT Set rsChains =
WScript.CreateObject("ADODB.Recordset")rsChains.open varSQL_Del, DSN, adOpenStatic, adLockOptimisticrsChains.open
varSQL,DSN, adOpenStatic, adLockOptimisticFor Each Item in ContactsItems        rsChains.addnew    rsChains("Project
Manager")= Item.SomeField    ...    rsChains.Update    rsChains.movenextnextrsChains.Close'****SETTING OBJECTS FOR
GETTINGOUTLOOK INFORMATION TO NOTHING
 
TO RELEASE THEM FROM MEMORY (sort of...)****Set objOutlook = nothingSet objNameSpace = nothingSet objPublicFolders =
nothingSetobjAllPublicFolders = nothingSet ContactsItems = nothing'****SET RECORDSET OBJECT TO NOTHING********Set
rsChains= Nothing
 
End Sub
Notice that there is a space in rsChains("Project Manager") =
Item.SomeField. 
I had to remove this space from my script to match the new field I created
on Postgres.
My question, then, is this: How can I access fields which have spaces and
Caps in them {CREATE TABLE table_name ("Project Manager Name" text,
...);...} from my VBScript (using the Windows Scripting Host) when I employ
the Postgres/ODBC driver from Insight?  
Is there some special way to pass these special field names to the driver so
that it can understand and properly translate them to Postgres (on Linux)?
Or must I resort to naming the fields in Postgres in the conventional way
and leave it at that?
Here is the driver information:
odbc driver version:  6.30.0250
postgresql database version: 6.4

Robert Carbonari
Programmer
Optimal Robotics Corp.
e-mail: robertc@optimal-robotics.com
phone:  (514)738-8885 ext. 146




Re: [INTERFACES] Problem with Postgres/ODBC Driver.

From
Byron Nikolaidis
Date:

Robert Carbonari wrote:

> <snip>

> My question, then, is this: How can I access fields which have spaces and
> Caps in them {CREATE TABLE table_name ("Project Manager Name" text,
> ...);...} from my VBScript (using the Windows Scripting Host) when I employ
> the Postgres/ODBC driver from Insight?
> Is there some special way to pass these special field names to the driver so
> that it can understand and properly translate them to Postgres (on Linux)?
> Or must I resort to naming the fields in Postgres in the conventional way
> and leave it at that?
> Here is the driver information:
> odbc driver version:  6.30.0250
> postgresql database version: 6.4
>

Version 6.4 of the driver/database and above will handle mixed case and spaces.
In order to preserve spaces and case, you need to use double quotes around the
field names.  This goes for table names as well, which can also have spaces and
mixed case.   I'm not sure if you are directly controlling this in your script
or not.  Some applications (e.g. Access) automatically add the quotes around
field and table names based on some info returns of the driver, such as the
following:

SQL_IDENTIFIER_CASE,
SQL_IDENTIFIER_QUOTE_CHAR,
SQL_QUOTED_IDENTIFIER_CASE

There were some anomolies with some applications that didn't exactly interpret
the above info returns the way I would think they should, and thus wouldn't work
properly with quotes.

Byron