[Danhae]

Hi,
everytime i use a Variable in an SQL Trigger i get the the Error 'syntax error, unexpected '' (T_STRING) in the debugger'.
If i set the value from my variables direct in the sql query i get the expected values back.

I tried all prefixes, set my variable field in the Dynaform to view/disabled and a lot of other stuff, but i cant figure it out.

Maybe some of you know this kind of problem? What am i doing wrong?

thank you.

Regards

[amosbatto]

If you post your code, maybe someone can help you. Also, see this:
http://stackoverflow.com/questions/1805 ... 7#18092277

[Danhae]

Hi, i was a little bussy when i wrote that last Post.

So here is my Trigger Code:

Code: Select all

$db = '3958231365812191caf2307054234752';
$query = 'Select 
Convert([varchar](10),[KHKVKBelege].[USER_Geplant],104) As Auslieferung,
  [KHKPpsFaBelegeBedarfVerursacher].[MengeBasis] as Menge,
  [KHKVKBelege].[A0Matchcode] as Kunde
From [KHKPpsFaBelegeBedarfVerursacher]
  Inner Join [KHKVKBelegePositionen] On [KHKVKBelegePositionen].[Mandant] =
    [KHKPpsFaBelegeBedarfVerursacher].[Mandant] And
    [KHKVKBelegePositionen].[BelPosID] =
    [KHKPpsFaBelegeBedarfVerursacher].[VerursacherID]
  Inner Join [KHKVKBelege] On [KHKPpsFaBelegeBedarfVerursacher].[Mandant] =
    [KHKVKBelege].[Mandant] And [KHKVKBelegePositionen].[BelID] =
    [KHKVKBelege].[BelID]
Where   
  [KHKPpsFaBelegeBedarfVerursacher].[RueckmeldungID] = 0 and
  [KHKPpsFaBelegeBedarfVerursacher].[BelID] = "@=FAID"';
$result = executeQuery($query, $db);
if (is_array($result) and count($result) > 0)
    @=VerursacherGrid = $result;

Maybe i just made something wrong, but i dont get it. I hope someone can help me.

Thank you

[ashkufaraz]

You must set point before and after of your variable

Code: Select all

 ".@=FAID."'

Try this

Code: Select all

$db = '3958231365812191caf2307054234752';
$query = 'Select 
Convert([varchar](10),[KHKVKBelege].[USER_Geplant],104) As Auslieferung,
  [KHKPpsFaBelegeBedarfVerursacher].[MengeBasis] as Menge,
  [KHKVKBelege].[A0Matchcode] as Kunde
From [KHKPpsFaBelegeBedarfVerursacher]
  Inner Join [KHKVKBelegePositionen] On [KHKVKBelegePositionen].[Mandant] =
    [KHKPpsFaBelegeBedarfVerursacher].[Mandant] And
    [KHKVKBelegePositionen].[BelPosID] =
    [KHKPpsFaBelegeBedarfVerursacher].[VerursacherID]
  Inner Join [KHKVKBelege] On [KHKPpsFaBelegeBedarfVerursacher].[Mandant] =
    [KHKVKBelege].[Mandant] And [KHKVKBelegePositionen].[BelID] =
    [KHKVKBelege].[BelID]
Where   
  [KHKPpsFaBelegeBedarfVerursacher].[RueckmeldungID] = 0 and
  [KHKPpsFaBelegeBedarfVerursacher].[BelID] = ".@=FAID."';
$result = executeQuery($query, $db);
if (is_array($result) and count($result) > 0)
    @=VerursacherGrid = $result;

[Danhae]

Thank you for your reply, but thats not working for me.

[Danhae]

Well, found it. I had to assign the case variables first to normal php variables like its well documented here http://wiki.processmaker.com/3.1/Proces ... uery.28.29

[amosbatto]

What data type is @=FAID? If it's a string and it comes from input in a DynaForm field, then you should do this to protect against sql injection attacks:

Code: Select all

function ms_escape_string($data) {
        if ( !isset($data) or empty($data) ) return '';
        if ( is_numeric($data) ) return $data;

        $non_displayables = array(
            '/%0[0-8bcef]/',            // url encoded 00-08, 11, 12, 14, 15
            '/%1[0-9a-f]/',             // url encoded 16-31
            '/[\x00-\x08]/',            // 00-08
            '/\x0b/',                   // 11
            '/\x0c/',                   // 12
            '/[\x0e-\x1f]/'             // 14-31
        );
        foreach ( $non_displayables as $regex )
            $data = preg_replace( $regex, '', $data );
        $data = str_replace("'", "''", $data );
        return $data;
}

$db = '3958231365812191caf2307054234752';
$faid = ms_escape_string(@=FAID);
$query = "Select
  Convert([varchar](10),[KHKVKBelege].[USER_Geplant],104) As Auslieferung,
  [KHKPpsFaBelegeBedarfVerursacher].[MengeBasis] as Menge,
  [KHKVKBelege].[A0Matchcode] as Kunde
 From [KHKPpsFaBelegeBedarfVerursacher]
  Inner Join [KHKVKBelegePositionen] On 
  [KHKVKBelegePositionen].[Mandant] =[KHKPpsFaBelegeBedarfVerursacher].[Mandant] And
  [KHKVKBelegePositionen].[BelPosID] = [KHKPpsFaBelegeBedarfVerursacher].[VerursacherID]
  Inner Join [KHKVKBelege] On 
  [KHKPpsFaBelegeBedarfVerursacher].[Mandant] =[KHKVKBelege].[Mandant] And 
  [KHKVKBelegePositionen].[BelID] = [KHKVKBelege].[BelID]
 Where   
  [KHKPpsFaBelegeBedarfVerursacher].[RueckmeldungID] = 0 and
  [KHKPpsFaBelegeBedarfVerursacher].[BelID] = '$faid' ";
$result = executeQuery($query, $db);
if (is_array($result) and count($result) > 0)
    @=VerursacherGrid = $result;

[amosbatto]

By the way, MSSQL by default uses single quotation marks to denote strings and double quotes for identifiers (e.g. table names or column names). If you want to use double quotations marks to denote strings, you can change the MSSQL configuration with this command:
SET QUOTED_IDENTIFIER ON