Re: Access violation - probably not the fault of Postgres - Mailing list pgsql-odbc
From | Paul Lambert |
---|---|
Subject | Re: Access violation - probably not the fault of Postgres |
Date | |
Msg-id | 45F08759.7010207@autoledgers.com.au Whole thread Raw |
In response to | Re: Access violation - probably not the fault of Postgres (David Gardner <david.gardner@yucaipaco.com>) |
Responses |
Re: Access violation - probably not the fault of Postgres
Re: Access violation - probably not the fault of Postgres |
List | pgsql-odbc |
David Gardner wrote: > Could you post the code in question? How are you initializing your > recordset object? Have you tried feeding the database object an insert > statement via the execute() function? > > Excuse the longwindedness of this... I've tried putting in all the relevant code and other information that I can. Recordset object is defined as thus: Private Debtor_table As Recordset About 30 odd times for the various different tables - this is then passed to the function whos code is below which receives it as variable name "table" Other relevant variable declarations: Private autodrs_db As DAO.Database Private autodrs_work As DAO.Workspace Database is opened as follows: Set autodrs_work = CreateWorkspace("autodrs", g_strUserName, g_strPWD, dbUseODBC) Set autodrs_db = autodrs_work.OpenDatabase("autodrs", _ dbDriverNoPrompt, False, "ODBC;DATABASE=" & g_strDBName & _ ";UID=" & g_strUserName & ";PWD=" & g_strPWD & ";DSN=" & g_strDBDSN & ";") The code causing the error is as follows: Call debug_message(60, "Criteria = " & criteria) task = "Check for Update or Add" criteria_orig = criteria criteria = "Select * from " & table_name & " where " & criteria Call debug_message(60, "Opening table with criteria=" & criteria) Set table = autodrs_db.OpenRecordset _ (criteria, dbOpenDynamic, 0, dbOptimistic) If table.RecordCount = 0 Then Call debug_message(60, "Record not found, adding new") task = "Add" table.AddNew Else Call debug_message(60, "Record found, updating") task = "Update" table.Edit End If lngStatusDB = load_xxx_to_db(table_name, table, keyname, keyname2, keyname3, keyname4, keyname5) Call debug_message(60, " - load_xxx_to_db exit status " + Str(lngStatusDB)) If lngStatusDB = 0 Then Call debug_message(60, " + updating table") table.Update Call debug_message(60, " - updating table") Else table.CancelUpdate load_xxx = lngStatusDB GoTo subroutine_exit End If The line "table.Update" is where the access violation is occuring. As explained before the error only occurs if the update is adding a new record to the table, updating existing records works fine. The function load_xxx_to_db called just before the update basically loops through the message received and puts the data into the appropriate field in the "table" buffer - the code is as follows: Private Function load_xxx_to_db(table_name As String _ , table As Recordset _ , keyname As String _ , keyname2 As String _ , keyname3 As String _ , keyname4 As String _ , keyname5 As String) As Long Dim ddmmyy As String On Error GoTo error_trap indexx = key_id_field + 1 Call debug_message(80, " + load_xxx_to_db") If table_name = "Employees" Then 'Last 60 fields of employee record are loaded to a different table, bypass them in this load. item_count = item_count - 60 End If ' The following section sets all the fields from the DMQ message into the appropriate fields in the database. Do Until (indexx > item_count) Select Case field_type(indexx) 'Straight text/string. Case "T" Call debug_message(90, " + load_xxx_to_db > Setting " & _ field_name(indexx) & ".value to " & _ field_contents(indexx)) table(field_name(indexx)).value _ = field_contents(indexx) 'Date in the formate dd-mmm-yyyy Case "X", "J", "I", "E" If ((field_contents(indexx) = "") Or (field_contents(indexx) = "00000000000")) Then 'Yes I know we shouldn't use Nulls, but this is replicating another database not designed/managed by me 'and I can't change this fact. Call debug_message(90, " + load_xxx_to_db > Setting " & _ field_name(indexx) & ".value to Null") table(field_name(indexx)).value = Null Else Call debug_message(90, " + load_xxx_to_db > Setting " & _ field_name(indexx) & ".value to " & _ field_contents(indexx)) table(field_name(indexx)).value _ = field_contents(indexx) End If 'Time Case "V" Call debug_message(90, " + load_xxx_to_db > Setting " & _ field_name(indexx) & ".value to " & _ field_contents(indexx)) table(field_name(indexx)).value _ = cvt_time(field_contents(indexx)) 'Numeric Case "B", "W", "L", "F", "M", "1", "2", "3", "4", "5", "6", "7", "8", "9" If (IsNumeric(field_contents(indexx))) Then Call debug_message(90, " + load_xxx_to_db > Setting " & _ field_name(indexx) & ".value to " & _ field_contents(indexx)) table(field_name(indexx)).value _ = Val(field_contents(indexx)) Else 'Yes I know we shouldn't use Nulls, but this is replicating another database not designed/managed by me 'and I can't change this fact. Call debug_message(90, " + load_xxx_to_db > Setting " & _ field_name(indexx) & ".value to Null") table(field_name(indexx)).value = Null End If 'Other unknown data type. Case Else Call log_load_error(table_name, "Unsupported data type") load_xxx_to_db = -10 GoTo subroutine_exit End Select indexx = indexx + 1 Loop load_xxx_to_db = 0 subroutine_exit: Exit Function error_trap: Dim MyError As Error For Each MyError In DBEngine.Errors With MyError Call debug_message(10, "--ODBC update error, " + Str(.Number) + " : " + .Description) End With Next MyError End Function Relevant section of the resulting logfile: (I've added a lot more than normal debugging lines to try tracking down what is causing it. "9/03/2007 6:12:29 AM dbg 80- [+loading sundry product table]" "9/03/2007 6:12:29 AM dbg 70- [+load_table(Sundry_Product,Product_id,Dealer_id,Franchise,Workshop,Price_Type)]" "9/03/2007 6:12:29 AM dbg 60- [Criteria = Product_id = 'BULLBAR' and Dealer_id = 'F65' and Franchise = 'BLANK' and Workshop = '0' and Price_Type = '0']" "9/03/2007 6:12:29 AM dbg 60- [Opening table with criteria=Select * from Sundry_Product where Product_id = 'BULLBAR' and Dealer_id = 'F65' and Franchise = 'BLANK' and Workshop = '0' and Price_Type = '0']" "9/03/2007 6:12:46 AM dbg 60- [Record not found, adding new]" "9/03/2007 6:12:46 AM dbg 80- [ + load_xxx_to_db]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DEALER_ID.value to F65]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DATE_CHANGED.value to 06-Mar-2007]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting TIME_CHANGED.value to 1809]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRODUCT_ID.value to BULLBAR]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DES_1.value to Bullbar]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DES_2.value to ]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DES_3.value to ]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DES_4.value to ]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRODUCT_TYPE.value to S]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRODUCT_SALES_GROUP.value to 45]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRICE_1.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRICE_2.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRICE_3.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRICE_4.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting COST.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PARTS_HANDLING.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting INCLUDING_SALES_TAX.value to ]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PARTS_HANDLING_LIMIT.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting HANDLING_LIMIT_PER_PART.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DISC_TYPE.value to ]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DISC_PERCENTAGE.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting MARK_UP_PERCENTAGE.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting ROUND_UP_TO.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting SUBTRACT_FROM_ROUND_UP.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DISC_MINIMUM.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting DISC_MAXIMUM.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting SUPPLIER_NO.value to 113]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting HANDLING_LIMIT_PER_RO.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting OBSOLETE.value to ]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting FRANCHISE.value to BLANK]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting WORKSHOP.value to 0]" "9/03/2007 6:12:46 AM dbg 90- [ + load_xxx_to_db > Setting PRICE_TYPE.value to 0]" "9/03/2007 6:12:46 AM dbg 60- [ - load_xxx_to_db exit status 0]" "9/03/2007 6:12:46 AM dbg 60- [ + updating table]" <logfile stops here everytime showing that the table.Update line is the point of failure> Apologies again for the length of this... but hey, you asked for it ;) Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers
pgsql-odbc by date: