Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

MS SQL and osCommerce


WisTex

Recommended Posts

I know I may be sounding sacrilegious to most Open Source advocates, but I have a situation where I need to use MS SQL instead of MySQL.

 

I know it can be done, as someone has already done it back in 2002.

 

http://www.oscommerce.com/forums/index.php?showtopic=23821

 

If you are wondering, what we want to do is use osCommerce on an existing website that already uses MS SQL. We want a universal login for the entire website, and the membership table already exists in MS SQL. We have already modified other scripts (mostly written in ASP) to use the universal members table instead of their own native members table. We have forums, links directory, recipe listings, events listings, etc. all tied to one members table for the entire website. The web server runs ASP & PHP (as well as other languages) and have a separate machine running the MS SQL Server 2000.

 

I would prefer to use Open Source code, but I unfortunately need to be able to use MS SQL for this particular project.

 

I have looked at the database structure for osCommerce, and it is obvious that the entire database has to be together (i.e. you can't seperate the members table from the rest due to a variety of database relationships).

 

So, if anyone has an MS SQL version floating around, or is interested in working on one, let me know.

Link to comment
Share on other sites

if you search the forums you will find out that it has not been done. i know there have been a few who have tried, and at best they may be another 6 months if even that close, more likely 1 year away.

 

now for the other . . .

 

if you are posting about the same subject, post in your same originating thread, per forum rules

Link to comment
Share on other sites

if you search the forums you will find out that it has not been done.  i know there have been a few who have tried, and at best they may be another 6 months if even that close, more likely 1 year away.

 

now for the other .  . .

 

if you are posting about the same subject, post in your same originating thread, per forum rules

Sorry, the other post wasn't about this topic, it was actually about the search not working properly with searches less than 3 letters. We got sidetracked into the question of MS SQL, which wasn't approriate for that thread, so I posted the question in the appropriate forum instead to comply with forum rules about being on-topic. I guess I broke a rule trying to follow a rule.

 

Also, it HAS been done:

 

I've completed the migration of osCommerce database from mySQL to Microsoft SQL Server 2000.  I made it paraemeter driven so you can select which DB you want to use.  I converted the 9/15 snapshot after installing Gift Voucher, Coupons, Customer Discounts and a few other contributions.

 

Visit forum post http://www.oscommerce.com/forums/index.php?showtopic=23821 to verify.

Link to comment
Share on other sites

Sorry, the other post wasn't about this topic, it was actually about the search not working properly with searches less than 3 letters.  We got sidetracked into the question of MS SQL, which wasn't approriate for that thread, so I posted the question in the appropriate forum instead to comply with forum rules about being on-topic.  I guess I broke a rule trying to follow a rule.

 

Also, it HAS been done:

Visit forum post http://www.oscommerce.com/forums/index.php?showtopic=23821 to verify.

 

Well, I can tell you this. It would be VERY difficult to do on the latest version. Currently, there is not DB abstration layer, and at last count, there were more than 1000 queries harcoded into OSC that are not ansi standard T-SQL, which is what MS SQL requires.

 

One alternative you migth consider, is creating a 'syncing' script, that runs on a regular basis (using WinAT, or the window task scheduler), extracts data from the MS SQL server, and loads it into the MYSQL database using an insert else update strategy. You could do this on an incremental basis based on last updated timestamps to minimize processing.

 

This sort of ETL programming is actually what I do for a living, so if you decide to go this route, and need some help, feel free to PM me.

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

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

Well, I can tell you this. It would be VERY difficult to do on the latest version. Currently, there is not DB abstration layer, and at last count, there were more than 1000 queries harcoded into OSC that are not ansi standard T-SQL, which is what MS SQL requires.

 

The 1000 Queries would not ber alll that difficult to change over, it is some of the other functionality which MySQL uses/returns and MS SQL does not, and this is not ANSI T-SQL either but is used in the core functionality, for example performing logic based on num of returned rows etc. It is this part which will lead to the headaches for re-coding.....

 

I would love to see osCommerce comply in the next version with ANSI Standards, and really do not care if it takes additional time before release. What is a few more months... B)

 

And a in-sync as wizardsandwars suggested is probably the easiest way, but if data is critcal, it is not a viable solution, since it will not always be in sync at all times, rather only on the frequency choosen to run the app....

 

Cheers,

 

Peter M.

Peter McGrath

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

See my Profile (click here) for more information and to contact me for professional osCommerce support that includes SEO development, custom development and security implementation

Link to comment
Share on other sites

Well, I can tell you this. It would be VERY difficult to do on the latest version. Currently, there is not DB abstration layer, and at last count, there were more than 1000 queries harcoded into OSC that are not ansi standard T-SQL, which is what MS SQL requires.

 

One alternative you migth consider, is creating a 'syncing' script, that runs on a regular basis (using WinAT, or the window task scheduler), extracts data from the MS SQL server, and loads it into the MYSQL database using an insert else update strategy. You could do this on an incremental basis based on last updated timestamps to minimize processing.

 

This sort of ETL programming is actually what I do for a living, so if you decide to go this route, and need some help, feel free to PM me.

Actually, what I was thinking of doing if I couldn't find a MS SQL version was this:

  • Modify the existing Registration script in ASP so that it similtaneously creates a record in MS SQL (universal login member table) and MySQL (osCommerce member table). The universal login member table currently does not store address information like osCommerce member table does, so it would be acceptable to have seperate tables linked by a username as the key.
  • Modify the existing site login (written in ASP using MS SQL) to set a cookie or session variable that osCommerce would recognize as having the user logged in. That way they are logged in via cookie to the forum, via session variable to the directory, via cookie/session variable (whatever you use) for osCommerce. I am sure I can get this to work if osCommerce uses cookies, but it may be tricky if you use session variables. I've never tried setting a PHP session variable when the user logs in via an ASP page... so it could get interesting. I may have to add a PHP page to the login process for the sole purpose of setting the osCommerce session variable when they login to the universal login. The universal login already sets cookies and session variables for various scripts that use different means of detecting a logged on user, but all those scripts are written in ASP or use cookies to detect logged in users. Another alternative is to add code that loads on every osCommerce page that checks for the cookie and sets the osCommerce session variable if the cookie says they are logged in.
  • Replace / modify the osCommerce login box to use the existing site login script written in ASP using MS SQL. Or modify it so that it sets the cookie that the ASP pages expect to see.
  • Either add links to the osCommerce user's profile pages to the existing site User Control Panel or write new pages that modify the osCommerce User Profile. I may build special pages in situations where it is desirable to modify BOTH member tables at the same time. (The same ASP or PHP script can be written to write the same data to more than one table.) It may be necessary to change osCommerce to use my Profile pages, instead of the native ones, but I doubt that will be necessary.
  • Import all existing users into osCommerce member table or change the code so that if the logged on user (via universal login) does not have an osCommerce member record, it creates one automatically when they visit the shopping cart or when they login.

Not any easy solution, but it sounds better than finding and modifying 1000 SQL queries. And it may actually be easier than what I outlines above. When I added the directory to the site, I thought it would be hard to integrate the two login systems (cookies for most of the site vs. ASP session variable for the directory). It turned out to be as simple as changing the directory's native login page to redirect to the existing universal login page, and adding one line of code that sets the session variable for the directory upon detecting that they are logged in (using the cookie).

 

For those of you more familiar with the code, do you see if I missed something? I know it may not make much sense without knowing the whole picture, but hopefully I was detailed enough to give you an idea of what is going on.

 

Adventures in Integration. :D

Link to comment
Share on other sites

The 1000 Queries would not ber alll that difficult to change over, it is some of the other functionality which MySQL uses/returns and MS SQL does not, and this is not ANSI T-SQL either but is used in the core functionality, for example performing logic based on num of returned rows etc. It is this part which will lead to the headaches for re-coding.....

 

A quick reply here, if you don't mind.

 

All of the 'other functionality' that I've seen is in php function, not MySQL SQL. From what I've seen, only the SQL itself would need to be changed. That and I wouldn't know where to begin to code an ADO connection in php.

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

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

For those of you more familiar with the code, do you see if I missed something? I know it may not make much sense without knowing the whole picture, but hopefully I was detailed enough to give you an idea of what is going on.

 

Sounds logical. I hope you've got a ton of time on your hands to develop this. Sounds like a full time job for a few months.

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

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

All of the 'other functionality' that I've seen is in php function, not MySQL SQL. From what I've seen, only the SQL itself would need to be changed. That and I wouldn't know where to begin to code an ADO connection in php.

 

 if (tep_db_num_rows($zone_query))

 

For this logic, coming from this function in the database.php file

 

 ?function tep_db_num_rows($db_query) {
? ?return mysql_num_rows($db_query);
?}

 

I did not realize that this follow ANSI Standards,

 

 

It is this type of logic which would need to be looked at. I just grabbed a quick example, I could come up with many more if you like......

 

cheers,

 

Peter M.

Peter McGrath

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

See my Profile (click here) for more information and to contact me for professional osCommerce support that includes SEO development, custom development and security implementation

Link to comment
Share on other sites

Well, yes. Those php function could also have to be looked at, although the function calls themselves could probably remain. We'd still pass the the SQL to the function. But you're right, we'd also have to change how the function connects to the DB and returns the number of rows.

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

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

Sounds logical. I hope you've got a ton of time on your hands to develop this. Sounds like a full time job for a few months.

Yeah, it will be a bit of a pain. But because we want an integrated login for the entire site, its pretty much a given that I will have to do some modifications to any ecommerce software I install, even if it uses ASP and MS SQL. The only real difference will be the fact that I have to maintain two member tables instead of one in this case. All the other integration work would be the same no matter what I install. I've already integrated several other scripts into the universal login (which was originally the forum login, actually) and it works fine. But, yes, it takes some time.

Link to comment
Share on other sites

I hate to say it, but there are a number of ASP carts that would be more suitable for your project, IMO.

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

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

I hate to say it, but there are a number of ASP carts that would be more suitable for your project, IMO.

I have been looking around and one thing most are missing... except the expensive ones... is a multi-store / mall feature. osCommerce doesn't have that out of the box, but someone has written a Multi-Store Contribution for osCommerce.

 

Any suggestions on good multi-store / multi-vendor mall / carts out there in ASP that doesn't cost over $1000?

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...