Connection problems between MySQL and FM databases

[Wardiam]

Hi everybody,

 

I have trying to combine data from a MySQL source with my local FM database using a shadow table with ESS connector. Basically I have done a Point Of Sale (POS) that gets the data from a MySQL database (it contains the information about the products, prices, stock, etc). I have two problems:

 

1. I have done an script that connect to this shadow table when I insert a barcode for a product and extract the information about that product. If I open the FM solution and initially run the script, the process is too slow but if I go firstly to the shadow table and then I run again the script, in that case the process is faster. I have solve this problem using and script that goes briefly to the shadow table (when I open the application) before returns to the main layout. Is there other alternative more elegant?

 

2. Certain products are duplicates in the database and have different barcodes. If I insert one of these barcodes, a floating panel is shown, an script using ExecuteSQL query is run to show only this products (from shadow table). To do this query, I need to go to the shadow table and do a search in Find Mode and then run again the ExecuteSQL query. 

 

I think that both problems are related and I suppose that I need to optimize the connection to MySQL shadow table. Could anyone help me to enhance it? Do I need to include any other step in the scripts to establish or maintain the connection to the MySQL database?

 

Thank you very much,

Wardiam

[ggt667]

1) Make tables with as little amount of fields as possible, this will avoid slow.

Tables that are interacting with the world outside FileMaker should contain only clean FileMaker fields without calculations, lookups or other auto enter options.

 

2) Make an equivalence table to be proactive and in that way avoid having to cope with this type of problem.

[Wim Decorte]

 

 

2. Certain products are duplicates in the database and have different barcodes. If I insert one of these barcodes, a floating panel is shown, an script using ExecuteSQL query is run to show only this products (from shadow table). To do this query, I need to go to the shadow table and do a search in Find Mode and then run again the ExecuteSQL query. 

 

 

This does not make sense...  Can you explain this in more detail?

 

As an aside: doing ExecuteSQL() calls on an ESS table is always going to be slow, it may be faster to just stick with the find.

[Wardiam]

Hi,

 

I will try to explain my problem more in detail:

 

This is my floating panel: https://www.dropbox.com/s/928klhk4h9uk7ly/Captura%20de%20pantalla%202014-08-02%2016.36.57.png

 

In "Buscar" field, I insert the barcode of the product. In the portal below, I get the information about "Barcode", "id_product" and "description" from the mySQL database. This database (mySQL) has 42 fields and 2257 records.

 

When I insert the barcode, then I click on "Buscar" button and this script is run:

 

Set variable [$result; Value:(*)

(*)
Let ( [ ~sql = "
SELECT ~idField
FROM ~table1
WHERE ~summaryField LIKE '%~value%'
";


$sqlQuery = Substitute ( ~sql ;
[ "~idField" ; SQLFieldName ( Facturas_PR_PRODUCT::ean13 ) ];
[ "~summaryField" ; SQLFieldName ( Facturas_PR_PRODUCT::ean13 ) ];
[ "~table1" ; SQLTableName ( Facturas_PR_PRODUCT::ean13 ) ];
[ "~value" ; FACTURAS::Buscar ]
);


$sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" )
];
If ( $sqlResult = "?" ;
False; //SQLDebugResult ( $sqlResult );
$sqlResult
)
)

If [not $result]
Show Custom Dialog ["Producto NO encontrado"; "No se ha encontrado ningún resultado"]
Else
Set Field [FACTURAS::PortalFilter; $result & ¶]
End If
Records/Requests []
Exit Script []

That's all.

 

If I run this script when I open the application, Filemaker is waiting indefinitely but if first I go to the mySQL table (layout) and for example I search the duplicates by barcode in FIND MODE and then I repeat the previous script again. I get the matching records in 1-2 seconds

 

Why???

 

Thank you very much for your help,

Wardiam

 

[Wim Decorte]

When you do the manual find, FM builds a cache locally and that is why the subsequents finds and sql queries are faster: the data has already been downloaded to your client.

 

The sql query you use is an "expensive" one because you use the LIKE operator.  Since this is a barcode, the values should be unique so there is no need I think for using LIKE here.

[Wardiam]

Hi Wim,

 

could you help me to remake the ExecuteSQL query. I have tried to replace "LIKE" for "=" but it doesn´t work.

 

Do you think that it's faster if I do the query using the FIND mode? I also thought to create a table with the same fields and synchronize it with the mySQL database but I don't know how to do it.

 

I need to do a lot of queries to mysql database and I don't know if maybe it would be better to have the database in local but how to synchronize it.

 

I'm a little lost.

 

Wardiam

[Wim Decorte]

I think you will have better performance from doing native FM finds on the ESS table than trying to do a SQL query.  So test that out first.

[Wardiam]

Ok, thanks Wim. I will try it.

 

Wardiam