Jump to content

Connection problems between MySQL and FM databases


Wardiam
 Share

This topic is 2551 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2551 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By 360Works
      How to Build FileMaker Web Apps for High Traffic using MySQL Backend and 360Works MirrorSync
      Why Sync FileMaker and MySQL?
      FileMaker is the best tool for rapid application development, but it is not the most reliable or scalable option for high traffic web sites. MySQL provides that reliability and scalability, but can't compare to FileMaker for development productivity and user friendliness.
      360Works MirrorSync bridges this gap, letting you use each tool for its best purpose. Quickly create layouts, scripts, and schema in FileMaker Pro, test and refine them, then use MirrorSync to publish all your record data to MySQL and keep both databases in sync moving forward. You can use industry-standard tools and frameworks (or hire from a vast talent pool) to create highly reliable and scalable web apps.
      In the event that FileMaker Server is down or rebooted for any reason (such as software upgrades), your MySQL web app will continue running and serving users. MirrorSync will detect when FileMaker Server comes back online and bring both databases back in sync.
      Video Demonstration
      This video, narrated by Junior Perez of 360Works, takes you on a step-by-step walk-through of how to sync between FileMaker Server and MySQL. Junior demonstrates how to start with a completely empty MySQL Server, create the database and tables, and set up an auto-scheduled sync to run every 60 seconds. The process is the same for any other JDBC-compatible database such as SQL Server, Oracle, or PostgreSQL. The license price for syncing one FileMaker Server with any one of these databases is $1,800, with discounts available for educational and non-profit organizations.
      If you would like to involve 360Works in configuring your sync, or in creating a web application backed by MySQL, please contact us for consulting and development assistance.
       
       
      Read More on MirrorSync or Download Now!
      360Works MirrorSync     Stay updated with 360Works
         
    • By Buckie
      Tried adding an ODBC source using both MySQL 8.0.22 and MariaDB 3.1.10 drivers in Unicode mode and I can connect just fine, however it's impossible to add a table onto the relationships graph. It sees the table's name but when I try to add it, I get 
      This action cannot be performed because the required table is missing. error. It works fine with ANSI version of the MySQL driver, sans the ability to work with Unicode of course. I've tried multiple combinations, including making the database and the table strictly "latin", it still refuses to add the table. The test database itself is very simple, just a single table and a single field, no spaces and no unicode characters in names. Test/test/test, basically, tested with an empty FM database. The server is running MariaDB 10.4.16. Any pointers to solve that?
    • By mountainx
      I am trying to import data from a MySQL database and need to somehow also import fieldnames to be the fieldname in FMP. Is there anyway to do that? I have a CSV of the fieldnames and I also have a MySQL dump of fieldnames and associated data.
      Thanks for any help.
    • By Stavie
      We have a complex MySQL and PHP; multi-tenanted application which we are now looking at syncing to an offline portable FileMaker model.
      I have essentially solved every problem thus far and have MirrorSync running properly.  
      My challenge is this:  Our main MySQL database works in tandem with a second MySQL database; the second database contains 25 million property records.
      The working set of data which needs to be synchronised is a small subset of data from the primary; and a single record from the secondary database into the mobile version of the application which consists of a single FileMaker file with identical tables to that of the primary mysql database and a table identical to that of that of the secondary.
      How do sync the two seperate databases in a single configuration?  Or is that even possible.  I could see that I could generate two seperate configurations; and perhaps split the mobile version into two seperate FileMaker files; i'm just wondering if there is a smarter way to go about what I need to achieve.
    • By Stavie
      We have a complex MySQL and PHP; multi-tenanted application which we are now looking at syncing to an offline portable FileMaker model.
      I have essentially solved every problem thus far and have MirrorSync running properly.  
      My challenge is this:  Our main MySQL database works in tandem with a second MySQL database; the second database contains 25 million property records.
      The working set of data which needs to be synchronised is a small subset of data from the primary; and a single record from the secondary database into the mobile version of the application which consists of a single FileMaker file with identical tables to that of the primary mysql database and a table identical to that of that of the secondary.
      How do sync the two seperate databases in a single configuration?  Or is that even possible.  I could see that I could generate two seperate configurations; and perhaps split the mobile version into two seperate FileMaker files; i'm just wondering if there is a smarter way to go about what I need to achieve.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.