0

I need some clarification. I'm planning to make a Scheduler program where it will fetch data from a MS Access Database, and I also want it to upload the data to a web server (MySQL Database) in JSON Format.

  1. In the first process which is the fetching, I'll use System.Data.Ole.db namespace. This namespace mostly worked in MS Access.

  2. In the 2nd process which is the uploading, I am planning to use FTP protocol and should be JSON Format.

I was just confused with the second step, is FTP protocol applicable for this process? I need some tips and suggestions.

I will make an Android apps to view all the data that will be saved on the web server.

Shadow The GPT Wizard
  • 66,030
  • 26
  • 140
  • 208
ching
  • 41
  • 5

2 Answers2

1

As I understand it, you want to copy data from your local MS Access database to a remote MySql database on a scheduled interval. Next you want to write an Android-app that consumes data from the MySql, this in JSON-format.

It's been a while since I last used MS Access, but I would simply setup the MySql destination table as a "linked table" in the MS Access database, then create a macro in MS Access database to INSERT rows into that linked table. I think that macros can be scheduled.

Alternatively, create a simple VB.NET console application, that reads rows from your MS Access database (as you said, using OleDbConnection), and inserts the rows into the MySql database (MySqlConnection - download from mysql.com). Schedule that with Task Scheduler.

Next, create a simple webpage (I'm guessing that this is hosted by a ASP somewhere) that reads data from the MySql and outputs that as JSON.

Hope this helps!

Fredrik Johansson
  • 3,477
  • 23
  • 37
  • Yup sir. thats right. I want it to be in JSON Format, so that it will be easily read by my Android App. The android app will then parsed the data. What confused me, Is that how I should store that data that I fetch from the MSAccess to be on the webserver (MySQL database)? What protocols sir? As i read from the statement above, regular connection string and used tcp/ip protocol.. Thanks for the help sir. Really want to clarify my concept. – ching Oct 09 '11 at 07:48
  • I think the confusion here is _when_ to use JSON. When you transport the data from MS Acccess to MySql you want to use standard TCP connections (OleDbProvider, MySqlConnection). Data should be stored in the MySql just as it is stored in the MS Access database - in a relational database manner. – Fredrik Johansson Oct 09 '11 at 07:53
  • Then, as I mentioned, create a simple webpage, at the ASP where you host the MySql, to query data from the MySql and output that in JSON-format. You could then call that page from your Android-app. – Fredrik Johansson Oct 09 '11 at 07:54
  • So, the scheduler program is nothing to do with the JSON?. The simple webpage should be the one to convert that in JSON Format. Sir,can i used PHP rather than ASP? I dont have background on ASP, only PHP a little.. – ching Oct 09 '11 at 08:00
  • Exactly. The scheduler just transports data using binary connections. The webpage (PHP will work) can just query the database (as you normally do in PHP, and "echo" a JSON structure as a response). Check out http://stackoverflow.com/questions/383631/json-encode-mysql-results for more on that. – Fredrik Johansson Oct 09 '11 at 08:59
  • Sir, thank you for your kindness for sharing me your knowledge.. Thanks – ching Oct 09 '11 at 11:03
0

I suspect that FTP for uploading data to SQL is way out of kilter.

Why don't you simply connect to both the databases using regular connection strings and pull data from one and push to another?

I don't know why you brought in JSON into the equation but if you want to store your data in SQL Server in JSON format you can use JSON.NET or the .NET MVC inbuild JavascriptSerializer to do the job and store the resulting JSON into a regular SQL Server table.

Zasz
  • 12,330
  • 9
  • 43
  • 63
  • But the scheduler program is installed in a desktop based.. So what protocols should i used to upload that data into the MYSQL database..? I want it to be in JSON Format., because Im planning to make an Android app and used HTTP post to get that data in JSON format. The android will then parse the data. Really need clarifications – ching Oct 09 '11 at 07:16
  • Please *edit the title* to reflect your actual question, it will attract more useful answers. Even if your scheduler is a desktop app, it can still connect over the network using the regular tcp/ip to your mysql server if you configure it correctly - take a look [here](http://www.connectionstrings.com/mysql) – Zasz Oct 09 '11 at 07:22
  • Ok sir. Should I used also database for my Schedular Program? So I will only used regular connection string and used tcp/ip protocol?.. because that data that I have fetch from MSaccess should be store on that webserver. Is that right sir? – ching Oct 09 '11 at 07:36
  • Sounds like a plan. There are many apps that store data on a remote mysql server over the network. What you described might work. – Zasz Oct 09 '11 at 07:51
  • Ok Sir.. Just confused with this concept. Im new to android. Thanks you, I taught FTP could be used on this but Im wrong. – ching Oct 09 '11 at 07:56