ODBC to MySQL Script?

[travisdh]

Hi All,

Has anyone successfully got a script to write to a mysql database using ODBC. I am trying to write some sensor information to a datalogging database just using dummy values for now but am having no luck getting it to write.

I have tried adding the references in the scripting for system.data.dll and system.data, and also adding the imports as per the below but just get this error:

Many thanks!

Feb-04 3:44:24 PM         Error    Compiling script C:\HomeSeer HS3\scripts\IntTemp.vb: 'MyConnection' is not declared. It may be inaccessible due to its protection level.
Feb-04 3:44:24 PM         Error    Compiling script C:\HomeSeer HS3\scripts\IntTemp.vb: Namespace or type specified in the Imports 'System.Core' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. 

Actual Script:

Imports System.Data.Odbc

Sub Main(parm as object)

Try
 
Dim MyConString As String = ("DRIVER={MySQL ODBC 5.3 Unicode Driver};" & "SERVER=192.168.x.x;" & "DATABASE=dlog;" & "UID=xx;" & "PASSWORD=xx;" & "OPTION=3")
Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()
Dim MyCommand As New OdbcCommand
MyCommand.Connection = MyConnection
MyCommand.CommandText = "INSERT INTO `dlog`.`RawData` (`SensorID`, `Measurement_Type`, `Measurement_Value`, `Measurement_Unit`) VALUES ('INTEMPH_ENV_001', 'TEMP', '10.5', '°C');"
MyCommand.ExecuteNonQuery()
MyConnection.Close()
MyConnection.Dispose() 

Catch ex as Exception
hs.writelog("ErrorMySQL",ex.message)
MyConnection.Close()
End Try

End Sub 

[reidfo]

To resolve the first error move the lines

Dim MyConString As String = ("DRIVER={MySQL ODBC 5.3 Unicode Driver};" & "SERVER=192.168.x.x;" & "DATABASE=dlog;" & "UID=xx;" & "PASSWORD=xx;" & "OPTION=3") 
Dim MyConnection As New OdbcConnection(MyConString)

above the Try statement. A variable declared within a Try block is not accessible outside of the Try, including in the Catch section.

[travisdh]

Thanks for your help, looks like it is working
Now to fix up the script to handle parameters lol.