SQL syntax error connecting FileVista 4.0 to MySQL Server 5.1

Hi Cem,

According this topic http://www.gleamtech.com/support/forums/2326/will-file-vista-support-mysql it should be possible to connect FileVista with a MySQL Server. However, that connectionstring looks like it belongs to a FileVista 3.x configuration. I've been trying to connect FileVista 4.0 to MySQL Server 5.1 but Configuration.aspx wasn’t able to finish because it couldn’t connect to the database. I tried these server settings:

Localhost
Localhost:3306
Localhost:3306\MySQL

Next I installed the Microsoft ODBC .NET Data Provider and MySQL Connector/ODBC 5.1.7. In FileVista.config the provider is set to System.Data.Odbc and the connectionstring is:

Driver={MySQL ODBC 5.1 Driver}; Server=localhost; Database=filevista; User=FileVista; Password=********; Option=0;

Running the website results in this SQL syntax error:

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.50-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Key], [Value] FROM [Configuration]' at line 1

Do you know what might be the problem?

Kind regards,

FileVista v4.0 uses DbFactory abstraction for connecting to the databases. So you should use .Net Connector instead of the ODBC driver:
http://dev.mysql.com/downloads/connector/net/

In web.config you should define (if not defined in machine.config, eg. by the installer) the DbFactory provider for MySQL (change the version according to the one you download):

<system.data>
    <DbProviderFactories>
       <add 
         name="MySQL Data Provider" 
         invariant="MySql.Data.MySqlClient" 
         description=".Net Framework Data Provider for MySQL" 
         type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.2.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
    </DbProviderFactories>
</system.data>

In filevista.config change these settings:

  <add key="Provider" value="MySql.Data.MySqlClient" />
  <add key="ConnectionString" value=" Server=localhost; Database=filevista; User=FileVista; Password=**" />

However, even you successully connect to MySQL database I am not sure if it will still accept all SQL queries syntax as they are optimized for ainly SQL Server. You should  test and let me know.