View Full Version : [Products/Categories] Product Manager using Libre/OpenOffice - localhost (and remote) database with image mass editing

01-27-2013, 01:56 PM
Having been frustrated by the lack of freely available applications to enable one to work with large numbers of products in OscMax/Oscommerce and as the only one available is payware and it doesn't support osCmax (nor add-ons!) I decided to build my own and share it with the community. You see my store will have over 4000 items to deal with and some of the problems were listing them, pricing, editing, and checking that the pictures were correct. Plus I had added a Canada Post module that required dimensions as well as customs info to be added and the dimensions must be (largest to smallest) Length, Width, Height (still have to fix the BASE script so it sorts these out correctly).

After a number of weeks of tinkering I have come up with something that is a bit ugly, but works enough that I felt it was time to invite others into the mix...

So here is my demo Product Manager (click, eh?) (http://www.flippers.com/zips/oscmax_252_base.odb.zip) - using LibreOffice BASE, you should be able to use OpenOffice or possibly NeoOffice as well.

Note you will need a localhost setup of your store, can't go into that here - there is info in the wiki on setting that up.

Place the expanded files (move oscmax252.odb from the generated sub-directory) into (a mirror of your online catalog images directory) - catalog/images/images_big - it will be launched from there. This allows the database to find your images_big for this application to work right.

First thing you have to do is after launching is to change the database links to match yours- so go to the top menu line, select Edit, then Database - and start with Properties (Advanced Settings) to change the database name to match yours, and set the login name as well. You don't need a password on localhost if you aren't sharing the computer so I don't bother ticking the Password Required box (you can of course!) click Test Connection, then go to Additional Settings and set the Host Name to Localhost (if you've changed it) and check that MySQL JDBC driver class is "com.mysql.jdbc.Driver". Click Test class - all OK? Great. Close that window.

Now you have to go to Tables, and see if your database is showing up. It should be (I think)...and if it is, then click on Forms where you should see the form Demo Product Management, right click on that and chose Edit. You should see a nice window open up with a bunch of names on a green background (BASE) - you are in the Edit window and now you need to fix some links...the bottom left side of the screen should have icons starting with an arrow pointing to the upper left. If so, count to the right (don't CLICK on any counts) four more symbols until you are hovering over Form Navigator - click that - a window should pop up listing a bunch of stuff starting with Forms/MainForm/MainForm_Grid/SubForm Main/... Great! Now right click on MainForm and select Properties - switch to the Data and then change the 'content' to match your database name.products (you will see oscmax252.products) by clicking on the down arrow, your fields should be right there and scroll down to your 'somename.products'. Now go back to the Form Navigator and click on the first Sub-Form... and fix its' Data Content the same way and be sure to edit ALL the Sub-Forms!

Done? Lets' test it by finding the icon for Design Mode On/Off and click it. If all is good you should see your database data displayed not unlike the packed picture with the zip file.

Problems with the script:

1) Can't add products yet (I've overlooked something!) working on this...
2) Editing doesn't work right - you change a field but the only way to have the change take is to close the application AND close BASE! Not good, another overlooked something...working on this as well
3) Description (HTML) doesn't work as I want it to. It should either link to something like LibreOffice WRITER (a window opens and closes for each entry as you run past them) or some sort of HTML editor opens up. Working on this too...
4) I'm sure folks (if anyone is interested) will find more problems - along with the solutions I hope!!


This also works just fine with a remote database at your real store - simply make a copy of your catalog/images directory from the web site and again put the .odb file in the images_big directory, however now you change the database info to your remote site and be sure to tick the Password required! I am not sure how BASE stores the password, so you may want to consider that (or do some research (http://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=54140)) potential risk to your site database security...

I am no expert on BASE so if you have questions I'll try to help, but what I am hoping happens is other people who are better at BASE take this and make it work better...I'm going to continue poking away in the meantime and happy to share what I figure out.

This is freeware and is licensed by me as such, you may not charge for this application/script or any or all derivatives of this application script. It should be covered under the LibreOffice/OpenOffice license, but as I'm no expert I want to clearly state that my intent is this is freely available to anyone.


John :-#)#

01-27-2013, 03:57 PM
Fantastic start and quick tutorial - very well done.
Have downloaded and will try to find time to test soon.

02-13-2013, 01:37 PM
I did find an open source Product Management (http://www.mariovaldez.net/software/oscpmwin/) Windows based program that is not bad - not as fast as what I am trying to do, but for basic product editing it would be hard to beat. What is interesting is I suspect it could be upgraded by someone who is familiar with programming. It does work with 2.5.2 on localhost by the way...


02-13-2013, 02:11 PM
I did find an open source Product Management (http://www.mariovaldez.net/software/oscpmwin/) Windows based program that is not bad
Yes, I've had that one downloaded and used, quite some time ago (Sat 02 Apr 2011 21:53:31 BST) and it even runs under Wine. :) I notice that it's exactly the same build, so updates are on a par with 'Max. :p I have only used it to attach to remote databases.
It would be much better to have a true Open Source one, that doesn't rely on an underlying proprietary OS (and likely IDE) to develop further. ;)

02-13-2013, 02:31 PM
I don't have a clue how to modify it so I'm still poking away at Base to see if I can get that to work as I want. Problem for me is little time and even less experience. I can always hope some clever person adopts either method and produces updates...
I did find I broke my localhost experimental database experimenting with Base (manufacturers were duplicated with interesting results), so any experiments must ALWAYS be done on localhost with a COPY of your database, not the live one! And after modifying the database test via Localhost before uploading to the site!

02-13-2013, 02:38 PM
I never use a localhost copy - it's an additional variable (the environment) that can add to issues and I much prefer to use a development domain on a server.
I took a quick look at the developer page: Delphi 6 etc., so defo. not for me, life's too short to learn yet another programming tool/language.
I need to concentrate on this flamin' OTF stuff, so can't consider looking at Base just now, either. :(

Keep at it, though. :)