AnsweredAssumed Answered

Updating FM12 db via ODBC from Visual Basic Application

Question asked by sales@varitek.com.au on Oct 22, 2013

Title

Updating FM12 db via ODBC from Visual Basic Application

Post

     Hi,
      
     This is my first post so if I am doing this incorrectly please tell me.
      
     We have a FM10 application that sends and receives SMS via a GSM modem. It has been working fine for that past couple of years but we would like to upgrade to FM12.
      
     The basic structure we have is a small VB6 program (called a Spooler) that uses ODBC to connect to an Inbox and an Outbox in FM10. When there is an SMS to send the Status field in the Outbox is set to 0 (meaning that it is ready to send), the VB6 program sees the Status of 0, reads the phone number and message to send, updates the status for the relevant record in the outbox to 99 (meaning that it is being sent). On completion there are 3 outcomes, -2 means that send failed, -1 means the SMS was sent but no confirmation was received from the SMSC or 1 meaning successfully sent and confirmed.
      
     So I converted the db to FM12 and the first attempt to run the Spooler gave an error as follows:
      
     FATAL -2147467259 [FileMaker][FileMaker] FQL0013/(1:20): Incompatible types in assignment.
      
     So I changed the Status field in the Outbox from a Number to Text data type and it overcame this error. The next step was to send an SMS which and when the spooler attempts to update the Status field to 99 an  it produces the following error:
      
     FATAL -2147217887 Multiple-step operation generated errors. Check each status value. The point in the VB6 code where this error is generated is highlighted below:
      
     Other possibly relevant info - using FM Pro Advanced 12.0v1 and FM 32 ODBC driver version 12.3.103.0
      
     Does anybody have any suggestions?
      
     Visual Basci 6 Code to send an SMS
      
     ==========================================================
      
     Public Function readOutbox(Optional status As Integer = 0)
         Dim outbox As New ADODB.Recordset
         Dim out_table As String
         Dim out_status As String
      
         Dim id As String
         Dim phone As String
         Dim message As String
      
         out_table = quoteIdent(m_outbox_table)
         out_status = quoteString(CStr(status))
      
         With outbox
             log 6, "SQL " & "SELECT id, phone, message, status FROM " & out_table & " WHERE status = " & out_status
      
             .CursorLocation = adUseClient
             .Open "SELECT id, phone, message, status FROM " & out_table & " WHERE status = " & out_status, m_adodb, adOpenStatic, adLockOptimistic
      
             If .EOF = True Then Exit Function
             sms.startBatch
             
             Do
                 id = ""
                 phone = ""
                 message = ""
      
                 If Not IsNull(.Fields(0)) Then id = .Fields(0)
                 If Not IsNull(.Fields(1)) Then phone = .Fields(1)
                 If Not IsNull(.Fields(2)) Then message = .Fields(2)
      
            .Update "status", 99
      
                 log 4, "FMSELECT out"
                 log 4, "FMUPDATE out (id=" & id & " status=99)"
      
                 m_sms.sendSMS phone, message, id
                 .MoveNext
             Loop Until .EOF = True
      
             sms.stopBatch
             .Close
         End With
     End Function
      
      
     ==========================================================
      
     What does work:
      
     The following VB6 code all works
      
     Public Function markSent(id As String, status As Integer)
         If (id = "" Or status = 0) Then Exit Function
      
         ' May still have failed: 1 - Success, -1 - Success but no confirmation, -2 Fail
         Dim out_table As String
         Dim out_id As String
         Dim out_status As String
      
         out_table = quoteIdent(m_outbox_table)
         out_id = quoteString(id)
         out_status = quoteString(CStr(status))
      
         If (status = 1) Then
             ' Update AND set SENT time
             log 6, "SQL " & "UPDATE " & out_table & " SET status = " & out_status & ", sent = CURRENT_TIMESTAMP WHERE id = " & out_id
             m_adodb.Execute "UPDATE " & out_table & " SET status = " & out_status & ", sent = CURRENT_TIMESTAMP WHERE id = " & out_id
         Else
             ' Update but DO NOT set SENT time
             log 6, "SQL " & "UPDATE " & out_table & " SET status = " & out_status & " WHERE id = " & out_id
             m_adodb.Execute "UPDATE " & out_table & " SET status = " & out_status & " WHERE id = " & out_id
         End If
      
         log 4, "FMUPDATE out (id=" & id & " status=" & CStr(status) & ")"
     End Function
      
     Regards...Colin Amos

Outcomes