I am using PostgreSQL, and I use the dblink feature to update data on remote database. My project using iBATIS to work with database.
I need to run the query statement with parameter but the problem is the db_link require SQL to be enclosed in quote. And iBATIS do not understand it..
I have iBATIS sql:
SELECT
dblink_exec(
'host=192.168.1.1 port=5432 user=abc dbname=wms password=abc',
'UPDATE m_user_profile
first_name = #4#,
last_name = #5# ,
mobile_no = #6#
WHERE user_id = CAST(#3# AS numeric)'
);
But due to sql is enclosed in quote, the iBATIS can not find parameter to replace.
Is any solution for me to do it?
Would format help? Smth like:
SELECT
dblink_exec(
'host=192.168.20.60 port=5432 user=clk dbname=clkdb_wms password=clk',
format('UPDATE m_user_profile
first_name = %L,
last_name = %L ,
mobile_no = %s
WHERE user_id = CAST(%s AS numeric)',#4#,#5#,#6#,#3#)
);