6 Replies Latest reply on Nov 5, 2013 6:19 PM by sales@varitek.com.au

    Updating FM12 db via ODBC from Visual Basic Application

    sales@varitek.com.au

      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