Merchant and SQL?

[Kent Multer]

Hi folks --

I have a client who wants to customize Merchant to send data directly to their SQL database. They're currently using MM4, and they might consider upgrading to MM5; but they have a lot of modules, so that could be impractical. I'm not sure if they're using MySQL or some other brand.

I was wondering if someone can give me a quick run-down on what "flavors" of SQL Miva can talk to? MySQL, of course; or is that only for Merchant 5? Sorry, I'm not an SQL guru. I know there's an "odbc" option for MvOPEN, but I don't know what flavors of SQL it works with.

Thanks --

[dotCOM_host]

Currently MIVA Merchant v4 supports only DBF file format natively, and once upon a time there used to be a "hack" to provide support for mySQL in v4 - though truth be told, stores running that extra layer were dog slow and unless you had a *REALLY* compelling reason to use MM4 with mySQL, I'd never recommend that solution. We've migrated stores that were using that back to plain v4 or later on to MM5 with mySQL, most often only because these stores were virtually unusable in their old state. I'm not even going to mention the name of that module as not to sound like any sort of recommendation for it. It was an interesting idea, however in execution it proved to be far less beneficial.

MIVA Merchant v5 supports natively mySQL v4.1.x (preferred setup), as well as the old DBF format files. There is also an Oracle library available for it, though I don't think I've ever seen anyone using MIVA Merchant with Oracle. Chances are that if someone needs the scalability and power of Oracle, they are not going to be looking at MIVA Merchant as their front-end application. So, for all intents and purposes, there's mySQL and DBF support in v5. There is currently no official support for SQL (I'm assuming you meant "Microsoft SQL Server"?), though depending on requests - MIVA may add support for it. Considering how poorly MIVA Merchant runs on Windows Server though.... I'd suspect this to be a rather distant possibility, or at the very best, perhaps they will allow one to run MIVA Merchant on Linux, but make a remote connection to MSSQL hosted on a separate Windows Server.

You mentioned the client has a lot of modules and upgrading to v5 "could be impractical", but you really don't have too many choices there. They may need to just bite the bullet and completely overhaul their site in v5 with mySQL support to accomplish what they need. The good news is that the upgrade to v5 can run in parallel to their old v4 store for a good 6 months, which should give you plenty of time to set up the new v5 store and not have the panicked rush to get the migration done in one day.

[cagdas]

I don't think I've ever seen anyone using MIVA Merchant with Oracle. Chances are that if someone needs the scalability and power of Oracle, they are not going to be looking at MIVA Merchant as their front-end application.

The Oracle connector mentioned here uses a native interface to Oracle and Merchant cannot run with it because of parameter syntax differences with MySQL and ODBC. The native interface works fine with Empresa but the application using it should write Oracle-specific SQL statements and Merchant is not written like that. The library supplied is purely for development purposes. For example, you can retrieve some external data from an Oracle database that is installed in your Merchant.

The way we envision Merchant to work with Oracle now is through ODBC. The ODBC connector is available as BETA for now and you can download it from our support suite. MM5 requires minor modifications to work with it and these files are supplied with the package as well. So far there are 48 downloads and no problem has been reported.

Other databases you can use with MM5 through ODBC include Microsoft SQL Server, Sybase and Postgres. I tested the following combinations and they seem to be working:

MM5 on Windows - Oracle
MM5 on Windows - Postgres
MM5 on Linux - Oracle
MM5 on Linux - Postgres
MM5 on FreeBSD - Oracle

These combinations should work as well but I have not tested them:

MM5 on Windows - Microsoft SQL Server / Sybase
MM5 on Linux - Microsoft SQL Server / Sybase
MM5 on FreeBSD - Microsoft SQL Server / Sybase
MM5 on FreeBSD - Postgres

Currently we don't support any of these officially since they are at beta stage but they will be moved to production state at some point if we don't find any problems. Any tests and test results would be very much appreciated.

Thank you,

[Brandon MUS]

We have a store that runs MM4 and MySQL both for product/category management. We created this store as an experiment to see if we could squeeze some extra features out of MM4 without writing in MivaScript (as MivaScript was too limited (or at least my understanding of it was) to get the features we wanted). There are a lot of problems that we had to address for this setup to work and a lot of them involved denormalized data and using custom imports and exports to keep the data synced as much as possible.

In the end, it works very well, but it does require a lot of work on the backend to keep both sides happy. Our customers more or less "browse" on the MySQL data, but buy the DBF data.

[dotCOM_host]

Brandon,

I think I've seen your site before... ;-) We have couple clients who have done something similar on their sites - custom built front-end in PHP/mySQL, but using MM4 for the checkout and back-end order processing. While this approach certainly works, it requires a fair amount of work to first build and then keep your custom front-end system updated, and of course this doubles all your databases and requires keeping them both in sync every time you add, delete, or edit a product. I'm not quite sure if it's worth the time and effort to implement something like this anymore, with MIVA Merchant v5 and native mySQL support. Heck, with MM5 you can keep just one native MIVA database and very quickly write your own PHP front-end that pulls data live from your store, without the need to duplicate anything, even if you couldn't do the front-end natively in StoreMorph. In terms of time to market (and effectively sales and generating profits), that would be the fastest way to solve the whole mySQL issue now that it's natively available for v5. The upgrade from v4 to v5 is certainly going to be less time consuming than building any custom systems like this... I guess what I'm trying to say is that all these custom solutions were an option for MM4 store owners - expensive and time consuming, but at least available. If you want to bypass all that work and use SQL natively - just upgrade to v5 and take advantage of everything that's already been done for you to quickly and easily access all that data from MM or from PHP - there's no longer a need to reinvent the wheel.

[Brandon MUS]

Brandon,

I think I've seen your site before... ;-) ... Heck, with MM5 you can keep just one native MIVA database and very quickly write your own PHP front-end that pulls data live from your store, without the need to duplicate anything, even if you couldn't do the front-end natively in StoreMorph.
Sounds like you've seen 2 of my sites now :D