Database insert

How to insert a record into a database table using the Database module?

@Ranjith, have a look at @Suniel’s post here. You are most likely going to have to use SQL to perform your database operations using the Database module.

Although this post concerns using SQL to treat Excel like a database, it may be of some help as well.

Regards,
burque505

1 Like

Thank @burque505, Actually, I’m trying to insert data into a database table. A connection is established with the database but while trying to execute the insert query it is showing

“Failed to obtain output for ‘Result’”.

1 Like

Are you able to execute something like

SELECT * FROM sample_table_values;

and get a Result? That might help narrow things down.

Regards,
burque505

1 Like

Yes I’m able to select the table and even insert is also working, the data is inserted into the database table but Robin showing this error message.

“Failed to obtain output for ‘Result’”.

In the screenshot, I ran the script multiple times that why there are multiple records are inserted in the table.

1 Like

@burque505 did you guys try with Oracle? I tried but it gives error:
OraOLEDB.Oracle.1 provider is not registered on the local machine
I checked forums and tried few workarounds but no luck.
Alos, from the same machine I am able to establish connection and query same db with same details from sql developer tool.
Any clue?

2 Likes

@Murali, I’ll be interested to know your results. I don’t have that installed.
Regards,
burque505

@Ranjith, looks like you’re getting closer. I see the first query did yield a result, but that the second Database.ConnectAndExecute threw the error.

Could you please try

Database.Execute Connection: Statement: Timeout:30 Result=> QueryResult

instead? Or perhaps try:

Database.Close Connection:

after the first query, then reopen with your original statement, and then close again.

Regards,
burque505

1 Like

@burque505 I tried the method that you suggested but no luck still I’m getting the same error.

Database.Connect ConnectionString: "Provider=SQLNCLI11.1;Persist Security Info=False;User ID=sa;password=rootranjith;Initial Catalog=sample_db;Data Source=localhost;" \
                 Connection=> SQLConnection

Database.Execute Connection: SQLConnection \ 
                 Statement: "select * from sample_table" \
                 Timeout:30 Result=> QueryResult
                           
Console.Write Message: " Query 01 Result : " + QueryResult.rows.count

Database.Execute Connection: SQLConnection \ 
                 Statement: "select distinct * from sample_table" \
                 Timeout:30 Result=> QueryResult

Console.Write Message: " Query 01 Result : " + QueryResult.rows.count

Database.Close Connection: SQLConnection

Database.Connect ConnectionString: "Provider=SQLNCLI11.1;Persist Security Info=False;User ID=sa;password=rootranjith;Initial Catalog=sample_db;Data Source=localhost;" \
                 Connection=> SQLConnection

Database.Execute Connection: SQLConnection \
                 Statement: "insert into sample_table values('ranjith','srinivasan')" \
                 Timeout:30 Result=> QueryResult

Database.Close Connection: SQLConnection

Output:

Checking script...
Loading robot...
Running script...
 Query 01 Result : 14
 Query 01 Result : 1

L21:0	Failed to obtain output for 'Result'
1 Like

@Murali Please share your connection string and error message.

2 Likes

Thanks Ranjith. Here is the connection string and error that I am getting:

Database.Connect ConnectionString:“Provider=OraOLEDB.Oracle.1;Password=xxxx;Persist Security Info=True;User ID=user_rpa;Data Source:1234=test1.host1.domain.net/YD01CTD.CT.net” Connection=> SQLConnection

Error:
Checking script…
Loading robot…
Running script…

L1:0 Cannot connect to Data Source: The ‘OraOLEDB.Oracle.1’ provider is not registered on the local machine.

1 Like

I believe this is a bug.

set constring to "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\RobinTests\RobinTest.accdb;Persist Security Info=False;"

set insertrow to "Bill, 48"

Database.ConnectAndExecute \
    ConnectionString: constring \
    Statement: 'INSERT INTO Table1 Values (4, "Bill", "45")' \
    Timeout:30 Result=> QueryResult

Console.Write Message: QueryResult

My result:

L5:0	Failed to obtain output for 'Result' 

As Ranjith has pointed out, the data is in fact inserted.

db_status

Regards,
burque505

1 Like

Thanks @burque505. Yes this is a bug.

1 Like

@Murali Please go thru this website and configure ODBC driver and use it as your data source(connection string).

Thanks,
Ranjith

1 Like