How can I manually connect to Oracle DB using variables

[j.wheeler]

Hello. I’m currently having an issue which may be simpler to solve than I am able to see.

Firstly, I’m using the Free Community version of UiPath, so I do not have access to Orchestrator.
In my research I’ve found mixed answers; some stating that having the variables I wish to use be String type, while others stating the variables should be UiPath.Database.DatabaseConnection type, and I’m not sure which would be best to use.

Secondly, I’m looking at the SQL connection script, and when using literals (pulled from connecting with the wizard) it looks like this (with xxx used to substitute sensitive information, of course):

“USER ID=xxx;DATA SOURCE=”(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.com)(PORT = ####)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = xxx)))“;PASSWORD=xxx”

When placing the variables in place of the literals, it does not work. I’m not sure how to insert the variables into the script, as I get syntax errors every time. Can someone please assist with how to properly place a variable into the Oracle DB Connection script? Thank you.

It should be noted that I did also use this solution that was listed by another user:
“USER ID=”+ SLogin +“;PASSWORD=”+SPassword+“;DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = “+SServer+” )(PORT = “+SPort+” )))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = “+SSid+” )))”
*Although this solution did not produce a syntax error, it did produce a runtime error “Connect to database: Input string was not in a correct format.”

[ushu (Usha kiranmai)]

Hi @j.wheeler - You can use variables of type String in the connection string. Did you check the connection string working without using any variables in it. This is to confirm, successful connection to db. If it works then, try passing variables in it. Hope below link useful

How to pass variables dynamically in connection string

Hi im using excel as automation and used OleDB connection and the below is my connection string “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=“C:\Users\Muthulakshmi\Downloads\Requester_1.xlsx”; Extended Properties=‘Excel 12.0 XML;HDR=YES;ReadOnly=False’” In the above i have passed the data source as the Excel file path but i want to store the excel path in a variable and then use the variable in the connection string instead of the actual path, but i throws error as the Connection String is …

[j.wheeler]

Hello, and thanks for your reply.
Yes, I did make the connection already using the wizard as a template, as previously stated. The example I provided was the connection string that was copy/pasted direct from what the connection wizard provided, and the connection is successful across all databases that my automation will need to connect to. When I directly plug the String variables in place of the literals, I get connection errors. My guess is that I need to wrap the String variable some kind of way so that the connection string can actually read it as a String variable and not just a syntax error, and I don’t know how to go about doing this.
Again, any help with this will be appreciated.

[ushu (Usha kiranmai)]

@j.wheeler Can you try the below query

“USER ID='”+ SLogin +“'+;PASSWORD='”+SPassword+“'+;DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '”+SServer+“'+ )(PORT = '”+SPort+“'+ )))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = '”+SSid+“'+ )))”

Make sure all the variables are of string type

[j.wheeler]

I have tried it as you described. All variables are String type and when using the variables in place of the variables you provided, there were no syntax errors. However, I did get a compiler error when attempting to run. EDIT: It was not a compiler error, the compilation went through fine. This was actually a runtime error.

It may not be necessary to note, but the syntax you used does differ from the one that the connection wizard supplies. There are perhaps different syntax for different database connections?