I'm trying to insert some points into a mysql database and it won't work. the connection is ok, I can insert the date but when the script starts with the points it stops. Is there anybody who have done this before.

this is my script.

Option Explicit
Const cstdsn As String="dsn=mysql;uid=;psw="
Const csttblNaam As String="test"

Sub Main()

On Error GoTo err_

Dim varKol() As Variant
Dim strVollDSN As String, strQuery As String, strInsert As String, TimeStamp As String, strUpdate As String
Dim strStart As String, strStop As String
Dim lngConnID As Long
Dim lnginsert As String, lngUpdate As String
Dim Data() As Variant

lngConnID=SQLopen(cstdsn, strVollDSN, 4)
TimeStamp = format(now(),"yyyy.mm.dd hh:nn:ss")

' Make new row
strInsert = "INSERT into " & csttblNaam & "(timestamp)" + "VALUES('" & TimeStamp & "')"
lngInsert = SQLExecquery(lngConnID,strInsert)

' Get data and update

strUpdate = "UPDATE " & csttblNaam & " SET BOOL1= " + "('" & PointGet("BOOL1")& "')" + " WHERE TIMESTAMP = " + "('" & TimeStamp & "')"
lngUpdate = SQLExecquery(lngConnID,strUpdate)

strUpdate = "UPDATE " & csttblNaam & " SET INT1= " + "('" & PointGet("INT1")& "')" + " WHERE TIMESTAMP = " + "('" & TimeStamp & "')"
lngUpdate = SQLExecquery(lngConnID,strUpdate)

strUpdate = "UPDATE " & csttblNaam & " SET REAL1= " + "('" & PointGet("REAL1")& "')" + " WHERE TIMESTAMP = " + "('" & TimeStamp & "')"
lngUpdate = SQLExecquery(lngConnID,strUpdate)

strUpdate = "UPDATE " & csttblNaam & " SET TEXT1= " + "('" & PointGet("TEXT1")& "')" + " WHERE TIMESTAMP = " + "('" & TimeStamp & "')"
lngUpdate = SQLExecquery(lngConnID,strUpdate)




exit_:
On Error GoTo 0
erase varKol, Data
lngConnID = SQLClose(lngConnID)
Exit Sub

err_:
trace error$
LogStatus CIM_FAILURE,"scripting","Error in script"
Resume exit_
End Sub

 

Check what type of data that you have set your columns to in the MySQL Database, it looks like you might have them set to an "INT" style of data and you are feeding it "STRING" data, also check the logs to the mysql database for any clues.

if you are running LINUX, "/var/logs/" will be the most common directory.

P.S. Check out the program called "NAVICAT", well worth it if you are going to play with mysql a lot.

Regards,
Matt

 

<p>Here is the cimplicity code that I used that seems to be working.
<pre>
Public PLArray() As Variant
Dim s As String
Dim qry As Long
Dim StyleNum As Integer

Sub Main()

StyleNum = PointGet("LD_STYLE_NO_POS06")

PL& = SQLOpen("dsn=SQLDatabase;uid=;pwd=;",s$,3)

qry = SQLExecQuery(PL&,"Update load set stylecode= " & StyleNum & "where id = 1 ")

PL& = SQLClose(PL&)

End Sub
</pre>