Use UI
Use sql.pkg
Use Datadict.pkg
//Use cPerfMon.pkg 
    
Class cSQLHelper is a cObject
    
    Procedure Construct_Object
        Forward Send Construct_Object
        {DesignTime= True}
        {Visibility=Public}
        {Description="The File Number of a table from which to grab the connection Info"} 
        Property Integer piRefrenceFileNo 1 
        Property String psCS_CONNECT
        Property String psDRV_ID
        // used to point to the composite child object that provides the SQL
        // interfaces for a driver. This can be used with DAW drivers. Other drivers may require
        // that you sub-class your DD and create a different object based on a custom class 
        { Visibility=Private }
        Property Handle phoSQLHelper (Create(Self,RefClass(cDAWSQLDriverHelper)))    
 
    End_Procedure
    
    {Visibility= Public }
    {Description="Pass an SQL statement from which a no value is returned, but an SQL action is performd."}
    Procedure SQLUpdateTest String mySQL 
        String  soLogin
        Integer hoSQL
        Integer hdbc
        Integer hstmt
        Integer iFetchResult
        Integer iReferenceFileNo 
        Integer  iMessage iNextSet
       
        String  sMessage 
        Integer iNoOfMessages
        
        
        Get piRefrenceFileNo to iReferenceFileNo  
        // Showln mySQL 
        //*** The SQL handle manager object
        Get Create (RefClass(cSQLHandleManager)) to hoSQL 
        String sDataBaseType 
        //*** Connect to the database server
        If ((piRefrenceFileNo(Self)) < 0) Begin 
            Move (psCS_CONNECT(Self)) to soLogin 
            Move (psDRV_ID(Self))     to sDataBaseType 
        End
        Else Begin 
            Get_Attribute DF_FILE_LOGIN of (piRefrenceFileNo(Self)) to soLogin
            Get_Attribute DF_FILE_DRIVER of (piRefrenceFileNo(Self)) to sDataBaseType 
            If (sDataBaseType = "DATAFLEX") Procedure_Return 
        End 
        Send SQLSetConnect of hoSQL sDataBaseType soLogin
                
        Get SQLConnect     of hoSQL "" "" to hdbc
                
        If (hdbc <> 0) Begin
            //*** Open a statement
            Get SQLOpen of hdbc to hstmt
                
            If (hstmt <> 0) Begin
                //*** Execute the statement, we do it only once so use direct
                //*** execution
                Send SQLExecDirect of hstmt mySQL
                
                
                Send SQLClose of hstmt
                
            End
            Send SQLDisconnect of hdbc
                
        End
        
        //*** Destroy the SQL handle manager object
        Send Destroy_Object of hoSQL
    End_Procedure
    {Visibility= Public }
    {Description="Pass an SQL statement from which a no value is returned, but an SQL action is performd."}
    Procedure SQLUpdate String mySQL Integer piDelay Handle phoBPOStatus
        String  soLogin
        Integer hoSQL
        Integer hdbc
        Integer hstmt
        Integer iFetchResult
        Integer iReferenceFileNo 
        Integer iDelay iMessage iNextSet
        Handle  hoBPOStatus
        String  sMessage 
        Integer iNoOfMessages
        
        If (num_arguments > 1) Begin 
            Move piDelay to iDelay 
        End
        Else Move 0 to iDelay 
        If (num_arguments > 2) Begin 
            Move phoBPOStatus to hoBPOStatus 
        End
        Else Move 0 to hoBPOStatus 
        
        Get piRefrenceFileNo to iReferenceFileNo  
        // Showln mySQL 
        //*** The SQL handle manager object
        Get Create (RefClass(cSQLHandleManager)) to hoSQL 
        String sDataBaseType 
        //*** Connect to the database server
        If ((piRefrenceFileNo(Self)) < 0) Begin 
            Move (psCS_CONNECT(Self)) to soLogin 
            Move (psDRV_ID(Self))     to sDataBaseType 
        End
        Else Begin 
            Get_Attribute DF_FILE_LOGIN of (piRefrenceFileNo(Self)) to soLogin
            Get_Attribute DF_FILE_DRIVER of (piRefrenceFileNo(Self)) to sDataBaseType 
            If (sDataBaseType = "DATAFLEX") Procedure_Return 
        End 
        
        Send SQLSetConnect of hoSQL sDataBaseType soLogin
        Get SQLConnect     of hoSQL "" "" to hdbc
        If (hdbc <> 0) Begin
            //*** Open a statement
            Get SQLOpen of hdbc to hstmt
            If (hstmt <> 0) Begin
                //*** Execute the statement, we do it only once so use direct
                //*** execution
                Send SQLExecDirect of hstmt mySQL
                If  (iDelay > 0) Begin
                    Sleep iDelay 
                End
                If (iDelay < 0) Begin 
                    Repeat
                         Get SQLStmtAttribute of hStmt SQLSTMTATTRIB_NUMMESSAGES to iNoOfMessages
                
                        If (iNoOfMessages > 0) Begin
                            For iMessage from 1 to iNoOfMessages
                                Get SQLGetMessage of hStmt iMessage to sMessage
                                Get RemoveComponentIdentifier of hStmt sMessage to sMessage
//vv                                 Send Update_Status of hoBPOStatus sMessage 
                            Loop
                        End
                           
                        Get SQLNextResultSet of hstmt to iNextSet
                    Until (iNextSet = 0)
                End
                Send SQLClose of hstmt
            End
            Send SQLDisconnect of hdbc
        End
        
        //*** Destroy the SQL handle manager object
        Send Destroy_Object of hoSQL
    End_Procedure
    
    {Visibility= Public }
    {Description="Pass an SQL statement from which a single value is returned"}
    Function SQLItem String mySQL Returns String
         
        String  soLogin
        Integer hoSQL
        Integer hdbc
        Integer hstmt iResult
        Integer iFetchResult
        Integer iReferenceFileNo
        String sparam sDataBaseType
        Get piRefrenceFileNo to iReferenceFileNo  
        Open iReferenceFileNo 
        
        //*** The SQL handle manager object
        Get Create (RefClass(cSQLHandleManager)) to hoSQL 
//Showln mySQL         
        //*** Connect to the database server
        If ((piRefrenceFileNo(Self)) < 0) Begin 
            Move (psCS_CONNECT(Self)) to soLogin 
            Move (psDRV_ID(Self))     to sDataBaseType 
        End
        Else Begin 
            Get_Attribute DF_FILE_LOGIN of (piRefrenceFileNo(Self)) to soLogin
            Get_Attribute DF_FILE_DRIVER of (piRefrenceFileNo(Self)) to sDataBaseType 
            If (sDataBaseType = "DATAFLEX") Procedure_Return 
        End
        Send SQLSetConnect of hoSQL sDataBaseType soLogin
        Get SQLConnect     of hoSQL "" "" to hdbc
        If (hdbc <> 0) Begin
            //*** Open a statement
            Get SQLOpen of hdbc to hstmt
            If (hstmt <> 0) Begin
                //*** Execute the statement, we do it only once so use direct
                //*** execution
                Send SQLExecDirect of hstmt mySQL
                Get SQLFetch of hStmt to iResult
                If (iResult <> 0) Begin
                    Get SQLColumnValue of hStmt 1 to sparam
                End
                Send SQLClose of hstmt
            End
            Send SQLDisconnect of hdbc
        End
        
        //*** Destroy the SQL handle manager object
        Send Destroy_Object of hoSQL
        Function_Return sparam 
    End_Function
    
    Procedure WriteClipboard String sData 
        Direct_Output channel 9 "Clipboard:" 
         Writeln channel 9 sData
        Close_Output channel 9  
        
    End_Procedure
    
     // this returns a 2d array of items that can be used for parsing data, or filling a (read only) grid
     Function SQLItems String mySQL Returns String[][]
        
        String  soLogin
        String[][]  sResult 
        Integer hoSQL
        Integer hdbc
        Integer hstmt
        Integer iFetchResult
        String sDataBaseType 
       // Send WriteClipboard MySQl 
        //*** The SQL handle manager object
        Get Create (RefClass(cSQLHandleManager)) to hoSQL 
        
        //*** Connect to the database server
        If ((piRefrenceFileNo(Self)) < 0) Begin 
            Move (psCS_CONNECT(Self)) to soLogin 
            Move (psDRV_ID(Self))     to sDataBaseType 
        End
        Else Begin 
            Get_Attribute DF_FILE_LOGIN of (piRefrenceFileNo(Self)) to soLogin
            Get_Attribute DF_FILE_DRIVER of (piRefrenceFileNo(Self)) to sDataBaseType 
            If (sDataBaseType = "DATAFLEX") Procedure_Return 
        End
        
        Send SQLSetConnect of hoSQL sDataBaseType soLogin
        Get SQLConnect     of hoSQL "" "" to hdbc
        If (hdbc <> 0) Begin
            //*** Open a statement
            Get SQLOpen of hdbc to hstmt
            If (hstmt <> 0) Begin
                //*** Execute the statement, we do it only once so use direct
                //*** execution
                Send SQLExecDirect of hstmt mySQL 
                Get SQLFetchResultsetValues of hStmt to sResult  
             
                Send SQLClose of hstmt
            End
            Send SQLDisconnect of hdbc
        End
        //*** Destroy the SQL handle manager object
        Send Destroy_Object of hoSQL
        
        
        Function_Return sResult 
   
     End_Function 
     

    

    // Calls a Stored Procedure by name May need worked with for more than 3 params. 
    // Expects only one value as a result. "Success" or "Failure" usually.
    Function SQLItemSP String sProcName String sParam1 String sParam2 String sParam3 Returns String 
        String sLabel sColumn sLineFeed soLogin 
        Handle hoSQL hDbc hStmt
        Integer iFetchResult iNumCols iCol iResult iNextSetResult
    
   
        //*** The SQL handle manager object
        Object oSQLHandler is a cSQLHandleManager
            Move Self to hoSQL
        End_Object
    
        //*** Connect to the database server
         Get_Attribute DF_FILE_LOGIN of (piRefrenceFileNo(Self)) to soLogin
        String sDataBasType 
        Get_Attribute DF_FILE_DRIVER of (piRefrenceFileNo(Self)) to sDataBasType 
        If (sDataBasType = "DATAFLEX") Procedure_Return 
        
        Send SQLSetConnect of hoSQL sDataBasType soLogin
        Get SQLConnect     of hoSQL "" "" to hdbc
        If (hDbc <> 0) Begin
            //*** Open a statement
            Get SQLOpen of hDbc to hStmt
            If (hStmt <> 0) Begin
                //*** Setup the procedure
                Send SQLSetProcedurename of hStmt sProcName
                Send SQLSetArgument of hStmt 1 sParam1
                If (num_arguments > 2) Begin 
                  Send SQLSetArgument of hStmt 2 sParam2
                End
                If (num_arguments > 3) Begin 
                  Send SQLSetArgument of hStmt 3 sParam2
                End
    
                //*** Call the procedure
                Send SqlCall of hStmt
    
                //** Check the return value
                Get SQLReturnValue of hStmt to iResult
                If (iResult = 0) Begin
                   Get SQLFetch of hStmt to iFetchResult
                   If (iFetchResult <> 0) Begin
                      Get SQLColumnValue of hStmt 1 to sColumn
                   End
                End
                Send SQLClose of hStmt
            End
            Send SQLDisconnect of hDbc
        End
    
        //*** Destory the SQL handle manager object
        Send Destroy_Object of hoSQL
    
    End_Procedure
    
    Function SQLROWTOARRAY String mySQL Returns String[]
        
           String  soLogin
             String sResult 
            Integer hoSQL
            Integer hdbc
            Integer hstmt
            Integer iFetchResult 
            String[] StringArrayVariable
            
            //*** The SQL handle manager object
            Get Create (RefClass(cSQLHandleManager)) to hoSQL 
         
            //*** Connect to the database server
            Get_Attribute DF_FILE_LOGIN of (piRefrenceFileNo(Self)) to soLogin
            String sDataBasType 
            Get_Attribute DF_FILE_DRIVER of (piRefrenceFileNo(Self)) to sDataBasType 
            If (sDataBasType = "DATAFLEX") Procedure_Return 
            
            Send SQLSetConnect of hoSQL sDataBasType soLogin
            Get SQLConnect     of hoSQL "" "" to hdbc
            If (hdbc <> 0) Begin
                //*** Open a statement
                Get SQLOpen of hdbc to hstmt
                If (hstmt <> 0) Begin
                    //*** Execute the statement, we do it only once so use direct
                    //*** execution
                    Send SQLExecDirect of hstmt mySQL 
                    Get SQLFetchRowValues of hStmt to StringArrayVariable 
                    
         
                    Send SQLClose of hstmt
                End
                Send SQLDisconnect of hdbc
            End
            //*** Destroy the SQL handle manager object
            Send Destroy_Object of hoSQL
        
           
            Function_Return StringArrayVariable
          
        
    End_Function
    


    Procedure End_Construct_Object
        Forward Send End_Construct_Object

    End_Procedure
    
     // This function allows you to put the SQL into an CM Image. This makes it much easier to
    // copy and paste between the VDF program and an SQL Editor.
    Function ImageToString Integer iImg String sArg1 String sArg2 String sArg3 String sArg4 String sArg5 String sArg6 String sArg7 String sArg8 String sArg9 String sArg10 String sArg11 String sArg12 String sArg13 String sArg14 Returns String
        Integer iChannel
        String sTextValue sLine
        Boolean bSeqEof
        
        Move "" to sTextValue
        
        Get Seq_New_Channel to iChannel
        If (iChannel = DF_SEQ_CHANNEL_NOT_AVAILABLE) Send Stop_box 'No channel available...'
        Else Begin        
            Direct_Input channel iChannel ("image: "+String(iImg))
            Move (SeqEof) to bSeqEof
            While (not(bSeqEof))
                Readln channel iChannel sLine
                Move (SeqEol) to bSeqEof
                If (not(bSeqEof)) Begin
                    Move (sTextValue+sLine+character(13)) to sTextValue
                End
            Loop
            Send Seq_Release_Channel iChannel
        End
        If (Num_Arguments >  1) Move (Replaces( '$1',sTextValue, sArg1)) to sTextValue
        If (Num_Arguments >  2) Move (Replaces( '$2',sTextValue, sArg2)) to sTextValue
        If (Num_Arguments >  3) Move (Replaces( '$3',sTextValue, sArg3)) to sTextValue
        If (Num_Arguments >  4) Move (Replaces( '$4',sTextValue, sArg4)) to sTextValue
        If (Num_Arguments >  5) Move (Replaces( '$5',sTextValue, sArg5)) to sTextValue
        If (Num_Arguments >  6) Move (Replaces( '$6',sTextValue, sArg6)) to sTextValue
        If (Num_Arguments >  7) Move (Replaces( '$7',sTextValue, sArg7)) to sTextValue
        If (Num_Arguments >  8) Move (Replaces( '$8',sTextValue, sArg8)) to sTextValue
        If (Num_Arguments >  9) Move (Replaces( '$9',sTextValue, sArg9)) to sTextValue
        If (Num_Arguments > 10) Move (Replaces('$a',sTextValue,sArg10)) to sTextValue
        If (Num_Arguments > 11) Move (Replaces('$b',sTextValue,sArg11)) to sTextValue
        If (Num_Arguments > 12) Move (Replaces('$c',sTextValue,sArg12)) to sTextValue
        If (Num_Arguments > 13) Move (Replaces('$d',sTextValue,sArg13)) to sTextValue
        If (Num_Arguments > 14) Move (Replaces('$e',sTextValue,sArg14)) to sTextValue
        Function_Return sTextValue
    End_Function

    Function SQLFormat String sText ;
                        String s1 String s2 String s3 String s4 String s5 String s6 String s7 String s8 String s9 ;
                        Returns String
        String sParam
        Integer iArg
        If (num_arguments>10) Error DFERR_WRONG_NUMBER_OF_ARGUMENTS // only allow max of 9 params
        Move (Replaces("%%",sText,"$%$")) to sText // %% is used when you want a single % followed by a 1-9. e.g. "%%1"=%1
        For iArg from 2 to Num_Arguments
            Move  iArg&  to sParam
            Move (Replaces("'",sParam,"''")) to sParam
            Move (Replaces("%"+String(iArg-1),sText,sParam)) to sText
        Loop
        Move (Replaces("$%$",sText,"%")) to sText
        Function_Return sText
    End_Function

    Function SequenceNext String sSequence Returns Integer
        String sMySQl 
        Integer iPosition
        
        Move (SQLFormat(Self,"Select next value for %1 as foo",sSequence)) to sMySQl 
        Get SQLItem sMysql to iPosition 

        Function_Return iPosition
    End_Function
    
    Procedure SequenceReset String sSequence Integer iRestart
        String sMySQl 
        Integer iPosition
        
        Move (SQLFormat(Self,"Alter sequence %1 Restart with %2",sSequence,iRestart)) to sMySQl 
        Send SQLUpdate sMySQl 
    End_Procedure

    
    Function SequenceCurrent String sSequence Returns Integer 
        String sSQL
        String sSeq 
        
        Move (SQLFormat(self,"Select current_value from sys.sequences where name = '%1'",sSequence)) to sSQL
        Get SQLItem sSQL to sSeq 
        Function_Return sSeq 
    End_Function
    
    // creates escaped SQL string by replacing single quote with two single quotes
   Function SQLEscapedStr String sSQL Returns String
        Handle hoSQLHelper 
        Get phoSQLHelper to hoSQLHelper
        Get SQLEscapedStr of hoSQLHelper sSQL to sSQL
        Function_Return sSQL
   End_Function
   
   Function SQL_Date_as_String DateTime dWhen Returns String 
        String sqlToday 
        String HH MM SS DD MO YYYY strPART 
        String strDateWhen 
        Move dWhen to strDateWhen 
        Move (Right(Trim(strDateWhen),2)) to strpart
        If (not((strPART = "AM") or (StrPart = "PM"))) Begin 
          Move '' to strPart 
        End
        Move (Right(SFormat("00%1",(DateGetHour(dWhen))),2)) to HH 
        Move (Right(SFormat("00%1",(DateGetMinute(dWhen))),2)) to mm 
        Move (Right(SFormat("00%1",(DateGetSecond(dWhen))),2)) to ss 
        Move (Right(SFormat("00%1",(DateGetDay(dWhen))),2)) to DD 
        Move (Right(SFormat("00%1",(DateGetMonth(dWhen))),2)) to MO 
        Move (Right(SFormat("20%1",(DateGetYear(dWhen))),4)) to YYYY 
         
        Move (SFormat("%1-%2-%3 %4:%5:%6 %7",YYYY,MO,DD,HH,MM,SS,Strpart )) to SQLToday
        //Move (sFprmat("CONVERT(DATETIME,'%1',120)",sqlToday)) to sqlToday 
        Function_Return sqlToday 
    End_Function

   
    
End_Class

#IFNDEF ghoSQLHelper 

Global_Variable Integer ghoSQLHelper 
Get Create (RefClass(cSQLHelper)) to ghoSQLHelper 

#ENDIF