Logging, Exporting or saving manually (SEQUENCE) on DB thru ODBC (SQL) by a trigger


nanpipe00

Recommended Posts

Hi, I want to save into a database (SQLSERVER) using ODBC some information triggered by the change of a variable.

I have had some struggle using Logging, Exporting and SEQUENCE.

1) Logging is the easiest in my case, but I have no control about what I am saving ( I don't know how to ) so I am currently saving duplicated information that is useless, I tried to use Logging.LoggingName.startstop() + delay(milliseconds) but sometimes works and sometimes it doesn't and I still have no control.

2) Using exporting was almost the best... I did a script (as shown below)  and worked fine, except that anytime it was trying to connect to the database it was asking for the password so... once again useless.

ID is an OPC value that is randomly changing between -100 and 100

Global oldValue = 0
Global newValue = 0

while(1)
     newValue = ID
     if(newValue!=oldValue
           export.Exp.Start()  // I also used beginexport(exp)
           oldValue = NewValue 
   endif
endwhile

3) I finally when through the manual way... 

time, temp and pres, are OPC values in channels.

Global count = 0
Global oldValue = 0
Global newValue = 0

while(1)
     newValue = Valor1
           if(newValue!=oldValue)
                     count++
                     dbase = db.OpenEx("DSN=DAQF;UID=batch;PWD=12345")
             if(count==1)
                 db.Execute(dbase,'CREATE TABLE Hartman(time datetime, ID int, Temp int, Pres int)')

             endif
  // db.Execute(dbase,'INSERT INTO Hartman values (' + FormatDateTime("%y-%m-%d %H:%M:%S",systime()) + ',' + count + ',' + Valor1 + ',' + Valor + ')')

// db.Execute(dbase,'INSERT INTO Hartman values (' + Valor.time[0] + ',' + count + ',' + Valor1 + ',' + Valor + ')')

// db.Execute(dbase,'INSERT INTO Hartman values (' + systime() + ',' + count + ',' + Valor1 + ',' + Valor + ')')
     
                    db.Execute(dbase,'INSERT INTO Hartman values (' + Tiempo + ',' + count + ',' + Valor1 + ',' + Valor + ')')
                    db.Close(dbase)
      oldValue = NewValue
      endif

endwhile

I wanted to use the red-color-text lines instead of the blue one, but it was showing an error (shown below (1))

(1) db.Execute(dbase,'INSERT INTO Hartman values (' + FormatDateTime("%y-%m-%d %H:%M:%S",systime()) + ',' + count + ',' + Valor1 + ',' + Valor + ')')
Unable to execute: HY010:  Line 1

(2) db.Execute(dbase,'INSERT INTO Hartman values (' + systime() + ',' + count + ',' + Valor1 + ',' + Valor + ')')
Unable to execute: HY010:  Line 1

so I couldn't use the DAQF time in order to save it into the database, instead I had to import from OPC the time which makes no sense for me...

In this moment the last script is working and doing what I wanted to... but i have those doubts and I would want to solve them.

I appreciate any help... I attach the file used for this...

Basculas.ctl

Link to comment
Share on other sites

For maximum flexibility you should use the later method, with db.execute().  When doing db.execute, I usually assign the sql statement to a string variable, then execute that string variable.  That way I can add a ? sql and display the what I'm sending to the database in the command / alert window.  Its then usually obvious why a sql statement fails as your did.  So, do:

private string sql
sql = 'INSERT INTO Hartman values (' + FormatDateTime("%y-%m-%d %H:%M:%S",systime()) + ',' + count + ',' + Valor1 + ',' + Valor + ')'
? sql
db.execute(dbase, sql)

I believe your problem is that SQL statements require dates in ymd format to be enclosed in quotes.  You did not do this.  Also, I believe you also need to use %Y not %y as some SQL databases require the full 4 digit year.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.