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 7, 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 1, 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