nanpipe00 Posted November 24, 2016 Share Posted November 24, 2016 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 More sharing options...
AzeoTech Posted November 28, 2016 Share Posted November 28, 2016 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.