Exporting data from web & import into servoy

Classic Servoy
1

Hi,

I have a website where people signup for the seminars.

I have a table in Servoy to record the seminar_signups.

Since the signups are done online (PHP & MYSQL) which is residing on seperate web server, I have to some how download the data from the web and update it in Servoy

I have assumed to accomplish the task using the following preocedures:

  • I have a table called Seminar_Signups online in MYSql database
  • Each record has a unique ID
  • I have another field called update_status (values could be 0 & 1).Default = 0 (when created)

Step 1:
In Servoy the user clicks the button called “Receive Online Signups”

Step 2.1:
A method in Servoy calls an URL (a php file) in the website

Step 2.2:

  • The php script sends a query to “Seminar_Signups”
  • This query finds all the records with the update_status = 0
  • A Php functions creates a tab file & and writes the records found (tab seperated)
  • Change the value for update_status = 1 (so that it’s not exported again)
  • The tab file now stored under the web server

Step 3:
How to download the exported tab file to local machine?

  • The method in servoy continues..
  • Using FTP bean Servoy connects with the web server and go upto export directory
  • Now the exported tab file is downloaded locally to a temp directory

Step 4:
How to update/ inserts the records in tab file into Servoy (seminar_signups)?

  • Servoy should some how read the data and convert into an array
  • The array should be looped and
  • if the serial already exists the record has to be simply updated in seminar_signups
  • if the serial does not exist the new record has to be created in seminar_signups

??Question:??

Before I start implementing this I would like hear your valuable comments.

? Is the above steps are right or am I doing anything wrong?
? Do I need to download the file using FTP bean or any alternative way to read the tab file locally?
? And I don’t know how to do Step 4

Awaiting for your replies,

Thanks
Ahmad

2

Question 1: Why are you importing at all? Why not just use that MySQL table on your ISP as a “live” resource within your solution?

Here’s the way I would do it:

  1. Make a connection to the MySQL dataabase on web

  2. Make a form based on that connection

  3. Write a method that would search for update_status = 0, then would loop through that recordset and would just create a new record in your local database (controller.newRecord() - then update all the fields), then mark the update_status = 1.

Simple. :D

Hope this helps,

Bob Cusick

3

Hey..

This is great and worked great.. saved lot of time & coding..

Thanks Bob for your help..

But I have few problems..

  1. I have different websites on different web host from which data has to be download into servoy. Unfortunately some web host don’t open the port for mySql so that I can connect from my servoy solution. I don’t know what to do for this case.

  2. If I use relations between the web & servoy tables and if I’m not connected to the internet, errors keep coming. Ofcourse I will be connected to the net when I need to download the data but not all the times. So I decided not to use any relation between the web & servoy tables.

Your suggestions are most welcome

Thanks

4

ahmad:
This is great and worked great.. saved lot of time & coding..

Thanks Bob for your help..

GLAD I COULD HELP! :)

ahmad:
But I have few problems..

  1. I have different websites on different web host from which data has to be download into servoy. Unfortunately some web host don’t open the port for mySql so that I can connect from my servoy solution. I don’t know what to do for this case.

You’re out of luck. You can have them open the port - or work with you to do an automated export and email to you, or ??? EASIEST: Have them open the port to you via VPN or SSL or something.

ahmad:
2. If I use relations between the web & servoy tables and if I’m not connected to the internet, errors keep coming. Ofcourse I will be connected to the net when I need to download the data but not all the times. So I decided not to use any relation between the web & servoy tables.

Yes, that is correct. If you’re offline then Servoy can’t make the connection to the remote database, so any form you load that has fields that use the relation will error. It’s not that big of a deal - just don’t view or show the forms that have the related fields and you’re ok.

Cheers,

Bob Cusick

5

So, I have the same basic problem, except no ASP/ISP, instead I have a instrument that spits out CSV data. And therefore it is not in a MySQL table, that is what I hope Servoy can do for me: move the CSV data in MySQL.

I created the table in MySQL using Servoy, and can run the import manually, but the process is labor intensive and I want to script it using Methods, since the CSV format does not change in this case, and further down the road I want Servoy to do some data validation for me, now just want to upload to MySQL though.

I am new to methods/Servoy, and it is not jumping out how to ‘fix’ the import order like in FMPro’s ‘keep import order’ part of scripting.

Any help for a reforming FMPro developer is appreciated, or if you can point me to Servoy manual I appreciate that as well; however online help did not jump out with the answer.

6

ross_lindell:
how to ‘fix’ the import order like in FMPro’s ‘keep import order’ part of scripting.

Include a header for each column (field), as in a FileMaker Merge[*.mer] export, like this:

FieldName1,FieldName2,FieldName3
“a”,“b”,“c”
“d”,“e”,“f”

This will save you from having to match up every column while importing from within Servoy.