topic RE: Data - SQL Server: Set Connection - Problem with the connection in Product Forum

Data - SQL Server: Set Connection - Problem with the connection

AlbertoGómez — Mon, 24 Jan 2022 09:01:00 GMT

Hello.
I'm trying to connect to a MySQL Database from my BP PC. But I can't 😞
I'm ussing the Data - SQL Server Object, but I don't know what is wrong with my connection.
If I try to connect with other aplication (such HeidiSQL) I could connect to this database and I could obtain some kind of data. But when I try to obtain these data using BP, .... I can't, so I am sure that I am doing something wrong.

Using this objet, Data - SQL Server, I write:
Server: The IP address of my database. It is all that I have from the database server. And I have to write the port used by the server connection.
Database: The database name schema that I have to obtain data.
User: The username to access to the Server Database
Password: The password

I suposse that my problem could be in the server instance, that I don't have to write only the IP address and the port. Or maybe I don't have the right driver to use this object.

Please, help.
Thank you very much.

------------------------------
Alberto Gómez
------------------------------

RE: Data - SQL Server: Set Connection - Problem with the connection

PabloSarabia — Mon, 24 Jan 2022 10:42:00 GMT

Hi,

If i'm not wrong, the problem here is that you try to open a connection to a MySQL data base using the SQLConnection Class. HeidiSQL works correctly with SQLConnection, but MySql dont.

You can try making the connection with some C# code. This piece of code looks similar to SQL Server, here you have some links:

Build the connection String: https://www.connectionstrings.com/mysql/
Large example of code: https://zetcode.com/csharp/mysql/
Download the dll: https://es.dll-files.com/mysql.data.dll.html

And some quick steps to proceed:

1. Add the reference to the mysql.data.dll in your BP Object
2. Add a step of code with this lines (example)

using var con = new MySqlConnection(cs);
con.Open();

var stm = "SELECT VERSION()";
var cmd = new MySqlCommand(stm, con);
using MySqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine(rdr.GetInt32(0))
Console.WriteLine(rdr.GetString(1))
}

Hope this helps you 🙂

Bye!

------------------------------
Pablo Sarabia
Programmer
Altamira AM
------------------------------

RE: Data - SQL Server: Set Connection - Problem with the connection

ewilson — Mon, 24 Jan 2022 11:36:00 GMT

@Alberto Gomez,

There is a MySQL-specific connector available on the Digital Exchange.

https://digitalexchange.blueprism.com/dx/entry/3439/solution/mysql-utility

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Data - SQL Server: Set Connection - Problem with the connection

AlbertoGómez — Mon, 24 Jan 2022 11:38:00 GMT

Entonces, entiendo que el problema es que si desde mi equipo con BP tengo que conectar a una base de datos MySQL no puedo utilizar el Objeto "Data - SQL Server" que viene con BP. He intentado también con el OLEDB y tampoco me deja. Solución, ¿ir al código? Lo digo por no buscar mucho más, porque de momento, no estoy siendo capaz de hacer nada y me estoy volviendo un poco loco. Ya no se si es problema que no estoy siendo capaz de realizar la instancia adecuadamente, si me falta algún driver, o si que simplemente, el Objet de BP no es el adecuado.
Cuando tenga unos minutos a lo largo del día lo pruebo.

Muchas gracias y te voy comentando.

------------------------------
Alberto Gómez
------------------------------

RE: Data - SQL Server: Set Connection - Problem with the connection

AlbertoGómez — Mon, 24 Jan 2022 15:24:00 GMT

Thank you very much, Eric.
I tried it before. I saw you in others post and I tried to connect to my MySQL data base, but at least, I always get the same. Nothing.
When I run this stage (MySQL (Modified)), it had to return me a kind of collection (in my example, named "Results"). But I get nothing. No error, nothing wrong, no signals, any clue about what is going on. So I am a little bit disappointed.
Anyway, thank you very much

------------------------------
Alberto Gómez
------------------------------

RE: Data - SQL Server: Set Connection - Problem with the connection

ewilson — Mon, 24 Jan 2022 15:57:00 GMT

Hello @Alberto Gomez,

It's hard to say what might be going on with your modified version of the VBO. In the standard VBO, you would call the Set Connection action before anything else. This is where you would pass in your server name, the database name, and your username and password. This builds the connection string for you.

After that, you would call the Execute action (assuming you do not expect any output beyond the Success flag and/or an error message) passing in your SQL statement. If you're trying to execute a query that returns data you would call one of the other helper actions like Get Collection.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------