Firebird to My sql query giving error on query in my php script

Question

This question is probably duplicate and will be greatly down voted, but I am so desperate, I need to ask. Can anyone see the syntax error in this query? Any suggestions? Should I post the whole php script file?

SELECT 
                            a.sifra_robe,
                            a.kod AS kod_artikla,
                            a.cenovnik,
                            a.akcija_od,
                            a.akcija_do,
                            a.cenamp_stara,
                            a.cenamp_sa_popustom,
                            a.datum_unosa,
                            a.sifra_kategorije,
                            a.id_podkategorije,
                            a.date_change,
                            a.kod_pr,
                            (SELECT 
                                 c.barkod
                        FROM tblbarkod c
                             WHERE c.sifra_robe = a.sifra_robe LIMIT 1) AS barkod,
                            REPLACE(a.artikal, '''', '.') AS artikal,
                            REPLACE(cast(A.OPIS as varchar(32000)), '''', '.') AS opis, 
                            CAST(a.cenamp AS DECIMAL(18,2)) AS cenamp,
                            a.cenavp,
                            p.proizvodjac,
                            p.sifra_proizvodjaca,
                            CAST(SUM(l.kolicina) AS DECIMAL(18,2)) AS kolicina,
                            REPLACE(cast(list(distinct k.naziv) as varchar(32000)),'''','.') as model
                        FROM tblartikli a
                            INNER JOIN tblproizvodjac p ON p.sifra_proizvodjaca = a.sifra_proizvodjaca
                            LEFT JOIN tlokacija l ON l.sifra_robe = a.sifra_robe
                            LEFT JOIN tbl_art_komp ak on ak.sifra_robe = a.sifra_robe
                            LEFT JOIN tbl_kompatibila k on k.id = ak.id_kompatibile
                        WHERE a.id_podkategorije IN ($kat)
                            AND a.web_b2c = 1
                            AND (a.cenovnik = 'A'
                            OR a.cenovnik = '1'
                            OR a.cenovnik = 'R'
                            OR a.cenovnik = 'D'
                            OR a.cenovnik = 'N')
                            AND (a.cenovnik = 'D'
                            OR (l.sifra_magacina IN (1001, 1003, 1004, 1010, 1012, 1020, 1092)))
                        GROUP BY a.sifra_robe, a.kod, a.cenovnik, a.akcija_od, a.akcija_do, a.cenamp_stara, a.cenamp_sa_popustom, a.datum_unosa, a.sifra_kategorije, a.id_podkategorije, a.date_change, a.kod_pr, a.artikal, a.opis, a.cenamp, a.cenavp, p.proizvodjac, p.sifra_proizvodjaca
                        ORDER BY a.artikal ASC

I get the following error:

Warning: ibase_query(): Dynamic SQL Error SQL error code = -104 Token unknown - line 31, column 54 ) in

I do not recognise `SELECT FIRST 1` as MYSQL syntax, and it just happens to be on Line 14 — RiggsFolly, Jul 12 '17 at 13:03
It is also very useful if you show us ALL the error message and not just a summary of it — RiggsFolly, Jul 12 '17 at 13:04
You have to remove `FIRST 1` and add `LIMIT 1` at the end of the sub-select. — Sebastian Brosch, Jul 12 '17 at 13:05
This looks more like a InterBase or Firebird query, not MySQL. — Māris Kiseļovs, Jul 12 '17 at 13:21
With your last edit, the error no longer matches the query shown. Please clean up your question so it's consistent and describe your problem better. — Mark Rotteveel, Jul 12 '17 at 13:34
I find it interesting that you changed your question from a MySQL syntax error to a Firebird (or Interbase) syntax error. What exactly are you trying to do? — Mark Rotteveel, Jul 12 '17 at 13:42
I am trying to synchronize firebird db data with mysql db data. To import data from firebird to mysql database with the help of this query. — Nancy, Jul 12 '17 at 13:43
The query as shown will produce a _"Token unknown - line 17, column 64 LIMIT"_ instead, as `LIMIT` is not valid for Firebird (or are you using Interbase instead?). I ask you again to cleanup your question and make sure all information is consistent, we are not here to play human debugger. If I change back to the original query, then I get _"Token unknown - line 31, column 54 $"_, which would suggest that `$kat` is replaced with an empty string before you execute the query. — Mark Rotteveel, Jul 12 '17 at 13:48
`To import data from firebird to mysql database with the help of this query` - that is not possible to do with one query. You can not access BOTH servers, Firebird and MySQL, from one query. So, you have to make up your mind, which of two servers you access with this query. Do you read Firebird database or do you read MySQL database with this query? — Arioch 'The, Jul 12 '17 at 17:17

Māris Kiseļovs · Answer 1 · 2017-07-12T13:14:34.880

1

SELECT FIRST 1 ... in your subquery is not a valid MySQL syntax. You need to use LIMIT 1 at end of subquery.

Fixed query:

SELECT 
      a.sifra_robe,
      a.kod AS kod_artikla,
      a.cenovnik,
      a.akcija_od,
      a.akcija_do,
      a.cenamp_stara,
      a.cenamp_sa_popustom,
      a.datum_unosa,
      a.sifra_kategorije,
      a.id_podkategorije,
      a.date_change,
      a.kod_pr,
      (
           SELECT 
               c.barkod
           FROM tblbarkod c
           WHERE c.sifra_robe = a.sifra_robe 
           LIMIT 1
      ) AS barkod,
      REPLACE(a.artikal, "''", '.') AS artikal,
      REPLACE(cast(A.OPIS as char(32000)), "''", '.') AS opis, 
      CAST(a.cenamp AS DECIMAL(18,2)) AS cenamp,
      a.cenavp,
      p.proizvodjac,
      p.sifra_proizvodjaca,
      CAST(SUM(l.kolicina) AS DECIMAL(18,2)) AS kolicina,
      REPLACE(cast(k.naziv as char(32000)),"''",'.') as model
  FROM tblartikli a
      INNER JOIN tblproizvodjac p ON p.sifra_proizvodjaca = a.sifra_proizvodjaca
      LEFT JOIN tlokacija l ON l.sifra_robe = a.sifra_robe
      LEFT JOIN tbl_art_komp ak on ak.sifra_robe = a.sifra_robe
      LEFT JOIN tbl_kompatibila k on k.id = ak.id_kompatibile
  WHERE a.id_podkategorije IN ($kat)
      AND a.web_b2c = 1
      AND (a.cenovnik = 'A'
      OR a.cenovnik = '1'
      OR a.cenovnik = 'R'
      OR a.cenovnik = 'D'
      OR a.cenovnik = 'N')
      AND (a.cenovnik = 'D'
      OR (l.sifra_magacina IN (1001, 1003, 1004, 1010, 1012, 1020, 1092)))
  GROUP BY a.sifra_robe, a.kod, a.cenovnik, a.akcija_od, a.akcija_do, a.cenamp_stara, a.cenamp_sa_popustom, a.datum_unosa, a.sifra_kategorije, a.id_podkategorije, a.date_change, a.kod_pr, a.artikal, a.opis, a.cenamp, a.cenavp, p.proizvodjac, p.sifra_proizvodjaca
  ORDER BY a.artikal ASC

edited Jul 12 '17 at 13:14

answered Jul 12 '17 at 13:05

Māris Kiseļovs

15,549
5
37
48

Hi, tried that and in my php script (in which I call this query) I got the following error now: Warning: ibase_query(): Dynamic SQL Error SQL error code = -104 Token unknown - line 17, column 64 LIMIT in I am connecting to firebird db and with this query I load data from it to mysql databse. Hence the ibase_query error.It seems there is still some syntax error in my query. Any suggestions? Should I post the whole php script file? – Nancy Jul 12 '17 at 13:10
@Nancy i fixed this as valid MySQL query by also replacing "cast as varchar" to "cast as char" and replaced "list(distinct k.naziv)" to "k.naziv" but I cannot guarantee for this query to produce accurate results because I don't know what it's supposed to do and what data structure you got. Also your error message says that it's InterBase not MySQL. – Māris Kiseļovs Jul 12 '17 at 13:19
Thanx, I will change the tags and title of my question then. – Nancy Jul 12 '17 at 13:22
1

[`list()`](https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-aggfuncs.html#fblangref25-functions-aggfuncs-list) in Firebird is [`group_concat()`](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat) in MySQL. – Mark Rotteveel Jul 12 '17 at 13:31
1

you also changed apostrophes to quotes - `REPLACE(a.artikal, "''", '.')` which would make the query invalid in Firebird modern SQL Dialect 3 – Arioch 'The Jul 12 '17 at 17:21
Thanx everyone for your effort in trying to help me. I guess I have much more learning to do. If I come up with the right solution, i will make sure to post the answer here. Cheers. – Nancy Jul 13 '17 at 06:24

Firebird to My sql query giving error on query in my php script

1 Answers1