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