MySQL Connection String

Discuss RoboTask here
Post Reply
diego.belohlavek
Posts: 2
Joined: Wed Jan 22, 2014 7:06 pm

MySQL Connection String

Post by diego.belohlavek »

Hi,

I am new on Robotask, i want to execute some sql queries in a local MySQL batabase.

I am using Windows ODBC connection. I have try:
-MySQL ODBC 5.2w Driver
-MySQL ODBC 5.2 ANSI Driver
-MySQL ODBC 5.2w UNICODE Driver

with this connection strings:
-Driver={MySQL ODBC 5.2w Driver};Server=localhost;Port=3307;Database=db_name;User=root;Password=1111;Option=3;
-Driver={MySQL ODBC 5.2 ANSI Driver};Server=localhost;Port=3307;Database=db_name;User=root;Password=1111;Option=3;
-Driver={MySQL ODBC 5.2 UNICODE Driver};Server=localhost;Port=3307;Database=db_name;User=root;Password=1111;Option=3;

but id dosn´t work.

the Database parameter, is the database name or the ODBC resource name

Any idea?

Thanks
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: MySQL Connection String

Post by Oleg »

In order to use ODBC driver you have to specify ODBC DSN (Data Source Name) in your system.
I don't know all options of connection string, but I always use system wizard to build necessary connection string.
See below how to use system dialogs and wizards to create connection string. This is standard interface of system.

When I already have configured system or user DSN in my ODBC configuration.
1. Open action editor and press the button to open wizard
begin.png
begin.png (20.01 KiB) Viewed 14751 times
2. Choose "Use connection string" and press the button at the right.
step1.png
step1.png (22.4 KiB) Viewed 14751 times
3. Choose "Microsoft OLE DB provider for ODBC drivers" and press "Next"
step2.png
step2.png (53.17 KiB) Viewed 14751 times
4. Choose "Use data source name" and select configured DSN in the drop-down list
step3-1.png
step3-1.png (57.65 KiB) Viewed 14751 times
Now you can test the connection and press OK.
I get such connection string: Provider=MSDASQL.1;Persist Security Info=False;Data Source=MySQLTest

How to create new DSN in my ODBC configuration and use it.
1-st and 2-nd steps the same:
1. Open action editor and press the button to open wizard
begin.png
begin.png (20.01 KiB) Viewed 14751 times
2. Choose "Use connection string" and press the button at the right.
step1.png
step1.png (22.4 KiB) Viewed 14751 times
3. Choose "Use connection string" and press "Build" button
step3-2-1.png
step3-2-1.png (48.1 KiB) Viewed 14751 times
4. Select "Machine data source" tab and press "New" button
step3-2-2.png
step3-2-2.png (46.79 KiB) Viewed 14751 times
5. Select "User data source" and press "Next"
step3-2-3.png
step3-2-3.png (52.9 KiB) Viewed 14751 times
6. Select "MySQL ODBC driver" and press "Next"
step3-2-4.png
step3-2-4.png (52.9 KiB) Viewed 14751 times
7. Press "Finish"
step3-2-5.png
step3-2-5.png (51.71 KiB) Viewed 14751 times
8. Now you see "MySQL connector" dialog. Input necessary parameters for this connection. I use localhost as my server and test as my database (choose from drop-down list)
You can test your connection and press OK to save parameters
step3-2-6.png
step3-2-6.png (56.99 KiB) Viewed 14751 times
9. Select crated connection and press OK
step3-2-7.png
step3-2-7.png (47.2 KiB) Viewed 14751 times
10. Test your connection and press OK to save data
step3-2-8.png
step3-2-8.png (51.87 KiB) Viewed 14751 times
I get such connection string: Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=testMySQL2;DESCRIPTION=Another test connection;SERVER=localhost;UID=root;DATABASE=test;PORT=3306"
Both configurations work well.
You can use new DSN as already configured DSN in other tasks.

Below is my small example:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|693052941
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task699"
Hide=INTEGER|0
ID=INTEGER|1804063903
LogOnAsUser=INTEGER|1
Name=STRING|"SQL Query2"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER

[Actions\Action1]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER

[Actions\Action1\Params]
assignvar=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DSN=testMySQL2;DESCRIPTION=Another test connection;SERVER=localhost;UID=root;DATABASE=test;PORT=3306"""
override=STRING|"0"
password=STRING|"2005621899218992061821389"
sql=STRING|"""select 'hello world'"""
timeout=STRING|"60"
Oleg Yershov
diego.belohlavek
Posts: 2
Joined: Wed Jan 22, 2014 7:06 pm

Re: MySQL Connection String

Post by diego.belohlavek »

Great!!

It works perfect...

Thanks
Post Reply