Announcement

Collapse
No announcement yet.

ODBC to MySQL Script?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    ODBC to MySQL Script?

    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!

    PHP Code:
    Feb-04 3:44:24 PM         Error    Compiling script C:\HomeSeer HS3\scripts\IntTemp.vb'MyConnection' is not declaredIt 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'use any aliases
    Actual Script:

    PHP Code:
    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 
    HS3 PRO, Win10, WeatherXML, HSTouch, Pushover, UltraGCIR, Heaps of Jon00 Plugins, Just sold and about to move so very slim system.

    Facebook | Twitter | Flickr | Google+ | Website | YouTube

    #2
    To resolve the first error move the lines
    Code:
    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.
    HS Pro 3.0 | Linux Ubuntu 16.04 x64 virtualized under Proxmox (KVM)
    Hardware: Z-NET - W800 Serial - Digi PortServer TS/8 and TS/16 serial to Ethernet - Insteon PLM - RFXCOM - X10 Wireless
    Plugins: HSTouch iOS and Android, RFXCOM, BlueIris, BLLock, BLDSC, BLRF, Insteon PLM (MNSandler), Device History, Ecobee, BLRing, Kodi, UltraWeatherWU3
    Second home: Zee S2 with Z-Wave, CT101 Z-Wave Thermostat, Aeotec Z-Wave microswitches, HSM200 occupancy sensor, Ecolink Z-Wave door sensors, STI Driveway Monitor interfaced to Zee S2 GPIO pins.

    Comment


      #3
      Thanks for your help, looks like it is working
      Now to fix up the script to handle parameters lol.
      HS3 PRO, Win10, WeatherXML, HSTouch, Pushover, UltraGCIR, Heaps of Jon00 Plugins, Just sold and about to move so very slim system.

      Facebook | Twitter | Flickr | Google+ | Website | YouTube

      Comment

      Working...
      X