File: pwd_ds_importtables.sru
Size: 5898
Date: Sat, 08 Dec 2007 15:38:39 +0100
$PBExportHeader$pwd_ds_importtables.sru
$PBExportComments$Demo how to implement a table import.
forward
global type pwd_ds_importtables from datastore
end type
type ids_import from datastore within pwd_ds_importtables
end type
end forward

global type pwd_ds_importtables from datastore
string dataobject = "pwd_d_importtables"
ids_import ids_import
end type
global pwd_ds_importtables pwd_ds_importtables

type prototypes
Function Int GetTempFileName(Ref String ls_path,Ref String ls_filename,Int li_Seed,Ref String ls_result_name) ALIAS FOR  GetTempFileNameW LIBRARY "kernel32"

end prototypes

type variables
string is_importlog
string is_dberror

end variables
forward prototypes
public function string of_invokemethod (string as_method)
public subroutine of_import ()
end prototypes

public function string of_invokemethod (string as_method);Choose Case as_method
   Case "of_import"
      of_import ()
      // Populate report (Tutorial Message)
      Return '<html>~n<head>~n<title>PB Web Easy - Import Table Data Example </title>~n</head>~n'+&
      '<body>~n' + &
      This.is_importlog  +&
      '~n</body>~n</html>'
      
   Case "of_showform"      
      string ls_file
      // Import Pre-Sets from CSV Files...
      ls_file = ProfileString(cgienv.of_getINIFileName(), getApplication().AppName , "importdir", "" ) + "importtables.csv"
      log.of_addmessage ( 1 , ls_file )

      This.ImportFile  ( CSV! , ls_file  )
   
      // Show Form
      Return '<html>~n<head>~n<title>PB Web Easy - Import Table Data Example </title>~n</head>~n<body>~n' &
            +  This.Object.DataWindow.data.HTML + &
            +' ~n</body>~n</html>'

   Case Else
      Return "Error in '" + Classname  ( This ) + "'<br />Method '" + as_method +"' unknown" 

End Choose

end function

public subroutine of_import ();/**
  This is a MS SQL Server specific import using the 
  'SET IDENTITY_INSERT .. ' option.
*/
long     ll_row
string   ls_dwsyntax
string   ls_sqlselect
string   ls_presentation
string   ls_error
string   ls_importfile
string   ls_importdir
string   ls_tablename
string   ls_sqlimmediate
long     ll_poskey
long     ll_rc_importfile
string   ls_file

ls_file = ProfileString(cgienv.of_getINIFileName(), getApplication().AppName , "importdir", "" ) + "importtables.csv"
log.of_addmessage ( 1 , ls_file )
This.ImportFile  ( CSV! , ls_file  )
   
// Accept Submitted data (Action Update)
This.setHTMLAction( &
   '' /*cgienv.of_getParam( DataObject+'_action' )*/ , &
   cgienv.of_getParam( DataObject+'_context' ) )

is_importlog = "<h3>Import Log</h3>~n"

// Check User
If session.of_getusername() = n_ds_session.SUPERUID then
Else
   is_importlog += "Not connected as SUPERUSER.<br />Function not available.<br />"
   Return
End If

ls_importdir      = ProfileString(cgienv.of_getINIFileName(), getApplication().AppName , "importdir", "" )
ls_presentation   = "style ( type=grid )  DataWindow ( units=1)   column ( border=1 ) text ( border=1)"

For ll_row = 1 To This.RowCount()
   If This.GetItemNumber (ll_row , "import_yn" ) = 1 Then
      is_dberror = ""
      ls_tablename =  This.GetItemString (ll_row , "tablename" )
      ls_sqlselect = "select * from " + ls_tablename
      
      ls_dwsyntax = SQLCA.SyntaxFromSQL ( ls_sqlselect, ls_presentation, ls_error )

      If Len (ls_error) > 0  Then
         is_importlog += ls_error + "<br />"
      Else
         
         // Strip off identity 
         ll_poskey = Pos (ls_dwsyntax,  "key=yes identity=yes" ) 
         Do While ll_poskey > 0
            ls_dwsyntax = Replace ( ls_dwsyntax , ll_poskey , 20 , "")
            ll_poskey = Pos (ls_dwsyntax,  "key=yes identity=yes" ) 
         Loop
      
         ids_import.Create(ls_dwsyntax)
         ids_import.SetTransObject(SQLCA)
         
         // XML overrules CSV
         Choose Case True
            Case FileExists ( ls_importdir + ls_tablename + ".xml" ) 
               ll_rc_importfile = ids_import.ImportFile  ( XML! , ls_importdir + ls_tablename + ".xml" )
            Case FileExists ( ls_importdir + ls_tablename + ".csv" ) 
               ll_rc_importfile = ids_import.ImportFile  (  CSV! ,ls_importdir + ls_tablename + ".csv" )
            Case Else
               ll_rc_importfile = -99
         End Choose
         
         is_importlog += "Table '" + ls_tablename + "' , " 
         
         Choose Case ll_rc_importfile 
            Case Is > 0 
               // For MSS  Deactivate Identity
               ls_sqlimmediate = "SET IDENTITY_INSERT " +ls_tablename +  " ON"
               EXECUTE IMMEDIATE :ls_sqlimmediate USING SQLCA;
               
               If ids_import.Update () = 1 Then
                  is_importlog += String ( ll_rc_importfile )+ " row(s) successful imported.<br />~n"
                  COMMIT USING SQLCA;
               Else 
                  If Len ( is_dberror) > 0 Then 
                     is_importlog += "Error during import.<br />" +is_dberror + "<br />~n"
                  Else
                     is_importlog += "Error during import.<br />~n"  
                  End If
                  
                  ROLLBACK USING SQLCA;
                  
               End If
               
               ls_sqlimmediate= "SET IDENTITY_INSERT " +ls_tablename +  " OFF"
               EXECUTE IMMEDIATE :ls_sqlimmediate USING SQLCA;
               COMMIT USING SQLCA;  
               
            Case  0
                  is_importlog += "No rows in file to import.<br />~n"     
                  
            Case Is < 0
                  is_importlog += "Error during Importfile ("+String(ll_rc_importfile) + ").<br />~n"    
         End Choose
                        
      End If
      
      ids_import.Reset ()
      
   End if
Next

Return 




end subroutine

on pwd_ds_importtables.create
call super::create
this.ids_import=create ids_import
TriggerEvent( this, "constructor" )
end on

on pwd_ds_importtables.destroy
TriggerEvent( this, "destructor" )
call super::destroy
destroy(this.ids_import)
end on

type ids_import from datastore within pwd_ds_importtables descriptor "pb_nvo" = "true" 
end type

on ids_import.create
call super::create
TriggerEvent( this, "constructor" )
end on

on ids_import.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on

event dberror;if Len ( sqlerrtext  ) > 0 then is_dberror += sqlerrtext +"</br>"
end event