[MySql] - Add Entity from External Database (MySQL) using Integration Studio

[MySql] - Add Entity from External Database (MySQL) using Integration Studio

  

Hi guys!

I'm trying to add an external entity from a MySql Database, using Integration Studio.

I have the connection and it's working properly.

Using Integration Studio (and the wizard) I can select the tables that I would like to import but the last step returns the following error:

Error retrieving entities: One or more errors occurred.

Looking at Service Center:

[2] Unknown column 'DATETIME_PRECISION' in 'field list'
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at OutSystems.HubEdition.DatabaseProvider.MySQL.IntrospectionService.MySQLIntrospectionService.GetColumns(IEnumerable`1 tableSources, CreateDataTypeInfo createDataTypeInfo, CreateColumnInfo createColumnInfo)
   at OutSystems.NssIntegrationStudio.CssIntegrationStudio.#Ocd(RCEntityIntrospectionServerRecord ssserver, RCEntityIntrospectionTableNameRecord table, IIntrospectionService introspectionService, IExecutionService executionService)
   at OutSystems.NssIntegrationStudio.CssIntegrationStudio.#yr.#rdd(RCEntityIntrospectionTableNameRecord table)
   at System.Threading.Tasks.Parallel.<>c__DisplayClass31_0`2.<ForEachWorker>b__0(Int32 i)
   at System.Threading.Tasks.Parallel.<>c__DisplayClass17_0`1.<ForWorker>b__1()
   at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
   at System.Threading.Tasks.Task.<>c__DisplayClass176_0.<ExecuteSelfReplicating>b__0(Object )


Since none of the tables have "DateTime" fields, I'm wondering, is this a permission problem?

I'm using the Personal Environment (10.0.105.0).

Thanks!


since you are saying none of the tables..

is it possible to single out the table that is causing it?

It's a crappy job, but at least we know what table causes it and we can go from there.



Hello Hélder

Can you tell me the version of the MySQL database you are trying to connect with?

You can check the version by running the query:

SELECT VERSION();

Let me know the result so we can help identify the problem.

J. wrote:

since you are saying none of the tables..

is it possible to single out the table that is causing it?

It's a crappy job, but at least we know what table causes it and we can go from there.



Yes, I did that! One by one, but all failed.

Also some of the tables were quite simple, like Id and Code (so, without DateTime fields there).


João Amorim wrote:

Hello Hélder

Can you tell me the version of the MySQL database you are trying to connect with?

You can check the version by running the query:

SELECT VERSION();

Let me know the result so we can help identify the problem.

Hi João,

I'm running 5.5.52-cll.

From the Release Notes, I can't see any major change regarding the DB structure.



Hi Helder

OutSystems only supports integration with MySQL databases in versions 5.6+

The DATETIME_PRECISION field is a metadata field present in MySQL (only 5.6+) that is used to obtain information about the columns.


Hélder Anselmo wrote:

João Amorim wrote:

Hello Hélder

Can you tell me the version of the MySQL database you are trying to connect with?

You can check the version by running the query:

SELECT VERSION();

Let me know the result so we can help identify the problem.

Hi João,

I'm running 5.5.52-cll.

From the Release Notes, I can't see any major change regarding the DB structure.





Hi Joao,

For my understanding, the method is being used to get the Entity structure from the DB.

Is there a way to avoid this? 

I'm thinking about creating the entity structure (field by field) in Integration Studio and change the configuration itself to point the table to the MySQL connection.


João Amorim wrote:

Hi Helder

OutSystems only supports integration with MySQL databases in versions 5.6+

The DATETIME_PRECISION field is a metadata field present in MySQL (only 5.6+) that is used to obtain information about the columns.


Hélder Anselmo wrote:

João Amorim wrote:

Hello Hélder

Can you tell me the version of the MySQL database you are trying to connect with?

You can check the version by running the query:

SELECT VERSION();

Let me know the result so we can help identify the problem.

Hi João,

I'm running 5.5.52-cll.

From the Release Notes, I can't see any major change regarding the DB structure.







Hi Helder 

Yes, if you create the entity manually you should be able to go around the import. 

However you might still run into some runtime problems or unexpected behaviours caused by the different MySQL versions

If it's possible update MySQL to a supported version my friend, its a free product!

On a second note: during advanced developer course an Outsystems rep said something about date time precision increasing again with the 10 update to include milliseconds, did your code work on 9? or is this something new you are trying?

No sure if that precision increase actually happened because can't find it in the patch notes.

Edit: looks like it did not make the patch: data types

There were also some changes to the integration with the external database in OutSystems 10. You can read about it in the Extensibility section of the Release Notes

A more detailed reference content for the changes is also coming soon.