Skip site navigation (1) Skip section navigation (2)

Re: Access violation - probably not the fault of Postgres

From: Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Access violation - probably not the fault of Postgres
Date: 2007-03-08 21:59:53
Message-ID: 45F08759.7010207@autoledgers.com.au (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-odbc by date

Next:From: noreplyDate: 2007-03-09 01:07:43
Subject: [ psqlodbc-Bugs-1002503 ] ODBC Failure
Previous:From: noreplyDate: 2007-03-08 21:44:45
Subject: [ psqlodbc-Bugs-1002503 ] ODBC Failure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group