Timestamp issues regarding db insert

General Discussion
, ,
1

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 :cry:

But nothing seems to work, I cannot insert the date when my button is pressed for the ID :sob: :sob: :sob: :sob: :sob: :sob: :sob: :sob: :sob: :sob:

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 :angry:

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 :confused:

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 :cry:

2

I don’t think you’re getting a proper datetime value to put in the database. Try this python code to get the current datetime value (there are other ways of doing this as well but this is what I use).

from java.util import Calendar
cal = Calendar.getInstance()
now = cal.getTime()
1 Like
3

Hi Kevin. The Microsoft equivalent for MySQL’s now() function is getutcdate() or getdate().

1 Like
4

Hi Kevin,
You are mixing a few non-scripting items into scripts where they don’t belong. The now() function you tried to use is an Ignition expression function, not a scripting function. You need to use something like Duffinator’s code to acquire an actual timestamp in jython (though I would grab an instance of java.util.Date directly instead of using java.util.Calendar).
When now() didn’t work for you, you tried to insert SQL’s CURRENT_TIMESTAMP built-in. That would have worked if placed directly within the SQL text, but it cannot be substitution item. Jython tries to find it as a variable name in jython, and that doesn’t exist.
Try this:

from java.util import Date
system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,?,?)",
	[wo_number,Date(),value_state])
1 Like
5

Or this:

system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,CURRENT_TIMESTAMP,?)",
	[wo_number, value_state])
1 Like
6

Your answer made me take a second look. There is an SQL function now() but it is better used with a Select statement. I simply did this when I used an Insert

tstamp = system.db.dateFormat(system.tag.read("[System]Client/System/CurrentDateTime").value, “yyyy-MM-dd HH:mm:ss”)
system.db.runPrepUpdate(“INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,?,?)”,
[wo_number,tstamp,value_state])

But if I had to use a Select I would still use the native date time function for my DB.

1 Like
7

This can also be simplified even more using Ignition's built in system.date functions:

system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,?,?)",  [wo_number,system.date.now(),value_state])
3 Likes
8

Hello all. Thanks for the support.
I tried all your suggestions and finally decided to change the timestamp value of my column to datetime (alter drop and alter add), and use the system.date.now() provided by @PGriffith

Thank you all for the fast response, it was really useful for determining what was the best suitable option for my project.