Perl Script: SQL commands with IF are displayed as syntax errors-CodePudding

SQL commands with IF which are working in MySQL DB are displayed as syntax errors.

if (exists(
    SELECT * FROM tanss.leistungen
    where
    firmenid = Tanss
    and date(FROM_UNIXTIME(datum)) >= DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-6 MONTH), INTERVAL -0 DAY)
    and date(FROM_UNIXTIME(datum)) < DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE) MONTH), INTERVAL -0 DAY)
) =0 ,"ohne","vorhanden") as Leistung

or more simple such as

if(niederlassung.name is null,"Niederlassung Warendorf",niederlassung.name) as Niederlassung

Error output of cmd is:

Bareword found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 123, near ") =0 ,"ohne" (Might be a runaway multi-line "" string starting on line 115) (Missing operator before ohne?) String found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 123, near "ohne","" Bareword found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 123, near "","vorhanden" (Missing operator before vorhanden?) String found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 123, near "left join tanss.anfahrtpauschale_preise as anfahrtfirma on anfahrtfirma.linkID = vertrag.id and anfahrtfirma.zoneID = "" (Missing semicolon on previous line?)

Bareword found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 115, near "if(niederlassung.name is null,"Niederlassung" (Might be a runaway multi-line "" string starting on line 111) (Do you need to predeclare if?)

Im using DBD::ODBC

Is there any way that Perl can accept such extended SQL commands without me having to edit it in the script, like im currently doing?

update:

the SQL is a string and the full statement has quotation around it
the SQL statement is 100% working

main part of code (but still not full code):

    sub getLeadingMethode{
    $SQL = "
    SELECT
        vertrag.ID as VertragsDBid,
        vertrag.name as Vertragsart,
        vertrag.datumvon as von,
        vertrag.datumbis as bis,
        firmen.displayID as Tanss,
        firmen.name as Kundenname,
    if (exists(
            SELECT * FROM tanss.leistungen
            where
            firmenid = Tanss
            and date(FROM_UNIXTIME(datum)) >= DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-6 MONTH), INTERVAL -0 DAY)
            and date(FROM_UNIXTIME(datum)) < DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE) MONTH), INTERVAL -0 DAY)
        ) =0 ,"$4","$5") as Leistung

    FROM tanss.vertrag
        left join tanss.firmen on tanss.firmen.ID = vertrag.firmenID
        left join tanss.anfahrtpauschale_preise as anfahrtfirma on anfahrtfirma.linkID = vertrag.id and anfahrtfirma.zoneID = "$6" and anfahrtfirma.linktypid = "$7"
        left join (select * from tanss.firmen_fahrt as fahrt_1 where km_einfach =(select min(km_einfach) from tanss.firmen_fahrt where firmenID = fahrt_1.firmenid) ) as anfahrt on anfahrt.firmenID = firmen.id
        left join tanss.firmen as niederlassung on niederlassung.id = anfahrt.mandantID
        left join tanss.f_info_werte as vb on vb.firmenid = firmen.id and defID = "$8";";

Full error message:

C:\Entwicklung\LWEI_SampleCodes>sampleCode20.pl
Scalar found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 115, near ") =0 ,"$4"
  (Might be a runaway multi-line "" string starting on line 100)
        (Missing operator before $4?)
String found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 115, near "$4",""
        (Missing operator before ","?)
Scalar found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 115, near "","$5"
        (Missing operator before $5?)
String found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 115, near "left join tanss.anfahrtpauschale_preise as anfahrtfirma on anfahrtfirma.linkID = vertrag.id and anfahrtfirma.zoneID = ""
        (Missing semicolon on previous line?)
Scalar found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 119, near "left join tanss.anfahrtpauschale_preise as anfahrtfirma on anfahrtfirma.linkID = vertrag.id and anfahrtfirma.zoneID = "$6"
  (Might be a runaway multi-line "" string starting on line 115)
        (Do you need to predeclare left?)
String found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 119, near "$6" and anfahrtfirma.linktypid = ""
        (Missing operator before " and anfahrtfirma.linktypid = "?)
Scalar found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 119, near "" and anfahrtfirma.linktypid = "$7"
        (Missing operator before $7?)
String found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 119, near "left join tanss.f_info_werte as vb on vb.firmenid = firmen.id and defID = ""
        (Missing semicolon on previous line?)
Scalar found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 122, near "left join tanss.f_info_werte as vb on vb.firmenid = firmen.id and defID = "$8"
  (Might be a runaway multi-line "" string starting on line 119)
        (Do you need to predeclare left?)
String found where operator expected at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 122, near """
        (Missing semicolon on previous line?)
syntax error at C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl line 115, near ") =0 ,"$4"
Execution of C:\Entwicklung\LWEI_SampleCodes\sampleCode20.pl aborted due to compilation errors.

CodePudding user response：

    $SQL = "
    .......
        ) =0 ,"$4","$5") as Leistung

You have un-escaped double quotes inside a double quoted string. This will break your quotation. You need to escape the quotes, e.g.:

    $SQL = "
    .......
        ) =0 ,\"$4\",\"$5\") as Leistung

Or use qq/q...

    $SQL = qq#
    .......
        ) =0 ,"$4","$5") as Leistung
    #

Or use a heredoc....

    $SQL = <<"END_SQL"
    .......
        ) =0 ,"$4","$5") as Leistung
END_SQL

Or if you are using DBI, instead of interpolating variables in a string, you should use placeholders

    $SQL = "
    .......
        ) =0 , ? , ?) as Leistung

And later supply the variables in your execute statement.

$dbi->prepare($SQL);        # sample code, not for use
$dbi->execute($4, $5);

Also, the variables $4, $5 etc are built-in variables for the regex match capture. It can be dangerous to use them so detached from the regex capture. I assume you are doing something like..

if ($foo =~ /(...)(...)..../) {   # capturing $1 .... $7 etc
    getLeadingMethode();          # relying on global scope variables

But it would be better to pass the values along with the sub call, like so:

if ($foo =~ /(...)(...)..../) {     # capturing $1 .... $7 etc
    getLeadingMethode($4, $5, $7);  # passing values directly, encapsulated approach

And like I said above, with DBI you want to use placeholders and let the module handle the quoting.