0001: package com.quadcap.sql;
0002:
0003: /* Copyright 1999 - 2003 Quadcap Software. All rights reserved.
0004: *
0005: * This software is distributed under the Quadcap Free Software License.
0006: * This software may be used or modified for any purpose, personal or
0007: * commercial. Open Source redistributions are permitted. Commercial
0008: * redistribution of larger works derived from, or works which bundle
0009: * this software requires a "Commercial Redistribution License"; see
0010: * http://www.quadcap.com/purchase.
0011: *
0012: * Redistributions qualify as "Open Source" under one of the following terms:
0013: *
0014: * Redistributions are made at no charge beyond the reasonable cost of
0015: * materials and delivery.
0016: *
0017: * Redistributions are accompanied by a copy of the Source Code or by an
0018: * irrevocable offer to provide a copy of the Source Code for up to three
0019: * years at the cost of materials and delivery. Such redistributions
0020: * must allow further use, modification, and redistribution of the Source
0021: * Code under substantially the same terms as this license.
0022: *
0023: * Redistributions of source code must retain the copyright notices as they
0024: * appear in each source code file, these license terms, and the
0025: * disclaimer/limitation of liability set forth as paragraph 6 below.
0026: *
0027: * Redistributions in binary form must reproduce this Copyright Notice,
0028: * these license terms, and the disclaimer/limitation of liability set
0029: * forth as paragraph 6 below, in the documentation and/or other materials
0030: * provided with the distribution.
0031: *
0032: * The Software is provided on an "AS IS" basis. No warranty is
0033: * provided that the Software is free of defects, or fit for a
0034: * particular purpose.
0035: *
0036: * Limitation of Liability. Quadcap Software shall not be liable
0037: * for any damages suffered by the Licensee or any third party resulting
0038: * from use of the Software.
0039: */
0040:
0041: import java.io.Externalizable;
0042: import java.io.IOException;
0043: import java.io.ObjectInput;
0044: import java.io.ObjectOutput;
0045:
0046: import java.util.Calendar;
0047: import java.util.Hashtable;
0048: import java.util.Random;
0049:
0050: import java.math.BigDecimal;
0051:
0052: import java.security.MessageDigest;
0053:
0054: import java.text.SimpleDateFormat;
0055:
0056: import java.sql.Date;
0057: import java.sql.SQLException;
0058: import java.sql.Time;
0059: import java.sql.Timestamp;
0060:
0061: import com.quadcap.sql.types.*;
0062:
0063: import com.quadcap.util.text.Soundex;
0064:
0065: import com.quadcap.util.Debug;
0066:
0067: /**
0068: * Implement function expressions. All of them.
0069: *
0070: * @author Stan Bailes
0071: */
0072: public class FunctionExpression extends Expression implements
0073: Externalizable {
0074: String name;
0075: String uname;
0076: VectorExpression args;
0077: boolean not = false;
0078: Value value = null;
0079:
0080: //static final Calendar cal = Calendar.getInstance();
0081: static final SimpleDateFormat dayNameFmt = new SimpleDateFormat(
0082: "EEE");
0083: static final SimpleDateFormat monthNameFmt = new SimpleDateFormat(
0084: "MMM");
0085:
0086: static final int TSI_FRAC_SECOND = -1;
0087: static final int TSI_SECOND = Calendar.SECOND;
0088: static final int TSI_MINUTE = Calendar.MINUTE;
0089: static final int TSI_HOUR = Calendar.HOUR;
0090: static final int TSI_DAY = Calendar.DAY_OF_YEAR;
0091: static final int TSI_WEEK = Calendar.WEEK_OF_YEAR;
0092: static final int TSI_MONTH = Calendar.MONTH;
0093: static final int TSI_QUARTER = -2;
0094: static final int TSI_YEAR = Calendar.YEAR;
0095:
0096: // map fn name to Integer fn num
0097: static final Hashtable functions = new Hashtable();
0098:
0099: //#global ecnt; set ecnt 0
0100: //#proc nxt {} { global ecnt; return [incr ecnt] }
0101: //#set fns {
0102: //# ABS ASCII
0103: //# ACOS ASIN ATAN ATAN2
0104: //# BIT_LENGTH
0105: //# CASE CAST CEILING CHAR {CHAR_LENGTH CHARACTER_LENGTH}
0106: //# COALESCE
0107: //# COS COT CONCAT CURDATE CURTIME
0108: //# DATABASE DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR DEGREES DIFFERENCE
0109: //# EXP
0110: //# FLOOR
0111: //# HOUR
0112: //# IFNULL INSERT
0113: //# LEFT LENGTH LOCATE LOG LOG10 {LOWER LCASE} LTRIM
0114: //# MINUTE MOD MONTH MONTHNAME
0115: //# NOW NULLIF
0116: //# OCTET_LENGTH
0117: //# PI POWER
0118: //# QUARTER
0119: //# RADIANS RAND REPEAT REPLACE RIGHT ROUND RTRIM
0120: //# SECOND SIGN SIN SOUNDEX SPACE SQRT SUBSTRING
0121: //# TAN TIMESTAMPADD TIMESTAMPDIFF TRUNCATE
0122: //# {UPPER UCASE} USER
0123: //# WEEK
0124: //# YEAR
0125: //# DIGEST LAST_INSERT_ID
0126: //#}
0127: //#foreach f $fns {
0128: //# set name [lindex $f 0]
0129: //# set fname [format "%-24s" $name]
0130: //> static final int fn${fname} = [nxt];
0131: //#}
0132: //>
0133: //> static {
0134: //#foreach f $fns {
0135: //# set name [lindex $f 0]
0136: //# foreach n $f {
0137: //> functions.put(\"${n}\", new Integer(fn${name}));
0138: //# }
0139: //#}
0140: //> }
0141: //>
0142: //> /*{functionIndex.xml-100}
0143: //> * <ul>
0144: //#foreach f $fns {
0145: //# set name [lindex $f 0]
0146: //# foreach n $f {
0147: //> * <li><a href=\"./fnvarExpression.html#${name}\">${n}</a></li>
0148: //# }
0149: //#}
0150: //> * </ul>
0151: //> */
0152: //>/*
0153: //#foreach f $fns {
0154: //> <row><entry>[concat $f]</entry><entry>Yes</entry></row>
0155: //#}
0156: //>*/
0157: //#autogen begin
0158: static final int fnABS = 1;
0159: static final int fnASCII = 2;
0160: static final int fnACOS = 3;
0161: static final int fnASIN = 4;
0162: static final int fnATAN = 5;
0163: static final int fnATAN2 = 6;
0164: static final int fnBIT_LENGTH = 7;
0165: static final int fnCASE = 8;
0166: static final int fnCAST = 9;
0167: static final int fnCEILING = 10;
0168: static final int fnCHAR = 11;
0169: static final int fnCHAR_LENGTH = 12;
0170: static final int fnCOALESCE = 13;
0171: static final int fnCOS = 14;
0172: static final int fnCOT = 15;
0173: static final int fnCONCAT = 16;
0174: static final int fnCURDATE = 17;
0175: static final int fnCURTIME = 18;
0176: static final int fnDATABASE = 19;
0177: static final int fnDAYNAME = 20;
0178: static final int fnDAYOFMONTH = 21;
0179: static final int fnDAYOFWEEK = 22;
0180: static final int fnDAYOFYEAR = 23;
0181: static final int fnDEGREES = 24;
0182: static final int fnDIFFERENCE = 25;
0183: static final int fnEXP = 26;
0184: static final int fnFLOOR = 27;
0185: static final int fnHOUR = 28;
0186: static final int fnIFNULL = 29;
0187: static final int fnINSERT = 30;
0188: static final int fnLEFT = 31;
0189: static final int fnLENGTH = 32;
0190: static final int fnLOCATE = 33;
0191: static final int fnLOG = 34;
0192: static final int fnLOG10 = 35;
0193: static final int fnLOWER = 36;
0194: static final int fnLTRIM = 37;
0195: static final int fnMINUTE = 38;
0196: static final int fnMOD = 39;
0197: static final int fnMONTH = 40;
0198: static final int fnMONTHNAME = 41;
0199: static final int fnNOW = 42;
0200: static final int fnNULLIF = 43;
0201: static final int fnOCTET_LENGTH = 44;
0202: static final int fnPI = 45;
0203: static final int fnPOWER = 46;
0204: static final int fnQUARTER = 47;
0205: static final int fnRADIANS = 48;
0206: static final int fnRAND = 49;
0207: static final int fnREPEAT = 50;
0208: static final int fnREPLACE = 51;
0209: static final int fnRIGHT = 52;
0210: static final int fnROUND = 53;
0211: static final int fnRTRIM = 54;
0212: static final int fnSECOND = 55;
0213: static final int fnSIGN = 56;
0214: static final int fnSIN = 57;
0215: static final int fnSOUNDEX = 58;
0216: static final int fnSPACE = 59;
0217: static final int fnSQRT = 60;
0218: static final int fnSUBSTRING = 61;
0219: static final int fnTAN = 62;
0220: static final int fnTIMESTAMPADD = 63;
0221: static final int fnTIMESTAMPDIFF = 64;
0222: static final int fnTRUNCATE = 65;
0223: static final int fnUPPER = 66;
0224: static final int fnUSER = 67;
0225: static final int fnWEEK = 68;
0226: static final int fnYEAR = 69;
0227: static final int fnDIGEST = 70;
0228: static final int fnLAST_INSERT_ID = 71;
0229:
0230: static {
0231: functions.put("ABS", new Integer(fnABS));
0232: functions.put("ASCII", new Integer(fnASCII));
0233: functions.put("ACOS", new Integer(fnACOS));
0234: functions.put("ASIN", new Integer(fnASIN));
0235: functions.put("ATAN", new Integer(fnATAN));
0236: functions.put("ATAN2", new Integer(fnATAN2));
0237: functions.put("BIT_LENGTH", new Integer(fnBIT_LENGTH));
0238: functions.put("CASE", new Integer(fnCASE));
0239: functions.put("CAST", new Integer(fnCAST));
0240: functions.put("CEILING", new Integer(fnCEILING));
0241: functions.put("CHAR", new Integer(fnCHAR));
0242: functions.put("CHAR_LENGTH", new Integer(fnCHAR_LENGTH));
0243: functions.put("CHARACTER_LENGTH", new Integer(fnCHAR_LENGTH));
0244: functions.put("COALESCE", new Integer(fnCOALESCE));
0245: functions.put("COS", new Integer(fnCOS));
0246: functions.put("COT", new Integer(fnCOT));
0247: functions.put("CONCAT", new Integer(fnCONCAT));
0248: functions.put("CURDATE", new Integer(fnCURDATE));
0249: functions.put("CURTIME", new Integer(fnCURTIME));
0250: functions.put("DATABASE", new Integer(fnDATABASE));
0251: functions.put("DAYNAME", new Integer(fnDAYNAME));
0252: functions.put("DAYOFMONTH", new Integer(fnDAYOFMONTH));
0253: functions.put("DAYOFWEEK", new Integer(fnDAYOFWEEK));
0254: functions.put("DAYOFYEAR", new Integer(fnDAYOFYEAR));
0255: functions.put("DEGREES", new Integer(fnDEGREES));
0256: functions.put("DIFFERENCE", new Integer(fnDIFFERENCE));
0257: functions.put("EXP", new Integer(fnEXP));
0258: functions.put("FLOOR", new Integer(fnFLOOR));
0259: functions.put("HOUR", new Integer(fnHOUR));
0260: functions.put("IFNULL", new Integer(fnIFNULL));
0261: functions.put("INSERT", new Integer(fnINSERT));
0262: functions.put("LEFT", new Integer(fnLEFT));
0263: functions.put("LENGTH", new Integer(fnLENGTH));
0264: functions.put("LOCATE", new Integer(fnLOCATE));
0265: functions.put("LOG", new Integer(fnLOG));
0266: functions.put("LOG10", new Integer(fnLOG10));
0267: functions.put("LOWER", new Integer(fnLOWER));
0268: functions.put("LCASE", new Integer(fnLOWER));
0269: functions.put("LTRIM", new Integer(fnLTRIM));
0270: functions.put("MINUTE", new Integer(fnMINUTE));
0271: functions.put("MOD", new Integer(fnMOD));
0272: functions.put("MONTH", new Integer(fnMONTH));
0273: functions.put("MONTHNAME", new Integer(fnMONTHNAME));
0274: functions.put("NOW", new Integer(fnNOW));
0275: functions.put("NULLIF", new Integer(fnNULLIF));
0276: functions.put("OCTET_LENGTH", new Integer(fnOCTET_LENGTH));
0277: functions.put("PI", new Integer(fnPI));
0278: functions.put("POWER", new Integer(fnPOWER));
0279: functions.put("QUARTER", new Integer(fnQUARTER));
0280: functions.put("RADIANS", new Integer(fnRADIANS));
0281: functions.put("RAND", new Integer(fnRAND));
0282: functions.put("REPEAT", new Integer(fnREPEAT));
0283: functions.put("REPLACE", new Integer(fnREPLACE));
0284: functions.put("RIGHT", new Integer(fnRIGHT));
0285: functions.put("ROUND", new Integer(fnROUND));
0286: functions.put("RTRIM", new Integer(fnRTRIM));
0287: functions.put("SECOND", new Integer(fnSECOND));
0288: functions.put("SIGN", new Integer(fnSIGN));
0289: functions.put("SIN", new Integer(fnSIN));
0290: functions.put("SOUNDEX", new Integer(fnSOUNDEX));
0291: functions.put("SPACE", new Integer(fnSPACE));
0292: functions.put("SQRT", new Integer(fnSQRT));
0293: functions.put("SUBSTRING", new Integer(fnSUBSTRING));
0294: functions.put("TAN", new Integer(fnTAN));
0295: functions.put("TIMESTAMPADD", new Integer(fnTIMESTAMPADD));
0296: functions.put("TIMESTAMPDIFF", new Integer(fnTIMESTAMPDIFF));
0297: functions.put("TRUNCATE", new Integer(fnTRUNCATE));
0298: functions.put("UPPER", new Integer(fnUPPER));
0299: functions.put("UCASE", new Integer(fnUPPER));
0300: functions.put("USER", new Integer(fnUSER));
0301: functions.put("WEEK", new Integer(fnWEEK));
0302: functions.put("YEAR", new Integer(fnYEAR));
0303: functions.put("DIGEST", new Integer(fnDIGEST));
0304: functions.put("LAST_INSERT_ID", new Integer(fnLAST_INSERT_ID));
0305: }
0306:
0307: /*{functionIndex.xml-100}
0308: * <ul>
0309: * <li><a href="./fnvarExpression.html#ABS">ABS</a></li>
0310: * <li><a href="./fnvarExpression.html#ASCII">ASCII</a></li>
0311: * <li><a href="./fnvarExpression.html#ACOS">ACOS</a></li>
0312: * <li><a href="./fnvarExpression.html#ASIN">ASIN</a></li>
0313: * <li><a href="./fnvarExpression.html#ATAN">ATAN</a></li>
0314: * <li><a href="./fnvarExpression.html#ATAN2">ATAN2</a></li>
0315: * <li><a href="./fnvarExpression.html#BIT_LENGTH">BIT_LENGTH</a></li>
0316: * <li><a href="./fnvarExpression.html#CASE">CASE</a></li>
0317: * <li><a href="./fnvarExpression.html#CAST">CAST</a></li>
0318: * <li><a href="./fnvarExpression.html#CEILING">CEILING</a></li>
0319: * <li><a href="./fnvarExpression.html#CHAR">CHAR</a></li>
0320: * <li><a href="./fnvarExpression.html#CHAR_LENGTH">CHAR_LENGTH</a></li>
0321: * <li><a href="./fnvarExpression.html#CHAR_LENGTH">CHARACTER_LENGTH</a></li>
0322: * <li><a href="./fnvarExpression.html#COALESCE">COALESCE</a></li>
0323: * <li><a href="./fnvarExpression.html#COS">COS</a></li>
0324: * <li><a href="./fnvarExpression.html#COT">COT</a></li>
0325: * <li><a href="./fnvarExpression.html#CONCAT">CONCAT</a></li>
0326: * <li><a href="./fnvarExpression.html#CURDATE">CURDATE</a></li>
0327: * <li><a href="./fnvarExpression.html#CURTIME">CURTIME</a></li>
0328: * <li><a href="./fnvarExpression.html#DATABASE">DATABASE</a></li>
0329: * <li><a href="./fnvarExpression.html#DAYNAME">DAYNAME</a></li>
0330: * <li><a href="./fnvarExpression.html#DAYOFMONTH">DAYOFMONTH</a></li>
0331: * <li><a href="./fnvarExpression.html#DAYOFWEEK">DAYOFWEEK</a></li>
0332: * <li><a href="./fnvarExpression.html#DAYOFYEAR">DAYOFYEAR</a></li>
0333: * <li><a href="./fnvarExpression.html#DEGREES">DEGREES</a></li>
0334: * <li><a href="./fnvarExpression.html#DIFFERENCE">DIFFERENCE</a></li>
0335: * <li><a href="./fnvarExpression.html#EXP">EXP</a></li>
0336: * <li><a href="./fnvarExpression.html#FLOOR">FLOOR</a></li>
0337: * <li><a href="./fnvarExpression.html#HOUR">HOUR</a></li>
0338: * <li><a href="./fnvarExpression.html#IFNULL">IFNULL</a></li>
0339: * <li><a href="./fnvarExpression.html#INSERT">INSERT</a></li>
0340: * <li><a href="./fnvarExpression.html#LEFT">LEFT</a></li>
0341: * <li><a href="./fnvarExpression.html#LENGTH">LENGTH</a></li>
0342: * <li><a href="./fnvarExpression.html#LOCATE">LOCATE</a></li>
0343: * <li><a href="./fnvarExpression.html#LOG">LOG</a></li>
0344: * <li><a href="./fnvarExpression.html#LOG10">LOG10</a></li>
0345: * <li><a href="./fnvarExpression.html#LOWER">LOWER</a></li>
0346: * <li><a href="./fnvarExpression.html#LOWER">LCASE</a></li>
0347: * <li><a href="./fnvarExpression.html#LTRIM">LTRIM</a></li>
0348: * <li><a href="./fnvarExpression.html#MINUTE">MINUTE</a></li>
0349: * <li><a href="./fnvarExpression.html#MOD">MOD</a></li>
0350: * <li><a href="./fnvarExpression.html#MONTH">MONTH</a></li>
0351: * <li><a href="./fnvarExpression.html#MONTHNAME">MONTHNAME</a></li>
0352: * <li><a href="./fnvarExpression.html#NOW">NOW</a></li>
0353: * <li><a href="./fnvarExpression.html#NULLIF">NULLIF</a></li>
0354: * <li><a href="./fnvarExpression.html#OCTET_LENGTH">OCTET_LENGTH</a></li>
0355: * <li><a href="./fnvarExpression.html#PI">PI</a></li>
0356: * <li><a href="./fnvarExpression.html#POWER">POWER</a></li>
0357: * <li><a href="./fnvarExpression.html#QUARTER">QUARTER</a></li>
0358: * <li><a href="./fnvarExpression.html#RADIANS">RADIANS</a></li>
0359: * <li><a href="./fnvarExpression.html#RAND">RAND</a></li>
0360: * <li><a href="./fnvarExpression.html#REPEAT">REPEAT</a></li>
0361: * <li><a href="./fnvarExpression.html#REPLACE">REPLACE</a></li>
0362: * <li><a href="./fnvarExpression.html#RIGHT">RIGHT</a></li>
0363: * <li><a href="./fnvarExpression.html#ROUND">ROUND</a></li>
0364: * <li><a href="./fnvarExpression.html#RTRIM">RTRIM</a></li>
0365: * <li><a href="./fnvarExpression.html#SECOND">SECOND</a></li>
0366: * <li><a href="./fnvarExpression.html#SIGN">SIGN</a></li>
0367: * <li><a href="./fnvarExpression.html#SIN">SIN</a></li>
0368: * <li><a href="./fnvarExpression.html#SOUNDEX">SOUNDEX</a></li>
0369: * <li><a href="./fnvarExpression.html#SPACE">SPACE</a></li>
0370: * <li><a href="./fnvarExpression.html#SQRT">SQRT</a></li>
0371: * <li><a href="./fnvarExpression.html#SUBSTRING">SUBSTRING</a></li>
0372: * <li><a href="./fnvarExpression.html#TAN">TAN</a></li>
0373: * <li><a href="./fnvarExpression.html#TIMESTAMPADD">TIMESTAMPADD</a></li>
0374: * <li><a href="./fnvarExpression.html#TIMESTAMPDIFF">TIMESTAMPDIFF</a></li>
0375: * <li><a href="./fnvarExpression.html#TRUNCATE">TRUNCATE</a></li>
0376: * <li><a href="./fnvarExpression.html#UPPER">UPPER</a></li>
0377: * <li><a href="./fnvarExpression.html#UPPER">UCASE</a></li>
0378: * <li><a href="./fnvarExpression.html#USER">USER</a></li>
0379: * <li><a href="./fnvarExpression.html#WEEK">WEEK</a></li>
0380: * <li><a href="./fnvarExpression.html#YEAR">YEAR</a></li>
0381: * <li><a href="./fnvarExpression.html#DIGEST">DIGEST</a></li>
0382: * <li><a href="./fnvarExpression.html#LAST_INSERT_ID">LAST_INSERT_ID</a></li>
0383: * </ul>
0384: */
0385: /*
0386: <row><entry>ABS</entry><entry>Yes</entry></row>
0387: <row><entry>ASCII</entry><entry>Yes</entry></row>
0388: <row><entry>ACOS</entry><entry>Yes</entry></row>
0389: <row><entry>ASIN</entry><entry>Yes</entry></row>
0390: <row><entry>ATAN</entry><entry>Yes</entry></row>
0391: <row><entry>ATAN2</entry><entry>Yes</entry></row>
0392: <row><entry>BIT_LENGTH</entry><entry>Yes</entry></row>
0393: <row><entry>CASE</entry><entry>Yes</entry></row>
0394: <row><entry>CAST</entry><entry>Yes</entry></row>
0395: <row><entry>CEILING</entry><entry>Yes</entry></row>
0396: <row><entry>CHAR</entry><entry>Yes</entry></row>
0397: <row><entry>CHAR_LENGTH CHARACTER_LENGTH</entry><entry>Yes</entry></row>
0398: <row><entry>COALESCE</entry><entry>Yes</entry></row>
0399: <row><entry>COS</entry><entry>Yes</entry></row>
0400: <row><entry>COT</entry><entry>Yes</entry></row>
0401: <row><entry>CONCAT</entry><entry>Yes</entry></row>
0402: <row><entry>CURDATE</entry><entry>Yes</entry></row>
0403: <row><entry>CURTIME</entry><entry>Yes</entry></row>
0404: <row><entry>DATABASE</entry><entry>Yes</entry></row>
0405: <row><entry>DAYNAME</entry><entry>Yes</entry></row>
0406: <row><entry>DAYOFMONTH</entry><entry>Yes</entry></row>
0407: <row><entry>DAYOFWEEK</entry><entry>Yes</entry></row>
0408: <row><entry>DAYOFYEAR</entry><entry>Yes</entry></row>
0409: <row><entry>DEGREES</entry><entry>Yes</entry></row>
0410: <row><entry>DIFFERENCE</entry><entry>Yes</entry></row>
0411: <row><entry>EXP</entry><entry>Yes</entry></row>
0412: <row><entry>FLOOR</entry><entry>Yes</entry></row>
0413: <row><entry>HOUR</entry><entry>Yes</entry></row>
0414: <row><entry>IFNULL</entry><entry>Yes</entry></row>
0415: <row><entry>INSERT</entry><entry>Yes</entry></row>
0416: <row><entry>LEFT</entry><entry>Yes</entry></row>
0417: <row><entry>LENGTH</entry><entry>Yes</entry></row>
0418: <row><entry>LOCATE</entry><entry>Yes</entry></row>
0419: <row><entry>LOG</entry><entry>Yes</entry></row>
0420: <row><entry>LOG10</entry><entry>Yes</entry></row>
0421: <row><entry>LOWER LCASE</entry><entry>Yes</entry></row>
0422: <row><entry>LTRIM</entry><entry>Yes</entry></row>
0423: <row><entry>MINUTE</entry><entry>Yes</entry></row>
0424: <row><entry>MOD</entry><entry>Yes</entry></row>
0425: <row><entry>MONTH</entry><entry>Yes</entry></row>
0426: <row><entry>MONTHNAME</entry><entry>Yes</entry></row>
0427: <row><entry>NOW</entry><entry>Yes</entry></row>
0428: <row><entry>NULLIF</entry><entry>Yes</entry></row>
0429: <row><entry>OCTET_LENGTH</entry><entry>Yes</entry></row>
0430: <row><entry>PI</entry><entry>Yes</entry></row>
0431: <row><entry>POWER</entry><entry>Yes</entry></row>
0432: <row><entry>QUARTER</entry><entry>Yes</entry></row>
0433: <row><entry>RADIANS</entry><entry>Yes</entry></row>
0434: <row><entry>RAND</entry><entry>Yes</entry></row>
0435: <row><entry>REPEAT</entry><entry>Yes</entry></row>
0436: <row><entry>REPLACE</entry><entry>Yes</entry></row>
0437: <row><entry>RIGHT</entry><entry>Yes</entry></row>
0438: <row><entry>ROUND</entry><entry>Yes</entry></row>
0439: <row><entry>RTRIM</entry><entry>Yes</entry></row>
0440: <row><entry>SECOND</entry><entry>Yes</entry></row>
0441: <row><entry>SIGN</entry><entry>Yes</entry></row>
0442: <row><entry>SIN</entry><entry>Yes</entry></row>
0443: <row><entry>SOUNDEX</entry><entry>Yes</entry></row>
0444: <row><entry>SPACE</entry><entry>Yes</entry></row>
0445: <row><entry>SQRT</entry><entry>Yes</entry></row>
0446: <row><entry>SUBSTRING</entry><entry>Yes</entry></row>
0447: <row><entry>TAN</entry><entry>Yes</entry></row>
0448: <row><entry>TIMESTAMPADD</entry><entry>Yes</entry></row>
0449: <row><entry>TIMESTAMPDIFF</entry><entry>Yes</entry></row>
0450: <row><entry>TRUNCATE</entry><entry>Yes</entry></row>
0451: <row><entry>UPPER UCASE</entry><entry>Yes</entry></row>
0452: <row><entry>USER</entry><entry>Yes</entry></row>
0453: <row><entry>WEEK</entry><entry>Yes</entry></row>
0454: <row><entry>YEAR</entry><entry>Yes</entry></row>
0455: <row><entry>DIGEST</entry><entry>Yes</entry></row>
0456: <row><entry>LAST_INSERT_ID</entry><entry>Yes</entry></row>
0457: */
0458: //#autogen end
0459: static final Number toNumber(Value v) throws SQLException,
0460: ValueException {
0461: Object obj = v.asJavaObject();
0462: if (obj instanceof Number) {
0463: return (Number) obj;
0464: } else {
0465: throw new ValueException("Not a number: " + v);
0466: }
0467: }
0468:
0469: static final Value stringLength(Value v, int div, int mul)
0470: throws ValueException, SQLException {
0471: if (Value.isNull(v)) {
0472: return v;
0473: } else {
0474: int len;
0475: if (v instanceof ValueOctets) {
0476: len = ((ValueOctets) v).getLength() * mul;
0477: } else if (v instanceof ValueString) {
0478: len = ((ValueString) v).stringValue().length() * 16;
0479: } else if (v instanceof ValueBlob) {
0480: len = (int) (((ValueBlob) v).length() * 8 * mul);
0481: } else {
0482: throw new ValueException("BIT_LENGTH(" + v
0483: + "), wrong type");
0484: }
0485: return new ValueInteger((len + div - 1) / div);
0486: }
0487: }
0488:
0489: static final String stringValue(Value v) throws ValueException {
0490: if (!(v instanceof ValueString)) {
0491: v = v.convert(TypeVarChar.typeVarChar);
0492: }
0493: return ((ValueString) v).stringValue();
0494: }
0495:
0496: static final ValueString getString(Row values, int i)
0497: throws ValueException, SQLException {
0498: Value v = values.item(i);
0499: ValueString vs = null;
0500: if (v instanceof ValueString) {
0501: vs = (ValueString) v;
0502: } else {
0503: vs = (ValueString) (v.convert(TypeVarChar.typeVarChar));
0504: }
0505: return vs;
0506: }
0507:
0508: static final Number getNumber(Row values, int i)
0509: throws ValueException, SQLException {
0510: return toNumber(values.item(i));
0511: }
0512:
0513: static final Timestamp getTimestamp(Value v, String fn)
0514: throws ValueException, SQLException {
0515: if (!(v instanceof ValueTimestamp)) {
0516: throw new ValueException("fn called with "
0517: + "non-TIMESTAMP argument: "
0518: + v.getType().getTypeName());
0519: }
0520: Timestamp t = (Timestamp) ((ValueTimestamp) v).asJavaObject();
0521: return t;
0522: }
0523:
0524: static final int getMonths(Timestamp t) {
0525: Calendar c = Calendar.getInstance();
0526: c.setTime(t);
0527: return c.get(Calendar.YEAR) * 12 + c.get(Calendar.MONTH);
0528: }
0529:
0530: /**
0531: * Generic implementation of time field extraction from DATE, TIME,
0532: * TIMESTAMP and INTERVAL components
0533: */
0534: public Value getTimeComponent(Value v, int field, int offset,
0535: boolean time) throws ValueException, SQLException {
0536: Value ret = null;
0537: if (Value.isNull(v)) {
0538: ret = v;
0539: } else if (v instanceof ValueInterval) {
0540: ret = ((ValueInterval) v).getTimeComponent(field);
0541: } else {
0542: Calendar c = Calendar.getInstance();
0543: boolean ok = v instanceof ValueTimestamp;
0544: if (!ok) {
0545: if (time)
0546: ok = v instanceof ValueTime;
0547: else
0548: ok = v instanceof ValueDate;
0549: }
0550: if (!ok) {
0551: throw new ValueException(uname + "() called with "
0552: + "non-" + (time ? "TIME" : "DATE")
0553: + " argument: " + v.getType().getTypeName()
0554: + "(" + v.getClass().getName() + ")");
0555: }
0556: Date d = new Date(((ValueDateTime) v).getTime());
0557: c.setTime(d);
0558: ret = new ValueInteger(c.get(field) + offset);
0559: }
0560: return ret;
0561:
0562: }
0563:
0564: public FunctionExpression() {
0565: }
0566:
0567: public FunctionExpression(String name, VectorExpression args) {
0568: this .name = name;
0569: this .uname = name.toUpperCase();
0570: this .args = args;
0571: }
0572:
0573: public void invert() {
0574: not = !not;
0575: }
0576:
0577: public int rank() {
0578: return 0;
0579: }
0580:
0581: /*{fnvarExpression.xml-100}
0582: * <section name="Functions">
0583: */
0584: /*{fnvarExpression.xml-990}
0585: * </section>
0586: */
0587:
0588: public Value getValue(Session session, Cursor cursor)
0589: throws SQLException {
0590: Integer fi = (Integer) functions.get(uname);
0591: Row values = args.getValues(session, cursor);
0592: Value v = values.size() > 0 ? values.item(1) : null;
0593: Value ret = null;
0594: if (fi != null)
0595: switch (fi.intValue()) {
0596: case fnABS:
0597: /*{fnvarExpression.xml-105}
0598: * <subsection name="ABS">
0599: *
0600: * <h4>Syntax</h4>
0601: * <code>ABS(<i>numeric value</i>)</code>
0602: *
0603: * <h4>Description</h4>
0604: * <p><code>ABS</code> returns the <i>absolute value</i> of the
0605: * numeric argument. The numeric argument may be of any integral,
0606: * exact numeric or approximate numeric type, and the result will
0607: * be of the same type.</p>
0608: * </subsection>
0609: */
0610: ret = v;
0611: if (!Value.isNull(ret)
0612: && Value.boolOp(Op.LT, ret, ValueInteger.ZERO)) {
0613: ret = ret.unop(Op.MINUS);
0614: }
0615: break;
0616:
0617: case fnASCII:
0618: /*{fnvarExpression.xml-110}
0619: * <subsection name="ASCII">
0620: *
0621: * <h4>Syntax</h4>
0622: * <code>ASCII(<i>character string</i>)</code>
0623: *
0624: * <h4>Description</h4>
0625: * <p><code>ASCII</code> returns the integer ASCII code value
0626: * of the first character in the string.</p>
0627: * </subsection>
0628: */
0629: if (Value.isNull(v)) {
0630: ret = v;
0631: } else {
0632: ret = new ValueInteger(
0633: stringValue(v).charAt(0) & 0xff);
0634: }
0635: break;
0636:
0637: case fnACOS:
0638: /*{fnvarExpression.xml-120}
0639: * <subsection name="ACOS">
0640: *
0641: * <h4>Syntax</h4>
0642: * <code>ACOS(<i>numeric value</i>)</code>
0643: *
0644: * <h4>Description</h4>
0645: * <p><code>ACOS</code>returns the arc cosine of an angle
0646: * expressed in radians.</p>
0647: * </subsection>
0648: */
0649: if (Value.isNull(v)) {
0650: ret = v;
0651: } else {
0652: ret = new ValueDouble(Math.acos(toNumber(v)
0653: .doubleValue()));
0654: }
0655: break;
0656:
0657: case fnASIN:
0658: /*{fnvarExpression.xml-130}
0659: * <subsection name="ASIN">
0660: *
0661: * <h4>Syntax</h4>
0662: * <code>ASIN(<i>numeric value</i>)</code>
0663: *
0664: * <h4>Description</h4>
0665: * <p><code>ASIN</code>returns the arc sine of an angle
0666: * expressed in radians.</p>
0667: * </subsection>
0668: */
0669: if (Value.isNull(v)) {
0670: ret = v;
0671: } else {
0672: ret = new ValueDouble(Math.asin(toNumber(v)
0673: .doubleValue()));
0674: }
0675: break;
0676:
0677: case fnATAN:
0678: /*{fnvarExpression.xml-140}
0679: * <subsection name="ATAN">
0680: *
0681: * <h4>Syntax</h4>
0682: * <code>ATAN(<i>numeric value</i>)</code>
0683: *
0684: * <h4>Description</h4>
0685: * <p><code>ATAN</code>returns the arc tangent of an angle
0686: * expressed in radians.</p>
0687: * </subsection>
0688: */
0689: if (Value.isNull(v)) {
0690: ret = v;
0691: } else {
0692: ret = new ValueDouble(Math.atan(toNumber(v)
0693: .doubleValue()));
0694: }
0695: break;
0696:
0697: case fnATAN2:
0698: /*{fnvarExpression.xml-150}
0699: * <subsection name="ATAN2">
0700: *
0701: * <h4>Syntax</h4>
0702: * <code>ATAN2(<i>y</i>, <i>x</i>)</code>
0703: *
0704: * <h4>Description</h4>
0705: * <p><code>ATAN2</code>converts the specified rectangular
0706: * coordinates to polar and returns the theta component of the
0707: * point (<i>r</i>, <i>theta</i>) in polar coordianate space
0708: * that correspondes to the point (<i>x</i>, <i>y</i>) in
0709: * rectangular space.</p>
0710: * </subsection>
0711: */
0712: if (Value.isNull(v)) {
0713: ret = v;
0714: } else {
0715: double y = toNumber(v).doubleValue();
0716: double x = toNumber(values.item(2)).doubleValue();
0717: ret = new ValueDouble(Math.atan2(y, x));
0718: }
0719: break;
0720:
0721: case fnBIT_LENGTH:
0722: /*{fnvarExpression.xml-160}
0723: * <subsection name="BIT_LENGTH">
0724: *
0725: * <h4>Syntax</h4>
0726: * <code>BIT_LENGTH(<i>character or bit string</i>)</code>
0727: *
0728: * <h4>Description</h4>
0729: * <p><code>BIT_LENGTH</code> returns the length of the string
0730: * argument in bits.</p>
0731: * </subsection>
0732: */
0733: ret = stringLength(v, 1, 1);
0734: break;
0735:
0736: case fnCASE:
0737: /*{fnvarExpression.xml-163}
0738: * <subsection name="CASE">
0739: *
0740: * <h4>Syntax</h4>
0741: * <code>CASE ( WHEN <i>condition</i> THEN <i>value</i> )*
0742: * ( ELSE <i>else-value</i> )?
0743: * END
0744: *</code>
0745: *
0746: * <h4>Description</h4>
0747: * <p>The value of the <code>CASE</code> expression is the
0748: * value of the '<code>THEN</code> <i>value</i>' clause
0749: * for the first '<code>WHEN</code> <i>condition</i>' which
0750: * evaluates to <code><b>TRUE</b></code>.</p>
0751: * <p>If none of the <code>WHEN</code> clauses evaluate to
0752: * true, the value returned is that of the '<code>ELSE</code>
0753: * <i>else-value</i>' clause, or <code>NULL</code>.</p>
0754: * </subsection>
0755: */
0756: ret = null;
0757: for (int i = 1; ret == null && i < values.size(); i += 2) {
0758: Value when = values.item(i);
0759: if (Value.isTrue(when)) {
0760: ret = values.item(i + 1);
0761: }
0762: }
0763: if (ret == null && ((values.size() % 2) == 1)) {
0764: ret = values.item(values.size());
0765: }
0766: if (ret == null) {
0767: ret = ValueNull.valueNull;
0768: }
0769: break;
0770:
0771: case fnCAST:
0772: /*{fnvarExpression.xml-165}
0773: * <subsection name="CAST">
0774: *
0775: * <h4>Syntax</h4>
0776: * <code>CAST <i>expression</i> AS <i>datatype</i></code>
0777: *
0778: * <h4>Description</h4>
0779: * <p>Converts <i>expression</i> to <i>datatype</i>, where
0780: * <i>datatype</i> is one of the following types:
0781: * <ul>
0782: * <li>INT OR INTEGER</li>
0783: * <li>SMALLINT</li>
0784: * <li>TINYINT</li>
0785: * <li>BIGINT</li>
0786: * <li>VARCHAR</li>
0787: * <li>CHAR</li>
0788: * <li>DECIMAL</li>
0789: * <li>DEC</li>
0790: * <li>NUMERIC</li>
0791: * <li>REAL</li>
0792: * <li>DOUBLE</li>
0793: * <li>FLOAT</li>
0794: * <li>BOOLEAN</li>
0795: * <li>BIT</li>
0796: * <li>BINARY</li>
0797: * <li>BIT VARYING</li>
0798: * <li>VARBINARY</li>
0799: * <li>BLOB</li>
0800: * <li>BINARY LARGE OBJECT</li>
0801: * <li>DATE</li>
0802: * <li>TIME</li>
0803: * <li>TIMESTAMP</li>
0804: * <li>INTERVAL</li>
0805: * </ul>
0806: * </p>
0807: * </subsection>
0808: */
0809: if (Value.isNull(v)) {
0810: ret = v;
0811: } else {
0812: ValueType vt = (ValueType) values.item(2);
0813: Type type = vt.getType();
0814: if (type == null) {
0815: throw new ValueException(
0816: "Can't convert value to type: " + type);
0817: }
0818: ret = type.convert(v);
0819: }
0820: break;
0821:
0822: case fnCEILING:
0823: /*{fnvarExpression.xml-170}
0824: * <subsection name="CEILING">
0825: *
0826: * <h4>Syntax</h4>
0827: * <code>CEILING(<i>numeric value</i>)</code>
0828: *
0829: * <h4>Description</h4>
0830: * <p><code>CEILING</code>returns the least integer which is
0831: * greater or equal to the specified numeric value.</p>
0832: * </subsection>
0833: */
0834: if (Value.isNull(v)) {
0835: ret = v;
0836: } else {
0837: ret = new ValueDouble(Math.ceil(toNumber(v)
0838: .doubleValue()));
0839: }
0840: break;
0841:
0842: case fnCHAR:
0843: /*{fnvarExpression.xml-180}
0844: * <subsection name="CHAR">
0845: *
0846: * <h4>Syntax</h4>
0847: * <code>CHAR(<i>ascii code value</i>)</code>
0848: *
0849: * <h4>Description</h4>
0850: * <p><code>CHAR</code>returns a string of length one, containing
0851: * the character represented by the specified ASCII code value,
0852: * as an integer in the range 0-255.</p>
0853: * </subsection>
0854: */
0855: if (Value.isNull(v)) {
0856: ret = v;
0857: } else {
0858: int n = toNumber(v).intValue();
0859: if (n < 0 || n > 255) {
0860: throw new ValueException("CHAR(" + n
0861: + "), not an ASCII value");
0862: }
0863: char[] cv = new char[1];
0864: cv[0] = (char) n;
0865: ret = new ValueString(new String(cv));
0866: }
0867: break;
0868:
0869: case fnCHAR_LENGTH:
0870: /*{fnvarExpression.xml-190}
0871: * <subsection name="CHARACTER_LENGTH">
0872: *
0873: * <h4>Syntax</h4>
0874: * <code>CHAR_LENGTH(<i>character or bit string</i>)</code>
0875: * <code>CHARACTER_LENGTH(<i>character or bit string</i>)</code>
0876: *
0877: * <h4>Description</h4>
0878: * <p>If the argument is a character string,
0879: * <code>CHAR_LENGTH</code> and
0880: * <code>CHARACTER_LENGTH</code>
0881: * return the length of the string argument in characters,
0882: * otherwise,
0883: * they return the number of octets in the argument.</p>
0884: * </subsection>
0885: */
0886: ret = stringLength(v, 16, 2);
0887: break;
0888:
0889: case fnCOS:
0890: /*{fnvarExpression.xml-200}
0891: * <subsection name="COS">
0892: *
0893: * <h4>Syntax</h4>
0894: * <code>COS(<i>numeric value</i>)</code>
0895: *
0896: * <h4>Description</h4>
0897: * <p><code>COS</code>returns the cosine of an angle
0898: * expressed in radians.</p>
0899: * </subsection>
0900: */
0901: if (Value.isNull(v)) {
0902: ret = v;
0903: } else {
0904: ret = new ValueDouble(Math.cos(toNumber(v)
0905: .doubleValue()));
0906: }
0907: break;
0908:
0909: case fnCOALESCE:
0910: /*{fnvarExpression.xml-205}
0911: * <subsection name="COALESCE">
0912: *
0913: * <h4>Syntax</h4>
0914: * <code>COS(<i>expression</i>, ...)</code>
0915: *
0916: * <h4>Description</h4>
0917: * <p><code>COALESCE</code>returns the first non-null value in
0918: * the expression list, or NULL if all values in the list are
0919: * NULL.</p>
0920: * </subsection>
0921: */
0922: ret = ValueNull.valueNull;
0923: for (int i = 1; i <= values.size(); i++) {
0924: Value vt = values.item(i);
0925: if (!Value.isNull(vt)) {
0926: ret = v;
0927: break;
0928: }
0929: }
0930: break;
0931:
0932: case fnCOT:
0933: /*{fnvarExpression.xml-210}
0934: * <subsection name="COT">
0935: *
0936: * <h4>Syntax</h4>
0937: * <code>COT(<i>numeric value</i>)</code>
0938: *
0939: * <h4>Description</h4>
0940: * <p><code>COT</code>returns the cotangent of an angle
0941: * expressed in radians.</p>
0942: * </subsection>
0943: */
0944: if (Value.isNull(v)) {
0945: ret = v;
0946: } else {
0947: double x = toNumber(v).doubleValue();
0948: ret = new ValueDouble(Math.cos(x) / Math.sin(x));
0949: }
0950: break;
0951:
0952: case fnCONCAT:
0953: /*{fnvarExpression.xml-220}
0954: * <subsection name="CONCAT">
0955: *
0956: * <h4>Syntax</h4>
0957: * <code>CONCAT(<i>string1</i>, <i>string2</i>)</code>
0958: *
0959: * <h4>Description</h4>
0960: * <p><code>CONCAT</code> returns a string formed by concatenating
0961: * <i>string1</i> with <i>string2</i>.</p>
0962: * </subsection>
0963: */
0964: if (Value.isNull(v) || Value.isNull(values.item(2))) {
0965: ret = ValueNull.valueNull;
0966: } else {
0967: ValueString s1 = getString(values, 1);
0968: ValueString s2 = getString(values, 2);
0969: ret = new ValueString(s1.stringValue()
0970: + s2.stringValue());
0971: }
0972: break;
0973:
0974: case fnCURDATE:
0975: /*{fnvarExpression.xml-230}
0976: * <subsection name="CURDATE">
0977: *
0978: * <h4>Syntax</h4>
0979: * <code>CURDATE()</code>
0980: *
0981: * <h4>Description</h4>
0982: * <p><code>CURDATE</code> returns the current date as a
0983: * <code>DATE</code> value.</p>
0984: * </subsection>
0985: */
0986: ret = new ValueDate(System.currentTimeMillis());
0987: break;
0988:
0989: case fnCURTIME:
0990: /*{fnvarExpression.xml-240}
0991: * <subsection name="CURTIME">
0992: *
0993: * <h4>Syntax</h4>
0994: * <code>CURTIME()</code>
0995: *
0996: * <h4>Description</h4>
0997: * <p><code>CURTIME</code> returns the current local time as a
0998: * <code>TIME</code> value.</p>
0999: * </subsection>
1000: */
1001: ret = new ValueTime(System.currentTimeMillis());
1002: break;
1003:
1004: case fnDATABASE:
1005: /*{fnvarExpression.xml-245}
1006: * <subsection name="DATABASE">
1007: *
1008: * <h4>Syntax</h4>
1009: * <code>DATABASE()</code>
1010: *
1011: * <h4>Description</h4>
1012: * <p><code>DATABASE</code> returns the URL of the current
1013: * database.</p>
1014: * </subsection>
1015: */
1016: ret = new ValueString(session.getDatabase().getURL());
1017: break;
1018:
1019: case fnDAYNAME:
1020: /*{fnvarExpression.xml-250}
1021: * <subsection name="DAYNAME">
1022: *
1023: * <h4>Syntax</h4>
1024: * <code>DAYNAME(<i>date</i>)</code>
1025: *
1026: * <h4>Description</h4>
1027: * <p><code>DAYNAME</code> returns the a string representing
1028: * the day component of <i>date</i>.</p>
1029: * </subsection>
1030: */
1031: if (Value.isNull(v)) {
1032: ret = v;
1033: } else {
1034: if (!(v instanceof ValueDate || v instanceof ValueTimestamp)) {
1035: throw new ValueException(
1036: "DAYNAME() called with "
1037: + "non-date argument");
1038: }
1039: Date d = new Date(((ValueDateTime) v).getTime());
1040: synchronized (dayNameFmt) {
1041: ret = new ValueString(dayNameFmt.format(d));
1042: }
1043: }
1044: break;
1045:
1046: case fnDAYOFMONTH:
1047: /*{fnvarExpression.xml-260}
1048: * <subsection name="DAYOFMONTH">
1049: *
1050: * <h4>Syntax</h4>
1051: * <code>DAYOFMONTH(<i>date</i>)</code>
1052: *
1053: * <h4>Description</h4>
1054: * <p><code>DAYOFMONTH</code> returns an integer in the range
1055: * 1-31 representing the day of the month in <i>date</i>.</p>
1056: * </subsection>
1057: */
1058: ret = getTimeComponent(v, Calendar.DAY_OF_MONTH, 0,
1059: false);
1060: break;
1061:
1062: case fnDAYOFWEEK:
1063: /*{fnvarExpression.xml-270}
1064: * <subsection name="DAYOFWEEK">
1065: *
1066: * <h4>Syntax</h4>
1067: * <code>DAYOFWEEK(<i>date</i>)</code>
1068: *
1069: * <h4>Description</h4>
1070: * <p><code>DAYOFWEEK</code> returns an integer in the range
1071: * 1-7 representing the day of the week in <i>date</i>,
1072: * where '1' represents Sunday.</p>
1073: * </subsection>
1074: */
1075: ret = getTimeComponent(v, Calendar.DAY_OF_WEEK, 0,
1076: false);
1077: break;
1078:
1079: case fnDAYOFYEAR:
1080: /*{fnvarExpression.xml-280}
1081: * <subsection name="DAYOFYEAR">
1082: *
1083: * <h4>Syntax</h4>
1084: * <code>DAYOFYEAR(<i>date</i>)</code>
1085: *
1086: * <h4>Description</h4>
1087: * <p><code>DAYOFYEAR</code> returns an integer in the range
1088: * 1-366 representing the day of the year in <i>date</i>.</p>
1089: * </subsection>
1090: */
1091: ret = getTimeComponent(v, Calendar.DAY_OF_YEAR, 0,
1092: false);
1093: break;
1094:
1095: case fnDEGREES:
1096: /*{fnvarExpression.xml-290}
1097: * <subsection name="DEGREES">
1098: *
1099: * <h4>Syntax</h4>
1100: * <code>DEGREES(<i>numeric value</i>)</code>
1101: *
1102: * <h4>Description</h4>
1103: * <p><code>DEGREES</code>converts radians to degrees</p>
1104: * </subsection>
1105: */
1106: if (Value.isNull(v)) {
1107: ret = v;
1108: } else {
1109: ret = new ValueDouble(Math.toDegrees(toNumber(v)
1110: .doubleValue()));
1111: }
1112: break;
1113:
1114: case fnDIFFERENCE:
1115: /*{fnvarExpression.xml-300}
1116: * <subsection name="DIFFERENCE">
1117: *
1118: * <h4>Syntax</h4>
1119: * <code>DIFFERENCE(<i>string1</i>, <i>string2</i>)</code>
1120: *
1121: * <h4>Description</h4>
1122: * <p><code>DIFFERENCE</code> returns difference between the
1123: * soundex functions of the two arguments. It does so by
1124: * comparing the two soundex codes character by character
1125: * and returning the count of identical characters. Higher
1126: * values therefore indicate a closer match.</p>
1127: * </subsection>
1128: */
1129: if (Value.isNull(v)) {
1130: ret = v;
1131: } else {
1132: String s1 = getString(values, 1).stringValue();
1133: String s2 = getString(values, 2).stringValue();
1134: ret = new ValueInteger(Soundex.difference(s1, s2));
1135: }
1136: break;
1137:
1138: case fnEXP:
1139: /*{fnvarExpression.xml-310}
1140: * <subsection name="EXP">
1141: *
1142: * <h4>Syntax</h4>
1143: * <code>EXP(<i>numeric value</i>)</code>
1144: *
1145: * <h4>Description</h4>
1146: * <p><code>EXP</code>returns the exponential value, i.e.
1147: * <i>e</i> raised to the power of the argument.</p>
1148: * </subsection>
1149: */
1150: if (Value.isNull(v)) {
1151: ret = v;
1152: } else {
1153: ret = new ValueDouble(Math.exp(toNumber(v)
1154: .doubleValue()));
1155: }
1156: break;
1157:
1158: case fnFLOOR:
1159: /*{fnvarExpression.xml-320}
1160: * <subsection name="FLOOR">
1161: *
1162: * <h4>Syntax</h4>
1163: * <code>FLOOR(<i>numeric value</i>)</code>
1164: *
1165: * <h4>Description</h4>
1166: * <p><code>FLOOR</code>returns the greatest integer which is
1167: * less or equal to the specified numeric value.</p>
1168: * </subsection>
1169: */
1170: if (Value.isNull(v)) {
1171: ret = v;
1172: } else {
1173: ret = new ValueDouble(Math.floor(toNumber(v)
1174: .doubleValue()));
1175: }
1176: break;
1177:
1178: case fnHOUR:
1179: /*{fnvarExpression.xml-330}
1180: * <subsection name="HOUR">
1181: *
1182: * <h4>Syntax</h4>
1183: * <code>HOUR(<i>time</i>)</code>
1184: *
1185: * <h4>Description</h4>
1186: * <p><code>HOUR</code> returns an integer in the range
1187: * 0-23 representing the hour component of the specified
1188: * time value</p>
1189: * </subsection>
1190: */
1191: ret = getTimeComponent(v, Calendar.HOUR_OF_DAY, 0, true);
1192: break;
1193:
1194: case fnIFNULL:
1195: /*{fnvarExpression.xml-340}
1196: * <subsection name="IFNULL">
1197: *
1198: * <h4>Syntax</h4>
1199: * <code>
1200: * IFNULL(<i>expression</i>, <i>value</i>)
1201: * </code>
1202: *
1203: * <h4>Description</h4>
1204: * <p><code>IFNULL</code> returns <i>expression</i>, unless
1205: * <i>expression</i> is <code>NULL</code>, in which case
1206: * it returns <i>value</i>.</p>
1207: * </subsection>
1208: */
1209: if (Value.isNull(v)) {
1210: ret = values.item(2);
1211: } else {
1212: ret = v;
1213: }
1214: break;
1215:
1216: case fnINSERT:
1217: /*{fnvarExpression.xml-345}
1218: * <subsection name="INSERT">
1219: *
1220: * <h4>Syntax</h4>
1221: * <code>
1222: * INSERT(<i>string1</i>, <i>start</i>,
1223: * <i>length</i>, <i>string2</i>)
1224: * </code>
1225: *
1226: * <h4>Description</h4>
1227: * <p><code>INSERT</code> returns a new string formed by deleting
1228: * <i>length</i> characters from <i>string1</i> starting at
1229: * <i>start</i>, and inserting <i>string2</i> into <i>string1</i>
1230: * at <i>start</i>.</p>
1231: * </subsection>
1232: */
1233: {
1234: try {
1235: String s1 = getString(values, 1).stringValue();
1236: int start = getNumber(values, 2).intValue();
1237: int len = getNumber(values, 3).intValue();
1238: String s2 = getString(values, 4).stringValue();
1239: StringBuffer sb = new StringBuffer(s1.substring(0,
1240: start));
1241: sb.append(s2);
1242: sb.append(s1.substring(start + len));
1243: ret = new ValueString(sb.toString());
1244: } catch (ValueException e) {
1245: if (Value.isNull(v) || Value.isNull(values.item(2))
1246: || Value.isNull(values.item(3))
1247: || Value.isNull(values.item(4))) {
1248: ret = ValueNull.valueNull;
1249: } else {
1250: throw e;
1251: }
1252: }
1253: break;
1254: }
1255:
1256: case fnLEFT:
1257: /*{fnvarExpression.xml-350}
1258: * <subsection name="LEFT">
1259: *
1260: * <h4>Syntax</h4>
1261: * <code>
1262: * LEFT(<i>string</i>, <i>count</i>)
1263: * </code>
1264: *
1265: * <h4>Description</h4>
1266: * <p><code>LEFT</code> returns a new string consisting of
1267: * the <i>count</i> leftmost characters of <i>string</i>.</p>
1268: * </subsection>
1269: */
1270: if (Value.isNull(v) || Value.isNull(values.item(2))) {
1271: ret = ValueNull.valueNull;
1272: } else {
1273: String s = getString(values, 1).stringValue();
1274: int count = getNumber(values, 2).intValue();
1275: ret = new ValueString(s.substring(0, count));
1276: }
1277: break;
1278:
1279: case fnLENGTH:
1280: /*{fnvarExpression.xml-360}
1281: * <subsection name="LENGTH">
1282: *
1283: * <h4>Syntax</h4>
1284: * <code>
1285: * LENGTH(<i>string</i>)
1286: * </code>
1287: *
1288: * <h4>Description</h4>
1289: * <p><code>LENGTH</code> returns the number of characters
1290: * in <i>string</i>, not counting trailing spaces.</p>
1291: * </subsection>
1292: */
1293: if (Value.isNull(v)) {
1294: ret = v;
1295: } else {
1296: String s = getString(values, 1).stringValue();
1297: int len = s.length() - 1;
1298: while (len >= 0 && Character.isSpace(s.charAt(len)))
1299: len--;
1300: ret = new ValueInteger(len + 1);
1301: }
1302: break;
1303:
1304: case fnLOCATE:
1305: /*{fnvarExpression.xml-370}
1306: * <subsection name="LOCATE">
1307: *
1308: * <h4>Syntax</h4>
1309: * <code>
1310: * LOCATE(<i>string1</i>, <i>string2</i>[, <i>start</i>])
1311: * </code>
1312: *
1313: * <h4>Description</h4>
1314: * <p><code>LOCATE</code> returns the position of the first
1315: * occurrence of <i>string1</i> in <i>string2</i>. Searching
1316: * begins with the first character of <i>string2</i>, unless
1317: * <i>start</i> is specified, in which case searching begins with
1318: * the character specified by <i>start</i>. 0 is returned if
1319: * the search string is not found. The position of the first
1320: * character of the 1.</p>
1321: * </subsection>
1322: */
1323: if (Value.isNull(v)
1324: || Value.isNull(values.item(2))
1325: || (values.size() > 2 && Value.isNull(values
1326: .item(3)))) {
1327: ret = ValueNull.valueNull;
1328: } else {
1329: String s1 = getString(values, 1).stringValue();
1330: String s2 = getString(values, 2).stringValue();
1331: int pos = 0;
1332: if (values.size() > 2) {
1333: pos = getNumber(values, 3).intValue() - 1;
1334: }
1335: int idx = s2.indexOf(s1, pos) + 1;
1336: ret = new ValueInteger(idx);
1337: }
1338: break;
1339:
1340: case fnLOG:
1341: /*{fnvarExpression.xml-380}
1342: * <subsection name="LOG">
1343: *
1344: * <h4>Syntax</h4>
1345: * <code>LOG(<i>numeric value</i>)</code>
1346: *
1347: * <h4>Description</h4>
1348: * <p><code>LOG</code>returns the base <i>e</i> logarithm
1349: * of the argument.</p>
1350: * </subsection>
1351: */
1352: if (Value.isNull(v)) {
1353: ret = v;
1354: } else {
1355: ret = new ValueDouble(Math.log(toNumber(v)
1356: .doubleValue()));
1357: }
1358: break;
1359:
1360: case fnLOG10:
1361: /*{fnvarExpression.xml-390}
1362: * <subsection name="LOG10">
1363: *
1364: * <h4>Syntax</h4>
1365: * <code>LOG10(<i>numeric value</i>)</code>
1366: *
1367: * <h4>Description</h4>
1368: * <p><code>LOG</code>returns the base 10 logarithm
1369: * of the argument.</p>
1370: * </subsection>
1371: */
1372: if (Value.isNull(v)) {
1373: ret = v;
1374: } else {
1375: ret = new ValueDouble(Math.log(toNumber(v)
1376: .doubleValue())
1377: / Math.log(10.0));
1378: }
1379: break;
1380:
1381: case fnLOWER:
1382: /*{fnvarExpression.xml-400}
1383: * <subsection name="LOWER">
1384: *
1385: * <h4>Syntax</h4>
1386: * <code>LOWER(<i>character string</i>)</code>
1387: * <code>LCASE(<i>character string</i>)</code>
1388: *
1389: * <h4>Description</h4>
1390: * <p><code>LOWER</code> and <code>LCASE</code> return a string with
1391: * each upper-case letter in
1392: * the string argument replaced by its corresponding lower-case
1393: * letter.</p>
1394: * </subsection>
1395: */
1396: if (Value.isNull(v)) {
1397: ret = v;
1398: } else {
1399: String s = getString(values, 1).stringValue();
1400: ret = new ValueString(s.toLowerCase());
1401: }
1402: break;
1403:
1404: case fnLTRIM:
1405: /*{fnvarExpression.xml-410}
1406: * <subsection name="LTRIM">
1407: *
1408: * <h4>Syntax</h4>
1409: * <code>LTRIM(<i>string</i>)</code>
1410: *
1411: * <h4>Description</h4>
1412: * <p><code>LTRIM</code> removes leading whitespace from
1413: * <i>string</i>.</p>
1414: * </subsection>
1415: */
1416: if (Value.isNull(v)) {
1417: ret = v;
1418: } else {
1419: String s = getString(values, 1).stringValue();
1420: int i = 0;
1421: while (i < s.length()
1422: && Character.isWhitespace(s.charAt(i))) {
1423: i++;
1424: }
1425: if (i > 0)
1426: s = s.substring(i);
1427: ret = new ValueString(s);
1428: }
1429: break;
1430:
1431: case fnMINUTE:
1432: /*{fnvarExpression.xml-420}
1433: * <subsection name="MINUTE">
1434: *
1435: * <h4>Syntax</h4>
1436: * <code>MINUTE(<i>time</i>)</code>
1437: *
1438: * <h4>Description</h4>
1439: * <p><code>MINUTE</code> returns an integer in the range
1440: * 0-59 representing the minute component of the specified
1441: * time value</p>
1442: * </subsection>
1443: */
1444: ret = getTimeComponent(v, Calendar.MINUTE, 0, true);
1445: break;
1446:
1447: case fnMOD:
1448: /*{fnvarExpression.xml-430}
1449: * <subsection name="MOD">
1450: *
1451: * <h4>Syntax</h4>
1452: * <code>MOD(<i>integer1</i>, <i>integer2</i>)</code>
1453: *
1454: * <h4>Description</h4>
1455: * <p><code>MOD</code> returns the remainder from the
1456: * division <i>integer1</i>/<i>integer2</i>.</p>
1457: * </subsection>
1458: */
1459: if (Value.isNull(v)) {
1460: ret = v;
1461: } else {
1462: long x = toNumber(v).longValue();
1463: long y = toNumber(values.item(2)).longValue();
1464: long m = x % y;
1465: if (m < Integer.MAX_VALUE && m > Integer.MIN_VALUE) {
1466: ret = new ValueInteger((int) m);
1467: } else {
1468: ret = new ValueLong(m);
1469: }
1470: }
1471: break;
1472:
1473: case fnMONTH:
1474: /*{fnvarExpression.xml-440}
1475: * <subsection name="MONTH">
1476: *
1477: * <h4>Syntax</h4>
1478: * <code>MONTH(<i>date</i>)</code>
1479: *
1480: * <h4>Description</h4>
1481: * <p><code>MONTH</code> returns an integer in the range
1482: * 1-12 representing the month in <i>date</i>.</p>
1483: * </subsection>
1484: */
1485: ret = getTimeComponent(v, Calendar.MONTH, 1, false);
1486: break;
1487:
1488: case fnMONTHNAME:
1489: /*{fnvarExpression.xml-450}
1490: * <subsection name="MONTHNAME">
1491: *
1492: * <h4>Syntax</h4>
1493: * <code>MONTHNAME(<i>date</i>)</code>
1494: *
1495: * <h4>Description</h4>
1496: * <p><code>MONTHNAME</code> returns the a string representing
1497: * the month component of <i>date</i>.</p>
1498: * </subsection>
1499: */
1500: if (Value.isNull(v)) {
1501: ret = v;
1502: } else {
1503: if (!(v instanceof ValueDate || v instanceof ValueTimestamp)) {
1504: throw new ValueException(
1505: "MONTHNAME() called with "
1506: + "non-date argument");
1507: }
1508: Date d = new Date(((ValueDateTime) v).getTime());
1509: synchronized (monthNameFmt) {
1510: ret = new ValueString(monthNameFmt.format(d));
1511: }
1512: }
1513: break;
1514:
1515: case fnNOW:
1516: /*{fnvarExpression.xml-460}
1517: * <subsection name="NOW">
1518: *
1519: * <h4>Syntax</h4>
1520: * <code>NOW()</code>
1521: *
1522: * <h4>Description</h4>
1523: * <p><code>NOW</code> returns the current date and time as a
1524: * <code>TIMESTAMP</code> value.</p>
1525: * </subsection>
1526: */
1527: ret = new ValueTimestamp(System.currentTimeMillis());
1528: break;
1529:
1530: case fnNULLIF:
1531: /*{fnvarExpression.xml-465}
1532: * <subsection name="NULLIF">
1533: *
1534: * <h4>Syntax</h4>
1535: * <code>
1536: * NULLIF(<i>value1</i>, <i>value2</i>)
1537: * </code>
1538: *
1539: * <h4>Description</h4>
1540: * <p><code>NULLIF</code> returns <code>NULL</code> if
1541: * <i>value1</i> is equal to <i>value2</i>, otherwise
1542: * it returns <i>value1</i>.</p>
1543: * </subsection>
1544: */
1545: if (Value.boolOp(Op.EQ, v, values.item(2))) {
1546: ret = ValueNull.valueNull;
1547: } else {
1548: ret = v;
1549: }
1550: break;
1551:
1552: case fnOCTET_LENGTH:
1553: /*{fnvarExpression.xml-470}
1554: * <subsection name="OCTET_LENGTH">
1555: *
1556: * <h4>Syntax</h4>
1557: * <code>OCTET_LENGTH(<i>character or bit string</i>)</code>
1558: *
1559: * <h4>Description</h4>
1560: * <p><code>OCTET_LENGTH</code> returns the length of the string
1561: * argument in octets.</p>
1562: * </subsection>
1563: */
1564: ret = stringLength(v, 8, 1);
1565: break;
1566:
1567: case fnPI:
1568: /*{fnvarExpression.xml-480}
1569: * <subsection name="PI">
1570: *
1571: * <h4>Syntax</h4>
1572: * <code>PI()</code>
1573: *
1574: * <h4>Description</h4>
1575: * <p><code>PI</code> returns the constant <i>pi</i> as a
1576: * <code>DOUBLEd PRECISION</code> value.</p>
1577: * </subsection>
1578: */
1579: ret = new ValueDouble(Math.PI);
1580: break;
1581:
1582: case fnPOWER:
1583: /*{fnvarExpression.xml-490}
1584: * <subsection name="POWER">
1585: *
1586: * <h4>Syntax</h4>
1587: * <code>POWER(<i>number1</i>, <i>number2</i>)</code>
1588: *
1589: * <h4>Description</h4>
1590: * <p><code>POWER</code> returns the <i>number1</i> raised to
1591: * the <i>number2</i> power.</p>
1592: * </subsection>
1593: */
1594: if (Value.isNull(v) || Value.isNull(values.item(2))) {
1595: ret = ValueNull.valueNull;
1596: } else {
1597: double x = toNumber(v).doubleValue();
1598: double y = toNumber(values.item(2)).doubleValue();
1599: double res = Math.pow(x, y);
1600: if ((int) res == res) {
1601: ret = new ValueInteger((int) res);
1602: } else if ((long) res == res) {
1603: ret = new ValueLong((long) res);
1604: } else {
1605: ret = new ValueDouble(res);
1606: }
1607: }
1608: break;
1609:
1610: case fnQUARTER:
1611: /*{fnvarExpression.xml-500}
1612: * <subsection name="QUARTER">
1613: *
1614: * <h4>Syntax</h4>
1615: * <code>QUARTER(<i>time</i>)</code>
1616: *
1617: * <h4>Description</h4>
1618: * <p><code>QUARTER</code> returns an integer in the range
1619: * 1-4 representing the quarter (i.e. three-month period)
1620: * component of the date value.</p>
1621: * </subsection>
1622: */
1623: ret = getTimeComponent(v, Calendar.MONTH, 0, false);
1624: ret = new ValueInteger(((((ValueInteger) ret)
1625: .intValue() - 1) / 3) * 4 + 1);
1626: break;
1627:
1628: case fnRADIANS:
1629: /*{fnvarExpression.xml-510}
1630: * <subsection name="RADIANS">
1631: *
1632: * <h4>Syntax</h4>
1633: * <code>RADIANS(<i>numeric value</i>)</code>
1634: *
1635: * <h4>Description</h4>
1636: * <p><code>RADIANS</code>converts degrees to radians.</p>
1637: * </subsection>
1638: */
1639: if (Value.isNull(v)) {
1640: ret = v;
1641: } else {
1642: ret = new ValueDouble(Math.toRadians(toNumber(v)
1643: .doubleValue()));
1644: }
1645: break;
1646:
1647: case fnRAND:
1648: /*{fnvarExpression.xml-520}
1649: * <subsection name="RAND">
1650: *
1651: * <h4>Syntax</h4>
1652: * <code>RAND([<i>seed</i>])</code>
1653: *
1654: * <h4>Description</h4>
1655: * <p><code>RAND</code> returns a pseudo-random, uniformly
1656: * distributed number between 0 and 1. If <i>seed</i> is
1657: * specified, the random number generator is first seeded with
1658: * the <i>seed</i> value. Each <code>Connection</code> has its
1659: * own random number generator.</p>
1660: * </subsection>
1661: */
1662: if (values.size() > 0) {
1663: if (Value.isNull(v)) {
1664: ret = ValueNull.valueNull;
1665: } else {
1666: Random r = session.getRandom();
1667: r.setSeed(toNumber(v).longValue());
1668: ret = new ValueDouble(r.nextDouble());
1669: }
1670: } else {
1671: ret = new ValueDouble(session.getRandom()
1672: .nextDouble());
1673: }
1674: break;
1675:
1676: case fnREPEAT:
1677: /*{fnvarExpression.xml-530}
1678: * <subsection name="REPEAT">
1679: *
1680: * <h4>Syntax</h4>
1681: * <code>REPEAT(<i>string</i>, <i>number</i>)</code>
1682: *
1683: * <h4>Description</h4>
1684: * <p><code>REPEAT</code> returns a string formed by concatenating
1685: * <i>number</i> occurrences of <i>string</i>.</p>
1686: * </subsection>
1687: */
1688: if (Value.isNull(v) || Value.isNull(values.item(2))) {
1689: ret = ValueNull.valueNull;
1690: } else {
1691: String s = getString(values, 1).stringValue();
1692: int cnt = getNumber(values, 2).intValue();
1693: StringBuffer sb = new StringBuffer();
1694: while (cnt-- > 0) {
1695: sb.append(s);
1696: }
1697: ret = new ValueString(sb.toString());
1698: }
1699: break;
1700:
1701: case fnREPLACE:
1702: /*{fnvarExpression.xml-540}
1703: * <subsection name="REPLACE">
1704: *
1705: * <h4>Syntax</h4>
1706: * <code>REPLACE(<i>string1</i>, <i>string2</i>, <i>string3</i>)</code>
1707: *
1708: * <h4>Description</h4>
1709: * <p><code>REPLACE</code> returns a string formed by replacing
1710: * all occurrences of <i>string2</i> in <i>string1</i> with
1711: * <i>string3</i>.</p>
1712: * </subsection>
1713: */
1714: if (Value.isNull(v) || Value.isNull(values.item(2))
1715: || Value.isNull(values.item(3))) {
1716: ret = ValueNull.valueNull;
1717: } else {
1718: String s1 = getString(values, 1).stringValue();
1719: String s2 = getString(values, 2).stringValue();
1720: String s3 = getString(values, 3).stringValue();
1721: StringBuffer sb = new StringBuffer();
1722: int start = 0;
1723: while (start < s1.length()) {
1724: int pos = s1.indexOf(s2, start);
1725: if (pos < 0) {
1726: sb.append(s1.substring(start));
1727: start = s1.length();
1728: } else {
1729: sb.append(s1.substring(start, pos));
1730: sb.append(s3);
1731: start = pos + s2.length();
1732: }
1733: }
1734: return new ValueString(sb.toString());
1735: }
1736: break;
1737:
1738: case fnRIGHT:
1739: /*{fnvarExpression.xml-550}
1740: * <subsection name="RIGHT">
1741: *
1742: * <h4>Syntax</h4>
1743: * <code>
1744: * RIGHT(<i>string</i>, <i>count</i>)
1745: * </code>
1746: *
1747: * <h4>Description</h4>
1748: * <p><code>RIGHT</code> returns a new string consisting of
1749: * the <i>count</i> rightmost characters of <i>string</i>.</p>
1750: * </subsection>
1751: */
1752: if (Value.isNull(v) || Value.isNull(values.item(2))) {
1753: ret = ValueNull.valueNull;
1754: } else {
1755: String s = getString(values, 1).stringValue();
1756: int count = getNumber(values, 2).intValue();
1757: ret = new ValueString(s.substring(s.length()
1758: - count));
1759: }
1760: break;
1761:
1762: case fnROUND:
1763: /*{fnvarExpression.xml-560}
1764: * <subsection name="ROUND">
1765: *
1766: * <h4>Syntax</h4>
1767: * <code>ROUND(<i>number</i>, <i>scale</i>)</code>
1768: *
1769: * <h4>Description</h4>
1770: * <p><code>ROUND</code> returns <i>number</i>, with the
1771: * number of digits to the right of the decimal point rounded
1772: * to <i>scale</i> places.</p>
1773: * </subsection>
1774: */
1775: if (Value.isNull(v) || Value.isNull(values.item(2))) {
1776: ret = ValueNull.valueNull;
1777: } else {
1778: ValueScaledInteger si = (ValueScaledInteger) v
1779: .convert(TypeDecimal.typeDecimal);
1780: BigDecimal d = (BigDecimal) si.asJavaObject();
1781: int scale = getNumber(values, 2).intValue();
1782: d = d.setScale(scale, BigDecimal.ROUND_HALF_DOWN);
1783: ret = new ValueScaledInteger(d);
1784: }
1785: break;
1786:
1787: case fnRTRIM:
1788: /*{fnvarExpression.xml-570}
1789: * <subsection name="RTRIM">
1790: *
1791: * <h4>Syntax</h4>
1792: * <code>RTRIM(<i>string</i>)</code>
1793: *
1794: * <h4>Description</h4>
1795: * <p><code>RTRIM</code> removes trailing whitespace from
1796: * <i>string</i>.</p>
1797: * </subsection>
1798: */
1799: if (Value.isNull(v)) {
1800: ret = v;
1801: } else {
1802: String s = getString(values, 1).stringValue();
1803: int i = s.length() - 1;
1804: while (i > 0 && Character.isWhitespace(s.charAt(i))) {
1805: i--;
1806: }
1807: if (i < s.length() - 1)
1808: s = s.substring(0, i + 1);
1809: ret = new ValueString(s);
1810: }
1811: break;
1812:
1813: case fnSECOND:
1814: /*{fnvarExpression.xml-580}
1815: * <subsection name="SECOND">
1816: *
1817: * <h4>Syntax</h4>
1818: * <code>SECOND(<i>time</i>)</code>
1819: *
1820: * <h4>Description</h4>
1821: * <p><code>SECOND</code> returns an integer in the range
1822: * 0-59 representing the second component of the specified
1823: * time value.</p>
1824: * </subsection>
1825: */
1826: ret = getTimeComponent(v, Calendar.SECOND, 0, true);
1827: break;
1828:
1829: case fnSIGN:
1830: /*{fnvarExpression.xml-585}
1831: * <subsection name="SIGN">
1832: *
1833: * <h4>Syntax</h4>
1834: * <code>SIGN(<i>numeric value</i>)</code>
1835: *
1836: * <h4>Description</h4>
1837: * <p><code>SIN</code>returns -1, 0, or 1 to indicates that the
1838: * number is respectively, negative, zero, or positive.</p>
1839: * </subsection>
1840: */
1841: if (Value.isNull(v)) {
1842: ret = v;
1843: } else {
1844: double d = toNumber(v).doubleValue();
1845: int sign = 0;
1846: if (d != 0) {
1847: if (d > 0)
1848: sign = 1;
1849: else
1850: sign = -1;
1851: }
1852: ret = new ValueInteger(sign);
1853: }
1854: break;
1855:
1856: case fnSIN:
1857: /*{fnvarExpression.xml-590}
1858: * <subsection name="SIN">
1859: *
1860: * <h4>Syntax</h4>
1861: * <code>SIN(<i>numeric value</i>)</code>
1862: *
1863: * <h4>Description</h4>
1864: * <p><code>SIN</code>returns the sine of an angle
1865: * expressed in radians.</p>
1866: * </subsection>
1867: */
1868: if (Value.isNull(v)) {
1869: ret = v;
1870: } else {
1871: ret = new ValueDouble(Math.sin(toNumber(v)
1872: .doubleValue()));
1873: }
1874: break;
1875:
1876: case fnSOUNDEX:
1877: /*{fnvarExpression.xml-600}
1878: * <subsection name="SOUNDEX">
1879: *
1880: * <h4>Syntax</h4>
1881: * <code>SOUNDEX(<i>character string</i>)</code>
1882: *
1883: * <h4>Description</h4>
1884: * <p><code>SOUNDEX</code> returns the four character SOUNDEX
1885: * code corresponding to the string argument.</p>
1886: * </subsection>
1887: */
1888: {
1889: String s = getString(values, 1).stringValue();
1890: ret = new ValueString(Soundex.soundex(s));
1891: break;
1892: }
1893:
1894: case fnSPACE:
1895: /*{fnvarExpression.xml-610}
1896: * <subsection name="SPACE">
1897: *
1898: * <h4>Syntax</h4>
1899: * <code>SPACE(<i>number</i>)</code>
1900: *
1901: * <h4>Description</h4>
1902: * <p><code>SPACE</code> returns a string consisting of
1903: * <i>number</i> spaces.</p>
1904: * </subsection>
1905: */
1906: if (Value.isNull(v)) {
1907: ret = ValueNull.valueNull;
1908: } else {
1909: int count = toNumber(v).intValue();
1910: StringBuffer sb = new StringBuffer();
1911: while (count-- > 0)
1912: sb.append(' ');
1913: ret = new ValueString(sb.toString());
1914: }
1915: break;
1916:
1917: case fnSQRT:
1918: /*{fnvarExpression.xml-620}
1919: * <subsection name="SQRT">
1920: *
1921: * <h4>Syntax</h4>
1922: * <code>SQRT(<i>number</i>)</code>
1923: *
1924: * <h4>Description</h4>
1925: * <p><code>SQRT</code>returns square root of <i>number</i>.</p>
1926: * </subsection>
1927: */
1928: if (Value.isNull(v)) {
1929: ret = v;
1930: } else {
1931: ret = new ValueDouble(Math.sqrt(toNumber(v)
1932: .doubleValue()));
1933: }
1934: break;
1935:
1936: case fnSUBSTRING:
1937: /*{fnvarExpression.xml-630}
1938: * <subsection name="SUBSTRING">
1939: *
1940: * <h4>Syntax</h4>
1941: * <code>
1942: * SUBSTRING(<i>string1</i>, <i>start</i>, <i>length</i>)
1943: * </code>
1944: *
1945: * <h4>Description</h4>
1946: * <p><code>SUBSTRING</code> returns a string consisting
1947: * of the characters from <i>string</i> starting with the
1948: * character at position <i>start</i> and for <i>length</i>
1949: * characters. The first character in the source string
1950: * is a position 1.</p>
1951: * </subsection>
1952: */
1953: if (Value.isNull(v)
1954: || Value.isNull(values.item(2))
1955: || (values.size() > 2 && Value.isNull(values
1956: .item(3)))) {
1957: ret = ValueNull.valueNull;
1958: } else {
1959: String s = getString(values, 1).stringValue();
1960: int len = s.length();
1961: int end = len;
1962: int start = getNumber(values, 2).intValue() - 1;
1963: if (values.size() > 2) {
1964: end = start + getNumber(values, 3).intValue();
1965: }
1966: if (end < start) {
1967: throw new SQLException(
1968: "SUBSTRING error, string = '" + s
1969: + "', length = " + s.length()
1970: + ", start = " + start
1971: + ", end = " + end);
1972: }
1973: if (start >= len || end < 0) {
1974: s = "";
1975: } else {
1976: if (start < 0)
1977: start = 0;
1978: if (end > len)
1979: end = len;
1980: s = s.substring(start, end);
1981: }
1982: ret = new ValueString(s);
1983: }
1984: break;
1985:
1986: case fnTAN:
1987: /*{fnvarExpression.xml-640}
1988: * <subsection name="TAN">
1989: *
1990: * <h4>Syntax</h4>
1991: * <code>TAN(<i>numeric value</i>)</code>
1992: *
1993: * <h4>Description</h4>
1994: * <p><code>TAN</code>returns the tangent of an angle expressed
1995: * in radians.</p>
1996: * </subsection>
1997: */
1998: if (Value.isNull(v)) {
1999: ret = v;
2000: } else {
2001: ret = new ValueDouble(Math.tan(toNumber(v)
2002: .doubleValue()));
2003: }
2004: break;
2005:
2006: case fnTIMESTAMPADD:
2007: /*{fnvarExpression.xml-650}
2008: * <subsection name="TIMESTAMPADD">
2009: *
2010: * <h4>Syntax</h4>
2011: * <code>
2012: * TIMESTAMPADD(<i>interval</i>, <i>count</i>,
2013: * <i>timestamp</i>)
2014: * </code>
2015: *
2016: * <h4>Description</h4>
2017: * <p><code>TIMESTAMPADD</code>returns the timestamp resulting
2018: * from adding <i>count</i> of type <i>interval</i> to
2019: * <i>timestamp</i>.</p>
2020: *
2021: * <p><i>interval</i> is one of the following keywords:
2022: * <ul>
2023: * <li>SQL_TSI_FRAC_SECOND</li>
2024: * <li>SQL_TSI_SECOND</li>
2025: * <li>SQL_TSI_MINUTE</li>
2026: * <li>SQL_TSI_HOUR</li>
2027: * <li>SQL_TSI_DAY</li>
2028: * <li>SQL_TSI_WEEK</li>
2029: * <li>SQL_TSI_MONTH</li>
2030: * <li>SQL_TSI_QUARTER</li>
2031: * <li>SQL_TSI_YEAR</li>
2032: * </ul></p>
2033: * </subsection>
2034: */
2035: if (Value.isNull(v) || Value.isNull(values.item(2))
2036: || Value.isNull(values.item(3))) {
2037: ret = ValueNull.valueNull;
2038: } else {
2039: int itype = toNumber(v).intValue();
2040: int count = toNumber(values.item(2)).intValue();
2041: Timestamp t = getTimestamp(values.item(3),
2042: "TIMESTAMPADD");
2043: Calendar c = Calendar.getInstance();
2044: c.setTime(t);
2045: if (itype >= 0) {
2046: c.add(itype, count);
2047: t.setTime(c.getTime().getTime());
2048: } else
2049: switch (itype) {
2050: case TSI_FRAC_SECOND:
2051: t.setNanos(t.getNanos() + count);
2052: break;
2053: case TSI_QUARTER:
2054: c.add(Calendar.MONTH, count * 3);
2055: t.setTime(c.getTime().getTime());
2056: break;
2057: }
2058: return new ValueTimestamp(t);
2059:
2060: }
2061: break;
2062:
2063: case fnTIMESTAMPDIFF:
2064: /*{fnvarExpression.xml-660}
2065: * <subsection name="TIMESTAMPDIFF">
2066: *
2067: * <h4>Syntax</h4>
2068: * <code>
2069: * TIMESTAMPDIFF(<i>interval</i>, <i>timestamp1</i>,
2070: * <i>timestamp2</i>)
2071: * </code>
2072: *
2073: * <h4>Description</h4>
2074: * <p><code>TIMESTAMPDIFF</code>returns the the difference between
2075: * <i>timestamp2</i> and <i>timestamp1</i>, expressed in
2076: * <i>interval</i> units.</p>
2077: *
2078: * <p><i>interval</i> is one of the following keywords:
2079: * <ul>
2080: * <li>SQL_TSI_FRAC_SECOND</li>
2081: * <li>SQL_TSI_SECOND</li>
2082: * <li>SQL_TSI_MINUTE</li>
2083: * <li>SQL_TSI_HOUR</li>
2084: * <li>SQL_TSI_DAY</li>
2085: * <li>SQL_TSI_WEEK</li>
2086: * <li>SQL_TSI_MONTH</li>
2087: * <li>SQL_TSI_QUARTER</li>
2088: * <li>SQL_TSI_YEAR</li>
2089: * </ul></p>
2090: * </subsection>
2091: */
2092: if (Value.isNull(v) || Value.isNull(values.item(2))
2093: || Value.isNull(values.item(3))) {
2094: ret = ValueNull.valueNull;
2095: } else {
2096: int itype = toNumber(v).intValue();
2097: Timestamp t1 = getTimestamp(values.item(2),
2098: "TIMESTAMPDIFF");
2099: Timestamp t2 = getTimestamp(values.item(3),
2100: "TIMESTAMPDIFF");
2101: long secdiff = t2.getTime() / 1000 - t1.getTime()
2102: / 1000;
2103: long nanodiff = t2.getNanos() - t1.getNanos();
2104: int monthdiff = getMonths(t2) - getMonths(t1);
2105: long diff;
2106: switch (itype) {
2107: case TSI_FRAC_SECOND:
2108: diff = secdiff * 1000000000L + nanodiff;
2109: break;
2110: case TSI_SECOND:
2111: diff = secdiff;
2112: break;
2113: case TSI_MINUTE:
2114: diff = secdiff / 60;
2115: break;
2116: case TSI_HOUR:
2117: diff = secdiff / 3600;
2118: break;
2119: case TSI_DAY:
2120: diff = secdiff / (3600 * 24);
2121: break;
2122: case TSI_WEEK:
2123: diff = secdiff / (3600 * 24 * 7);
2124: break;
2125: case TSI_MONTH:
2126: diff = monthdiff;
2127: break;
2128: case TSI_QUARTER:
2129: diff = monthdiff / 3;
2130: break;
2131: case TSI_YEAR:
2132: diff = monthdiff / 12;
2133: break;
2134: default:
2135: throw new SQLException(
2136: "Bad argument to TIMESTAMPDIFF");
2137: }
2138: ret = new ValueLong(diff);
2139: }
2140: break;
2141:
2142: case fnTRUNCATE:
2143: /*{fnvarExpression.xml-670}
2144: * <subsection name="TRUNCATE">
2145: *
2146: * <h4>Syntax</h4>
2147: * <code>TRUNCATE(<i>number</i>, <i>scale</i>)</code>
2148: *
2149: * <h4>Description</h4>
2150: * <p><code>TRUNCATE</code> returns <i>number</i>, with the
2151: * number of digits to the right of the decimal point truncated
2152: * to <i>scale</i> places.</p>
2153: * </subsection>
2154: */
2155: if (Value.isNull(v) || Value.isNull(values.item(2))) {
2156: ret = ValueNull.valueNull;
2157: } else {
2158: ValueScaledInteger si = (ValueScaledInteger) v
2159: .convert(TypeDecimal.typeDecimal);
2160: BigDecimal d = (BigDecimal) si.asJavaObject();
2161: int scale = getNumber(values, 2).intValue();
2162: d = d.setScale(scale, BigDecimal.ROUND_DOWN);
2163: ret = new ValueScaledInteger(d);
2164: }
2165: break;
2166:
2167: case fnUPPER:
2168: /*{fnvarExpression.xml-680}
2169: * <subsection name="UPPER">
2170: *
2171: * <h4>Syntax</h4>
2172: * <code>UPPER(<i>character string</i>)</code>
2173: * <code>UCASE(<i>character string</i>)</code>
2174: *
2175: * <h4>Description</h4>
2176: * <p><code>UPPER</code> and <code>UCASE</code> return a string with each
2177: * lower-case letter in the string argument replaced by
2178: * its corresponding upper-case letter.</p>
2179: * </subsection>
2180: */
2181: if (Value.isNull(v)) {
2182: ret = v;
2183: } else {
2184: String s = getString(values, 1).stringValue();
2185: ret = new ValueString(s.toUpperCase());
2186: }
2187: break;
2188:
2189: case fnUSER:
2190: /*{fnvarExpression.xml-690}
2191: * <subsection name="USER">
2192: *
2193: * <h4>Syntax</h4>
2194: * <code>USER()</code>
2195: *
2196: * <h4>Description</h4>
2197: * <p><code>USER</code> returns the name of the current
2198: * database user.</p>
2199: * </subsection>
2200: */
2201: ret = new ValueString(session.getConnection().getAuth());
2202: break;
2203:
2204: case fnWEEK:
2205: /*{fnvarExpression.xml-700}
2206: * <subsection name="WEEK">
2207: *
2208: * <h4>Syntax</h4>
2209: * <code>WEEK(<i>date</i>)</code>
2210: *
2211: * <h4>Description</h4>
2212: * <p><code>WEEK</code> returns an integer in the range
2213: * 1-53 representing the week in <i>date</i>.</p>
2214: * </subsection>
2215: */
2216: ret = getTimeComponent(v, Calendar.WEEK_OF_YEAR, 0,
2217: false);
2218: break;
2219:
2220: case fnYEAR:
2221: /*{fnvarExpression.xml-710}
2222: * <subsection name="YEAR">
2223: *
2224: * <h4>Syntax</h4>
2225: * <code>YEAR(<i>date</i>)</code>
2226: *
2227: * <h4>Description</h4>
2228: * <p><code>YEAR</code> returns an integer
2229: * representing the year component of <i>date</i>.</p>
2230: * </subsection>
2231: */
2232: ret = getTimeComponent(v, Calendar.YEAR, 0, false);
2233: break;
2234:
2235: case fnDIGEST:
2236: /*{fnvarExpression.xml-720}
2237: * <subsection name="DIGEST">
2238: *
2239: * <h4>Syntax</h4>
2240: * <code>DIGEST(<i>string</i> [, <i>algorithm</i>])</code>
2241: *
2242: * <h4>Description</h4>
2243: * <p><code>DIGEST</code> performs a one-way hash function using
2244: * the specified digest algorithm. The default digest algorithm
2245: * is <b><code>"SHA"</code></b>.
2246: * </subsection>
2247: */
2248: if (!Value.isNull(v)) {
2249: String algorithm = "SHA";
2250: if (values.size() > 1) {
2251: algorithm = getString(values, 1).stringValue();
2252: }
2253: try {
2254: MessageDigest md = MessageDigest
2255: .getInstance(algorithm);
2256: byte[] b = md.digest(Value.bytes(v));
2257: ret = new ValueOctets(b);
2258: } catch (Exception e) {
2259: throw new ValueException(e.toString());
2260: }
2261: }
2262: break;
2263:
2264: case fnLAST_INSERT_ID:
2265: /*{fnvarExpression.xml-720}
2266: * <subsection name="LAST_INSERT_ID">
2267: *
2268: * <h4>Syntax</h4>
2269: * <code>LAST_INSERT_ID()</code>
2270: *
2271: * <h4>Description</h4>
2272: * <p><code>LAST_INSERT_ID</code> returns the id of the last
2273: * auto-numbered (i.e., resulting from a <b>WITH IDENTITY</b>
2274: * constraint) row insertion.</p>
2275: * </subsection>
2276: */
2277: ret = new ValueLong(session.getLastInsertId());
2278: break;
2279:
2280: default:
2281: throw new SQLException(
2282: "scalar function not implemented: " + name);
2283: }
2284: if (ret == null) {
2285: throw new SQLException("undefined function: " + name);
2286: }
2287: return ret;
2288: }
2289:
2290: public Type getType(Session session, Cursor cursor)
2291: throws SQLException {
2292: Integer fi = (Integer) functions.get(uname);
2293: Expression e1 = args.size() > 0 ? args.get(0) : null;
2294: Type t = e1 != null ? e1.getType(session, cursor) : TypeAny.any;
2295: if (fi != null)
2296: switch (fi.intValue()) {
2297: case fnABS:
2298: break;
2299:
2300: case fnASCII:
2301: case fnBIT_LENGTH:
2302: case fnCEILING:
2303: case fnCHAR_LENGTH:
2304: case fnDAYOFMONTH:
2305: case fnDAYOFWEEK:
2306: case fnDAYOFYEAR:
2307: case fnDIFFERENCE:
2308: case fnHOUR:
2309: case fnLENGTH:
2310: case fnLOCATE:
2311: case fnMINUTE:
2312: case fnMONTH:
2313: case fnOCTET_LENGTH:
2314: case fnQUARTER:
2315: case fnSECOND:
2316: case fnSIGN:
2317: case fnWEEK:
2318: case fnYEAR:
2319: t = TypeInt.typeInt;
2320: break;
2321:
2322: case fnACOS:
2323: case fnASIN:
2324: case fnATAN:
2325: case fnATAN2:
2326: case fnCOS:
2327: case fnCOT:
2328: case fnDEGREES:
2329: case fnEXP:
2330: case fnFLOOR:
2331: case fnLOG:
2332: case fnLOG10:
2333: case fnPI:
2334: case fnPOWER:
2335: case fnRADIANS:
2336: case fnRAND:
2337: case fnSIN:
2338: case fnSQRT:
2339: case fnTAN:
2340: t = TypeReal.typeDouble;
2341: break;
2342:
2343: case fnCASE:
2344: t = null;
2345: if ((args.size() % 2) == 1) {
2346: t = args.get(args.size() - 1).getType(session,
2347: cursor);
2348: }
2349: for (int i = 1; i < args.size(); i += 2) {
2350: Expression e = args.get(i);
2351: Type et = e.getType(session, cursor);
2352: if (t == null) {
2353: t = et;
2354: } else if (t.getJDBCType() != et.getJDBCType()) {
2355: t = TypeAny.any;
2356: }
2357: }
2358: break;
2359:
2360: case fnCAST:
2361: t = args.get(1).getType(session, cursor);
2362: break;
2363:
2364: case fnCHAR:
2365: case fnCONCAT:
2366: case fnDATABASE:
2367: case fnDAYNAME:
2368: case fnINSERT:
2369: case fnLEFT:
2370: case fnLOWER:
2371: case fnLTRIM:
2372: case fnMONTHNAME:
2373: case fnREPEAT:
2374: case fnREPLACE:
2375: case fnRIGHT:
2376: case fnRTRIM:
2377: case fnSOUNDEX:
2378: case fnSPACE:
2379: case fnSUBSTRING:
2380: case fnUPPER:
2381: case fnUSER:
2382: t = TypeVarChar.typeVarChar;
2383: break;
2384:
2385: case fnCOALESCE:
2386: t = TypeAny.any;
2387: break;
2388:
2389: case fnCURDATE:
2390: t = TypeDate.typeDate;
2391: break;
2392:
2393: case fnCURTIME:
2394: t = TypeTime.typeTime;
2395: break;
2396:
2397: case fnIFNULL:
2398: case fnNULLIF:
2399: // keep default t, type of first arg
2400: break;
2401:
2402: case fnMOD:
2403: case fnTIMESTAMPDIFF:
2404: case fnLAST_INSERT_ID:
2405: t = TypeBigInt.typeBigInt;
2406: break;
2407:
2408: case fnNOW:
2409: case fnTIMESTAMPADD:
2410: t = TypeTimestamp.typeTimestamp;
2411: break;
2412:
2413: case fnROUND:
2414: case fnTRUNCATE:
2415: t = TypeDecimal.typeDecimal;
2416: break;
2417:
2418: case fnDIGEST:
2419: t = TypeVarBinary.typeVarBinary;
2420: break;
2421:
2422: default:
2423: throw new SQLException(
2424: "scalar function not implemented: " + name);
2425: }
2426: return t;
2427: }
2428:
2429: public void visitSubExpressions(ExpressionVisitor ev) {
2430: args.visitSubExpressions(ev);
2431: }
2432:
2433: public String toString() {
2434: StringBuffer sb = new StringBuffer(name);
2435: sb.append('(');
2436: sb.append(args.toString());
2437: sb.append(')');
2438: return sb.toString();
2439: }
2440:
2441: public void readExternal(ObjectInput in) throws IOException,
2442: ClassNotFoundException {
2443: name = (String) in.readObject();
2444: uname = name.toUpperCase();
2445: args = (VectorExpression) in.readObject();
2446: not = in.read() == 1;
2447: }
2448:
2449: public void writeExternal(ObjectOutput out) throws IOException {
2450: out.writeObject(name);
2451: out.writeObject(args);
2452: out.write(not ? 1 : 0);
2453: }
2454: }
|