1

I'm using the same query, through PDO connector, to concatenate string in both: MySQL database and a mirroring SQLite one. Both they have same table same structure etc.

But when the query is executed, MySQL performs the query correctly, while SQLite goes in error thus I get an error 500.

Is there a solution to have it working for both databases please? Here down below the parameterized Query I use.

As I said: Without the line `IP` = CONCAT(IP, :param3), everything is working fine (on MySQL as well as SQLite)

With the line `IP` = CONCAT(IP, :param3), MySQL is correctly concatenated, SQLite crashes

$query = "UPDATE downloaded SET
    `player` = player + 1,
    `when` = :param0,
    `IP` = CONCAT(IP, :param3)
    WHERE who = :param1 AND what = :param2";

$params = array(
    ':param0' => $when_,
    ':param1' => $Email,
    ':param2' => $file,
    ':param3' => $IP_Caller . "<br />"
);

Thanks in advance for your help

Tormy Van Cool
  • 658
  • 10
  • 29

1 Answers1

2

SQLite does not support the function CONCAT().
You will have to use the operator ||:

`IP` = IP || :param3

If you want to use the same code in MySql too, you will have to enable the PIPES_AS_CONCAT mode. This mode is by default disabled so the operator || is just an alias for the logical OR.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Also if you look at https://stackoverflow.com/a/37811056/1213708, *MySQL also supports || if PIPES_AS_CONCAT mode is enabled* – Nigel Ren Jul 19 '20 at 19:47
  • In this case SQLite is correctly concatenated. But MySQL substitutes the inner value, with 1. – Tormy Van Cool Jul 19 '20 at 20:03
  • @TormyVanCool if you want to use the same code in MySql too, you will have to enable the PIPES_AS_CONCAT mode: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_pipes_as_concat Thir mode is by default disabled so the operator `||` is just an alias for the logical `OR`. – forpas Jul 19 '20 at 20:09