Announcement

Collapse
No announcement yet.

Script help

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

  • Script help

    I'm trying to write a script that writes to a Mysql database. I have it done but I have several minor issues.
    I have a very long insert statement right now on one line, see below. How do I spit the statement in few extra lines?

  • #2
    Use hs.devicevalueEx(xxxx) instead.
    HS 3.0.0.548: 1990 Devices 1172 Events
    Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

    Comment


    • #3
      Thanks sparkman, how do I spit a long insert statement in multiple lines. Right now is diffould to read it. I tried _ but does not work.

      Comment


      • #4
        Can't see your script anymore, so don't know why not. One option may be to use more variables, define those before your insert statement and then use the variables in the insert statement, but without seeing it, hard to know for sure.
        HS 3.0.0.548: 1990 Devices 1172 Events
        Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

        Comment


        • #5
          sorry, here it is. I'm starting to replace with these instead but still a long query.
          Dim Dev_EnergyAddedbyLastCharge as Double
          Dev_EnergyAddedbyLastCharge = hs.devicevalue(2537)

          myCommand.CommandText = "Insert into teslacharge (chargestatus, energyaddedlastcharge, energycostlastcharge, energycostkwh, odometer, batterylevel, batteryrangeestimated, batteryrangeideal, batteryrange, milesaddedlastchargeestimated, milesaddedlastchargeideal, insideTemperature, outsideTemperature, latitude, longitude, location ) VALUES ('" & hs.devicevalue(2536) & "','" & Dev_EnergyAddedbyLastCharge & "','" & hs.devicevalue(2555) & "','" & hs.devicevalue(2554) & "','" & hs.devicevalue(2497) & "','" & hs.devicevalue(2498) & "','" & hs.devicevalue(2499) & "','" & hs.devicevalue(2500) & "','" & hs.devicevalue(2501) & "','" & hs.devicevalue(2539) & "','" & hs.devicevalue(2538) & "','" & hs.devicevalue(2518) & "','" & hs.devicevalue(2519) & "','" & hs.devicevalue(2547) & "','" & hs.devicevalue(2548) & "','" & hs.devicevalue(2549) & "')"

          Comment


          • #6
            You could shorten it up by just specifying the values and not the attributes as long as they align to the same order...
            HS3PRO 3.0.0.500 as a Fire Daemon service, Windows 2016 Server Std Intel Core i5 PC HTPC Slim SFF 4GB, 120GB SSD drive, WLG800, RFXCom, TI103,NetCam, UltraNetcam3, BLBackup, CurrentCost 3P Rain8Net, MCsSprinker, HSTouch, Ademco Security plugin/AD2USB, JowiHue, various Oregon Scientific temp/humidity sensors, Z-Net, Zsmoke, Aeron Labs micro switches, Amazon Echo Dots, WS+, WD+ ... on and on.

            Comment


            • #7
              It makes sense, i will do it. I'm surprised that VB does not have something that let you go to the next line. I saw in a script that they use this symbol "_" but does not work when I do it

              Comment


              • #8
                Originally posted by alphatech View Post
                It makes sense, i will do it. I'm surprised that VB does not have something that let you go to the next line. I saw in a script that they use this symbol "_" but does not work when I do it
                When you use the _ character to indicate the next source line, the _ must be separated by a space, and cannot be inside a quoted string.

                Code:
                buff = "this is a string " + _
                            "that is very long."

                Comment


                • #9
                  The issue is that the whole thing is one string, so _ won't work. You could do something like this:

                  Code:
                  Dim myCommandString as String = ""
                  myCommandString = "Insert into teslacharge (chargestatus, energyaddedlastcharge, energycostlastcharge, energycostkwh,"
                  myCommandString = myCommandString & "odometer, batterylevel, batteryrangeestimated, batteryrangeideal, batteryrange,"
                  myCommandString = myCommandString & "...
                  etc.
                  
                  
                  myCommand.CommandText = myCommandString
                  HS 3.0.0.548: 1990 Devices 1172 Events
                  Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

                  Comment


                  • #10
                    Thank you, you have been of a big help.
                    On this last part of the insert statement (Dev_Location) is a string. & Dev_Longitude & "','" & Dev_Location & "')" I do not know how to put the "" around it instead of a single quote. The Dev_location is a device string.

                    Comment


                    • #11
                      In that case you eliminate the single quotes around those.

                      Code:
                      & Dev_Longitude & "," & Dev_Location & ")"
                      HS 3.0.0.548: 1990 Devices 1172 Events
                      Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

                      Comment


                      • #12
                        Please use the script tags in the forum, as the forum can insert spaces into regular text, but it won't do that with script tags surround the text. It's best to delete your previous post, create a new post, insert the script tags and then paste your code in between. The script tags are available in the advanced editing mode, so you may need to click on the "A" button when creating the post. The script tags are the button with the # on it.
                        HS 3.0.0.548: 1990 Devices 1172 Events
                        Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

                        Comment


                        • #13
                          Sorry to give so much trouble, I tried it but does not work, I'm sure because I added another field after it.
                          Code:
                          myCommand.CommandText = "Insert into teslacharge (chargestatus, energyaddedlastcharge, energycostlastcharge, energycostkwh, odometer, batterylevel, batteryrangeestimated, batteryrangeideal, batteryrange, milesaddedlastchargeestimated, milesaddedlastchargeideal, insideTemperature, outsideTemperature, latitude, longitude, location, ymd ) VALUES ('" & Dev_ChargeStatus & "','" & Dev_EnergyAddedbyLastCharge & "','" & Dev_EnergyCostLastCharge & "','" & Dev_EnergyCostKwh & "','" & Dev_Odometer & "','" & Dev_BatteryLevel & "','" & Dev_BatteryRangeEstimated & "','" & Dev_BatteryRangeIdeal & "','" & Dev_BatteryRange & "','" & Dev_MilesAddedLastChargeEstimated & "','" & Dev_MilesAddedLastChargeIdeal & "','" & Dev_InsideTemperature & "','" & Dev_OutsideTemperature & "','" & Dev_Latitude & "','" & Dev_Longitude & "," & Dev_Location & "','" & strfulldate & "')"

                          Comment


                          • #14
                            Originally posted by alphatech View Post
                            Sorry to give so much trouble, I tried it but does not work, I'm sure because I added another field after it.
                            Can you post the whole script? Does MySql require the single quotations around the values you are inserting?
                            HS 3.0.0.548: 1990 Devices 1172 Events
                            Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

                            Comment


                            • #15
                              Please see attached. I believe on the string it needs this quote " as an example would be "home"
                              Attached Files

                              Comment

                              Working...
                              X