$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
- Sources
- pb9webeasy
- PBL
- pwd_ds_importtables.sru
File: pwd_ds_importtables.sru
Size: 5898
Date: Sat, 08 Dec 2007 15:38:39 +0100
Size: 5898
Date: Sat, 08 Dec 2007 15:38:39 +0100
- datastore pwd_ds_importtables(sru)