Issues with MySQL and JQGrid and FireDAC

[Omega8]

Just ported my database from MsAccess to MySQL. My application works fine with MsAccess. I have a table for JQScheduler and several tables that are used to make a Query for the JQGrid. So the only thing I changed was the FDConnection's properties which now point to MySQL.

Everything works fine except the grid shows no rows.

I started a new test application
1) Established a connection to MySQL
2) Dropped a JQGrid then a TIWCGJQGridDataSetProvider. Assigned the JQGridProvider to IWCGJQGridDataSetProvider1
3) Pointed the Dataset property in the provider to FDQuery
4) Created two Columns by selecting a row name from the Colomn.Name (Drop down List)
5) In UserSession on create event open the connection and activate the FDQuery

I also placed two edit fields which show the Query's values. There is only one row in the query.

I see the values in the edit fields but nothing shows in the Grid.
Could you please help me here? When I use the same configuration but this time pointing to MsAccess the Grid shows the values.
Thank you

[assapan]

Hi Omega8,
I use MySql without any problem so be confident.
Who owns the FDQuery ? Usersession or the form ? are you sure to not have two instances of Query ?

[Omega8]

I use a datamodule so the usersession owns the Query together with all other components like the connection, transaction, etc.

Why should I put the query on the form? Could you please advise. Because everything is in the datamodule, The are no duplicate querries or tables.

[assapan]

I use a datamodule so the usersession owns the Query together with all other components like the connection, transaction, etc.

Why should I put the query on the form? Could you please advise. Because everything is in the datamodule, The are no duplicate querries or tables.

There is no need to have the query on the form , but this can be done .
So datamodule is created by the usersession creation right ?
can you just try to set the properties by code in the oncreate of the form like

Code: Select all

mydatasetprovider.dataset := Usersession.Mymodule.myquery;


[Omega8]

Thanks you for your quick reply. I don't think the error is in this direction since when I change the FDConnections properties to access a
MSAccess db, the grid functions correctly like it supposed to. When I point the FDConnection to MySQL the grid does not work. In either case the querry works. I know this because the edit.text gets populated.

[Omega8]

Just did it what you proposed where mydatasetprovider.dataset := Usersession.Mymodule.myquery;

There is no change. Grid is still empty and the Edit.text are populated.

[Omega8]

MySQL uses a standard UTF8 Unicode Character Codes. The FDConnection.Params.Charset:=csUTF8;
Could this be an issue?

The MySQL server sits in the US in a VPS where as I am in Turkey. The querries take about a 2 to 3 seconds to load. How does the Grid load?
COuld this be an issue.

Thanks

[Omega8]

I also know that the issue is most likely not with the MySQL, I also have a JQScheduler in my application and this gets populated correctly
with MsAccess or MySQL. I'm stuck with the jqGrid issue . As a last resort, I may have to use CellProvider to load the grid.

[assapan]

WHat is the Mysql server host OS ? windows or linux ?
Linux is case sensitive in fields names , what are your fields names ?

[Omega8]

The VPS is a Windows 2008 Server. I have mySQL 5.5 installed. I used Delphi Design Time to assign the field names for the columns. They were all selected from a drop down list. In fact the spelling and the case of the column names are therefore identical with that of the FDQuery.
Both local MSAccess and the MySQL have identical Datasets in every way. The tables are simple tables with integer, TDateTime and String columns. The Table has only one Row. I output the rows values to Edit.text components to be sure that the FDQuery is working correctly.

The setup of the JQGrid is correct. I know this because when the FDConnection points to MsAccess the JQGrid show the row. When I however point to MySQL the Grid is empty but the underlying FDQuery's values are populated in the Edit.text components correctly.