Hello, let me give everyone the scenario so that you can understand the whole situation. Right now our website is hosted remotely. Our database (which is in a .fdb format) is on our server locally. What we want to do is be able have our inventory on our website live. How can I get access to the database on our local server from the website? The language has to be asp or asp.net, our hosting provider does not support php or coldfusion. Thanks for your help!
Giving the outside world access to your internal database is one of the biggest no-nos of all time! Hackers will love you, and ruin you.
Create a snapshot of your database contents at regular intervals, upload it, and provide a way for outsiders to view that snapshot, with a note saying last updated on…
You could create a total extract each time you wish to post an update, but that might be a big, time consuming file.
You could reduce the size of the upload by extracting all data from a given date forward to the current date, and at upload time delete all data from the same date forward to the current date, it’s just a couple of extra lines of sql. You could move the start date forward at regular intervals, say the first day of the week or month or every six months, whatever suits you.
I update a site where visitors are only interested in one of two types of data extracted from the main offline database. So all the joins are done offline when the data is extracted to produce a pair of views, and then I have just two tables for them to query separately. Which simplifies the querying interface a great deal!
Thanks for your help John, I too only need at the most, ten fields out of the database. The thing is I dont even know where to start with this. I have done html sites for the most part and I have no idea where to begin here. How do you take snapshots. Would I create another SQL db to upload this data to? Also I need all of this to be automated because we do not always have someone here to upload info to the db
“fdb extension”. I have searched on the web and come up with
Database that stores financial information for a Microsoft Dynamics NAV project; used for business administration and management; can be accessed by other programs that support ODBC connections. Microsoft Dynamics NAV was previously called Microsoft Navision.
Is this what you have?
The amount of information you require seems fairly modest, “ten fields”. Have you considered a simple comma separated file as either the transfer mechanism to the web server or perhaps even as the database on the web server. Clearly the latter will depend on the size and whether you need to update.
Yes that is correct, this is what I have. I could do what you are saying with the csv and I have also heard that I could do this with XML. I dont know how though and also is this process able to be automated? Whatever I do has to be automated because we will not have anyone available to do this all of the time. Thanks for your help
Subject to what you have available to you there is no reason why such a task should not be automated.
On machine with FDB file set up a job to run regularly. The job should
a. create the csv file from the data on the FDB database
b. Transfer the csv file created to the web server using ftp
On the web server either use the csv file directly or have a job that runs regularly, looks for a csv file and if it is there updates a database on the web server with the information from the csv file. Once the update is completed delete the csv file.
Thanks Phillip for your quick response. How do I go about creating a job? I will give you a little more information so that you can understand the whole set up. Our database is stored on our local server in our office. It is running windows server 2003, soon to be updated to 2008 r2. Our web server is not local, it is offsite and I do not have access to their server other than ftp. I dont know how to create an automated job for this, can you tell me? Thanks again
On you machine you need four things.
-
An ODBC connection to FDB database. ODBC connections are set up by Control panel > Administrative tools > data Sources. I am afraid I don’t know about FDB but there is probably a driver to use in the ODBC connection. This might already be present otherwise you will need to get one from the installation disk or supplier.
-
You will need a program that uses ODBC connection, retrieves the information desired and stores the information in a csv file.
-
You will need automate ftp using the -s:filename command line parameter to connect to the web server, log on, and transfer the file.
-
You will need a scheduled task to execute steps 2 and 3.
On the web server. if you are using the csv directly you need do nothing. If you want to populate a database you will need
-
A program to read the csv file (if it is there) and update/insert into the database as appropriate.
-
A scheduled job to run the program in 1 regularly.
ok Ive got most of it, a couple of things though. Totally lost on the -s:filename thing, how do I go about doing that? And then are there any programs that you recommend for extracting the data from the database.
The process that I will be using will probably have to be the database because there are over 10,000 items that have pictures associated with them as well, so I will probably need to import the data into the sql database on the web server.
Thanks Phillip
ftp is a dos command line utility. You can follow the ftp with parameters such as the ip address. The -s:filename parameter gives the name of a file from which ftp commands can be taken and used. The commands in the file will typically be user, cd, binary, lcd, put, bye.
“pictures associated with them” - You cannot not up binary data in a csv file.
I’m afraid the program will need to be written. What programming languages do you have available to you VB?, C?, Perl?. Shouldn’t be too much coding.
If server side you have SQLserver you might be able to uses Data Transformation Services and a scheduled job to get the information from the csv file.
I dont know what I was thinking, the pictures will be refrenced in with a path from the db. As far as the programming languages go, I have done some objective C on the mac, a little bit of java, and asp/asp.net. Thats about it besides html :(. I would really like to learn more but I feel like I need to learn basic principles of programming before I just jump into a language. Or do you think I should just learn a language and rest will come to me?
When you use asp/asp.net do you use Visual Studio. If so you should be able to use VB to produce the program to access the database, issue a SQL query to gather the data and output the resultset to a file in a comma delimited format.
Hey Phillip,
I do not have VB, when I have done asp/asp.net, I have used micrsoft visual web developer or an older version of DW. Dont really know much about VB, is there a cheap version of it or is it really expensive?