AnsweredAssumed Answered

Updating FM12 db via ODBC from Visual Basic Application

Question asked by sales@varitek.com.au on Oct 28, 2013
Latest reply on Nov 5, 2013 by 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

Outcomes