Announcement

Collapse
No announcement yet.

SQL Server

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

  • SQL Server

    Hi Guys

    Anyone using SQL server? if so, where does it write the temperature from the sensors. It seems to ignore initial catalog in connection string and I don't see any new tables (just my ones from UltraLog)

    Regards

    Mark
    Last edited by mark_anderson_us; January 7th, 2014, 01:19 PM.
    ________________________

    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
    I made some progress using SQL profiler and guessed at the schema. It's now writing data, but when i try to produce a chart I get this

    An unexpected error occured in the GetSensorChart() function/subroutine: [System.IndexOutOfRangeException: Cannot find table 0. at System.Data.DataTableCollection.get_Item(Int32 index) at HSPI_ULTRA1WIRE2.HSPI.GetSensorChart(Hashtable& PluginDevices, Hashtable& DeviceColors, String ChartType, ArrayList TempSensorList, DateTime dEndDateTime, String Interval)]

    This is failing because it's trying to use a column alias in a group by clause.

    PHP Code:
    SELECT 
        120
    *(ts/120) as ts_epoch
        
    code as deviceCode
        
    AVG(value) as sensorValue 
    FROM 
        tblTemperature 
    as 
    WHERE 
        ts 
    >= 1388543481 
        
    AND ts <= 1388545281 
        
    AND code IN ('/1'
    GROUP BY 
        ts_epoch
    , -- should be 120*(ts/120), 
        
    code 
    ORDER BY 
        ts_epoch ASC 
    Last edited by mark_anderson_us; January 1st, 2014, 10:46 AM.
    ________________________

    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


    • #3
      Bump
      ________________________

      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
        I'll look into this. I am not sure if you have noticed, but I am only using SQLite for my HS3 plug-ins. I have abandoned support for other database types.

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

        Comment


        • #5
          Originally posted by Ultrajones View Post
          I'll look into this. I am not sure if you have noticed, but I am only using SQLite for my HS3 plug-ins. I have abandoned support for other database types.

          Regards,
          Ultrajones
          Thanks

          that's a real shame. Nobody really wants to run 2 SQL databases and he SQL is pretty straightforward
          ________________________

          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


          • #6
            Originally posted by mark_anderson_us View Post
            Thanks

            that's a real shame. Nobody really wants to run 2 SQL databases and he SQL is pretty straightforward
            SQLite is a "self-contained, serverless, zero-configuration, transactional SQL database engine." There isn't anything you need to run or worry about
            Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

            Comment


            • #7
              Originally posted by Ultrajones View Post
              SQLite is a "self-contained, serverless, zero-configuration, transactional SQL database engine." There isn't anything you need to run or worry about
              True, but I use SQL reporting services and SSMS. Not sure if I can connect to an SQLLite DB with those. I noticed that you're now using monthly DB's for one of your plug-ins, which means my queries are much more complex to extract multi-month stats. If you don't want to rewrite the query every month, this will probably require a union query using dynamic sql which is poor from performance and security
              ________________________

              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

              Working...
              X