Two databases in one Event Handler

Ignition
1

I would like to insert data from one database table into another database table when the user clicks a button. Both databases reside on the same SQL 2008 server. How can the database be specified in the script?

sql = "INSERT INTO database1.table (a,b,c) SELECT (a,b,c) WHERE x FROM database2.table" system.db.runPrepUpdate(sql)

2

How about:

sql = "INSERT INTO database1.dbo.table (a,b,c) SELECT (a,b,c) FROM database2.dbo.table WHERE x "
system.db.runPrepUpdate(sql,[])
3

That seems to work. If I run the following in the database query browser I get my insert. If I run with my code on the button I get an error.

INSERT INTO AX_CarrierTrack.dbo.tblSequence (ORDER_NUMBER) SELECT (ORDER_NUMBER) FROM AX_POI.dbo.POI_HEADER WHERE ORDER_NUMBER = 014002473859

[code]Traceback (most recent call last):

File “event:actionPerformed”, line 7, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO AX_CarrierTrack.dbo.tblSequence (ORDER_NUMBER) SELECT (ORDER_NUMBER) FROM AX_POI.dbo.POI_HEADER WHERE ORDER_NUMBER = 014002473859, [null], , , false, false)

caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO AX_CarrierTrack.dbo.tblSequence (ORDER_NUMBER) SELECT (ORDER_NUMBER) FROM AX_POI.dbo.POI_HEADER WHERE ORDER_NUMBER = 014002473859, [null], , , false, false)
caused by GatewayException: Unexpected error for "INSERT INTO AX_CarrierTrack.dbo.tblSequence (ORDER_NUMBER) SELECT (ORDER_NUMBER) FROM AX_POI.dbo.POI_HEADER WHERE ORDER_NUMBER = 014002473859"
caused by NullPointerException

Ignition v7.6.6 (b2014040112)
Java: Sun Microsystems Inc. 1.6.0_31[/code]

4

You may be calling the system.db.runPrepUpdate function incorrectly.

Try this:

query = """INSERT INTO AX_CarrierTrack.dbo.tblSequence (ORDER_NUMBER) 
SELECT ORDER_NUMBER FROM AX_POI.dbo.POI_HEADER WHERE ORDER_NUMBER = ?"""
system.db.runPrepUpdate(query,"014002473859")
5

Thanks Nick, that’s it.