Hi!
I've been struggling with the timestamp insert into my database.
It's a Microsoft SQL Server using the MSSQL Translator.
My main window display 3 buttons, Start, Pause and Stop.
I'm trying to create a table in the windows using also a date Range component to track wheter for some ID product the button start was pressed and button stop was pressed. So when the user specifies the range, a whole list of each product ID will be displayed showing details of its behaviour.
Using my DB connection I'm attempting to send an Int value to the DB, creating new rows for every time the Start button is pressed. When pressed, my database should create a record of the date when the button was used, and then will register when the STOP button is pressed.
My idea for the table structure was something like
- id (int) not null
- StartDate (timestamp)
- Status (int)
- EndDate (timestamp)
So, first issue. When creating the table, an error regarding the timestamp columns didn't allow me to create two columns of this type, therefore I've created two tables with these two dates one on each table. This way when START is pressed, this should insert the StartDate on table1 with the ID specified and when STOP is pressed, that should insert the endDate on table 2 with same ID.
So when creating the main table, a simple join would allow me to see these whole information.
I've developed scripts for the behaviour of every button and attempted the following
system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date,"+
" status) VALUES (?,?,?)", [wo_number,now(0),value_state])
NameError: name 'now' is not defined
this
system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date,"+
" status) VALUES (?,?,?)", [wo_number,CURRENT_TIMESTAMP,value_state])
NameError: name 'CURRENT_TIMESTAMP' is not defined
and some other forum soultions like
import datetime
now = datetime.date.today()
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,?,?), [7811436, 2017-06-28, 1], , , false, false) Cannot insert an explicit value into a timestamp column.
Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. <- I've tried this also too
But nothing seems to work, I cannot insert the date when my button is pressed for the ID
Any kind of help will be appreciated. Am I copmplicating my existence? is there anyway of doing this simpler? Is there something like an Ignition feature that I don't know yet?
I've tried inserting row data without specifying the date, hoping the timestamp automaically will create that record, but had no success in the creation of this, trash data is inserted in that column. AND also when I first tried creating the table, this wouldn't allow me to define dafult values, like the on update current_timestamp
Some random thoughts: I'm almost sure that I don't require tags, because the data is for information purposes, and there are thousand of IDs, so doing something like historian wouldn't be useful, I guess
Some Background: I'm fully new at Ignition, I've completed the Core Ignition Training modules, and watched all videotutorials of the basics. I've previously worked with Java using netbeans and using timestamp in my databases was far too easy when doing this kind of stuff.
Help