OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to create an "upload to webserver" button?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
comomolo
General User
General User


Joined: 15 Jun 2004
Posts: 45

PostPosted: Mon Sep 04, 2006 8:54 am    Post subject: How to create an "upload to webserver" button? Reply with quote

Hi,

I've been through many "Export to MySQL" threads trying to find a solution to my problem, but I can't. Basically, using a form to modify a remote MySQL database via ODBC connection is unusable, so I'm forced to work on a local database and then copy the tables to the webserver via drag and drop. My database has many tables and this is really cumbersome and error prone procedure. Also, the forms I'm designing are going to be used by very non-techies, so any grade of complexity is better avoided.

So here's my question: it would be nice to have an "Upload" button. By pushing the button in the form, all tables applying to the form would be copied to the remote database, which is MySQL. Can that be done and how? Macros are welcome, but keeping things as easy as possible is always best.

TIA

CM
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Sep 04, 2006 9:46 am    Post subject: Reply with quote

Well, first and this is more for informational purposes, are saying ODBC to the remote server is unusable because of a timeout problem, or some other? Also, have you tried using JDBC instead?

Anyway - regarding a macro to export to MySQL, hmm. Shouldn't be that difficult to accomplish, your uses need to not see the inner working of the macro at all.

Question - You say there will be multiple persons using the local database. I assume then you mean there will be mutiple copies of the local database file floating about. So, when a user goes to POST this data back to the mySQL server do you want to perform updates to records that exist, and inserts to any new records. I would guess that is what you want - yes?
Back to top
View user's profile Send private message Send e-mail Visit poster's website
comomolo
General User
General User


Joined: 15 Jun 2004
Posts: 45

PostPosted: Mon Sep 04, 2006 12:16 pm    Post subject: Reply with quote

DrewJensen wrote:
Well, first and this is more for informational purposes, are saying ODBC to the remote server is unusable because of a timeout problem, or some other?

I'm not sure what the exact cause is. All I know are the symptoms:
-Opening the form using the remote database may take from 30 sec to 1 min.
-A simple "next record" button click may take som 10 to 15 sec
-Too many times a field won't be shown entirely but trimmed. For instance, the word "Congratulations" may appear as "Cong".
-Too many times the thing will freeze with no apparent coming back (I haven't been patient enough to see if it comes back to life after a long time). This might be the timeout issue, I don't know.

None of these symptoms appear when using the database locally. I'm on a 1 Mbps ADSL line, the database is used in a webpage where it goes fast and reliably.

Also, as I said, I can upload the tables by drag and drop and that works well most of the time (a crash every now and then of OOo Base, but that's pretty it).

Quote:
Also, have you tried using JDBC instead?

I'm using ODBC. I tried to use JDBC but something went wrong. I might try it again, though.

Quote:
Anyway - regarding a macro to export to MySQL, hmm. Shouldn't be that difficult to accomplish, your uses need to not see the inner working of the macro at all.

Exactly, I don't want the users to see the inners. Do you have any idea if such a macro exists already?

Quote:
Question - You say there will be multiple persons using the local database. I assume then you mean there will be mutiple copies of the local database file floating about. So, when a user goes to POST this data back to the mySQL server do you want to perform updates to records that exist, and inserts to any new records. I would guess that is what you want - yes?

The local database will be used by a number of people but just one at a time. Two or three people will have access to the .odb file. Only one of them will open it every once in a while (probably once a month at most), change a few things and upload the database to the server. I really don't need true syncing or managing the remote database in real time. A brute force full upload will be fine.

Thanks!
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Sep 04, 2006 1:44 pm    Post subject: Reply with quote

OK. Regarding the performance issue - as I reacall there was a thread, somewhere and I just can't remember where off the top of my head, a few months back about a person with a similar problem. If I remember correctly they did find a solution, and it was not something that would imediately come to mind - I will do a search of my notes and see if I can't find where that was. It might be something for you, it might not..don't know for sure.

-----as for the macro------

I have a few ideas, but want to test them first. I have access...maybe, if I can remember the password..LOL..to a MySQL server at my shared host. I want to try connecting myself..and then try a few of my ideas...

I'll be back in touch..

In the mean time here is a few thoughts:

don't know of any canned macro at the moment - but writing one shuold not be all that much to do. Especially if you are willing to start with one specific to your table structrues - then perhaps refactoring it for a general purpose.

The real question is the format. One could generate a csv file pretty easily and then transfer the whole thing. One could generate an xml file almost as easily. Another option would be to generate a dump file - HSQL can generate this quite easily using the SCRIPT command. This would give you all the insert commands for the data. The macro would have to do some reformating oft this to handle the syntax differences between HSQLDB and MySQL - but shouldn't be so bad.

For all of these approaches you would need some script on the web server side to read the file in and update MySQL. The only way around that would be a Base connection again to the server, but since that was the problem..well..

Anyway - as a start. If you open your local database, open the SQL window and enter this command
SCRIPT 'c:\tmp\mydata.sql'
you will find a text file named c:\tmp\mydata.sql, this will have all the DDL commands to recreate your schema, it will also have all the DML update statements to poulate it with the data currently contained in it. This would be my starting place for a task like this.

This first step is easily scripted, using a macro that issues the command via a statement object.

The next step would be to open the file and read it out to a second file, minus the DML commands. The file is such that once you see the first INSERT statement, that is all that follows - so this should be easy enough with the SimpleFile functions in OOBasic.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
comomolo
General User
General User


Joined: 15 Jun 2004
Posts: 45

PostPosted: Mon Sep 04, 2006 4:27 pm    Post subject: Reply with quote

DrewJensen wrote:
OK. Regarding the performance issue - as I reacall there was a thread, somewhere and I just can't remember where off the top of my head, a few months back about a person with a similar problem. If I remember correctly they did find a solution, and it was not something that would imediately come to mind - I will do a search of my notes and see if I can't find where that was. It might be something for you, it might not..don't know for sure.

Nice to know it's not something completely new. It would be great if you find it. I haven't found anything here in the forums.

Just in case it gives you any more clues, the structure of my database is as follows:

I have three tables connected in cascade: family->subfamily->item. The form shows data extracted from the three tables. By choosing a family, you get the subfamily and by choosing a subfamily you get an item. A navigation control for each table is the way to move around the whole database. It's quite simple, really and the tables are not really that big.

The .odb file is some 2,5 MB big. I've downloaded a backup of the MySQL version of it and it's just about 200 KB uncompressed (it should grow much bigger than that since it has images in it and it will be getting more and more images soon).

Quote:
-----as for the macro------I have a few ideas, but want to test them first. I have access...maybe, if I can remember the password..LOL..to a MySQL server at my shared host. I want to try connecting myself..and then try a few of my ideas...

Well, I must thank you for all your thoughts. You are really very kind. If you need MySQL access to my shared server in order to try your ideas, please don't hesitate to contact me privately.

Thanks again.

CM
Back to top
View user's profile Send private message
comomolo
General User
General User


Joined: 15 Jun 2004
Posts: 45

PostPosted: Mon Sep 04, 2006 4:59 pm    Post subject: Reply with quote

More "synptoms":

- The delay in getting data from the remote MySQL is bigger the higher in the hierarchy the navigation is made. Navigating families takes longer than navigating subfamilies and the latter takes longer than navigating items, which is pretty fast. That makes some sense since navigating a family makes the form fetch the subfamilies for that family and in turn the items for the first subfamily.

- I can see the trimming of the names happens only in the subfamily table and it trims every character in excess of 11. In other words, only 11 characters are shown. 11 happens to be the length of the int field... That is the only relation to anything I can think of (???)
EDIT: Trimming was my fault. I've seen a text limit on the field. I can't remember putting it there, but I probably did...
EDIT2: No. It's not me. The field becomes limited to 11 characters every time I switch Design ON/OFF, if it's set to 0. It stays at 25, though, if I put that limit manually. This must be a bug...


- Opening the database with a MySQL frontend such as Navicat, I can see that after dragging and dropping in Base from local to remote, ordinary text fields became memo (longtext) fields, bobs turned into longblobs, etc. Changing these to their original values only makes speed problems worse.

CM
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group