Search code examples
maximo

Insert Maximo data into another database server

I want to insert some maximo data into table in another database server. I create an automation script like this

from psdi.security import UserInfo
from psdi.server import MXServer
from psdi.util import MXApplicationException
from psdi.util import MXException
from java.rmi import RemoteException
from java.lang import System
from java.text import Format, DateFormat, SimpleDateFormat
from java.lang import System

mx = MXServer.getMXServer();
ui = mx.getSystemUserInfo();


url= "jdbc:sqlserver://MAXIMODEMO:1433; database=IntegrationTest; user=maxadmin; password=password; encrypt=false; trustServerCertificate=false; loginTimeout=30;";

from java.lang import Class
from java.sql import DriverManager,SQLException

#load driver and register
Class.forName(jdbc_driver).newInstance()
DriverManager.registerDriver(Class.forName(jdbc_driver).newInstance())

#get Connection
#connection = DriverManager.getConnection(jdbc_url, jdbc_user), jdbc_password)
connection = DriverManager.getConnection(url)

#find if item exist
sql = "Select itemnum from item where itemnum='"+mbo.getString("ITEMNUM")+"'"
result = connection.createStatement().executeQuery(sql)

sqlinsert = ""

sdf = SimpleDateFormat("yyyy-MM--dd");

if(result.next()) :
    sqlinsert = "Update Item set description='"+mbo.getString("DESCRIPTION")+"', orderunit='"+mbo.getString("ORDERUNIT")+"', status='"+mbo.getString("STATUS")+"' where ItemNum='"+mbo.getString("ITEMNUM")+"'"
else:
    sqlInsert="Insert into item(itemnum, description, orderunit, statusdate, status, groupname) values('" + mbo.getString("ITEMNUM")+ "','" + mbo.getString("Description") + "', '" + mbo.getString("ORDERUNIT") + "','" + sdf.format(mbo.getDate("STATUSDATE")) + "', '" + mbo.getString("STATUS") + "','" + mbo.getString("GROUPNAME") + "') "
result.close()

result = connection.createStatement().executeQuery(sqlinsert)

connection.close()

There's no error but the data not inserted. the query for select is working fine, it can return the value, but the insert / update is not working. Did I miss something in executing insert/update query?

Solution

  • You are missing a connection.commit() before your connection.close().

    Also, you should be calling executeUpdate() with your insert or update statement text instead of calling executeQuery() with it. The JavaDocs for java.sql.Statement.executeUpdate() say, "Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement."