Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

osCommerce and SQL Server?


Digit

Recommended Posts

Hi there,

 

Is it possible for osC to work with Microsoft's SQL Server instead of MySQL? If not, is there another cart system open source preferably, that might work as well as osC for SQL Server?

 

Much appreciated,

 

Digit.

Link to comment
Share on other sites

Not without changing the database access and query code.

I am wondering why you need to use SQL Server? MySQL is free, far more lighweight, and a lot better suited to PHP. The OSC database schema is very basic and doesn't really need the resources of a full RDBMS to handle.

Link to comment
Share on other sites

Thanks FalseDawn.

 

We currently have a Great Plains system in place which is just too big for our needs. However we need something that will be linked into our current stock system and database. So really we have to use somthing that will work with SQL Server. Any ideas?

 

Digit.

 

Not without changing the database access and query code.

I am wondering why you need to use SQL Server?  MySQL is free, far more lighweight, and a lot better suited to PHP. The OSC database schema is very basic and doesn't really need the resources of a full RDBMS to handle.

Link to comment
Share on other sites

Well, a couple of things.

 

First, If you want to use OSCommerce, I would recommend writing a custom ETL process that would keep your osc database in 'sync' with your sql server dtabase by incrementally extracting the relevent information, and inserting else updating the osc database To extract the data from MS SQL Server, I'd use either

 

A.) a scheduled DTS package, which comes with MS SQL Server, in conjunction with the MyODBC driver to extract, transform and load/update the data directly into the MySQL database.

B.) a vbscript on the server, utilizing an ADODBC connection and recordsets to extract the data, in conjunction with the MyODBC driver to extract, transform and load the data directly into the MySQL database.

c.) a BAT file the uses BCP into a delimited file, and sends an indicator to the MySQL server. Then on the MySQL side, you'd have a shell or php script to iterate through each recod and insert/update where appropriate.

 

Which any of these options, you could update as frequently as once per hour. It wouldn't be real time, but It would probalby be close enough.

 

This solution could also be written in the opposite direction so that your MySQL database could be used to update your MS SQL Server database.

 

Also, I wanted to comment on ...

 

I am wondering why you need to use SQL Server? MySQL is free, far more lighweight, and a lot better suited to PHP. The OSC database schema is very basic and doesn't really need the resources of a full RDBMS to handle.

 

Let's not get carried away here. MySQL is terrific for a small to intermediate sized store. And that would cover more than 95% of all businesses, that's for sure. However MySQL is severly limited when you start talking about scalability. Off of the top of my head, MySQL doesnt' offer bitwize indexing, instances, rollback support, or partitioning. There's also a pretty severe limitation with concurrent users, storing indexs on seperate devices/tables spaces etc. And the SQL it supports is rudimentary as well. It just barely supports very basic types of coorolated subqueries and multi-table updates. And it's horribly inefficient with just about any type of aggregation or large amount of data, so analytical reporting on large amounts of data is practically out of the question, although if you are doing analytical reporting on a large OLTP, then you don't know what you're doing in the first place.

 

Don't get me wrong, MySQL works pretty well with a small to intermediate basic OSC store. But if you start to approach a million rown in any given table, you are going to start to see a performance degradation, even on a dedicated server. And I've worked for dozens of compnaies that have more data than that in their OLTP for their web application.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Thanks a lot wizard, sounds like a bit of work involved. Do you know of any good ASP/SQL systems out of the box? Opensource or otherwise.

 

thanks again,

 

Andrew.

 

Well, a couple of things.

 

First, If you want to use OSCommerce, I would recommend writing a custom ETL process that would keep your osc database in 'sync' with your sql server dtabase by incrementally extracting the relevent information, and inserting else updating the osc database To extract the data from MS SQL Server, I'd use either

 

A.) a scheduled DTS package, which comes with MS SQL Server, in conjunction with the MyODBC driver to extract, transform and load/update the data directly into the MySQL database.

B.) a vbscript on the server, utilizing an ADODBC connection and recordsets to extract the data, in conjunction with the MyODBC driver to extract, transform and load the data directly into the MySQL database.

c.) a BAT file the uses BCP into a delimited file, and sends an indicator to the MySQL server. Then on the MySQL side, you'd have a shell or php script to iterate through each recod and insert/update where appropriate.

 

Which any of these options, you could update as frequently as once per hour. It wouldn't be real time, but It would probalby be close enough.

 

This solution could also be written in the opposite direction so that your MySQL database could be used to update your MS SQL Server database.

 

Link to comment
Share on other sites

Thanks a lot wizard, sounds like a bit of work involved. Do you know of any good ASP/SQL systems out of the box? Opensource or otherwise.

 

thanks again,

 

Andrew.

 

Yes, it is a bit of work, but its not really terribly complicated, if you're familiar with such things. It's certainly not a 'plug 'n play' type of thing, although I've seem some pretty amazing things done with DTS.

 

No, I'm not a fan of ASP, and I can't, in good concious, recommend a cart that uses it. sorry.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Let's not get carried away here. MySQL is terrific for a small to intermediate sized store. And that would cover more than 95% of all businesses, that's for sure. However MySQL is severly limited when you start talking about scalability. Off of the top of my head, MySQL doesnt' offer bitwize indexing, instances, rollback support, or partitioning.

 

Whoa, you obviously know your stuff with databases, but we're talking about an OSC store here - it will never need NASA-type power to function.

 

MySQL has no problems with scalabilty and can handle enterprise-level systems if properly configured. Badly scalable apps are invariably the fault of the developer not understaning how databases work at a lower level.

http://www.mysql.com/products/cluster/

 

However, you are right in that probably the best way to solve this is with scheduled DTS packages. A decent DB developer should be able to knock something up in less than a week.

Personally, I would go the VB route, but only because DTS can sometimes be buggy and you have a lot more flexibility in code.

 

Anyway, even if an ASP solution was available, unless your existing data schema matched that of the cart (unlikely), you'd still need some sort of transformation process to keep the databases in sync.

Link to comment
Share on other sites

but we're talking about an OSC store here - it will never need NASA-type power to function.

 

Not NASA power, no, but it depends on how much data your osc store holds. For 95% of all web stores out there, you're definatly correct. But for that top 5%, maybe 10% or web stores out there, mySQL just isn't going to be sufficent. Like I said, once you get to around a million rows in any table, MySQL starts to break down, and become more of a hinderance than a help. And I can easily see some of the busiest stores around having a million members, or addresses, or lines in their order tables.

 

Of course, if they are that busy, they can afford to hire their own programming team, and develop their own solutions, and the point would be moot.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...