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?
I think MySQL can import MS-Access databases, if not you can write an import asp script that will do it.
--fatboy
Export, import.
yet another lame first posting.
but -the question is a fine one indeed and I'd surely like to get a few m$ based companies dealing in database solutions to look this way.
"There should be a science of discontent. People need hard times and oppression to develop psychic muscles"
*y2k -Azathoths minions had it coming*
I believe there is a perl odbc driver.
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
Perhaps some advice is in order...
t ml#ODBC which should maybe make things a tad easier, but then again....
First: Check http://www.mysql.org out. It answers a fair amount of the questions you might have already.
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.
Use ODBC to pull your information from the Access Database. Use the DBI module under perl. Installing mod-perl is pretty simple, but if you are not real familier you might want to just set it up as a standard cgi.
Lando
/* TODO: Spawn child process, interest child in technology, have child write a new sig */
You might want to try exporting from Access as some form of ASCII delimited text. I've done Access->PostgreSQL like this with CSV (comma-separated values), but there were some glitches with regard to fields that were null. In any event, it appears that you'll have to keep a Windows box around, if they send updates as .mdb files.
Upsize the Access database to a SQL server. Only the insane use Access for anything bigger than 1 meg. That will solve your problem... not moving to some operating system that has hype left and right around it. Fix the problem, don't just look for the easy out, or else your next OS will be a C-64 because you heard that doesn't crash.
You can handle this problem in a number of ways.
1. You can export the Access table to a CSV file(comma separated value) and then import the file to the database(with database suplied tools or a script).
2. PostgreSQL and probably all the other Open Source Databases have ODBC drivers available which allows MS Access to link directly to the database server.
I think there is an ODBC driver for MySQL. Write an Access Macro that rips yer tables from and crams them through the ODBC handle in Access. If that doesn't git you goin' Try Oracle 8i for Linux. If ya can't get yer ODBC to talk to a Oracle database, you've got bigger problems than just MDB files...
Call your local MSP (Microsoft Solution Provider). I'm sure they will find something for you to help you out. After all, if you're not part of the Microsoft Solution then you're part of the Linux Problem. Windows2000 sounds like an appropriate solution. It is an *enterprise class* operating system that has the best possible performance, reliability, security, ease of use, and the lowest total cost of ownership. So call your local MSP today and ditch that ancient and inferior UNIX or VMS forever!
mysql is a good databse,as others have mentioned, you can use access to connect to it...
perl or php3 would probably be good choices for the language, as both are nice high level things that interact w/ databses easily
i strongly recommend not attemtping to use minivend though, as its a major PITA to get working, and the documentation is old, brief, and in some cases wrong
Need a Catering Connection
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"
I know a lot of people are going to advocate PERL, but you should also look into the php ( http://www.php.net/ ) and mySQL combination.
with php4/zend on the horizon, php is looking to be an increasingly robust and viable alternative to PERL.
----- --- - - -
jacob rothstein
jacob rothstein reed college
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--
For another client I eventually made a web-based editing interface and eliminated their Access database altogether. Works even better :)
-- Tom Rathborne
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
I've been using PostgresSQL for a web-style application and it works just fine. My co-worker on the project has used MySQL and that also works just fine. IMHO, MySQL is probably better (it has some neat features such as the LIMIT/OFFSET on queries), but I cant affort the licensing restructions of MySQL for this.
Also, if you're going to be learning Perl for this application, might I suggest using Python instead? It can do anything Perl can, but its a considerably cleaner language (read: more readable), and there's also a Apache module for it.
Perl's fine for programs around 50-100 lines or so, but larger than that and it starts to be come really messy.
YAMS is a total solution to e-commerce, and is open source. It uses MySQL as the backend and modperl. It seems pretty nice, you might want to look into it.
http://yams.screamdesign.com/
Sigs are awesome huh?
I don't think there is anything out there to plug into Access from Linux, but you can certainly export from Access into MySQL.
Either use MyODBC as someone pointed out or you can use a script available here to dump an Access DB into nicely formatted MySQL compatible SQL.
There are a couple of these utilities available on the MySQL web site.
FYI: The choice I have made for my company for end-to-end web based ordering is Linux, Apache, mod_ssl, PHP, MySQL, Cybercash. PHP allows you to link right against the Cybercash MCK and even comes with some nice pre-written interface functions.
Good luck. Moving away from Access (even toward MS-SQL server or otherwise_ will help you A TON. Win NT ODBC drivers are really really poor.
---
Don Rude - AKA - RudeDude
RudeDude
Perl/Linux/PHP hacker
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.
I don't know what technologies you're using other than Access, which is seldom a good solution for a website. But NT works well for us. As with any public server, you'll want to pare it down to run exactly what you need, no more.
I do root for Linux, though, so if you go that way, best of luck!
You could probably get a better database a avoid reinventing the wheel. If you are going to do it all over again on Linux you should look at Java Servlets rather than Perl (Don't get me wrong, I like Perl.)
advantages over perl:
henri
contains DBD::ODBC. should allow you to connect to odbc driver on nt box.
...sie sind nicht grün
The course of action I would probably take would be to export the data from Access into a format that your favorite SQL database can handle.
Access exports into Some spreadsheet formats, text, rich text format, Foxpro, dBase and even outputs to ODBC databases,(though you'd have to see if you can get a driver for access from the manufacturer).
dBase is probably your best bet as far as real database formats are concerned.
ODBC drivers, would be interesting to see if MS Access would accept PostgreSQL drivers, or if it would revolt. Anyway, the Access help file has some info re. installing ODBC drivers.
If nothing else, most DBMS should import a text format table.
Good luck.
Hail the Penguin!
"I'll take the red pill. No! Blue! AAAaaaahhhhhhhhh"
- Monty Python meets the Matrix
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
Also learn SQL you'll soon see that importing data from any ODBC compliant DB is either simple or logical or both. If you don't want to then install MSSQL on your NT boxes and run the Access Upgrade wizard to import the data into new tables.
Hire a NT Administrator. I work on both Linux and NT Servers and I can make a Linux Server as unstable as most NT Servers. Remember most people who call NT unstable do not know fully how to configure and maintain a NT Server. To make matters worse their are also many NT Administrators who don't know how to properly manage a NT Server. It sounds like your NT Administrators fall under the latter catagory.
Export the Access tables to a comment-delimited format and import it into your favourite unix sql server. In MySQL you'd use a statement like:
load data local infile '/home/me/table1.txt' into table table1 fields terminated by ',' optionally enclosed by "'" escaped by '\\' ignore 1 lines (col1, col2, col3, col4);
But check the docs to see if you need anything else. sed and awk are good if you need/want to sanitize the table a bit first.
Then use an ODBC driver on the client machines to allow updates. MyODBC works well with Access 97 but I've heard of some problems with Access 2000 (I think someone at Microsoft is working on a patch for the next service pack?). Make sure all of the tables you want to update have a primary key defined.
Read your SQL server's docs for access control - with MySQL, I added a user name, password and IP address triplet to the mysql.user table with 'N' for all of the global perms, and added a line to the mysql.db table allowing the user select, insert, update and delete privs to the given database from their IP address. I'm sure there are other, better ways of doing it though.
I prefer PHP (http://www.php.net/) for the WWW frontend, but that's just me.
I'd recommend PHP over PERL for the programming language if you do convert, PHP4 looks like it's going to blow both PERL and ASP away in terms of performance, and you'll probably be able to convert most of your current ssytem over to PHP easier than to PERL (there is an ASP->PHP converter available if you are using ASP at the moment.)
For the database server, I'd recommend PostgresSQL over MySQL (transactions are nice), or you could just keep the database on the NT box and use ODBC (although I'd recommend upgrading to a database server like Oracle or MS SQL 7, MS Access is NOT meant for what you are using it for.)
The easiest thing you can do to help reduce the frequency of those crashes is to get the data off of Access. That poor little DBMS was just never designed to put up with the abuse that is constantly heaped upon it. You should be able to move the data to any ODBC compliant DBMS with little effort. I would suggest picking one that was cross platform so that you only had to move the database once. I suggest you take a look at Progress, as well as Oracle, Sybase, Ingress and, of course, MySQL. It's been a few years since I used Progress, but from what I have heard, it's just gotten better. MySQL is free (as in beer) on Unices, but not on Windows, and that's just one of many reasons it would make sense to put the database on Linux. Moving to a more robust DBMS will buy you time to port the application correctly.
Make full use of that time to do the job right. I strongly suggest that you do not attempt to do the port yourself in a language you are in the process of learning. Everybody writes really horrible code during the learning process of any language, and you do not want that code enshrined forever in an application that's critical to your business. Hire someone who knows how to do this type of app, and perform a real analysis and design before doing the coding. You will be doing the second system, so you have a pretty good idea what the requirements are. You will, of course, follow my advice and bring in someone who knows how to write this kind of application; and when you understand the problem, the technology, and the data there is no better model to follow than the top down model: analyse, then design, then code. Of course even when you understand all the elements of your application the creation of the application itself changes the environment it lives in, and so some adjustments to the design, and perhaps even the analysis will be made. But these adjustments will always be more expensive if done after the coding has started then if done before.
You have a working system. Fix it then replace it with a well-engineered second system; and your management will be pleased. Replace it with another hack, and nobody will be happy.
Information is not Knowledge
Run an untested beta for a server os! I know, he could just use windows 3.1. After all, it was made by the same "wonderful" company that wrote 2000.
Ryan Minihan
ryan@linuxhardware.com
If you're not already decided on perl and your site isn't terribly complex (or even if it is), you definitely should look into PHP3 (also check out phplib - very nice). I have found it to be extremely easy to use, especially for connecting to MySQL, but that's me. As I'm sure everyone will tell you, getting the data out of access and into MySQL is trivial and could be done many ways, so I won't say anything on the subject except, DO IT!
Mysql.org and Mysql.com are the USA mirror of Mysql.net the german site. So if .com or .org is down, try .net. I cannot remember their other domain off the top of my head.
Also, as their site says: Our connection to the world has been down between Saturday 19991030 GMT 22.32 to Monday 19991101 GMT 17.10
---
"...silence is a dangerous sound."
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.
...particularly an order entry system. Until it supports rollback/commit transactions, it will not be appropriate for serious web sites. Now, that's not to say it's not a useful tool for non-critical databases (say, a visitor log or something), but someone using MySQL for anything financial is either a) crazy or b) has never used a serious database, and doesn't understand how to design database back-ends.
1) Windows 2000 is a yet UNPROVEN OS. This also means that saying it's an enterprise class solution is nonsense. Windows NT 4, with it's latest server pack 6, still doesn't live up to it's (read MS's) promisses, not saying it doesn't live up to some. 2) saying that just changing the OS would be a solution is BS. You'd first have to _identify_ the problem before you could cure it, and for all we know, it might be something else then the OS. (Actually, to me it sounds like the Access database and/or the propiaty software might be the biggest problem.) 3) Saying that upgrading NT4 to Win2K would solve a problem is saying that NT4 _is_ the problem, which would decrease my trust in any MS product. 4) the debate on whether UNIX, VMS or a MS product is a complex one, and turns more and more into a religious debate instead of one based on facts and technical information. Saying one is by definition inferior to the other is unintelligent and non-constructive, and should be ignored. I wouldn't be surprised if the previous poster was in some way financially dependent on MS products. At the moment, so am I, but that doesn't make me blind. Don't choose a solution because it is from a certain vendor (or movement, in the case of Linux). Choose a solution because it _works best_
You mention only that the catalugue is in Acces... You could use MySQL for it, but I would never trust any financial data to a database server that does not support elementary database operations like transactions, or the ability to have an transaction log on a separate physical device...
I personally like Sybase (11.0.3.3 is free to use in deployment on Linux), but Oracle or the other big names have also proven themselves in years of use..
-- Nothing is as subjective as reality --
We run multiple E-Commerce sites at my company, and after about 5 megs of DB data, and an average amount of site hits, access looks like a squirrel under an 18 wheeler. An easier move would definitely be to move from access to SQL. Even on a Linux box, Access will be unwielding poltergheist (poltergiest?) crashing your servers.
Th
1. Use PHP3 on top of PostgreSQL. 2. Create an HTML UI for sales people to update the prices in the pg database. 3. No dumping or syncing required. There really is no technological reason for a business not to run on Open Source software these days.
And I suppose MS Access is a Serious Database... In that case why not just use perl or php with dbf files... (by the way, guy who had the original question, postgres, for one, has a dbf to pg convertor... although postgres lacks autoinc which suck, but there are add-in functions that can accomplish it)
--
Marques Johansson
displague@linuxfan.com
Marques Johansson
I agree with what Bill's suggested. By essentially running in parallel, you're being cautious about not breaking the flow of the business (a good trait) and you can get comfortable with Perl, et. al. and confirm that data imports are happening correctly.
Another selling point to your boss is that not only are you replicating and improving on existing functionality under NT, but once on a Linux/Apache platform, a lot of zero-cost *additional* functions can be added as needed. You'll also be much better poised to start taking advantage of XML parsers, etc. when that need comes along.
er... since when do you need transaction rollback mechanisms for read only databases, which 99% of all of the web database access I have done is? Most of the time you are not doing the standars a->b->c->a mechanism (the bank example) in a web enviroment...
every time I hear someone say that you need transaction rollback for a web enviroment i cringe...
Moderate this, please.
MySQL sucks. Have you ever read the LICENSE it comes under?? Try Zope. And take a look at some Open Source sites that use it daily, like AppWatch and Technocrat.
I have built a few E-Commerce sites using Linux with the following
Minivend shopping cart and SSL Webserver I use Mysql with DBI you can use an ODBC connector from windoze to the Mysql Server. You will find a converter filter for acces at www.mysql.com that will move your mdb file into the SQL server. Using it this way still allows Windoze apps to access the data and run reports as if it was still an mdb file.
I'm in the process of building two separate database driven e-commerce sites. My OS of choice is FreeBSD, but that's irrelavent... I've converted several Access databases to MySql using the MyODBC drivers available from www.mysql.org. O'Reilly has also just published a book on Mysql. It's a must read if you're new to Mysql or if you need a brush up on database design. I'm using php3 for one project and just installed PHP4 (beta 2) which has session management features yesterday. I've played with it a bit and it seems to work very well. The mysql database access functions are easy to use and the embedded scripting is wonderful. Documentation online for both products are extremely good. You shouldn't have any trouble getting started if you've got some previous programming experience.
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
----
We're doing all of our data collection using PosgreSQL on Linux and transferring it to MS ACCESS for the client every now and again. All of the forms processing & DB interface is done in PERL.
When it's time to transfer data, we copy the tables using the 'copy to' command in psql. Then we 'dosify' the text files containing the tables using a utility like unix2dos (adds a linefeed). Transfer the files to a windows machine. Import into the access database. You're done.
I'm assuming that something similar can be done the other way: export from access to a text file; ftp to linux machine; import into PostgreSQL via a 'copy from' command.
It's kind of a hassle, but if it doesn't have to be done all that often, it's doable.
A trappable error occurred in an external object?
This isually means the "World Wide Web Publishing Service" has fallen over (like IIS does only too well).
I've converted over to Perl under NT and it's a dream compared to ASP's or compiled CGI programs!
Though, if I had my way, we'd be using Oracle or MySQL under Linux instead of that lame excuse for an SQL Server from Microsoft - it's so dumb that it won't let you create a relationship between to tables within different "databases" on the same server! Thanks Microsoft!
There has never been a stable version of Windows, has there? Running a business on NT is not wise, even if some people manage to make it work. I write NT software for businesses, and my coworkers and I laugh all the time about how ludicrous our jobs are. Ignore the marketing buzz.
I have deployed several SQL 7.0 database projects since it was released running from small desktop to data warehouses. I also used v 6.5 previously and I definately vote for 7.0 in terms of scalability, performance, flexiblity and stability.
Your solution (cluster machines) is sensible.
I think it _is_ NT's fault, tho, else why reboot? Just kill the process, and a decent OS will reclaim the memory. If your OS doesn't, then it's only doing half of its job!
I love the php3/mySql combination. PHP makes the task of turning your data into web pages almost trivial, and both MySQL and PHP have excellent documentation. As for getting the data out of access, I'd save the data as delimited ascii, and then import it using perl with the DBD/DBI modules. That's also useful later on for various types of database maintenance.
I hate to see this guy go to all the trouble of making server os changes (to linux) when it isn't really necessary. He doesn't need linux or perl scripts or MySql. All of this just adds unneccessary complexity to the situation. And to solve this problem first we should look at his situation carefully: 1) He's got an NT server 2) He's got an Access MDB file about 14 MEGS His problem: 1) reliability problems Analysis of problem: 1) This is probably pushing Access too much. He needs a real database server. Access is fine for small databases with no more than one user accessing at a time. This is well known. 2) He needs a reliable inexpensive database server. 3) The OS he is using (NT) may not be his (or my) OS of choice, but the facts are it is more than reliable for what he is trying to do and it is already there. 4) MS SQL SERVER 6.5 or 7.0 (I would prefer 7.0) is a decent database and would probably work extremely well in this situation. ACCESS also supports upsizing to sql server directly in the Access App. Solution to Problem: A) create a ODBC SQL SERVER DSN B) upsize all the tables in the access database to Sql Server using the DSN created from 1). Access will replace the old tables with copies linked to the sql server. C) then compact and repair the database and enjoy! Isn't this easier than creating a bunch of interface scripts using DBI with PostGreSQL??? The answer is: Yes, it's as simple as A-B-C. So don't go overengineering your problems when it is not necessary as it is not in this case!
Visit the Perl Search Engine at www.linuxextreme.com
I hate to see this guy go to all the trouble of making server os changes (to linux) when it isn't really necessary. He doesn't need linux or perl scripts or MySql. All of this just adds unneccessary complexity to the situation. And to solve this problem first we should look at his situation carefully: 1) He's got an NT server 2) He's got an Access MDB file about 14 MEGS His problem: 1) reliability problems Analysis of problem: 1) This is probably pushing Access too much. He needs a real database server. Access is fine for small databases with no more than one user accessing at a time. This is well known. 2) He needs a reliable inexpensive database server. 3) The OS he is using (NT) may not be his (or my) OS of choice, but the facts are it is more than reliable for what he is trying to do and it is already there. 4) MS SQL SERVER 6.5 or 7.0 (I would prefer 7.0) is a decent database and would probably work extremely well in this situation. ACCESS also supports upsizing to sql server directly in the Access App. Solution to Problem: A) create a ODBC SQL SERVER DSN B) upsize all the tables in the access database to Sql Server using the DSN created from 1). Access will replace the old tables with copies linked to the sql server. C) then compact and repair the database and enjoy! Isn't this easier than creating a bunch of interface scripts using DBI with PostGreSQL??? The answer is: Yes, it's as simple as A-B-C. So don't go overengineering your problems when it is not necessary as it is not in this case! ps, I reposted this because my previous message was unreadable.
Visit the Perl Search Engine at www.linuxextreme.com
How fast/robust is an ODBC connection going to be
to pump over 14 MB from Access?
PHP is awesome. I've just started getting into it. It has tons of functionality, it can get stuff out of almost any database, including access under the right conditions. It's very easy language and PHP4 which is in beta now supports Secure transactions. www.php.net is a good starting place along with www.phpbuilder.com
PROBLEM 1: require stable OS's ... but rip off anything you dont need on both machines, re-install to get a clean system. This should stop the problem of NT operating system crashing, but not rouge software. Generally if the machine is configured (HW & software) they crash as often as reported.
Having 2 NT machines may point to badly configured (I can hear the sniggers now )
Problem 2:
database problems Our catalog has around 25,000 items in it. It's held in an Access database right now... around 14 Megs.
been there, done that. At my old work we had similiar problems. I think it's a bit premature to write NT off for this kind of a problem just yet. It's not the environment (caveat: well configured NT system with min 128MbRAM, Fast disk drives etc..) it's your database engine.
Access is good for playing with and possibly for serious applications a couple of years ago. But for a cat of say 25,000 records and more than a half dozen users Access will bog down under it's own limitations (file sharing database, not relational, mem leaks, your configuration settings, code access methods). I think before you start changing the operating system, code base and more importantly *mindset*, investigate a MS SQL server (or other politically correct db's) license.
If management is balking at the cost... then cost out the hours to rebuild, test and get the system working using a linux/db/scripting approach. I say this because it's as much a business problem as a technical one. Change takes time, time equals cost - the same money to buy a new 'relational' database on your existing code base. With NT SQL Server you have the ability to upsize the DDL, re-insert the data etc...
<rant>
beware of suggestions to change your current setup by those preaching alternative OS's without fully investigating your current problems
changing to Linux for instance is not the immediate problem.
I'm a bit dubious here, and dont get me wrong I use/worship Linux as much as the next nutter...but dont do yourself and your company a diservice until you are 100% sure NT can't handle your business needs and not some *geek-lust* need to install a new OS. </rant>
peterrenshaw ~ Another Scrappy Startup
There have been lots of messages about how bad Access is, but can anyone quantitatively explain why it is so bad? I was thinking of using Access as a database engine with a middle layer of MTS components. Can some please explain in detail why this is a bad idea?
No flames please.
This is unrelated to the question, but a not-so-widely known fact is that one of the major early e-commerce companies providing third party credit card processing for online merchants was running their entire financial processing system (everything but the webserver) on Linux back in early 1995, before you could get secure servers for Linux. Best yet, it was done for technical reasons that couldn't be solved on any Microsoft or other Unix products. (posting anonymously without details for legal reasons, sorry)
I'm PROVING it every day on my machine for three weeks now! Win 2k pro is going to smoke NT4 and 98
A single person sitting at a single system cannot, of course, "prove" the power or stability of any operating system. This scenario does not even rule out the possibility that some other person sitting at the same system might crash every ten minutes.
I say "of course' because this is absolutely obvious to me. The fact that it's not obvious to our AC might indicate that he has only ever been exposed to a single operating system, and does not need much in the way of proof.
Information is not Knowledge
As you noted, you are concerned - quite rightly - , about the conversion of your database. There are a couple of things to note here, and other posters have already made one of these points.
./ about a Linux e-commerce package not so long ago. Perhaps you might want to check the archives.
1. There are both middleware tools to convert Access databases, and direct imports in the databases you want.
2. It is possible to create a new database from the old one *****if***** you wish to write a program to do the conversion. Your new preferred databases are widely used in the open source community ( MySQL is nice, and solid when properly configured ) and one can convert from Access almost trivially, as the SDK is available for free from M$, showing the exact underlying format of the database.
As others have also noted, Access is rather sloppy in it's data rules, so be careful here.
Another solution is to use an open e-commerce solution and port your data. There was an article on our very own
Just my two cents.
***Unix is user friendly. It's just very particular about who it's friends are***
Problem 1: require stable OS's ... but rip off anything you dont need on both machines, re-install to get a clean system. This should stop the problem of NT operating system crashing, but not rouge software. Generally if the machine is configured (HW & software) they crash as often as reported.
:)
Having 2 NT machines may point to badly configured (I can hear the sniggers now )
Problem 2: database problems
Our catalog has around 25,000 items in it. It's held in an Access database right now... around 14 Megs.
been there, done that. At my old work we had similiar problems. I think it's a bit premature to write NT off for this kind of a problem just yet. It's not the environment (caveat: well configured NT system with min 128MbRAM, Fast disk drives etc..) it's your database engine.
Access is good for playing with and possibly for serious applications a couple of years ago. But for a cat of say 25,000 records and more than a half dozen users Access will bog down under it's own limitations (file sharing database, not relational, mem leaks, your configuration settings, code access methods). I think before you start changing the operating system, code base and more importantly *mindset*, investigate a MS SQL server (or other politically correct db's) license.
If management is balking at the cost... then cost out the hours to rebuild, test and get the system working using a linux/db/scripting approach. I say this because it's as much a business problem as a technical one. Change takes time, time equals cost - the same money to buy a new 'relational' database on your existing code base. With NT SQL Server you have the ability to upsize the DDL, re-insert the data etc...
<rant> - sig to noise
beware of suggestions to change your current setup by those preaching alternative OS's without fully investigating your current problems
changing to Linux for instance is not the immediate problem.
I'm a bit dubious about posts just throwing around languages, OS's and db's without working the problem first.. Don't get me wrong, I use/worship Linux as much as the next nutter...but dont do yourself and your company a disservice until you are 100% sure NT can't handle your business needs and not some *geek-lust* need to install a new OS, and code perl
</rant>
peterrenshaw ~ Another Scrappy Startup
sorry...please read the above url... this got submitted by mistake.
This was released a few weeks ago or so, the thing about it that makes it so cool is the fact you can create specific task based roles. Roles are great because of the limitations they let you past with groups.
I would hope that more people gave this excellent oppurtunity to test it out a chance in their environments.
(Been doing Sybase administration for almost 4 years now, I have never worked with a more extensible, reliable and powerfull system...)
-Dextius Alphaeus
-- Java is not a Jedi trait... "do, or do not, there is no try" --
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.
I've had excellent luck with this free product.
It takes some effort to pick up, but it's very flexible, and you get a lot of cool stuff without much effort. Like automatic shipping pricing based on zone, credit card encryption, database compatibility, awareness of accessories and properties (like color), quantity pricing, etc.
My catalog only has about 300 items, but it is reputed to work well up into the millions.
The basic concept is that you set it up to generate on the fly pages based on what's in the database. You can also link to it from static product pages if you want (which we do).
The programming is done by writing perl code and sticking it in web pages to be run as the page loads. You can also call static functions that you put in configuration files, where they only have to be parsed once.
The only downside is that while there is about an inch thick stack of documentation, it is very poorly written and hard to understand. Fortunately there is an active mailing list for support.
Good Luck,
-Loopy
I would suggest that you check out using an application server mod for any major web server. The application server will allow you to use JSP and Servlets with Java. This is a good alternitive to CGI. Using Java (being a popular term) you could have an easier time getting the funding for the upgrade to a new system.
Java Apache Page
This site will point you into the right direction getting everything that you need. Should you need to get a developer I would suggest contacting Web Programmers, Inc. We have used them for our development and they've done a great job on our up coming project for Ruptime.com
Your comment about moving Access to MySQL to get to a "REAL" SQL made me laugh.
MySQL does the barest of bones SQL. There is no replication (pending however) and no transactions (status?). If this guy's got a 25k-item catalogue and is expecting any kind of web traffic and wants data inegrity, he NEEDS transactions. Table locks suck.
I suggest Postgres (maybe, it's what I use but not in a heavy production environment) or something meatier like Informix or Oracle. You do NOT want to be doing anything regarding money or inventory without transactions and row-level locks.
Wait one bloody minute... You've got a e-commerce solution running over a serial cable against a Access database and you're wondering why it's unstable? It has nothing to do with Linux vs. NT it has everything to do with your application architects being idiots.
What kind of moron uses ACCESS for a web server application???????
The solution is for your company to fire YOU and hire someone who knows what the hell they are doing.
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.
By your standards, lots and lots and lots of morons. There are LOADS of such implementations out there, and for a while every computer magazine I picked up had an article on how to get your Access DB onto a web. It's not difficult, and it actually works well enough if throughput is low and the app is not mission critical.
... just go away, OK?
No one's going to take you seriously. Anyone who has abuse but no better solutions to offer can and will safely be ignored. Anyway, the choice of Access may not have been his, he may just have had to pick it up. Don't go away mad
As plentry of people have said, exporting data from an Access database is trivial. If you don't want to get a copy of Access, use ActivePerl (www.activestate.com) with DBI and DBD:ODBC and roll your own. You could perhaps use another DBD for the database you plan to use and do the entire data migration in a single script.
Eagles may soar, but weasels don't get sucked into jet engines.
I was facing the exact same problem just last month. In my experience, if you want to develop scalable server-side software, and you're using OO technology, you need a layer of database objects to encapsulate all your data access logic. You then build service-oriented objects on top of those. This is very similar to the EJB model (minus containers/transactions/etc), but you can't afford EJB because a good server is >$10k.
But this object layer can prove to be a high maintenance piece of software. Every time a data structure changes, both the object layer and the database scripts have to incorporate the change.
My software is written to read a single specification file, and from that file it generates:
So, yes, the specification file you feed the script can be quite complex, but it's nice to maintain your database AND your object layer from one place.
Well, my software is really just a hack I put together, I wouldn't be surprised at all if many other people have done the same thing already. I dunno.
Anyway, if anyone's interested in using my software, it's still not 100% foolproof, so I'd like to give it some nice stress testing in an open source environment.
Any takers?
- jonathan.
"Every tool is a weapon, if you hold it right." - ani difranco
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
If you're NT is constantly die-ing then you probably have a misconfigured item somewhere in your system.
Good configured NT systems are as stable as Unix machines.
Of course, it is MS's own mistake to have so much 'bad' sysadmin's out there. Unix has thousands of capable sysadmin's, they learned the trade in ten years or more of experience. NT admins however often just follow a course, take an NT certified whatever exam and hup, they are promoted to full blown NT sysadmins.
If you compare NT to unix, don't forget to compare their sysadmins as well.
But .. NT is harder to administrate then Unix, so it is quite natural to have less good NT admins then Unix admins
Been there, done that: 1.- Install mysql 2.- Install myodbc in the clients 3.- from access export using the ODBC db 4.- make sure there is a primary key in every table 5.- add a timestamp field to every table: alter table table_name add modified timestamp;
>Please don't listen to the "go to MS-SQL Server" people. >A vendor said that to a company I worked for and it had more stability (but what doesn't have more stability than Access?), but it came with its own set of stupid problems. Well, building an e-commerce without a DBA is like running a company without a CEO : you don't really need them if everything is going smoothly and if you're luky .... In my opinion you should move to more serious tools (mySQL or Postgres + Linux + JSP, Sun solaris + Oracle depending on your willing to keep your system open) and buy a skilled DBA to run the system. If this is not an option, your least cost move would definitely be buying MS-SQL6.5 and staying on NT. But keep in mind that this OS is a desktop toy : it hides you the complexity of the problem as long as it can. And suddenly you find yourself driving into the brick wall .... saslawsky@hotmail.com
If you are going to do anything involving both Visual FoxPro and the internet, you should really check out Web Connection. It's a framework that allows you to create HTML pages on the fly directly from VFP code. It's really fast and the toolkit is highly scalable and very well supported.
Visual FoxPro doesn't get much press from Microsoft because they want to push SQL and seat licenses. But people who use it know that VFP is very fast and reliable. If you are going to mess with databases, you should be coding with a database language.
WebConnection runs Egghead's SurplusDirect site (among others), which gets up to 55,000 hits an hour during peak bidding times. VFP can handle it, as long as you're not writing crappy and inefficient code.
There is support for XML, MSMQ, COM, sending data using HTTP (with a VFP app on the client side), email generation, PDF doc creation, etc. You can also access other databases from within FoxPro using ODBC.
At work, I developed an e-commerce project that does all the online work for several clients that we support, all running from one base class, all coding done in FoxPro. One of the best things is that you can debug it just like a regular VFP app.
I'm not associated with this company but I have used the products and can tell you it's very powerful. The price is way cheap and a shareware version is available for downloading. Check it out. Note: This is for Windows only, but you are not tied to IIS.
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!
Check out AKtivate, it uses MySQL as the back end and all that Perl has been done for you.
Aktivate is at
http://www.allen-keul.com/aktivate/
MySQL comes with an admin tool so that you can load up tab delimied or comma delimited data from your existing Access db.
HTH
The cost of Linux is the cost of learning--a price always worth paying. hehe
Hi, I didn't had the chance to read all comments so I hope I don't react with the same things people said before. If so, I'm sorry. I'm setting up a e-shop also at the moment and I can advise you two things: Use a sql server you know. I read something about people telling you mySql ain't that good. I prefer Oracle but have noticed that if you know how to handle mySql that it is as good as Oracle or sybase or PostgresSql. Use your knowledge, don't use something you think is better if you don't know how to handle it. And ofcoz, try not to use access. For the webpages I use ColdFusion (Allaire, www.coldfusion.com) running on a solaris machine with Apache. ColdFusion is very good for 'programming' e-commerce pages. If you want more info: check allaire site, they have a pretty large amount of information about e-commerce online. I think the coldfusion 4.5 runs on linux also. Pepijn Pepijn@kangaroot.net www.kangaroot.net will go 'live' soon now.
Visit http://www.kangaroot.net/ for all linux related stuff like T-shirts, gadg
Serial cable? Access as your database?
Talk about an idiot architecture for an e-commerce site.
And now we have loads of idiot amateur Slashdot lemmings proclaiming he should "rip and replace" his whole system and replace it with Linux and MySQL.
If this e-commerce site is "taking orders" or doing transactions, MySQL is yet another idiot choice.
Fine to use for your read-only DBs or toy systems, but if an employee of mine ever suggesting running something like Amazon or Dell.com commerce on MySQL, I'd instantly fire him.
Slashdot users spend way too much time fixating on price, and not enough time on minimizing risk, keeping schedule, and using the right tool for the right job.
An architect's job is to design a system to meet the requirements using principles that will work and taking into consideration the landscape..
Platform RELIGION is not part of job, anymore than 747 engineers should use parts from christian suppliers only.
grow up kids!
I too have an MS Access database that I would like to convert to Linux. However, the problem is not converting the data, but converting the MS Application part of it. How do I do a front end for my datatbase in Linux? I basically need forms and reports? Any suggestions welcomed!
Chris
Okay, Access is the spawn of satan; NT is buggy; SQL Server is a poor relation of Oracle - yeah yeah, or no no - this is a great argument but I thought that one of the main reasons for the problem could be the link using the serial port. This must be slow, and probably uses some home-spun protocol, which may contain some hidden bugs.
Worth investigating replacing that with a simple network link instead ?
It's all very well in practice, but it will never work in theory.
You have another option - move to Oracle which will run on NT or Linux. They have a special utillity that will convert Access systems. It works automatically if you haven't done much outside the wizards.
Yeah I know I've read the Halloween documents. ;)
New things are always on the horizon
Oracle runs on Linux.
t s/files/msaccess/intro.htm
The MS Access Migration Wizard is available for download (requires free registration) at
http://technet.oracle.com/tech/migration/toolki
We hope to be in a position to provide more migration tools in the future.
Turloch
Migration Technology Group,
Oracle Migration Workbench Team.
Be Free: Free Software Tuition
But the ASP "trappable error" usually comes from a programming error in an COM object written by someone other then MS. IIS is a lot more susceptable to programmer errors because most of the COM objects people use run in the inetinfo.exe process space (not somewhere you want to reference uninitialized pointers. IMNSHO, ASP is a the best Web solution out there for enterprise development right now.
Yes, I'm trying to recall why we *didn't* go with Raven SSL. I think there was some issue in our particular environment (migrating from Netscape Enterprise to some sort of Apache SSL). But you're correct, Raven is a good option and so is mod_ssl if you aren't constrained by US regs.
As far as Postgres vs. MySQL, the transaction support is the big difference in my mind. The MySQL folks steadfastly argue that you don't really need transactions (read the docs), but I'm not convinced. Speed-wise, however, MySQL is a big win. Also, Postgres has shown some strange behaviors for us in situations where we have complex joins (star-joins, actually) and has actually corrupted data before (we never did figure out why...)
It's a strange world -- let's keep it that way
A very good point -- web databases != internal databases. Quite often, we construct database systems for use on the web by extracting and reformatting internal data. We do this because the set of business rules used and the data access needs we have are completely at odds with the way the internal users view the data.
Often, the "web database" can be much more simple -- a list of products, some pricing, some tables to store users and orders. The "real" database has to deal with inventory control, order fulfillment, accounting, etc. We usually establish a data feed between the two, updated on whatever cycle is most appropriate.
To take off my open-source hat for a minute, I also have to say that if you're doing serious database work with large tables and you need top performance and scalability, don't look at any current open-source solution. Go with something like Oracle (now on Linux!), they've been doing this for a long time and really do have tremendous advantages over the folks doing it in their spare time for free... especially at the higher ends where the typical "free" system isn't designed to scale (the free systems tend to optimize for small, fast, simple architectures).
It's a strange world -- let's keep it that way
Reading your post, you say that the existing system "takes an order, and as soon as they hit commit, sends it to our main system via a serial interface" (may be paraphrases to some degree). Having said this, why is Access even in the picture? Apparently you aren't describing the whole system. Could you elaborate on this a bit more?
I, like others, doubt seriously that NT is the problem, nor do I suspect Access (in an of itself) is the culprit. However, having said that, I wouldn't myself have chosen Access as a component of the solution (but, again stating the above question again, I don't see where ANY local database is required, based on your requirements as currently know). Is ASP fundamentally buggy? Probably not, but again, I wouldn't chose it (primarily due to knowledge base more than anything else).
If you want to make a jump to Linux, then make a solid business case. Demonstrate the ROI, given a new technology vs "fixing" (which may mean completely re-writing in the current technology) what you have. In doing so, you'll help to better understand your problem yourself, and certainly appear much more professional than many who simply rant and rave about how great one toolset is over another.
my $.02
Alan
You're trying way too much new stuff waaaay too fast. Going to learn sql, apache, perl and what else? You need to find a way to make some trial runs to prove the technology (and your ability to make it work in this application) before you go replacing an even marginally functional system.
Wow. You clearly don't have a clue. A. That database should be stored in Oracle. No other. B. If you can't keep NT boxes running, then you are simply imcompetent. My company specializes in NT E-commerce solutions. What's your site/company name? You clearly should be repalced by somebody who has a clue.
Yes, Oracle is the giant of the DB world. But that's not always a good thing (can you say Microsoft? I knew you could! :). Oracle licenses get ridiculous when you start getting into larger machines. We've got it running on some Sun Entriprise 450 & 4500 machines, and they multiply the cost BY THE CPU and BY THE CLOCK. So a multiprocessor license gets REALLY expensive. I'm talking 7 figures.
Oracle is quite fast. Oracle has full transaction support and row-level locking. And Oracle supports Linux (yay!). However, compared to, say, MySQL, Oracle has a primitive installation environment (I prefer configure && make install) and less than clear documentation. I've installed 8 then 8i, and both times had trouble with the libclntsh (it's their programming API called OCI). It was missing a bunch of symbol info that I had to track down with nm through all of their libraries and rebuild clntsh myself.
Anyway, the cost of licenses & support is really hurting. Not to mention that development is somewhat more lively in the Open Source world.
What would be REALLY nice would be something like Postgres that was designed to be run in a Beowulf cluster. Now THAT would kick ass all overy every DB out there.
Now I'm no script/perl/php expert but I'm somewhat sure that php can read from an access database. If not, setup a small odbc server that php can read. This way you don't have to convert the access files, and get the reliability of linux. You might want to look into it.
:)
ClarkBar
Slashdot Moderators most surely suck
Dude, he's talking about an online ordering system. That ain't read only. It's transactions involving money. You sure as hell do need rollback for that.
You're sending orders over a serial connection?? to an access database? your problem isn't NT, it's your idiotic design...
Postgres is slower because it is more robust. The transaction locking and the way Postgres commits transactions ensures data integrity in the event of a hardware catastrophe in the middle of a transaction. I don't think MySQL can claim anything like this. Because of this, PostgreSQL is more qualified to serve as a mission-crit DB server than MySQL ever will.
The PostgreSQL developers point out you can disable some of these features to speed things up quite a bit, but you take the chance of losing data if the system crashes.
I never said MySQL wasn't fast... that's one of its biggest plusses IMO...
If what you're really doing are transactions, this is fine. Build up sets of updates that are themselves transactions.
I'm not sure I follow... How do I have the system subtract one from inventory, add it in to the ship queue and add on the sale and taxes for accounting in one statement?
I could see "+1 this product" to an online database and then every hour or so syncing up to the master database, but now you've got two seperate sets of SQL (don't try to tell me that Oracle or Postgres commands will work on MySQL) to keep track of... why not use the same "dialect" on both systems?
You're right -- I don't agree on having the public hit your master database, but I would have made my online database a little meatier than most, I guess.. at least enough to give an order # and start shipping procedure I think. AFAIK, that kind of setup would require transactions.
apache+java serlets extensions..do the whole thing in mysql + JDK 1.1.8 with java server side extensions.
Hmm, Perl or PHP would be fine for the forms side of things, but wouldn't be much use for generating reports without a significant effort. Are there any report-generating packages for Linux?
I had an MS-ACCESS database and I needed to have it under mySQL. My solution was to use JDBC ; a small program did the job quite well.
www.mysql.net --> Wellers' JDBC type 4 driver (http://www.mysql.net/Contrib/)
www.easysoft.com --> JDBC-ODBC type 3 driver (http://www.easysoft.com/products/job/main.phtml)
hth
Good luck
Jean-Pierre
norguej8@cti.ecp.fr