mysql function example

delimiter //
create function mytest () returns char(50) deterministic
begin
declare c int;
select count(*) into c from table;
return c;
end
//
delimiter ;

select from two tables into a single column

delete from user where id not in (select a.id from person a UNION select b.id from merchant b)

Test mysql connection

mysql -u user -p -h 10.0.0.0

SQL: how to execute procs and run functions

In SQL client of your choice:

SQL: check if a query returned any rows

SQL: describe a select statement instead of table

Problem:
QA asked me to put max lengths in input fields that would map to values that would eventually be inserted into the Oracle database. The problem was that these values were not going into a single table, but about 10 different tables, which were defined by a complex select statement. I didnt want to describe each individual table to find out the max lengths on the column names

Solution:
Take the complex SQL statement and create a view with it:


Then describe the view, and drop the view when you are done with it.

IBatis: Iterate over multiple lists at the same time

Problem:
I needed to verify if each record passed several rules, where each rule consisted of several flags and their values. The problem was complexity: I had 7 flags(which had 3 possible values) to check, and 1 to 5 rules per status. This could lead to some ugly and hard to test SQL.

Another problem was that you cant iterate over multiple lists at the same time (using the same index) in ibatis.

Solution:
The solution was to use Ibatis's Iterate directive. I would iterate over lists of maps, where each map represented a rule, and each bucket of the map represented a flag and its expected value.

Those iBastis code ended up looking something like this:


and the parameter class had the following code:



This worked pretty well to abstract the flags and rules away from the iBatis sql and keep them all in the rules class.

Oracle: convert nclob to nvarchar2 (part 2)

I needed to convert a column in a database from NCLOB to NVARCHAR2(2000) which is the max you can store in NVARCHAR2. The problem was that some columns in the table where larger then 2000 chars. To solve this i needed to use the oracle length function to select only rows who's lenth was less then 2000. In this example sql, the table that has the NCLOB is called THETABLE, and the column that needs to become NVARCHAR2 is called THECOLUMN.

<br />CREATE TABLE THETABLE_TEMP as select * from THETABLE;<br />alter table THETABLE drop COLUMN THECOLUMN;<br />alter table THETABLE add (THECOLUMN NVARCHAR2(2000));<br />UPDATE THETABLE p SET p.THECOLUMN = <br />(SELECT t.THECOLUMN FROM THETABLE_TEMP t <br />WHERE t.PRIMEKEY = p.PRIMEKEY <br />and dbms_lob.getlength(t.THECOLUMN) <= 2000) <br />

dbms_lob.getlength(column) is the key to success here

SQL: change column datatype

in this note to self, i need have a table where a column (COL2) is an NVARCHAR, and it need to become an NCLOB:

CREATE table TABLENAME_TEMP as select * from TABLENAME;
ALTER table TABLENAME drop COLUMN COL2;
DELETE from TABLENAME;
ALTER table TABLENAME add (COL2 NCLOB NOT NULL);
-- at this point the columns may not line up, so specify order...
INSERT into TABLENAME TN (TN.COL1, TN.COL3, TN.COL2) select * from TABLENAME_TEMP;
COMMIT;
DROP table TABLENAME_TEMP;

now the table TABLENAME has a column which is an NCLOB, yey!s

SQL: using rownum to make new primary keys for insert

INSERT INTO DEVICE

SELECT 1002403148+ROWNUM, DEVICE_TYPE_ID, 11, DISPLAY_NAME, SORT_ORDER FROM DEVICE;

the old uids stopped at 1002403148 and i needed to reinsert everything from the same table but changing one column value.