PowerAutomateでDataBase操作

備忘録として

--------------------------------------------------
    FUNCTION Main_コピー GLOBAL
        CALL setConfig
        CALL 'DB-Operation'
    END FUNCTION
 --------------------------------------------------    
    FUNCTION 'DB-Operation' GLOBAL
        DISABLE DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
        DISABLE Text.ConvertDateTimeToText.FromCustomDateTime DateTime: $fx'=CurrentDateTime' CustomFormat: $fx'yyyyMMdd_HHmmss' Result=> FormattedDateTime
        # SQL Serverに接続してSQL実行
        DISABLE Database.Connect ConnectionString: $fx'Provider=MSOLEDBSQL;Database=RPAtest;Integrated Security=SSPI;Persist Security Info=False' Connection=> WorkDB
        @@copilotGeneratedAction: 'False'
    DISABLE Database.ExecuteSqlStatement.Execute Connection: $fx'=WorkDB' Statement: $fx'SELECT TOP 100 * INTO [ODBC;DRIVER={SQL Server};SERVER=DESKTOP-2RTP4HU;Database=RPAtest;Trusted_Connection=Yes].[TestTBL${AccessTime}] FROM [;Database=C:\\Tool\\LABO\\BloodData.accdb].[PedNetQuery]' Timeout: $fx'=30' Result=> SQLSVResult
        DISABLE Database.Close Connection: $fx'=WorkDB'
        # Access Databaseに接続してSQL実行
        DISABLE Database.Connect ConnectionString: $fx'Provider=Microsoft.ACE.OLEDB.16.0;Data Source="C:\\RPA\\WorkDB.accdb";Persist Security Info=False' Connection=> WorkDB
        @@copilotGeneratedAction: 'False'
    DISABLE Database.ExecuteSqlStatement.Execute Connection: $fx'=WorkDB' Statement: $fx'SELECT TOP 100 * INTO [Excel 12.0 Xml;Database=C:\\RPA\\PAD\\byACCCB_${AccessTime}.xlsx].[PowerAutoMateTest] FROM [;Database=C:\\Tool\\LABO\\BloodData.accdb].[PedNetQuery];' Timeout: $fx'=30' Result=> QueryResult2
        DISABLE Database.Close Connection: $fx'=WorkDB'
        # Excel Workbookに接続してSQL実行
        DISABLE Database.Connect ConnectionString: $fx'Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0 Xml;Data Source="C:\\RPA\\WorkList.xlsx";;Persist Security Info=False' Connection=> WorkXLSX
        @@copilotGeneratedAction: 'False'
    DISABLE Database.ExecuteSqlStatement.Execute Connection: $fx'=WorkXLSX' Statement: $fx'SELECT TOP 10 * INTO [Excel 12.0 Xml;Database=C:\\RPA\\PAD\\byXLSX_${AccessTime}.xlsx].[PADtest] FROM [Excel 12.0 Xml;Database=C:\\RPA\\WorkList.xlsx].[Sample$];' Timeout: $fx'=30' Result=> QueryResult
        DISABLE Database.Close Connection: $fx'=WorkXLSX'
        # PostgreSQLに接続してSQL実行
        DISABLE Database.Connect ConnectionString: $fx'DRIVER={PostgreSQL ODBC Driver(ANSI)};SERVER=localhost;DATABASE=sampleDB;UID=postgres;PWD=postgre0911SQL' Connection=> WorkPG
        @@copilotGeneratedAction: 'False'
    DISABLE Database.ExecuteSqlStatement.Execute Connection: $fx'=WorkPG' Statement: $fx'SELECT "ID", "Name", "Gender" FROM "TestTBL" LIMIT 5;' Timeout: $fx'=30' Result=> PGResult
        DISABLE Display.ShowMessageDialog.ShowMessage Title: $fx'Data Count' Message: $fx'=PGResult' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
        DISABLE Database.Connect ConnectionString: $fx'Provider=Microsoft.ACE.OLEDB.16.0;Data Source="C:\\RPA\\WorkDB.accdb";Persist Security Info=False' Connection=> WorkPG
        @@copilotGeneratedAction: 'False'
    DISABLE Database.ExecuteSqlStatement.Execute Connection: $fx'=WorkPG' Statement: $fx'SELECT TOP 10 ID, Name, Gender INTO [TEXT;Database=C:\\RPA\\PAD].[byPG_${AccessTime}.csv] FROM [ODBC;DRIVER={PostgreSQL ODBC Driver(ANSI)};SERVER=localhost;DATABASE=sampleDB;UID=postgres;PWD=postgre0911SQL;PORT=5432].[TestTBL];' Timeout: $fx'=30' Result=> PGResult
        DISABLE Database.Close Connection: $fx'=WorkPG'
        # Windows Directoryに接続してSQL実行
        DISABLE Database.Connect ConnectionString: $fx'Provider=Microsoft.ACE.OLEDB.16.0;Data Source="C:\\RPA\\WorkDB.accdb";Persist Security Info=False' Connection=> WorkDB
        @@copilotGeneratedAction: 'False'
    DISABLE Database.ExecuteSqlStatement.Execute Connection: $fx'=WorkDB' Statement: $fx'SELECT TOP 100 * INTO [ODBC;DRIVER={SQL Server};SERVER=DESKTOP-2RTP4HU;Database=RPAtest;Trusted_Connection=Yes].[TestTBL${AccessTime}] FROM [;Database=C:\\Tool\\LABO\\BloodData.accdb].[PedNetQuery]' Timeout: $fx'=30' Result=> SQLSVResult
        DISABLE Database.Close Connection: $fx'=WorkDIR'
    END FUNCTION
--------------------------------------------------
    FUNCTION setConfig GLOBAL
        DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
        Text.ConvertDateTimeToText.FromCustomDateTime DateTime: $fx'=CurrentDateTime' CustomFormat: $fx'yyyyMMdd_HHmmss' Result=> AccessTime
    END FUNCTION