Announcement

Collapse
No announcement yet.

WeatherXML data to SQL Server Question

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

  • WeatherXML data to SQL Server Question

    Hi Jeff,

    I would like to switch data saved from SQLite to Full SQL. I have a SQL Server and created a database called HS_WeatherXML. In the plug in, I can give it the server\instance and port and then the database, but what I cannot see to figure out is what user is trying to get to the database to write to it. Once I enter the server and database and click the Create Table, it comes back with Table created but when I look in the database, the table is not there.

    What is in the HS log is:


    Jan-06 15:12:53 weatherXML Error Cannot find the object "current" because it does not exist or you do not have permissions.
    Jan-06 15:12:53 weatherXML Error Cannot find the object "current" because it does not exist or you do not have permissions.
    Jan-06 15:12:53 weatherXML Error Incorrect syntax near 'ID'.


    So how do I know what user the system is trying to use or can I set it in an ini file or somewhere?

    Update: When I did a refresh on SQL Server, the providerCounts table was created but the current table was not. When I look at the SQLite table structure I see the first column is ID. So it did try to create the table but the create table syntax is incorrect. I will try to create the table in the SQL2016 syntax and repost what the first column needs to be.

    There were a couple issues with using the SQLite version of the create table so I fixed them and make some adjustments to save database space and processing. Here is the code I use to create the SQL Server table:

    Code:
    CREATE TABLE [current]
    ([ID] INT NOT NULL PRIMARY KEY IDENTITY,
     [updatedTime] datetime NULL,
      [temperature] varchar(50) NULL,
      [feelsLike] varchar(50) NULL,
      [todaysHigh] varchar(50) NULL,
      [todaysLow] varchar(50) NULL,
      [todaysNormalHigh] varchar(50) NULL,
      [todaysNormalLow] varchar(50) NULL,
      [todaysRecordHigh] varchar(50) NULL,
      [todaysRecordHighYear] varchar(50) NULL,
      [todaysRecordLow] varchar(50) NULL,
      [todaysRecordLowYear] varchar(50) NULL,
      [lastYearsHigh] varchar(50) NULL,
      [lastYearsLow] varchar(50) NULL,
      [humidity] varchar(50) NULL,
      [windSpeed] varchar(50) NULL,
      [windDirection] varchar(50) NULL,
      [windGust] varchar(50) NULL,
      [windDegrees] varchar(50) NULL,
      [visibility] varchar(50) NULL,
      [dewpoint] varchar(50) NULL,
      [barometer] varchar(50) NULL,
      [barometerDir] varchar(50) NULL,
      [moonPhase] varchar(50) NULL,
      [weatherSpoken] varchar(5000) NULL,
      [locationID] TINYINT NOT NULL,
      [rainHour] REAL NULL,
      [rainToday] REAL NULL,
      [curCondition] varchar(50) )
    Next is to output the data I have in the current SQLite database into the SQL and then turn back on the scheduled downloads in WeatherXML.

    Had to make weatherSpoken 5000 but you could also use varchar(max). All others are ok at 50 but you could make them 255 also. Also made updatedate as a datetime instead of a varchar or text so I can report using date

    Thanks,

    Michael
    Last edited by Jobee; January 6th, 2018, 07:14 PM. Reason: Update

  • #2
    When you installed SQL server, you should have been prompted for a password for the sa account. You can use sa as the login and your entered password or you can create a new user on the weatherxml database and use that.
    --
    Jeff Farmer
    HS 3, HSPhone
    My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
    Other Plugins In Use: APCUPSD, BLOnkyo, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

    Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2

    Comment


    • #3
      Hey Jeff,

      It must use windows log in as it never asked me for a user or password in the plugin, only the server and database.

      It is working so that is good. Just had to setup the table first since the create table did not work.

      Michael

      Comment


      • #4
        I found the issue with creating the current table I had left off the ( right before [ID] in the column list.
        Got it added for a future release.

        I was thinking of one of my other plugins where I had added the ability to enter a db login and password. WeatherXML is using Trusted_Connection=yes.

        IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'current') CREATE TABLE [dbo].[current] ([ID] [int] IDENTITY(1,1) NOT NULL, [updatedTime] [datetime] NULL, [temperature] [nvarchar](10) NULL, [feelsLike] [nvarchar](10) NULL, [todaysHigh] [nvarchar](10) NULL, [todaysLow] [nvarchar](20) NULL, [todaysNormalHigh] [nvarchar](10) NULL, [todaysNormalLow] [nvarchar](10) NULL, [todaysRecordHigh] [nvarchar](10) NULL, [todaysRecordHighYear] [nvarchar](4) NULL, [todaysRecordLow] [nvarchar](10) NULL, [todaysRecordLowYear] [nvarchar](4) NULL, [lastYearsHigh] [nvarchar](10) NULL, [lastYearsLow] [nvarchar](10) NULL, [humidity] [nvarchar](10) NULL, [windSpeed] [nvarchar](10) NULL, [windDirection] [nvarchar](20) NULL, [windGust] [nvarchar](10) NULL, [windDegrees] [nvarchar](10) NULL, [visibility] [nvarchar](10) NULL, [dewpoint] [nvarchar](10) NULL, [barometer] [nvarchar](10) NULL, [barometerDir] [nvarchar](10) NULL, [moonPhase] [nvarchar](30) NULL, [weatherSpoken] [nvarchar](max) NULL, [locationID] [smallint] NULL, [rainHour] [float] NULL, [rainToday] [float] NULL, curCondition nvarchar(200) )
        --
        Jeff Farmer
        HS 3, HSPhone
        My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
        Other Plugins In Use: APCUPSD, BLOnkyo, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

        Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2

        Comment


        • #5
          Thank you Sir!

          It is working like a charm.

          If you set the "Save X months of weather data" to 999, I assume it will never delete data, which is what I want.

          It would be nice to have a way to turn off the delete of data altogether.

          Michael

          Comment


          • #6
            Originally posted by Jobee View Post
            Thank you Sir!

            It is working like a charm.

            If you set the "Save X months of weather data" to 999, I assume it will never delete data, which is what I want.

            It would be nice to have a way to turn off the delete of data altogether.

            Michael
            Well, if you set it to 999 it wouldn't start deleting for 83 years.

            I'll add the option to set it to never delete.
            --
            Jeff Farmer
            HS 3, HSPhone
            My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
            Other Plugins In Use: APCUPSD, BLOnkyo, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

            Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2

            Comment


            • #7
              It took me a bit to connect all the pieces and parts but you can also do this through MQTT. I put a how-to video together that goes through the configuration of everything. Essentially all you need is the HS3 MQTT Plugin, Docker Desktop running a MQTT Broker Container, Node-Red, and a SQL Server. At this point, it is all free software and pretty easy to configure. The attached link is the condensed version but I put together an in-depth version as well. Hope it helps a few people!

              --
              Tim Lampen
               

              Comment


              • #8
                If you have a working SQL Server setup, all you should need to do is select Database type of SQL Server on the database admin page. Enter the network address to SQL Server and the name of the database. You can then click on the button Create Table and the tables are created. From then on each time the current schedule runs it will save a record of the data.
                Attached Files
                --
                Jeff Farmer
                HS 3, HSPhone
                My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
                Other Plugins In Use: APCUPSD, BLOnkyo, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

                Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2

                Comment


                • #9
                  Hi Jeff,
                  No support for mysql correct?

                  Thanks,
                  Aldo

                  Sent from my SM-G935V using Tapatalk

                  Comment


                  • #10
                    I have code for mysql started. I would say about half way done. The problem with finishing it is two fold. I don't have as much time for my hobbies as I used to and having to replace Weather Underground.
                    --
                    Jeff Farmer
                    HS 3, HSPhone
                    My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
                    Other Plugins In Use: APCUPSD, BLOnkyo, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

                    Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2

                    Comment

                    Working...
                    X