'The change is from using a global SQL object to a local one. For EVERY query, we fetch a new pool connection (DBM.GetSQL), execute the SQL
' and close the pool connection.
'Fetching and closing pool connections are inexpensive and allow us to share the connections as needed.
'
Sub timSystem_Tick
timSystem.Enabled = False
If machine_id = 0 Then
Dim filter As Map: filter.Initialize
filter.Put("scanner_name", others.Get_DeviceID)
webapi1.API_LOAD("scanners", filter)
Wait For API_LOADED (L As List)
If L.Size > 0 Then
Dim machine_id As Int = L.Get(0).As(Map).Get("machine_id")
Log("machine_id = " & machine_id)
timFTPupload_Tick
Else
Log("machine_id trouble !")
End If
Else
'Create a local SQL object here
dim localSQL As SQL = DBM.GetSQL
Dim names As ResultCursor
'names.Initialize(db.SQL, "SELECT * FROM " & db.DBT_NAMES & " WHERE sent = '0'", Null, 1)
names.Initialize(localSQL, "SELECT * FROM " & db.DBT_NAMES & " WHERE sent = '0'", Null, 1)
localSQL.close ' Yup, done for now
For i = 0 To names.RowCount - 1
names.Position = i
Dim name As String = names.GetString("name")
Log("Sending ... " & name)
' db.SQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '2' WHERE name = '" & name & "'") 'sending status
localSQL = DBM.GetSQL
localSQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '2' WHERE name = '" & name & "'") 'sending status
localSQL.Close ' Yup, done once more
Dim Sending As ResumableSub = api.Send_Vag(name, "")
Do Until Sending.Completed
Sleep(50)
Loop
'*******
'******* The loop above can block many SQL connection requests to the pool !!!!!!!!
'******* That's why we are freeing the connection before we do the Send_Vag method
'*******
Dim filter As Map: filter.Initialize
filter.Put("machine_id", machine_id)
filter.Put("name", name)
webapi1.API_LOAD("objs", filter)
Wait For API_LOADED (L As List)
If L.Size > 0 Then
Dim obj_id As Int = L.Get(0).As(Map).Get("id")
Log("obj_id = " & obj_id)
Dim obj_img As String = L.Get(0).As(Map).Get("img")
Log("obj_img = " & obj_img)
'img name for server: sample_4_85.jpg = sample_ machine_id _ obj_id .jpg
Dim img_name As String = "sample_" & machine_id & "_" & obj_id & ".jpg"
Log("img_name = " & img_name)
Log("Sending2 ... " & name)
'db.SQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '2' WHERE name = '" & name & "'") 'sending status
localSQL = DBM.GetSQL
localSQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '2' WHERE name = '" & name & "'") 'sending status
localSQL.Close ' Yup, done once more
Dim Sending As ResumableSub = api.Send_Vag(name, img_name)
Do Until Sending.Completed
Sleep(50)
Loop
'***** Looks like another killer loop above
'Dim local_file As String = db.SQL.ExecQuerySingleResult("SELECT x.img FROM (SELECT img, id FROM " & db.DBT_OBJS & " WHERE name = '" & name & "' ORDER BY id DESC, rate DESC LIMIT 10) AS x LIMIT 1")
localSQL = DBM.GetSQL
Dim local_file As String = localSQL.ExecQuerySingleResult("SELECT x.img FROM (SELECT img, id FROM " & db.DBT_OBJS & " WHERE name = '" & name & "' ORDER BY id DESC, rate DESC LIMIT 10) AS x LIMIT 1")
localSQL.Close ' Yup, done once more
Log("local_file = " & local_file)
local_file = fs.getPath(local_file)
Log("local_file2 = " & local_file)
If local_file <> "" Then
If File.Exists(File.DirApp, "by_ftp") = False Then
File.MakeDir(File.DirApp, "by_ftp")
End If
Dim local_folder As String = File.Combine(File.Combine(File.DirApp, "by_ftp"), machine_id)
If File.Exists(local_folder, "") = False Then
File.MakeDir(local_folder, "")
End If
File.Copy("", local_file, local_folder, img_name) 'save file for next uploading by FTP
End If
Else
Log("no obj_id yet")
End If
Log("Sent: " & name)
'db.SQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '1' WHERE name = '" & name & "'") 'sent status finally
localSQL = DBM.GetSQL
localSQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '1' WHERE name = '" & name & "'") 'sent status finally
localSQL.Close ' Yup, done once more
Next
End If
timSystem.Enabled = True
End Sub