0 Replies Latest reply on Oct 22, 2013 9:59 PM by sales@varitek.com.au

    Updating FM12 db via ODBC from Visual Basic Application

    sales@varitek.com.au

      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