sqlpostgresqlibatisdblink

iBATIS and dblink?


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?


Solution

  • 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#)
       );