Announcement

Collapse
No announcement yet.

Error Writing to DB

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

    Error Writing to DB

    I have a few X10 devices that have house codes %1, etc.

    I get an error when they are triggered:

    10/9/2012 10:49:43 AM ~!~UltraLog Warning~!~InsertDeviceStatusIntoDB() Reports Error: [System.Data.OleDb.OleDbException: The statement has been terminated.String or binary data would be truncated. at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Command Behavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at HSPI_ULTRALOG.Database.InsertDeviceStatusIntoDB(DateTime ts, String hc, String dc, Int32 status, Int32 data1, Int32 data2)], Failed on SQL: INSERT INTO tblDeviceStatusLog (ts, hc, dc, status, data1, data2) VALUES ('10/9/2012 10:49:43 AM','[%]','[%9]',3,2,0).

    I'm using SQL server for messages and % is a wildcrd. Could that be the issue? Anyone got any idea how to change house codes with X10/ACRF2 plug-in?

    Regards

    Mark
    ________________________

    Dell Zino HD - HSPRo 2.x
    HSTouch - iPhone 3GS, 4S, iPad2 and iPad 3, 3xKindle Fire (Wall mounted)
    2 x Brultech ECM1240 with UltraECM
    USB UIRT
    Cooper Aspire Z-Wave Switches
    WGL800 w ACRF2 and 3xDS10a (Sump Level/Activity sensing)
    HM ST812-2 Flood Sensor
    HSM100 - Motion Detector with Light and Temperature
    2GIG Thermostat
    BLDSC - Alarm Plug-in
    BLUPS
    UltraLog (SQL2008)
    Jon00 Plugins
    Host XR3 BT and Jon00 BT Proximity
    Global Cache IP2IR
    Foscam FI8918W

    #2
    Hello. I added an escape character to the % character. Can you try UltraLog HSPI version 1.2.4665.33287 I just uploaded to the HomeSeer updater?

    Regards,
    Ultrajones
    Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

    Comment


      #3
      Originally posted by Ultrajones View Post
      Hello. I added an escape character to the % character. Can you try UltraLog HSPI version 1.2.4665.33287 I just uploaded to the HomeSeer updater?

      Regards,
      Ultrajones

      Thanks UJ.

      Will try tomorrow when back home

      Regards

      Mark
      ________________________

      Dell Zino HD - HSPRo 2.x
      HSTouch - iPhone 3GS, 4S, iPad2 and iPad 3, 3xKindle Fire (Wall mounted)
      2 x Brultech ECM1240 with UltraECM
      USB UIRT
      Cooper Aspire Z-Wave Switches
      WGL800 w ACRF2 and 3xDS10a (Sump Level/Activity sensing)
      HM ST812-2 Flood Sensor
      HSM100 - Motion Detector with Light and Temperature
      2GIG Thermostat
      BLDSC - Alarm Plug-in
      BLUPS
      UltraLog (SQL2008)
      Jon00 Plugins
      Host XR3 BT and Jon00 BT Proximity
      Global Cache IP2IR
      Foscam FI8918W

      Comment


        #4
        UJ, I'm also getting an insert error on devices using the % housecode. It was working with the version you had posted immediately prior to 1.2.4665.32287. I started getting the errors after updating to 1.2.4665.32287.

        Code:
        [COLOR=#D58000]InsertDeviceStatusIntoDB() Reports Error:  [System.Data.OleDb.OleDbException: The statement has been  terminated.String or binary data would be truncated.   at  System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior  behavior, String method)   at  System.Data.OleDb.OleDbCommand.ExecuteNonQuery()   at  HSPI_ULTRALOG.Database.InsertDeviceStatusIntoDB(DateTime ts, String hc,  String dc, Int32 status, Int32 data1, Int32 data2)], Failed on SQL:  INSERT INTO tblDeviceStatusLog (ts, hc, dc, status, data1, data2) VALUES  ('10/26/2012 11:00:14 AM','\%','\%49',17,790,0).
        [/COLOR]

        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


          #5
          Originally posted by reidfo View Post
          UJ, I'm also getting an insert error on devices using the % housecode. It was working with the version you had posted immediately prior to 1.2.4665.32287. I started getting the errors after updating to 1.2.4665.32287.

          Code:
          [COLOR=#D58000]InsertDeviceStatusIntoDB() Reports Error:  [System.Data.OleDb.OleDbException: The statement has been  terminated.String or binary data would be truncated.   at  System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior  behavior, String method)   at  System.Data.OleDb.OleDbCommand.ExecuteNonQuery()   at  HSPI_ULTRALOG.Database.InsertDeviceStatusIntoDB(DateTime ts, String hc,  String dc, Int32 status, Int32 data1, Int32 data2)], Failed on SQL:  INSERT INTO tblDeviceStatusLog (ts, hc, dc, status, data1, data2) VALUES  ('10/26/2012 11:00:14 AM','\%','\%49',17,790,0).
          [/COLOR]

          What database are you using?
          Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

          Comment


            #6
            Originally posted by Ultrajones View Post
            What database are you using?
            Using SQL 2008R2.
            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


              #7
              Any updates on this? I saw in another thread you were planning to make the escape character configurable.
              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


                #8
                Added it now... I wish I could find documentation showing what characters need to be escaped when using the Microsoft SQL database.

                Regards,
                Ultrajones
                Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

                Comment


                  #9
                  Originally posted by Ultrajones View Post
                  Added it now... I wish I could find documentation showing what characters need to be escaped when using the Microsoft SQL database.

                  Regards,
                  Ultrajones
                  Hi UltraJones

                  I do a lot of MS SQL dev. I usually avoid these (mostly when using LIKE). Single quote is the main one for a regular query, but I have had issues concatenating strings when there is an ampersand in the string.

                  percent sign (%)
                  underscore (_)
                  brackets ([ ])
                  caret (^)
                  hyphen (-)
                  ampersand (&)
                  single quote (')

                  Regards

                  mark
                  ________________________

                  Dell Zino HD - HSPRo 2.x
                  HSTouch - iPhone 3GS, 4S, iPad2 and iPad 3, 3xKindle Fire (Wall mounted)
                  2 x Brultech ECM1240 with UltraECM
                  USB UIRT
                  Cooper Aspire Z-Wave Switches
                  WGL800 w ACRF2 and 3xDS10a (Sump Level/Activity sensing)
                  HM ST812-2 Flood Sensor
                  HSM100 - Motion Detector with Light and Temperature
                  2GIG Thermostat
                  BLDSC - Alarm Plug-in
                  BLUPS
                  UltraLog (SQL2008)
                  Jon00 Plugins
                  Host XR3 BT and Jon00 BT Proximity
                  Global Cache IP2IR
                  Foscam FI8918W

                  Comment


                    #10
                    Wow, testing shows I don't need to escape those characters when doing an insert. The error "The statement has been terminated. String or binary data would be truncated" means the escape character is being ignored and treated as a normal string. I am removing it now and will post an update.

                    Regards,
                    Ultrajones
                    Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

                    Comment


                      #11
                      Sorry for the delay. I had to install SQL Server 2008 Express to see what was going on. I was able to duplicate the issue. UltraLog HSPI version 1.2.4775.22583 just posted which should fix the issue reported.

                      Regards,
                      Ultrajones
                      Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

                      Comment


                        #12
                        Originally posted by Ultrajones View Post
                        Sorry for the delay. I had to install SQL Server 2008 Express to see what was going on. I was able to duplicate the issue. UltraLog HSPI version 1.2.4775.22583 just posted which should fix the issue reported.

                        Regards,
                        Ultrajones
                        Thank you, Ultra. Where did you post this? I don't see it. Or do you mean you sent it to the updater and it will take a couple days?
                        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


                          #13
                          Originally posted by Ultrajones View Post
                          Wow, testing shows I don't need to escape those characters when doing an insert. The error "The statement has been terminated. String or binary data would be truncated" means the escape character is being ignored and treated as a normal string. I am removing it now and will post an update.

                          Regards,
                          Ultrajones
                          The only one you should need to escape for an insert is single quote
                          ________________________

                          Dell Zino HD - HSPRo 2.x
                          HSTouch - iPhone 3GS, 4S, iPad2 and iPad 3, 3xKindle Fire (Wall mounted)
                          2 x Brultech ECM1240 with UltraECM
                          USB UIRT
                          Cooper Aspire Z-Wave Switches
                          WGL800 w ACRF2 and 3xDS10a (Sump Level/Activity sensing)
                          HM ST812-2 Flood Sensor
                          HSM100 - Motion Detector with Light and Temperature
                          2GIG Thermostat
                          BLDSC - Alarm Plug-in
                          BLUPS
                          UltraLog (SQL2008)
                          Jon00 Plugins
                          Host XR3 BT and Jon00 BT Proximity
                          Global Cache IP2IR
                          Foscam FI8918W

                          Comment


                            #14
                            Originally posted by reidfo View Post
                            Thank you, Ultra. Where did you post this? I don't see it. Or do you mean you sent it to the updater and it will take a couple days?
                            Sorry, missed this question. I host my own plug-in downloads, so as soon as I post them they are available for download via the HomeSeer updater.

                            Regards,
                            Ultrajones
                            Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

                            Comment

                            Working...
                            X