SQL

I'm looking for an open source implementation of a stable, easy customizable, well supported ecommerce application like OsCommerce or CubeCart. To be able to try one or two of these apps for myself I installed a complete LAMP stack on a virtual windows XP image a while back.

I used MySQL 5 for that was the latest version at the time. Not aware of some of the changes in the default configuration, every ecommerce package that I tried to run gave me an error message at the point I tried to add something to the database. Be it a new category or item. OsCommerce as well as CubeCart could not insert anything into the database and gave me a message similar to this one:

1366: Incorrect decimal value: '' for column 'per_ship' at row 1

QUERY = INSERT INTO store_CubeCart_category (`cat_name`, `cat_father_id`, `cat_image`, `per_ship`, `item_ship`, `item_int_ship`, `per_int_ship`) VALUES ('test', '1', '', '', '', '', '')

The cause of this error can be traced back to the configuration of MySQL 5 and a new 'feature' called STRICT_TRANS_TABLES that is turned on by default. At least for the windows version of MySQL. The following line in the configuration file my.ini was causing these PHP applications to fail:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The MySQL documentation gives the following information about STRICT_TRANS_TABLES: If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. (Implemented in MySQL 5.0.2)

The solution to the errors is simple. Remove the STRICT_TRANS_TABLES from the sql-mode setting and everything runs smoothly.

I like Microsoft Sql Server, I like it a lot. But there are times when you are forced to work with an other RDBMS like Oracle. I had to rewrite a stored procedure for Oracle using PL/SQL developer. One cannot do without this tool when working with Oracle! When I write complicated stored procedures for Sql Server I like to write the query as a select statement first. I can declare variables and use them in my select statement so I can easely convert the query to a stored procedure later:

I was looking for a similar approach for Oracle with PL/SQL Developer but had a hard time finding one. After some time fiddeling with declare statements and test scripts I found substitution variables. With PL/SQL Developer, in a Sql window, you can use so called substitution variables. By specifying &deptno in the SQL text, you will be prompted for a value for this variable. You can even set default values by specifying &<name="deptno" default="102">.