Execute Function With Variables from Other DB

Question

I'm trying to execute a function with values from other DB.

I'm using Framework CakePHP 4.x with 2 DB, Postgresql and MariaDB.

In Controller/Programscontroller.php:

use Cake\Datasource\ConnectionManager;

public function pro()
{    $connection = ConnectionManager::get('default');// Postgresql,in this one i have my function test(character varying,bigint,bigint)
     $connection2 = ConnectionManager::get('test');// MariaDB, in this one i want some variables
     $data = $connection2->execute('SELECT stuffs from stuff'); //My $data i want
      foreach ($data as $data) //I declare here 3 variables, to save some $data
    {  $w1=$data['cod_item']; 
       $w2=$data['fecha_solicitud_pago'];
       $w3=$data['monto_total'];
       $connection->execute('SELECT test(w1,w2,w3)');//here is my problem
      }    
}

i execute and have the error SQLSTATE[42703]: Undefined column 7, dont exist column w1.How is the correct way to define my 3 variables w1,w2,w3, and use them in my function from other DB.

score 0 · Accepted Answer · answered Feb 13 '20 at 17:07

I see two problems:

1. $connection->execute('SELECT test(w1,w2,w3)'); is not using the php variables. It shoul be:

$connection->execute("SELECT test($w1,$w2,$w3)");

Note that you have to use double quotes to use a variable inside a string.

2. You have to use the function in the correct form. If the function receives (text,date,number) you have to send it accordingly:

$connection->execute("SELECT test('$w1','$w2',$w3)");
// or, for more accuracy
$connection->execute("SELECT test('$w1'::text,'$w2'::date,$w3)");

When you do $connection->execute() you are executing an SQL statement, so it has to be well written. The above examples would be interpreted in POSTGRESQL as:

SELECT test('asbd'::text,'2020-02-05'::date,39021)

Assuming the php variables had those values.

Execute Function With Variables from Other DB

1 Answers1