Escaping (solved)

[Bart Pietercil]

On 4-okt-07, at 21:16, Bart Pietercil wrote:

>
> On 4-okt-07, at 21:10, Thorsten Hohage wrote:
>
>> Hi Bart,
>>
>> On 2007-10-04, at 20:29, Bart Pietercil wrote:
>>
>>> where do if find the escape_string function in RB
>>
>> nowhere - there is no!
>>
>> * You can use some "tricky" way on mis-using the XML-Classes, but  
>> the XML classes are buggy ...
>>
>> * I can send you of-list a pair of classes OMStringBuilder and  
>> OMTextFilter that I use to generate "special" XML-output and fast  
>> mass-string concatenation, ... and escaping
>>
>> * or we make a FR for a escape_string function in Valentina and  
>> let Valentina do the stuff for us ;-)
>
> Strange since the function exists for Vphp and Ruby
>
> My problem is this:
>
> I'm trying to send the trigger creation sql to my db
>
> this is the function in RB
>
>   Dim StandardTriggerText As String
>   Dim AffectedRows As Integer
>   Dim aKey As Variant
>
>
>   Try
>
>     Select case KindOfTrigger
>     case "BU"
>       StandardTriggerText = "CREATE OR REPLACE TRIGGER  
> 'trigger_"+tblName+"_BU' BEFORE UPDATE ON '"+tblName+"' FOR EACH  
> ROW" + EndOfLine + _
>       "BEGIN" + EndOfLine+ _
>       "NEW.modification_dt = NOW();" +EndOfLine + _
>       "NEW.objptr_modified_by = @user_id;" + EndOfLine + _
>       "END;"
>       Valentina.
>       AffectedRows = SqlExecute(StandardTriggerText)
>     case "BI"
>
>       StandardTriggerText = "CREATE OR REPLACE TRIGGER  
> "+"trigger_"+tblName+"_BI' BEFORE INSERT ON '"+tblName+"' FOR EACH  
> ROW" + EndOfLine+ _
>       "BEGIN" + EndOfLine+ _
>       "if @user_id IS NOT null then" + EndOfLine + _
>       "NEW.objptr_created_by = @user_id;" + EndOfLine+ _
>       "NEW.objptr_modified_by = @user_id;" + EndOfLine + _
>       "End IF" + EndOfLine + _
>       "NEW.creation_dt = NOW();"+EndOfLine + _
>       "NEW.modification_dt = NOW();"+EndOfLine+ _
>       "END;"
>
>     Else
>       MsgBox("Unimplemented case in method CreateStandardTrigger")
>       Return False
>     end select
>
>
>     return True
>   catch Err as VException
>     MsgBox(Err.Message)
>     Return False
>   end Try
>
>
> now the problem is that Valentina does NOT like the single quotes  
> for the trigger name so I need to send double quotes.
>
> I am a bit a lost here.
>
This WORKS

   Dim StandardTriggerText As String
   Dim AffectedRows As Integer
   Dim aKey As Variant


   Try

     Select case KindOfTrigger
     case "BU"
       StandardTriggerText = "CREATE OR REPLACE TRIGGER "+Chr(34) 
+"trigger_"+tblName+"_BU"+Chr(34)+" BEFORE UPDATE ON "+Chr(34)+tblName 
+Chr(34)+" FOR EACH ROW" + EndOfLine + _
       "BEGIN" + EndOfLine+ _
       "NEW.modification_dt = NOW();" +EndOfLine + _
       "NEW.objptr_modified_by = @user_id;" + EndOfLine + _
       "END;"

       AffectedRows = SqlExecute(StandardTriggerText)
     case "BI"

       StandardTriggerText = "CREATE OR REPLACE TRIGGER "+Chr(34) 
+"trigger_"+tblName+"_BI"+Chr(34)+" BEFORE INSERT ON "+Chr(34)+tblName 
+Chr(34)+"' FOR EACH ROW" + EndOfLine+ _
       "BEGIN" + EndOfLine+ _
       "if @user_id IS NOT null then" + EndOfLine + _
       "NEW.objptr_created_by = @user_id;" + EndOfLine+ _
       "NEW.objptr_modified_by = @user_id;" + EndOfLine + _
       "End IF" + EndOfLine + _
       "NEW.creation_dt = NOW();"+EndOfLine + _
       "NEW.modification_dt = NOW();"+EndOfLine+ _
       "END;"

     Else
       MsgBox("Unimplemented case in method CreateStandardTrigger")
       Return False
     end select


     return True
   catch Err as VException
     MsgBox(Err.Message)
     Return False
   end Try

Bart