Insert query doesn't work on server

Question

Below code runs without any problems on my local server. However, when I try to run it on the intended server, two of my queries don't work - they do not INSERT as they are supposed to. I've marked two queries that don't work with comments, the rest works. Intended server runs on PHP 5.6.30-0+deb8u1.

UPDATE: thanks to aynber, I've tracked the error. This is the error for the first query: prepared statement \"editRecord\" does not exist" I don't understand why this works on local server but not on intended one.

UPDATE 2: error between prepared statement and execution: syntax error at or near \"ON\"\nLINE 3:

case "editRecord":

$id = openPandoraBox(post("id"));
$tutorAbsence = post("tutorAbsence");
$clientAbsence = post("clientAbsence");

if($tutorAbsence == "1") {

    if(post("tutor") != "0") {
        //  ------------this query does not work.-----------
        $absUpsSql = "INSERT INTO tutorabsence(id, tutorid, reason)
                      VALUES ($1, $2, $3)
                      ON CONFLICT (id)
                      DO UPDATE SET tutorid=$2, reason=$3";

        $absUpsPrep = pg_prepare($conn, 'editRecord', $absUpsSql);
        $absUpsQry = pg_execute($conn, 'editRecord',
                                array($id, post("tutor"), post("tutorreason"))
                     );
    } else {
        $tutorAbsence = "0";
    };
} else {
    $absDelSql = "DELETE FROM tutorabsence WHERE id=$1";
    $absDelPrep = pg_prepare($conn, 'absDel', $absDelSql);
    $absDelQry = pg_execute($conn, 'absDel', array($id));
};

if($clientAbsence == "1"){ 
    if(post("client") != "0") {
        //  ------------this query does not work.-----------
        $absUpsSql = "INSERT INTO clientabsence(id, clientid, reason)
                      VALUES ($1, $2, $3)
                      ON CONFLICT (id)
                      DO UPDATE SET clientid=$2, reason=$3";

        $absUpsPrep = pg_prepare($conn, 'absUps', $absUpsSql);
        $absUpsQry = pg_execute($conn, 'absUps',
                                array($id, post("client"), post("clientreason"))
                     );
    } else {
        $clientAbsence = "0";
    };
} else {
    $absDelSql = "DELETE FROM clientabsence WHERE id=$1"; 
    $absDelPrep = pg_prepare($conn, 'absDelOne', $absDelSql);
    $absDelQry = pg_execute($conn, 'absDelOne', array($id));
};

$resultSql = "UPDATE appointments
                 SET hour=$1, tutorid=$2, 
                     clientid=$3,  purpose=$4, 
                     tutornotshown=$5, clientnotshown=$6
              WHERE appid=$7"; 
$resultPrep = pg_prepare($conn, 'resultSql', $resultSql);
$result = pg_execute($conn, 'resultSql',
                     array(post('hour'), post("tutor"), post("client"),
                           post("purpose"), $tutorAbsence, $clientAbsence, $id
                     )
          );

echo json_encode(array("success" => 1));
break;

Have you checked for [errors](http://php.net/manual/en/function.pg-last-error.php)? What is $1, $2, and $3? — aynber, Mar 13 '17 at 14:41
I can't get any errors from console. I'm having trouble tracking errors, well I'm uh, a beginner. $1, $2, $3 are placeholders for prepared statement. — Ahmet, Mar 13 '17 at 14:42
Check the link I posted (click on the word errors), it will help you check for errors. — aynber, Mar 13 '17 at 14:44
you may want to make sure that the db user has insert permissions — happymacarts, Mar 13 '17 at 14:55
I can use insert on other situations, such as creating a person record. — Ahmet, Mar 13 '17 at 14:56
I'm guessing your prepare statement failed. Have you tried checking for errors between your prepare and execute statements? — aynber, Mar 13 '17 at 15:01
[postgres syntax error at or near “ON”](http://stackoverflow.com/questions/40327449/postgres-syntax-error-at-or-near-on) I think this question is what I'm looking for. I'll update once I finish required changes — Ahmet, Mar 13 '17 at 15:12
hi @Ahmet, I answered your question following your second update. — Gab, Mar 13 '17 at 15:36
Hi @Gab , thank you for your answer. I'm having a dinner now :) I'll check it out. — Ahmet, Mar 13 '17 at 15:45
ok. Please select it as the correct answer if it fixes your problem — Gab, Mar 13 '17 at 15:46
I've read your answer. `SELECT version();` will help me finding more accurate solutions which is why I upvoted your answer. However, I already found [this](http://stackoverflow.com/questions/40327449/postgres-syntax-error-at-or-near-on) link I've provided above before your answer, which already covers the content of your answer. I'll provide an answer that meets required changes in code or choose as the correct answer if it is provided before I change it myself. The solution I need right now is how to alter this upsert according to the version. — Ahmet, Mar 13 '17 at 16:44

score 1 · Answer 1 · answered Mar 13 '17 at 15:24

UPDATE 2: error between prepared statement and execution: syntax error at or near \"ON\"\nLINE 3:

If it works on your local server but not on production server, it is likely that they don't run the same version of PostgreSQL. ON CONFLICT is a feature that was released with PostgreSQL 9.5 (https://www.postgresql.org/docs/9.5/static/sql-insert.html) which is still fairly recent.

You should run this query on the production server to check out which version of PostgreSQL it uses:

SELECT version();

Your server probably runs PostgreSQL 9.5 or 9.6 while the production server is probably on an older release.

score 0 · Accepted Answer · answered Mar 13 '17 at 20:49

0

Upsert for PostgreSQL below 9.5 is too complicated. I am very short of time, so I'll just use SELECT COUNT(*) and if's.

answered Mar 13 '17 at 20:49

Ahmet

115
11

Insert query doesn't work on server

2 Answers2