[SAP IRPA CLOUD] connect sql server bd

[former_member732352]

Hi,

one question, how can I connect to a sql server db with SAPIPRA CLOUD

Is there a guide regarding bd?

[Chaitanya_Priya]

Hi luis_sandovalm42,

As of now , We don't have any guide.

Please refer to the custom script to connect to SQL server.

function displayResults(rs) {
    let i = 1;
    let nbFields = rs.Fields.Count;

    let header = ' | ';
    for(let k= 0;  k < nbFields; k++){
        header=header+rs.Fields(k).Name+' | ';
    }
    irpa_core.core.log(header );
    while(!rs.EOF) {
        let record = ' | ';
        for(let k= 0; k < nbFields; k++){
            record=record+ rs.Fields.Item(k) +' | ';
        }
        irpa_core.core.log(i+':'+record );
        i++;
        rs.MoveNext();
    }
    return;
}


try{
 
    var string_connection = "Provider=SQLOLEDB;Server=localhost\\SQLEXPRESS;Database="+database+";UID="+user+";PWD="+password;
    irpa_core.core.log("Creating the connection ...");
    let oConnection = irpa_core.activeX.create("ADODB.Connection");
    let rs =  irpa_core.activeX.create("ADODB.Recordset");
    oConnection.Open(string_connection);
    irpa_core.core.log('Connection successful!' );

    // Strings for SQL commands
    let select_invoices = "SELECT * FROM [dbo].[INVOICES]";
    let select_request = "SELECT [Supplier] ,[Material],[Plant],[Quantity],[PurchasingGroup],[NetOrderPrice] ,[Currency],[PurchasingOrganization] FROM [dbo].[PO]";
    let insert_request = "INSERT INTO [dbo].[PO] ([Supplier],[Material],[Plant],[Quantity],[PurchasingGroup],[NetOrderPrice],[Currency],[PurchasingOrganization]) VALUES (10100090,'TG0002',2020,150,001,35,'EUR',2020)";
    

    //show the content of the table: INVOICES table
    rs.Open(select_invoices, oConnection);
    displayResults(rs);
    rs.Close();
    
    // Insert query into a table: PO table 
    irpa_core.core.log("Doing insert request ....");
    rs = irpa_core.activeX.create("ADODB.Recordset");
    rs.Open(insert_request, oConnection);
    irpa_core.core.log("New data inserted !");
    
    // show the content of the table: PO table
    irpa_core.core.log("Supplier - Material - Plant - Quantity - Purchasing Group - Net Order Price - Currency - Purchasing Organization");
    rs =  irpa_core.activeX.create("ADODB.Recordset");
    rs.Open(select_request, oConnection);
    displayResults(rs);
    rs.Close();
} catch (error) {
    irpa_core.core.log('Connection failed: '+ error );
    irpa_core.core.log('Connection failed: '+ error.message );
}

[diegoiz]

Thanks chaitanyapriya.puvvada,

This was helpful for me!

Grettings,

Diego

[GimeMoreno29]

it shows me this error " DispInvoke: Open [Microsoft][ODBC Driver Manager] The data source name cannot be found and no default driver was specified"