Slashdot Mirror
E-commerce and Linux
paRcat asks: "My company is using a proprietary system for letting our customers order online. It takes the order, and as soon as they click submit, sends it on to our main system via a serial connection. Both systems are running on NT, and they die constantly. I've been pushing to get Linux in here, and I think replacing the online ordering server is the best way to start. Our catalog has around 25,000 items in it. It's held in an Access database right now... around 14 Megs. I suppose it could be converted, but every time a pricing update comes out, it's distributed in mdb format. What tools exist for Linux that can do what I need? It just needs to allow access to the database, take the order, and send it down the serial line. I was contemplating setting up mod_perl and just writing a bunch of code, but I'm still a bit new to PERL, and I'm not even sure if that's the best choice. " Apache, Perl/mod_perl and MySQL/Msql/PostgreSQL all sound perfect for this application, but the issue of getting the Access information (both the existing data and the future updates) might be a problem. Any ideas?
One: Check http://www.mysql.org out. It answers a fair amount of the questions you might have already.
t ml#ODBC which should maybe make things a tad easier, but then again....
Second: As far as I know there is a middleware to convert access databases to MySQL data. This should take care of the problem with two db's. The only problem would be that it is a total db dump so it could become more and more unwieldy as the db grows.
Third: You can also connect the Access db to MySQL using MyODBC if so is needed http://www.mysql.org/Manual_chapter/manual_ODBC.h
With hopes this helps...
/Lindus
Just create a similair table on your mysql server. Then link the table into your access database (create a new, linked table w/ the ODBC drivers for mysql)
Then just copy and paste.
You can automate this in about 5 lines of visual basic.
On the down side, you do need to do this on 95 or NT.
Well, the original problem seems to be that pricing updates come in mdb format. Although I can certainly agree with the original poster that I certainly wouldn't want to put an e-commerce server on NT, it seems to me that one NT workstation with a copy of MS Access shouldn't be too much of a problem. You create an mdb with linked tables, one linked to the file location where you will put your pricing updates, and one linked through the MySQL ODBC drivers to the MySQL (or PostgresSQL or whatever) databases. Then a little AutoExec macro, and you've got yourself an "update application".
Pound! Bang! Bin! Bash! is this a shell script or a Batman comic?
Access allows you to get away with somewhat sloppy data modeling, so you'll need to revisit the generated SQL and make sure you have all the "NOT NULL" and different data types in place as you need them (this can all be tweaked in the VBA without too much effort, but you should be knowledgable in VBA and SQL). You no doubt know that the MySQL data types are more specific than the data types used by Jet (the actual db engine Access uses). This means that you'll want to make sure that the column that was exported as "REAL" shouldn't really be "MEDIUMINT" instead.
The script generates SQL files which can then be used against MySQL in batch mode. So with it, you can set up the VBA function to create the appropriate SQL for refreshing your tables whenever you receive a new MDB.
My suggestion is to dump Access. It's nice for rapid devolopment, but if you're running an e-commerce thing with it, then I pity you. Not only would NT be your problem, but you'll have Access' sloppy page-level record locking and it's tendency to not release connections and record locks with the consistency e-commerce apps should have the right to expect.
"Man has always been his own most vexing problem." --Reinhold Niebuhr, "The Nature and Destiny of Man"
Well, it sounds like a lot more research is needed before you're going to be able to present a convincing case. The NT side has a few things going for it:
:-). But plan on it taking some time to get it right. Be sure to steal as much code as possible from the people who have gone before you -- I suggest a through reading of the WebTechniques archives to see some excellent solutions to common problems.
- It's there. He who gets there first has the home-field advantage
- It handles the Access data import needs without any problems (or at least you didn't mention any)
- The usual PHB tendency to swallow MS FUD(tm) will probably work against you.
That having been said, here's a start on countering it and working up a case:
- Definately look into ODBC or some sort of easy export from Access. I'm not familiar enough with the MS world to know a sure solution, but I imagine the worst case solution is some sort of pretty simple VBA scripting in Access to open the file and dump out selected records (or all records) in a nice format MySQL can import.
- I agree that MySQL is probably a good client database. Don't rule out other options, however, you don't want to find MySQL doesn't fit your needs and then have to propose *another* change to management. PostGres has worked well for us in some applications, and is a little more full-featured than MySQL (although not as fast, and feature-wise it's actually a pretty close race). Oracle on Linux is even a possible choice, but you haven't mention budget or database size. Since it's coming from Access, I'll assume it's a small database -- in that case, MySQL looks pretty good.
- Web server: Apache or Stronghold if you need SSL. We just started using Stronghold, and so far it seems dead easy (much better than Netscape Enterprise Server which was the only SSL solution we had tried before this). It's not expensive as far as SSL solutions go, and it seems to track Apache releases pretty well.
- Application coding:
Perl is great, but you say you're not experienced yet. That's not a show stopper at all, but consider carefully how to proceed. If you're willing to pick up the Camel book, the Ram Book, and a printout of the CGI.pm and mod_perl documentation, you may be ready to be a Perl Web Programmer
As far as non-Perl solutions, Python is great, and Zope seems to be getting more and more attention. Check out Zope and scour freshmeat for other Web Application architectures, you may find those solve some of your problems well. Java servlets are an excellent choice also, but expect those to require much more programming savvy.
Anyway, the first step really is to analyze the current system and figure out all the components. For each one, pick a few possible substitutes. Play around and convince yourself that the pieces you select play nice together. Then show *that* to the PHB and get the go-ahead, they tend to be pretty easy to convince if you know your stuff...
It's a strange world -- let's keep it that way
Ya I know everyone wants linux these days because its so stable. But the simple fact is that a system is as stable as the programmers and administrators creating it. I know many NT gurus who have had systems up for years with NT and I know the same with linux. It might be smarter to try and solve the problem then to just convert for conversion sake and then end up having problems with linux and giving linux a bad name. We don't need that.
--MD--
--MD--
Post your resume. Show us the questions you would ask. If you can't even think of better questions, then your real imagination exceeds your imaginary one.
Show us why you are better qualified than anyone for the job.
--
Infuriate left and right
The perl was a piece of cake to move over - we even switched to mod_perl along the way. The database, however, was a bit of a pain. We moved it first from Access to SQL 7, then used SQL 7s data export function to stuff it into Informix on the Linux box. It was a nightmare, there are so many things that just don't move across. Views, identifiers longer than 18 characters, etc.
My suggestions, from hard-won experience are:
This is a good lesson in why to create portable applications. Just move in pieces and you'll gradually see your system get more and more stable, without getting above your head in new things to learn.
Good luck.
You'll find that radical shifts are often resisted in the buisness world. A phased implementation might be the best way to go & would easy to do.
:)
You're on the right path... Perl can certainly do what you. In fact, most of the difficult code that you would need is already written: DBI & DBD::ODBC. What you'd want to do is establish an ODBC connection to your Access database & do all your data manipulation that way.
Using this, you can move the dynamic component of your site to Perl based CGIs running on your existing NT servers. (Take care to avoid anything platform specific, and avoid ASP/Perl-script. That will only furhter anchor you to NT.) Not a radical shift, and one that should both help your current situtation and increase everyone's confidence in what you're doing.
Next, on the side, implement you linux box running mysql. Write a Perl script using DBI, DBD::ODBC, and DBD::Mysql to periodically refresh the mysql database from the Access table. This will be the trickest part. I recommend keeping the mysql data model the same as the Access data model to keep things simple. Unfortunetly, there isn't an easy/inexpensive way to read Access files on a linux box, but that's not a big obstacle. You can use DBD::Proxy. Alternatively, have two Perl scripts: one on the NT box to export data to a tab-delimited flat file, and another script on the Linux side to import the file. With some smart scripting, the use of Net::FTP & NET::Telnet, you could integrate this into one file. (Simpler, but less cool, just do some intelligent automated scheduling on both sides via NT's scheduler & Linux's cron.)
Now, through mod_perl into the mix. Present each solution side by side to your superiours. Explain the performance difference, the scalability, the cost difference, the seemless integration, and the added functionality of the Linux/Apache/Perl/mod_perl/Mysql solution, and *presto* you have your solution (and you look like a superstar.
-Bill
SlashSig Karma: Excellent (mostly affected by moderatio
but I cant affort the licensing restructions of MySQL for this.
There's a GPL MySQL out there, perfect for you then.
Yours Truly,
Dan Kaminsky
DoxPara Research
http://www.doxpara.com
I'm currently in the throws of the same type of project as you.... The only addition that I have is I need to include an accounting system written in Visual Foxpro. My requirements are to access a MySQL system that's our main database system, with some information needing to be handled via MSACCESS, and needing to query the Foxpro based system as well. To make it even more challenging, the Foxpro based system HAS to be on NT server per our support contract with the vendor! Nasty, but there's a way to make it all play nice!
I decided to use Perl instead of PHP mainly because of Perl's flexibility, runs on NT and Linux with little changes, excellent performance, etc. I'm not trying to start up a holy war here, use what you're comfortable with. The biggest hurdle was accessing Foxpro system on NT. For that I used OpenLink Multi-Tier 3.2 on the NT box. All you'll need to do there is configure the ODBC driver for Access. If you can read the *.mdb file with Excel, then the ODBC driver is configured properly. Install the OpenLink on the NT box - read the docs that come with OpenLink, it's also straight forward. You'll also need to grab the UDBC & ODBC stuff from OpenLink as well. That needs to be used to compile the DBD::ODBC perl module. Again, RTFM it's all in there. I did run into one glitch on an unrecognized command when compiling DBD::ODBC with UDBC. I only tested the module against Foxpro tables, and it did generate some errors. They were all due to SQL commands being longer than 80 chars - not a problem in my environment though.
Once that's up and running, and you've decided to use Perl - head over to the DBI/DBD Faq. Section 3 & 4 covers what you need to do. It's really not as hard as you think to connect perl to use ODBC, even on a seperate NT server. Connecting to any other ODBC compatible database works the same way.
From what I've read, PHP is just as easy. I first started developing our databases using PHP, but switched because I have other projects that Perl can be used, but not PHP. Perl looks to be slightly more complex than PHP for straight database access, but so far it hasn't been that bad. Some of the PHP code that I have looks real close to perl, so the switch isn't as painful as I thought.
I haven't tried this, but if you dump NT you could use iODBC to access the Access DB. All the parts are there, I've just never tried it. That way, you could still distribute your database in MDB format. Another option would be to keep a skeleton MDB around, and run everything on MySQL. When needed, you could just dump the MySQL tables out to the MDB tables and send it on its way. The second thing I did was set up a test network and just started working on it. I've been able to connect all the different databases together after about 12 hours of work.... Just to give you an idea of time and effort involved.
.mark
HTH
Remove the '_nospam' from my email address....
If you can't convert the whole damn thing to Linux, I strongly suggest you look into MSDE. MSDE ("MicroSoft Data Engine") was released with Office 2000 and is essentially the core of Microsoft SQL server without all the admin stuff. It also lets you access *.mdb files without Access. (Or so I'm told. I haven't tried that part of it myself.) Access is truly a dog, but SQL Server performs pretty well and from what you've said of your app, should easily handle your load. (From what I understand, Oracle is superior, but it isn't exactly cheap.) MSDE does not have any runtime charge like SQL Server, so there is really no cost to use it. (More Info)
(Of course, being Microsoft, there's already been one service pack...)
The cake is a pie
If there are a lot of Access-based tools that are being used to "massage" the data internally, that is arguably not wonderful; what is crucial is that this not be used for the online copy.
You probably should consider using something like MySQL for handling the online data access for the web server; it would be entirely appropriate to build a process that synchronizes the online data with what's in your "back office" systems. This synchronization can add substantially to the overall robustness of your systems, as this can allow you to detect both:
- Discrepancies that might happen on the "Web Server" side, and
- Discrepancies that might happen on the "Back Office" side.
The online system that the public has access to doesn't need to have access to all the accounting information that the back office needs, and by clearly separating them, you can strengthen the security and robustness of both.It might be useful to build some abstractions behind the scenes like message queues like IBM's MQSeries, on which Microsoft's MSMQ is based); a free tool that is commercially used that does this sort of thing is Isect .
If you're not part of the solution, you're part of the precipitate.
I just played around with this yesterday - ODBC access to mySQL data works very well. So all you have to do for the update is to write an VBA (the Access language) program to take the data from the access database and append it to a copy of it in mySQL. This should work very nicely.
I definitely want to emphasize what many others have: DO NOT use Access for a multi-user application. It will work just well enough to fool you into committing resources, and then it will fall on its face. You are much better off getting the data into mySQL as soon as humanly possible, and then going from there.
D
----
You can find its website here: http://www.covalent.net/
Or if you live in a free country, you can use mod_ssl at http://www.modssl.org
Also, I wouldn't really call it a close race between Postgres and MySQL features. MySQL doesn't plan to do SQL Transactions, for instance, while Postgres does. MySQL, on the other hand, has much friendlier SQL extensions, particularly for date formatting and such. Both have commercial support options.
If what you're really doing are transactions, this is fine. Build up sets of updates that are themselves transactions.
Keep in mind that the web server is not the back office; the data should get pushed over to the "heavy duty SQL box" when it comes time to do the accounting for either money or for inventory.
Consider the MySQL database to be an "embedded" database system, intended to support just the web application. Make that robust, and leave the "heavy production environment" stuff for the other server.
After all, you don't want customers outside to be directly hitting your master database, do you? I don't spell that "security."
If you're not part of the solution, you're part of the precipitate.
Anyway, regarding picking a Unix-type OS, and database it's obvious it's a complex issue. The machine crashing may be an immediate problem, but there are long-term issues to face. For instance, I have loaded (read loaded as in doing something - i.e., not idle) machines that stay up hundredes of days for an upgrade. That's without reading between the lines, reloading the OS, hacking the configuration or random parts of the OS breaking between upgrades.
People may say that Unix systems require less effort to run, but what it really requires is more knowledge. For instance, the primary webserver I run for an "e-business" is a single Debian machine on a pentium pro 200. Through several Debian upgrades (including libc5 -> libc6) it has always been stable and reliable. No service packs that break half the stuff, no middle of the night crashes, nothing. The amount of administration effort to run the box (which does hundreds of thousands of $$ of business a year) is a few hours a month. The cost of the setup was around $5000.
Fast-forward to my day job at a Fortune 500
There they recently migrated our mail server from a single (1) machine running netscape mail server to a farm of NT servers running Exchange. The Netscape mail server was on a Sun Enterprise and was rock solid. The Exchange servers, on the other hand, are on a weekly reboot schedule. Our Exchange/NT team had done all it could, and came to the conclusion that either the machines could be rebooted every 7 days or crash on their own every 10.
Also of interest is the management capabilities inherent with Unix-based systems vs. Exchange. For instance, on the Netscape mail server, if a user wanted files from his mailbox restored, a few files were restored from backup, and presto! On the Exchange server, the entire mail database has to be rolled back to the state where the files still existed (for *everyone*)
Another item of interest is that when doing the mail server migration, the postmaster box ended up with over 60,000 pieces of mail in its box from warnings. With the server on solaris, I was able to write a quick perl script that would delete the files of specific subject line. The Exchange team's answer to a similar problem (this time with 100,000 emails) was to pull them up in Outlook and delete the messages 10 at a time. Of course, that wasn't possible, as the machine would just freeze due to the insane amount of RAM required to do such a thing (not to mention the time required to do this 10 at a time..) Luckily one of the up & coming unix geeks had a MS background and mentioned that outlook delete filters would do the trick (which they did - but it had to be accomplished from the client side)
Anyway, the moral of the story is that NT server installations as a rule will cost more, require more maintenance and make it difficult for you to fix things when it really counts.
Anyway, no matter what you do this time, I'd reccommend you at least set up an experimental server to do similar things to familarize yourself with a unix-like enironment. And, learn enough perl (or python/zope or php or java...) to put together the kind of web application you'd want. My personal favorite is perl, as CPAN has modules to do just about anything, and it's been invaluable to me as a system administrator and web programmer, but I know people have done very cool things with the others, also. Also, SQL. I'd recommend Postgres and MySQL (whichever fits the job) and, possibly Sybase (but its proprietary nature can be a pain at times). Don't forget about FreeBSD, either. Its scaled-down nature can make administration easier when you only want specific things on a box, and at present, it has some large file advantages over Linux.
Check out http://members.aol.com/bbirthisel/alpha.html. Bill Birthisel has provided a Win32::SerialPort module that is compatible with Device::SerialPort (CPAN) such that you can now talk to serial devices using the same code even in Dark Side of the Force environments.
Using SerialPort and Perl is very flexible and powerful, the package let's you do exactly what you want/need to do. I used it recently to prototype a project, presuming that looping to satisfy reads, timing out, calculating CRCs, and such on the fly with Perl would require a re-write in C once I got it all working. Not a problem. It's fast and flexible, and very robust because Perl makes it easy to do things right.
"Only entropy comes easy" --Lewis Mumford
Just show them the CNET story on Windows 2000 pricing and tell them you'll be bankrupt if you ever upgrade. Good luck on the conversion!
bun-fhuinneog agam!
I think it's clear from this thread that Access just isn't designed to run your enterprise. At best it's for non-DBA's to run simple databases for a relatively small number of users. So you're going to want to convert to a full-strength DBMS.
The real problem with converting from Access to a real DBMS like MySQL is not in converting the data itself, but in converting the stuff that's not the data. An MDB file is not just tables of data, but an all-encompassing file that contains tables, queries, forms, reports, macros, and VB modules, etc.
Any good database has rules which prevent invalid data from being entered. Just like any good program has good error handling to prevent any possible user entry from crashing the program. When you get bad data in your database the results are worse because you won't have any indication that your data is bad until it gets REALLY BAD, or painfully obvious. Not a good thing for business.
The problem with Access is that it doesn't have a tool for managing all of these rules. They are hidden, partly in the table definitions, partly on your forms, partly in macros and VB modules, and partly in the "Relationships" view. A business-class DBMS will have a tool to manage all these rules and mechanisms in one place. It will also allow flexible rules. For example, you can relax the rules in order to import a table that you know contains errors, then it can find all the errors, separate them or fix them, and start enforcing the rules for all new data entry. Access just isn't that sophisticated, and what controls it has, as I mentioned, are not all in one place.
The bottom line is that while it's easy to copy the data, it's very difficult to port over all of these hidden mechanisms that control data quality. That would require someone who knows a lot about Access, which I assume from your question that you are not (no offense). Your alternative is to copy the data to your favorite DBMS (MySQL, Oracle, or whatever), and then set up all your data integrity rules from scratch on the new system. That's probably what you'd end up doing anyway even if you could find all the hidden stuff in Access. It's also not a bad thing to do because it will force you to review and reconsider what data you are storing, how it is stored, how everything relates, what your data entry processes are, etc. That process is always a good thing. You would be wise to get help from a DBA.
If you feel confident hacking a DBMS without a DBA, then more power to ya, but you would be wise to proceed cautiously with your company's data. Build the tables and rules in your new DBMS and test it extensively with test data. Make sure it not only works, but also robustly handles errors and bad data entry before you stake your business on it. If you don't believe that it's important to address data integrity issues, take a look at the Y2K problem/response/expense and realize that you can have the same problems with ALL of your data, not just the dates.
If you aren't familiar with what data integrity/quality is, or how to maintain it, try the following links:
Database Central
DM Review
Best of Luck.
But in a servlet environment the JVM has already been started. So that nullifies the startup time difference except in cases where a new process has to be launched to run the perl script or CGI. And then we probably shouldn't even compare, should we?
One nice thing servlets has going for it is that all of the session management is handled for you. This is a big benefit.
Usually the only people that complain about Java's speed in a server-type environment have never even compaired it. Sort of like all of those Windows users who think Linux sucks but can't hardly spell it.
I've done both CGI (even in C) and Servlet development. And although I would be dragged kicking and screaming into full-time web development, I would still recommend Java servlets in the right situation.
My (stagnating) progeeks.com web site uses servlets and I was able to code the entire thing in one weekend. (Well, the parts I have finished anyway.) The company I actually work for is switching all of their web apps to servlets.
Fun stuff,
-Paul (pspeed@progeeks.com)
Edu. sig-line: Choose rhymes with lose. Chose rhymes with goes. Loose rhymes with goose.
Comparing? THEN use THAN.