I'm tring to use ADO to transfer a table from Access 97 to PostgreSQL. I get the error shown below on a currency field.
Can you suggest a fix for this? Is the ODBC driver doing the wrong thing? Using the PostreSQL ODBC driver at ftp://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc-07_01_0009.zip Code fragments ------------- import win32com.client srcConnectString = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s' \ % 'E:\\FoodBank\\Export20011222\\Export20011222.mdb' dstConnectString = 'DSN=FoodBankPG' # PostgreSQL through PSQL ODBC srcConnection = win32com.client.Dispatch(r'ADODB.Connection') srcConnection.Open(srcConnectString) dstConnection = win32com.client.Dispatch(r'ADODB.Connection') dstConnection.Open(dstConnectString) srcRs = win32com.client.Dispatch(r'ADODB.Recordset') srcRs.Open(self.srcSelectStr,srcConnection, win32com.client.constants.adOpenKeyset, win32com.client.constants.adLockOptimistic) dstCmd = win32com.client.Dispatch(r'ADODB.Command') dstCmd.ActiveConnection = dstConnection dstCmd.Prepared = 1 dstCmd.CommandType = win32com.client.constants.adCmdText dstCmd.CommandTimeout = 20 dstCmd.CommandText = self.dstInsertStr srcRs.MoveFirst() while srcRs.EOF == 0: parms = [] for ff in srcRs.Fields: parms.append(ff) dstCmd.Execute(Parameters=parms, Options=win32com.client.constants.adCmdText) self.rowXferCount = self.rowXferCount + 1 if 1: return dstStmt,ii # debug srcRs.MoveNext() The sql commands are -------------------- Select VisitNumber,FoodBankID,Date,EstServiceValue,Adults,Children,Gasoline,Tokens From [Visit Date]; Insert Into Visit_Date (VisitNumber,FoodBankID,Date,EstServiceValue,Adults,Children,Gasoline,Tokens) Values (?,?,?,?,?,?,?,?); Here is the error ---------------- Traceback (most recent call last): File "<interactive input>", line 1, in ? File "E:\FoodBank\PyTools\adoTableXfer.py", line 131, in ? tableXfer.xfer(srcConnectString,srcTableName,dstConnectString,dstTableName) File "E:\FoodBank\PyTools\adoTableXfer.py", line 106, in xfer return File "E:\FoodBank\PyTools\adoTableXfer.py", line 116, in _xferRecordset Options=win32com.client.constants.adCmdText) File "E:\Python21\win32com\gen_py\EF53050B-882E-4776-B643-EDA472E8E3F2x0x2x7.py", line 1746, in Execute return self._ApplyTypes_(0x5, 1, (9, 0), ((16396, 18), (16396, 17), (3, 49)), 'Execute', '{00000556-0000-0010-8000-00AA006D2EA4}',RecordsAffected, Parameters, Options) File "E:\Python21\win32com\client\__init__.py", line 343, in _ApplyTypes_ return self._get_good_object_(apply(self._oleobj_.InvokeTypes, (dispid, 0, wFlags, retType, argTypes) + args), user, resultCLSID) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for ODBC Drivers', "Error while executing the query;\nERROR: Attribute 'estservicevalue' is of type 'money' but expression is of type 'numeric'\n\tYou will need to rewrite or cast the expression", None, 0, -2147467259), None) -- Craig H. Anderson _______________________________________________ ActivePython mailing list [EMAIL PROTECTED] http://listserv.ActiveState.com/mailman/listinfo/activepython
