hi all,
i need to extract data from some related table in a SyBase/Servoy local solution (let’s call it “Local-it”) and pubblish them on a remote MySQL server. The MySQL server supports a web site.
So in Servoy i created a second solution (let’s call it “Webb-it”) builded on the remote MySQL.
In Local-it i created some new relations to the Webb-it tables and a new form where a method pass the Local-it fields data i need into the (new)related Webb-it.
This system seems to work pretty well, i click the button and in the form i immediatly see the new related fields on the remote MySQL box. Click another button and get a view of the related web-page with the populated fields.
The only problem is the delay that MySQL seems to suffer from the creation of the record and his “released” to the web.
While from my 10 mbit fiber connection it takes 10-20 seconds, from the ADSL line of my customer’s location (1.2mb/256kb) it takes up to 5 minutes(!); and it even happens with only a few text fields.
The method i use to write into the remote fields is:
relation_name.field_name = local_field_name;
Don’t know if it’s possible through a SQL query (INSERT) as i haven’t come up to how address into the method the remote server name.
Is there anything i’m missing here or am i making wrong?
Thanks
Armando
Ciao, Armando
AGhisalberti:
Don’t know if it’s possible through a SQL query (INSERT) as i haven’t come up to how address into the method the remote server name.
SQL INSERT cannot be performed from Servoy (it would generate data broadcasting problems).
Do you experience the delay when creating the related records or after that?
How do you publish the data on the web? With Servoy or using a tool like php?
How does your method looks like?
Hi Riccardo,
Riccardino:
SQL INSERT cannot be performed from Servoy (it would generate data broadcasting problems).
that makes sense, thanks.
Do you experience the delay when creating the related records or after that?
there is no delay IN CREATING the related record. Apparently it’s performed on the fly.
But as i click the “open url in browser” method (to get a view of my newly created record in FireFox/Explorer) the fields seems to be empty.
It takes a while (up to 5 minutes from my customer’s location) to get a sort of “refresh” of the MySQL tables and have the fields populated.
How do you publish the data on the web? With Servoy or using a tool like php?
PHP. On the MySQL server there is no tool related to Servoy.
It runs on a dedicated Win 2003 Server box with a 5mbit accreditated line. Actually this server stll keeps online a FileMaker powered site that’s supposed in short to be replaced by the new php/SQL one.
How does your method looks like?
On the form i placed the “local tables” fields on the left side; the “yet-to-be-created” remote related fields on the right.
The method is (a bit simplyfied):
// create the remote related record
relation_to_mysql.newRecord();
// populate the related fields
relation_to_mysql.lottiid = lottiid;
relation_to_mysql.in_evidenza = in_evidenza;
relation_to_mysql.idavviso = idavviso;
relation_to_mysql.regione = regione;
// check for some related tables with PDFs and/or images
if (relation_to_pdf) {
// populate blob fields from/to related tables
upload_pdf();
}
// mark up some local fields
data_pubbl_web = new Date();
data_fine_pubblicazione = null;
flag_online = 1;
This process seems to be performed quite fast, in case of 2 PDFs and a pair of images (let’s say 7/800 kb total) it takes, from my fiber connection ca. 2-3 seconds.
So, i can’t explain the reason why MySQL(?) seems to lock the new record for such a long period.
Thanks
Armando
AGhisalberti:
// populate the related fields
relation_to_mysql.lottiid = lottiid;
relation_to_mysql.in_evidenza = in_evidenza;
relation_to_mysql.idavviso = idavviso;
relation_to_mysql.regione = regione;
ah, i forgot to add:
“lottiid” is the local pk column; while the “relation_to_mysql.lottiid” is simply indexed and NOT the remote table pk.
thanks
Armando
Are you sure it is not a cache problem (local or server)?
Can you work with the new record in Servoy (display it , modify it)?
What happens when you pass a link to the pdf (already on the server) instead of putting the pdf in a blob?
thanks Odysseus,
i made some more tests at the same time with both Servoy Client and YourSQL connected in my office, and a Servoy Client connected from my customer’s.
The “delay” is confirmed.
While from my own station it’s only 3-4 seconds (not bad);
executing the method from the customer’s client it rise up to 4-5 minutes(!).
The new inserted row DOES NOT appear in YourSQL neither on my Servoy Client for the whole time it takes to be displayed in FireFox (4-5 minutes).
On another test we made, i pubblished a new record through my Servoy Client while still wayting for the customer’s one. Well it appeared in the usual 3-4 seconds, positioned BEFORE the one created by my customer, BUT with the right pk FOLLOWING the customer’s one.
So i had a view of “my” record (eg. pk = 100) before the one submitted by the customer (eg. pk = 99) and sorted in YourSQL in this way:
pk
97
98
100
99
In this “delayed” time my customer CAN EDIT the related fields.
And as the record is displayed it HAS the values correctly modified.
I cannot make a test on the pdf-link, as you requested; cause the text-field pointing to the right file wouldn’t be populated.
Odysseus:
Are you sure it is not a cache problem (local or server)?
I don’t know.
Surely not a “browser” cache problem; is there any other cache setting i should be aware of?
Thanks
Armando
SOLVED!
This delay seems caused by my pubblisher method, as it doesn’t EXIT the related record, keeping it locked on the MySQL side.
I just added a “saveData()” instruction ( ![:oops:]( "Embarassed")
) and now everything’s is displayed almost instantly.
thanks
Armando
The insert locking is a MySQL ‘feature’.
Thanks Robert,
a locked state was one of the option i (too briefly) considered at first.
The strange behavior was the very different time it was taking from two different locations: 2-3 seconds Vs. 4-5 minutes.
Client running on the same platform, my guess was the line as the only difference. Still wondering.
Armando