Problems with using BDC to connect to MySQL

In the past two days, I helped a friend debug the use of BDC (business data catalog) to connect data in MySQL, and found a lot of special attention. When using BDC for connection, there is a big difference between MySQL and ms SQL Server.

My friend is using the driver myodbc on the server. Lobsysteminstance is defined as follows:

<Lobsysteminstance name = "mysql_lob">
<Properties>
<Property name = "rdbconnection driver" type = "system. String"> {MySQL ODBC 3.51 driver} </property>
<Property name = "rdbconnection server" type = "system. String"> MySQL server name </property>
<Property name = "rdbconnection Database" type = "system. String"> MySQL database name </property>
<Property name = "rdbconnection user" type = "system. String"> root </property>
<Property name = "rdbconnection password" type = "system. String"> 123456 </property>
<Property name = "rdbconnection option" type = "system. String"> 3 </property>
<Property name = "rdbconnection trusted_connection" type = "system. String"> true </property>
<Property name = "databaseaccessprovider" type = "Microsoft. Office. server. applicationregistry. systemspecific. DB. dbaccessprovider"> odbc </property>
<Property name = "authenticationmode" type = "Microsoft. Office. server. applicationregistry. systemspecific. DB. dbauthenticationmode"> passthrough </property>
</Properties>
</Lobsysteminstance>

Different database drivers have different connection strings. Therefore, if you are not using the myodbc driver, the lobsysteminstance statement is different.

I am not familiar with MySQL, So I encountered many problems when writing entity methods. One of them is the parameter of the SQL statement. For Ms sqlserver, we can use a format similar to "@ parametername" to define parameters in SQL statements. However, this is not necessarily effective for MySQL. Using different database drivers to connect to MySQL has different definitions for parameters. I searched the myodbc document and found that it does not support naming parameters, but directly uses "?" And then add the parameter values in order.

Therefore, when writing an SQL query statement of entity method, if there is a parameter in it, it can only be written as similar:

Select customerid, customername, contactname from customers where customerid =?

However, the parameter definition in entity can still be the same as that of the named parameter.