How to connect MySQL to an Outsystems project?

How to connect MySQL to an Outsystems project?

  
I need to connect the tables from MySQL database to the entity tab of the service studio. How may i do this?
Hi Satya,

If I've understood your question, you are trying to import external DB tables into your application.
You can learn how to do it on the following training material:

http://www.outsystems.com/Training/Module.aspx?ShowTest=False&MaterialId=320&SkillLevelVersionId=97&RoleId=176

You can learn much more by doing the exams on http://www.outsystems.com/Training/


Best Regards,
José Martins
Hi Satya,
Following our call I decided to answer your question here as well. This way others can benefit from the answer to your question.

Outsystems does not support MySQL as the hub server database (the Agile Platform requirements can be found here) .

To access MySQL from the Agile Platform you can either do it like the example Jose mentioned or by creating an extension using Integration Studio. With integration Studio using standard C# project you can connect to the external data and seamless reuse it on Service Studio
Hello,

Are there any plans to support MySQL or PostgreSQL databases ?

Thanks

Ana
Francisco,

Unless I'm missing something, "the way Jose mentioned above" *is* using Integration Studio, isn't it?
Hi Jeffrey,

Yes, what José mentioned was to use Integration studio, but more specifically, to follow the tutorial in order to learn a bit more about it :)

Indeed, OutSystems still does not support MySQL as the hub server database, so the way to do for now is to query the MySQL database using C# or Java code.

I'm sorry I decided to jump to the answer, but since that discussion had been had 9 months ago, probably Francisco will not be aware that someone as asked this to him in the forums.

Let me know if there's something else you need!

Best regards,

Paulo Tavares



Is there any way to create an extension that connects to a MySql server directly so that you can read and write data on it?

The demos only show connecting to SQL Server and Oracle, which is all the types of database connections you appear to be able to create in Service Center.

Obviously there are a lot of legacy and non-legacy systems out there using MySql, so there must be a better way, but the tutorials and samples don't show it.

To be clear, I am not interested in using MySql as a hub server, but I have other product systems I want to be able to integrate with in Outsystems and those systems store data in MySql.
Hi Satya,

I have used MySQL in an Outsystems project. My requirement  was to be able to read data only though. For this I used the following technique:
  • On the Outsystems databaseserver: create an ODBC link to the MySQL database;
  • Create a linked server on the SQL Server;
  • Create views in a new or existing SQL Server database for all MySQL tables you need access to;
  • Now you can access the MySQL tables just like a MS SQL database and thus you can use Outsystems's standard tooling;

Be aware: this only works if you require read-only access only!

If you have any question: please feel free to ask.

Kind regards, Dick van der Reijden


Dick,

That approach, while rather cumbersome, will permit a kind of integration with mySql data sources.

However, as you point out, it's read-only.

There are myriad systems out there with mySql data sources -- there must be an Outsystems-prescribed method (best practice) for integrating with them in a performant, read-write manner.

I'm looking for that prescriptive guidance.

Can anyone from Outsystems point me at that information?
Hi Jeffrey,


To connect to external databases other than SQL Server or Oracle, you have two options:
  1. Create a DB Link to the external database and Views on SQL Server or Oracle
  2. Create an Extension that queries the external database
If you opt to use a DB Link and Views, then the performance issues and limitations were already discussed above.

If you create an Extension to connect directly to the external database, you need to write the C# or Java code that queries the external databases and then map the results into Structures of that Extension, so that you can use the results in Service Studio.

The direct connection to the external database can be done through ODBC, or any other connector, such as MySQL Connector/Net. You have a small tutorial here, particularly to the MySQL case, on how to write C# code to query MySQL.

Further, when writing this extension you can follow two different approaches:
  1. Query the MySQL directly through Actions of the Extension
    • This option is advisable if you only want to make a couple of queries
    • This option is advisable if you need to read / write data from / to the external database in real-time
  2. Mimic the MySQL database on the Agile Platform, read / write using the platform, and have the Extension synchronize everything to the external database
    • This option is advisable if you expect to do massive queries to the external data
    • This option is advisable if you do not need to read / write data from / to the external in real-time
  3. Mix the two modes, using local cache for reading and no cache for writing
    • This option is advisable if you need to write in real-time but can afford to read in non real-time

The attached solution exemplifies how you can read and write to an external database using an Extension and custom C# code. In the example, it connects to the Northwind SQL Server database, but the exact same principles apply to a MySQL database, only the connector changes.


Hope this helps,

Rodrigo Castelo
Hello everyone
I've spent a lot of time doing classes connecting ASP.NET to MySQL databases (because of budget constraints some companies cannot afford SQL Server, or ORACLE).  I believe that this framework is much  better one, if it integrates with MySQL databases, and any others e.g. Informix, Postgres etc.
So what I did was

  1. I've added a reference to the MySQL driver: MySql.Data.dll
  2. On the web. config I've placed the following key: <add key="constr" value="server=10.0.0.25;database=DB;uid=user;pwd='password';Pooling=true;Max Pool Size=200;Connection Lifetime=0;"/>
  3. Created a method to read web.config keys
  4. Created a method that uses that specific key and and executes any given query as entering parameter e.g. (update...)
  5. With those 2 methods then I started to fill in griviews, listbox with the query dataset
I believe that with Outsystems, the approach will be the same, nonetheless, I haven't achieved it yet, can someone help with a tutorial?
How can I integrate those classes/methods in Agile Outsystems?
I'm sorry but I'm still a bit green regarding OutSystems...

Cheers everyone

Francisco Lemos

Hi Francisco!

Good to have you here - there are definitely no "green" questions about OutSystems, we're all here to help!

Since you seem to have a background in .Net, the way to do it will be simple. Rodrigo Castelo - the post before yours - did an excelent summary of how to do it, and added a sample for you to see it working.

In this case, you do not have to do what you did for ASP.Net, changing the web.config file and the like.

As he mentioned,

To connect to external databases other than SQL Server or Oracle, you have two options:
  1. Create a DB Link to the external database and Views on SQL Server or Oracle
  2. Create an Extension that queries the external database
If you opt to use a DB Link and Views, then the performance issues and limitations were already discussed above.

The DB link should be done in your local SQL Server or Oracle server edition.

If you create an Extension to connect directly to the external database, you need to write the C# or Java code that queries the external databases and then map the results into Structures of that Extension, so that you can use the results in Service Studio.

The direct connection to the external database can be done through ODBC, or any other connector, such as MySQL Connector/Net. You have a small tutorial here, particularly to the MySQL case, on how to write C# code to query MySQL.

Further, when writing this extension you can follow two different approaches:
  1. Query the MySQL directly through Actions of the Extension
    • This option is advisable if you only want to make a couple of queries
    • This option is advisable if you need to read / write data from / to the external database in real-time
  2. Mimic the MySQL database on the Agile Platform, read / write using the platform, and have the Extension synchronize everything to the external database
    • This option is advisable if you expect to do massive queries to the external data
    • This option is advisable if you do not need to read / write data from / to the external in real-time
  3. Mix the two modes, using local cache for reading and no cache for writing
    • This option is advisable if you need to write in real-time but can afford to read in non real-time
The attached solution exemplifies how you can read and write to an external database using an Extension and custom C# code. In the example, it connects to the Northwind SQL Server database, but the exact same principles apply to a MySQL database, only the connector changes.

It is easy to integrate the Agile Platform with data that you have in a MySQL database - or any other, for that matter.

Even if you cannot afford the full package of SQL Server, you will always need either Oracle or SQL Server to run the Agile Platform - at least the free versions (SQL Server Express is the version that our free Community Edition uses). Download the Community Edition, and install it, since it will install the free version of SQL Server for you to get started with!

Let us know if it helps.
Hi guys,

Just found an idea in the Wisdom of Crowds for this. If you're interested vote for it: http://www.outsystems.com/ideas/364/mysql-integration/

Best,
Mário
Hi Paulo,

As you mentioned: If you create an Extension to connect directly to the external database, you need to write the C# or Java code that queries the external databases and then map the results into Structures of that Extension, so that you can use the results in Service Studio.

I have MySQL database with stored procedures for all required business logic. I'm looking to create the web application to be able edit this database. I don't have knowledge of C# or Java, but can write SQL.
Is it a way to use SQL queries directly or call SQL stored procedures from Service Studio?
Hi Sergey, and welcome to the OutSystems community forums.

Indeed, as mentioned before, it is possible to directly execute SQL and stored procedures from Service Studio on Oracle and SQL Server databases, but not for MySQL. In MySQL you will have to implement those operations in C# or Java.

It is not a complex thing to do, but if you have no prior experience in doing so, it may not be as easy as you would hope for. As Rodrigo suggested, the direct connection to the external database can be done through ODBC, or any other connector, such as MySQL Connector/Net. You also have a small tutorial here, particularly to the MySQL case, on how to write C# code to query MySQL, so I suggest you give it a try - that should get you started!

I hope this helps.

Regards
,

Paulo Tavares



Hello again
I was happy to see the uprising of this Agile Outsystems / MySQL  connection thing.
I droped the use of Agile Outsystem, although I've developed a complete set of classes to integrate ASP.Net with MySQL.
It would be in the comunity best interest to have some video demonstrating the same preformance seen with SQL server and Oracle, but in terms of MySQL.
The previous post has the basics, but for some of us (like mee, still at Outsystems kindergaten ), the video would be a major breakthrough.

Cheers

Francis


Hi Rodrigo,

    I went trough your Northwind connector application and I got the Idea, but now I'm trying to create a simple MySQL connector and I dont seem to get it...
   Could you provide me with just a simple solution, no need to show on a web screen, just the XIF file with a simple structure, like ID Name and one update function.


Thank you very much,
       João Ferreira
Hi João,


The attached extension exemplifies the access to an external MySQL database.

It is based in the tutorial and MySQL connector available at http://www.geekpedia.com/tutorial228_Connect-to-MySQL-using-Csharp-and-ConnectororNet.html


Hope it helps!
I'm totally new to OutSystems.  I've looked at this connector in the integration studio and it seems pretty straightforward.  How do I use it in a project?  Do I just drag it onto a flow before a query?
I've loaded the connector in to the latest version of Integration Studio (as of 7/14/11).  I tried to verify the extension and it says "Compile Error:  .NET Compilation" and "Error(s) found:  1 error(s) occurred while compiling the extension".  But there is no explanation as to what the compilation problem is.  

The following files in Resources\Source\NET now have red icons:

AssemblyInfo.cs
CustomExternalDB.cs
Interface.cs
RecordLists.cs
Records.cs
Structures.cs

I'm not a programmer.  How do I go about troubleshooting this?
I didn't have the Windows .NET SDK installed...

What I'd like to know is what do I need to modify in the config files to make it work with my database tables...
The following link is helpful for you. http://www.outsystems.com/Training/Module.aspx?ShowTest=False&MaterialId=320&SkillLevelVersionId=97&RoleId=176
A simple MySql Connection sample

Grval