Java API By Example, From Geeks To Geeks.

1 package com.quadcap.sql; 2 3 /* Copyright 1999 - 2003 Quadcap Software. All rights reserved. 4  * 5  * This software is distributed under the Quadcap Free Software License. 6  * This software may be used or modified for any purpose, personal or 7  * commercial. Open Source redistributions are permitted. Commercial 8  * redistribution of larger works derived from, or works which bundle 9  * this software requires a "Commercial Redistribution License"; see 10  * http://www.quadcap.com/purchase. 11  * 12  * Redistributions qualify as "Open Source" under one of the following terms: 13  * 14  * Redistributions are made at no charge beyond the reasonable cost of 15  * materials and delivery. 16  * 17  * Redistributions are accompanied by a copy of the Source Code or by an 18  * irrevocable offer to provide a copy of the Source Code for up to three 19  * years at the cost of materials and delivery. Such redistributions 20  * must allow further use, modification, and redistribution of the Source 21  * Code under substantially the same terms as this license. 22  * 23  * Redistributions of source code must retain the copyright notices as they 24  * appear in each source code file, these license terms, and the 25  * disclaimer/limitation of liability set forth as paragraph 6 below. 26  * 27  * Redistributions in binary form must reproduce this Copyright Notice, 28  * these license terms, and the disclaimer/limitation of liability set 29  * forth as paragraph 6 below, in the documentation and/or other materials 30  * provided with the distribution. 31  * 32  * The Software is provided on an "AS IS" basis. No warranty is 33  * provided that the Software is free of defects, or fit for a 34  * particular purpose. 35  * 36  * Limitation of Liability. Quadcap Software shall not be liable 37  * for any damages suffered by the Licensee or any third party resulting 38  * from use of the Software. 39  */ 40 41 import java.io.Externalizable ; 42 import java.io.IOException ; 43 import java.io.ObjectInput ; 44 import java.io.ObjectOutput ; 45 46 import java.util.Calendar ; 47 import java.util.Hashtable ; 48 import java.util.Random ; 49 50 import java.math.BigDecimal ; 51 52 import java.security.MessageDigest ; 53 54 import java.text.SimpleDateFormat ; 55 56 import java.sql.Date ; 57 import java.sql.SQLException ; 58 import java.sql.Time ; 59 import java.sql.Timestamp ; 60 61 import com.quadcap.sql.types.*; 62 63 import com.quadcap.util.text.Soundex; 64 65 import com.quadcap.util.Debug; 66 67 /** 68  * Implement function expressions. All of them. 69  * 70  * @author Stan Bailes 71  */ 72 public class FunctionExpression extends Expression implements Externalizable { 73     String name; 74     String uname; 75     VectorExpression args; 76     boolean not = false; 77     Value value = null; 78 79     //static final Calendar cal = Calendar.getInstance(); 80 static final SimpleDateFormat dayNameFmt = new SimpleDateFormat ("EEE"); 81     static final SimpleDateFormat monthNameFmt = new SimpleDateFormat ("MMM"); 82 83     static final int TSI_FRAC_SECOND = -1; 84     static final int TSI_SECOND = Calendar.SECOND; 85     static final int TSI_MINUTE = Calendar.MINUTE; 86     static final int TSI_HOUR = Calendar.HOUR; 87     static final int TSI_DAY = Calendar.DAY_OF_YEAR; 88     static final int TSI_WEEK = Calendar.WEEK_OF_YEAR; 89     static final int TSI_MONTH = Calendar.MONTH; 90     static final int TSI_QUARTER = -2; 91     static final int TSI_YEAR = Calendar.YEAR; 92 93     // map fn name to Integer fn num 94 static final Hashtable functions = new Hashtable (); 95 96 //#global ecnt; set ecnt 0 97 //#proc nxt {} { global ecnt; return [incr ecnt] } 98 //#set fns { 99 //# ABS ASCII 100 //# ACOS ASIN ATAN ATAN2 101 //# BIT_LENGTH 102 //# CASE CAST CEILING CHAR {CHAR_LENGTH CHARACTER_LENGTH} 103 //# COALESCE 104 //# COS COT CONCAT CURDATE CURTIME 105 //# DATABASE DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR DEGREES DIFFERENCE 106 //# EXP 107 //# FLOOR 108 //# HOUR 109 //# IFNULL INSERT 110 //# LEFT LENGTH LOCATE LOG LOG10 {LOWER LCASE} LTRIM 111 //# MINUTE MOD MONTH MONTHNAME 112 //# NOW NULLIF 113 //# OCTET_LENGTH 114 //# PI POWER 115 //# QUARTER 116 //# RADIANS RAND REPEAT REPLACE RIGHT ROUND RTRIM 117 //# SECOND SIGN SIN SOUNDEX SPACE SQRT SUBSTRING 118 //# TAN TIMESTAMPADD TIMESTAMPDIFF TRUNCATE 119 //# {UPPER UCASE} USER 120 //# WEEK 121 //# YEAR 122 //# DIGEST LAST_INSERT_ID 123 //#} 124 //#foreach f $fns { 125 //# set name [lindex $f 0] 126 //# set fname [format "%-24s" $name] 127 //> static final int fn${fname} = [nxt]; 128 //#} 129 //> 130 //> static { 131 //#foreach f $fns { 132 //# set name [lindex $f 0] 133 //# foreach n $f { 134 //> functions.put(\"${n}\", new Integer(fn${name})); 135 //# } 136 //#} 137 //> } 138 //> 139 //> /*{functionIndex.xml-100} 140 //> * <ul> 141 //#foreach f $fns { 142 //# set name [lindex $f 0] 143 //# foreach n $f { 144 //> * <li><a HREF=\"./fnvarExpression.html#${name}\">${n}</a></li> 145 //# } 146 //#} 147 //> * </ul> 148 //> */ 149 //>/* 150 //#foreach f $fns { 151 //> <row><entry>[concat $f]</entry><entry>Yes</entry></row> 152 //#} 153 //>*/ 154 //#autogen begin 155 static final int fnABS = 1; 156     static final int fnASCII = 2; 157     static final int fnACOS = 3; 158     static final int fnASIN = 4; 159     static final int fnATAN = 5; 160     static final int fnATAN2 = 6; 161     static final int fnBIT_LENGTH = 7; 162     static final int fnCASE = 8; 163     static final int fnCAST = 9; 164     static final int fnCEILING = 10; 165     static final int fnCHAR = 11; 166     static final int fnCHAR_LENGTH = 12; 167     static final int fnCOALESCE = 13; 168     static final int fnCOS = 14; 169     static final int fnCOT = 15; 170     static final int fnCONCAT = 16; 171     static final int fnCURDATE = 17; 172     static final int fnCURTIME = 18; 173     static final int fnDATABASE = 19; 174     static final int fnDAYNAME = 20; 175     static final int fnDAYOFMONTH = 21; 176     static final int fnDAYOFWEEK = 22; 177     static final int fnDAYOFYEAR = 23; 178     static final int fnDEGREES = 24; 179     static final int fnDIFFERENCE = 25; 180     static final int fnEXP = 26; 181     static final int fnFLOOR = 27; 182     static final int fnHOUR = 28; 183     static final int fnIFNULL = 29; 184     static final int fnINSERT = 30; 185     static final int fnLEFT = 31; 186     static final int fnLENGTH = 32; 187     static final int fnLOCATE = 33; 188     static final int fnLOG = 34; 189     static final int fnLOG10 = 35; 190     static final int fnLOWER = 36; 191     static final int fnLTRIM = 37; 192     static final int fnMINUTE = 38; 193     static final int fnMOD = 39; 194     static final int fnMONTH = 40; 195     static final int fnMONTHNAME = 41; 196     static final int fnNOW = 42; 197     static final int fnNULLIF = 43; 198     static final int fnOCTET_LENGTH = 44; 199     static final int fnPI = 45; 200     static final int fnPOWER = 46; 201     static final int fnQUARTER = 47; 202     static final int fnRADIANS = 48; 203     static final int fnRAND = 49; 204     static final int fnREPEAT = 50; 205     static final int fnREPLACE = 51; 206     static final int fnRIGHT = 52; 207     static final int fnROUND = 53; 208     static final int fnRTRIM = 54; 209     static final int fnSECOND = 55; 210     static final int fnSIGN = 56; 211     static final int fnSIN = 57; 212     static final int fnSOUNDEX = 58; 213     static final int fnSPACE = 59; 214     static final int fnSQRT = 60; 215     static final int fnSUBSTRING = 61; 216     static final int fnTAN = 62; 217     static final int fnTIMESTAMPADD = 63; 218     static final int fnTIMESTAMPDIFF = 64; 219     static final int fnTRUNCATE = 65; 220     static final int fnUPPER = 66; 221     static final int fnUSER = 67; 222     static final int fnWEEK = 68; 223     static final int fnYEAR = 69; 224     static final int fnDIGEST = 70; 225     static final int fnLAST_INSERT_ID = 71; 226 227     static { 228         functions.put("ABS", new Integer (fnABS)); 229         functions.put("ASCII", new Integer (fnASCII)); 230         functions.put("ACOS", new Integer (fnACOS)); 231         functions.put("ASIN", new Integer (fnASIN)); 232         functions.put("ATAN", new Integer (fnATAN)); 233         functions.put("ATAN2", new Integer (fnATAN2)); 234         functions.put("BIT_LENGTH", new Integer (fnBIT_LENGTH)); 235         functions.put("CASE", new Integer (fnCASE)); 236         functions.put("CAST", new Integer (fnCAST)); 237         functions.put("CEILING", new Integer (fnCEILING)); 238         functions.put("CHAR", new Integer (fnCHAR)); 239         functions.put("CHAR_LENGTH", new Integer (fnCHAR_LENGTH)); 240         functions.put("CHARACTER_LENGTH", new Integer (fnCHAR_LENGTH)); 241         functions.put("COALESCE", new Integer (fnCOALESCE)); 242         functions.put("COS", new Integer (fnCOS)); 243         functions.put("COT", new Integer (fnCOT)); 244         functions.put("CONCAT", new Integer (fnCONCAT)); 245         functions.put("CURDATE", new Integer (fnCURDATE)); 246         functions.put("CURTIME", new Integer (fnCURTIME)); 247         functions.put("DATABASE", new Integer (fnDATABASE)); 248         functions.put("DAYNAME", new Integer (fnDAYNAME)); 249         functions.put("DAYOFMONTH", new Integer (fnDAYOFMONTH)); 250         functions.put("DAYOFWEEK", new Integer (fnDAYOFWEEK)); 251         functions.put("DAYOFYEAR", new Integer (fnDAYOFYEAR)); 252         functions.put("DEGREES", new Integer (fnDEGREES)); 253         functions.put("DIFFERENCE", new Integer (fnDIFFERENCE)); 254         functions.put("EXP", new Integer (fnEXP)); 255         functions.put("FLOOR", new Integer (fnFLOOR)); 256         functions.put("HOUR", new Integer (fnHOUR)); 257         functions.put("IFNULL", new Integer (fnIFNULL)); 258         functions.put("INSERT", new Integer (fnINSERT)); 259         functions.put("LEFT", new Integer (fnLEFT)); 260         functions.put("LENGTH", new Integer (fnLENGTH)); 261         functions.put("LOCATE", new Integer (fnLOCATE)); 262         functions.put("LOG", new Integer (fnLOG)); 263         functions.put("LOG10", new Integer (fnLOG10)); 264         functions.put("LOWER", new Integer (fnLOWER)); 265         functions.put("LCASE", new Integer (fnLOWER)); 266         functions.put("LTRIM", new Integer (fnLTRIM)); 267         functions.put("MINUTE", new Integer (fnMINUTE)); 268         functions.put("MOD", new Integer (fnMOD)); 269         functions.put("MONTH", new Integer (fnMONTH)); 270         functions.put("MONTHNAME", new Integer (fnMONTHNAME)); 271         functions.put("NOW", new Integer (fnNOW)); 272         functions.put("NULLIF", new Integer (fnNULLIF)); 273         functions.put("OCTET_LENGTH", new Integer (fnOCTET_LENGTH)); 274         functions.put("PI", new Integer (fnPI)); 275         functions.put("POWER", new Integer (fnPOWER)); 276         functions.put("QUARTER", new Integer (fnQUARTER)); 277         functions.put("RADIANS", new Integer (fnRADIANS)); 278         functions.put("RAND", new Integer (fnRAND)); 279         functions.put("REPEAT", new Integer (fnREPEAT)); 280         functions.put("REPLACE", new Integer (fnREPLACE)); 281         functions.put("RIGHT", new Integer (fnRIGHT)); 282         functions.put("ROUND", new Integer (fnROUND)); 283         functions.put("RTRIM", new Integer (fnRTRIM)); 284         functions.put("SECOND", new Integer (fnSECOND)); 285         functions.put("SIGN", new Integer (fnSIGN)); 286         functions.put("SIN", new Integer (fnSIN)); 287         functions.put("SOUNDEX", new Integer (fnSOUNDEX)); 288         functions.put("SPACE", new Integer (fnSPACE)); 289         functions.put("SQRT", new Integer (fnSQRT)); 290         functions.put("SUBSTRING", new Integer (fnSUBSTRING)); 291         functions.put("TAN", new Integer (fnTAN)); 292         functions.put("TIMESTAMPADD", new Integer (fnTIMESTAMPADD)); 293         functions.put("TIMESTAMPDIFF", new Integer (fnTIMESTAMPDIFF)); 294         functions.put("TRUNCATE", new Integer (fnTRUNCATE)); 295         functions.put("UPPER", new Integer (fnUPPER)); 296         functions.put("UCASE", new Integer (fnUPPER)); 297         functions.put("USER", new Integer (fnUSER)); 298         functions.put("WEEK", new Integer (fnWEEK)); 299         functions.put("YEAR", new Integer (fnYEAR)); 300         functions.put("DIGEST", new Integer (fnDIGEST)); 301         functions.put("LAST_INSERT_ID", new Integer (fnLAST_INSERT_ID)); 302     } 303 304     /*{functionIndex.xml-100} 305      * <ul> 306      * <li><a HREF="./fnvarExpression.html#ABS">ABS</a></li> 307      * <li><a HREF="./fnvarExpression.html#ASCII">ASCII</a></li> 308      * <li><a HREF="./fnvarExpression.html#ACOS">ACOS</a></li> 309      * <li><a HREF="./fnvarExpression.html#ASIN">ASIN</a></li> 310      * <li><a HREF="./fnvarExpression.html#ATAN">ATAN</a></li> 311      * <li><a HREF="./fnvarExpression.html#ATAN2">ATAN2</a></li> 312      * <li><a HREF="./fnvarExpression.html#BIT_LENGTH">BIT_LENGTH</a></li> 313      * <li><a HREF="./fnvarExpression.html#CASE">CASE</a></li> 314      * <li><a HREF="./fnvarExpression.html#CAST">CAST</a></li> 315      * <li><a HREF="./fnvarExpression.html#CEILING">CEILING</a></li> 316      * <li><a HREF="./fnvarExpression.html#CHAR">CHAR</a></li> 317      * <li><a HREF="./fnvarExpression.html#CHAR_LENGTH">CHAR_LENGTH</a></li> 318      * <li><a HREF="./fnvarExpression.html#CHAR_LENGTH">CHARACTER_LENGTH</a></li> 319      * <li><a HREF="./fnvarExpression.html#COALESCE">COALESCE</a></li> 320      * <li><a HREF="./fnvarExpression.html#COS">COS</a></li> 321      * <li><a HREF="./fnvarExpression.html#COT">COT</a></li> 322      * <li><a HREF="./fnvarExpression.html#CONCAT">CONCAT</a></li> 323      * <li><a HREF="./fnvarExpression.html#CURDATE">CURDATE</a></li> 324      * <li><a HREF="./fnvarExpression.html#CURTIME">CURTIME</a></li> 325      * <li><a HREF="./fnvarExpression.html#DATABASE">DATABASE</a></li> 326      * <li><a HREF="./fnvarExpression.html#DAYNAME">DAYNAME</a></li> 327      * <li><a HREF="./fnvarExpression.html#DAYOFMONTH">DAYOFMONTH</a></li> 328      * <li><a HREF="./fnvarExpression.html#DAYOFWEEK">DAYOFWEEK</a></li> 329      * <li><a HREF="./fnvarExpression.html#DAYOFYEAR">DAYOFYEAR</a></li> 330      * <li><a HREF="./fnvarExpression.html#DEGREES">DEGREES</a></li> 331      * <li><a HREF="./fnvarExpression.html#DIFFERENCE">DIFFERENCE</a></li> 332      * <li><a HREF="./fnvarExpression.html#EXP">EXP</a></li> 333      * <li><a HREF="./fnvarExpression.html#FLOOR">FLOOR</a></li> 334      * <li><a HREF="./fnvarExpression.html#HOUR">HOUR</a></li> 335      * <li><a HREF="./fnvarExpression.html#IFNULL">IFNULL</a></li> 336      * <li><a HREF="./fnvarExpression.html#INSERT">INSERT</a></li> 337      * <li><a HREF="./fnvarExpression.html#LEFT">LEFT</a></li> 338      * <li><a HREF="./fnvarExpression.html#LENGTH">LENGTH</a></li> 339      * <li><a HREF="./fnvarExpression.html#LOCATE">LOCATE</a></li> 340      * <li><a HREF="./fnvarExpression.html#LOG">LOG</a></li> 341      * <li><a HREF="./fnvarExpression.html#LOG10">LOG10</a></li> 342      * <li><a HREF="./fnvarExpression.html#LOWER">LOWER</a></li> 343      * <li><a HREF="./fnvarExpression.html#LOWER">LCASE</a></li> 344      * <li><a HREF="./fnvarExpression.html#LTRIM">LTRIM</a></li> 345      * <li><a HREF="./fnvarExpression.html#MINUTE">MINUTE</a></li> 346      * <li><a HREF="./fnvarExpression.html#MOD">MOD</a></li> 347      * <li><a HREF="./fnvarExpression.html#MONTH">MONTH</a></li> 348      * <li><a HREF="./fnvarExpression.html#MONTHNAME">MONTHNAME</a></li> 349      * <li><a HREF="./fnvarExpression.html#NOW">NOW</a></li> 350      * <li><a HREF="./fnvarExpression.html#NULLIF">NULLIF</a></li> 351      * <li><a HREF="./fnvarExpression.html#OCTET_LENGTH">OCTET_LENGTH</a></li> 352      * <li><a HREF="./fnvarExpression.html#PI">PI</a></li> 353      * <li><a HREF="./fnvarExpression.html#POWER">POWER</a></li> 354      * <li><a HREF="./fnvarExpression.html#QUARTER">QUARTER</a></li> 355      * <li><a HREF="./fnvarExpression.html#RADIANS">RADIANS</a></li> 356      * <li><a HREF="./fnvarExpression.html#RAND">RAND</a></li> 357      * <li><a HREF="./fnvarExpression.html#REPEAT">REPEAT</a></li> 358      * <li><a HREF="./fnvarExpression.html#REPLACE">REPLACE</a></li> 359      * <li><a HREF="./fnvarExpression.html#RIGHT">RIGHT</a></li> 360      * <li><a HREF="./fnvarExpression.html#ROUND">ROUND</a></li> 361      * <li><a HREF="./fnvarExpression.html#RTRIM">RTRIM</a></li> 362      * <li><a HREF="./fnvarExpression.html#SECOND">SECOND</a></li> 363      * <li><a HREF="./fnvarExpression.html#SIGN">SIGN</a></li> 364      * <li><a HREF="./fnvarExpression.html#SIN">SIN</a></li> 365      * <li><a HREF="./fnvarExpression.html#SOUNDEX">SOUNDEX</a></li> 366      * <li><a HREF="./fnvarExpression.html#SPACE">SPACE</a></li> 367      * <li><a HREF="./fnvarExpression.html#SQRT">SQRT</a></li> 368      * <li><a HREF="./fnvarExpression.html#SUBSTRING">SUBSTRING</a></li> 369      * <li><a HREF="./fnvarExpression.html#TAN">TAN</a></li> 370      * <li><a HREF="./fnvarExpression.html#TIMESTAMPADD">TIMESTAMPADD</a></li> 371      * <li><a HREF="./fnvarExpression.html#TIMESTAMPDIFF">TIMESTAMPDIFF</a></li> 372      * <li><a HREF="./fnvarExpression.html#TRUNCATE">TRUNCATE</a></li> 373      * <li><a HREF="./fnvarExpression.html#UPPER">UPPER</a></li> 374      * <li><a HREF="./fnvarExpression.html#UPPER">UCASE</a></li> 375      * <li><a HREF="./fnvarExpression.html#USER">USER</a></li> 376      * <li><a HREF="./fnvarExpression.html#WEEK">WEEK</a></li> 377      * <li><a HREF="./fnvarExpression.html#YEAR">YEAR</a></li> 378      * <li><a HREF="./fnvarExpression.html#DIGEST">DIGEST</a></li> 379      * <li><a HREF="./fnvarExpression.html#LAST_INSERT_ID">LAST_INSERT_ID</a></li> 380      * </ul> 381      */ 382 /* 383   <row><entry>ABS</entry><entry>Yes</entry></row> 384   <row><entry>ASCII</entry><entry>Yes</entry></row> 385   <row><entry>ACOS</entry><entry>Yes</entry></row> 386   <row><entry>ASIN</entry><entry>Yes</entry></row> 387   <row><entry>ATAN</entry><entry>Yes</entry></row> 388   <row><entry>ATAN2</entry><entry>Yes</entry></row> 389   <row><entry>BIT_LENGTH</entry><entry>Yes</entry></row> 390   <row><entry>CASE</entry><entry>Yes</entry></row> 391   <row><entry>CAST</entry><entry>Yes</entry></row> 392   <row><entry>CEILING</entry><entry>Yes</entry></row> 393   <row><entry>CHAR</entry><entry>Yes</entry></row> 394   <row><entry>CHAR_LENGTH CHARACTER_LENGTH</entry><entry>Yes</entry></row> 395   <row><entry>COALESCE</entry><entry>Yes</entry></row> 396   <row><entry>COS</entry><entry>Yes</entry></row> 397   <row><entry>COT</entry><entry>Yes</entry></row> 398   <row><entry>CONCAT</entry><entry>Yes</entry></row> 399   <row><entry>CURDATE</entry><entry>Yes</entry></row> 400   <row><entry>CURTIME</entry><entry>Yes</entry></row> 401   <row><entry>DATABASE</entry><entry>Yes</entry></row> 402   <row><entry>DAYNAME</entry><entry>Yes</entry></row> 403   <row><entry>DAYOFMONTH</entry><entry>Yes</entry></row> 404   <row><entry>DAYOFWEEK</entry><entry>Yes</entry></row> 405   <row><entry>DAYOFYEAR</entry><entry>Yes</entry></row> 406   <row><entry>DEGREES</entry><entry>Yes</entry></row> 407   <row><entry>DIFFERENCE</entry><entry>Yes</entry></row> 408   <row><entry>EXP</entry><entry>Yes</entry></row> 409   <row><entry>FLOOR</entry><entry>Yes</entry></row> 410   <row><entry>HOUR</entry><entry>Yes</entry></row> 411   <row><entry>IFNULL</entry><entry>Yes</entry></row> 412   <row><entry>INSERT</entry><entry>Yes</entry></row> 413   <row><entry>LEFT</entry><entry>Yes</entry></row> 414   <row><entry>LENGTH</entry><entry>Yes</entry></row> 415   <row><entry>LOCATE</entry><entry>Yes</entry></row> 416   <row><entry>LOG</entry><entry>Yes</entry></row> 417   <row><entry>LOG10</entry><entry>Yes</entry></row> 418   <row><entry>LOWER LCASE</entry><entry>Yes</entry></row> 419   <row><entry>LTRIM</entry><entry>Yes</entry></row> 420   <row><entry>MINUTE</entry><entry>Yes</entry></row> 421   <row><entry>MOD</entry><entry>Yes</entry></row> 422   <row><entry>MONTH</entry><entry>Yes</entry></row> 423   <row><entry>MONTHNAME</entry><entry>Yes</entry></row> 424   <row><entry>NOW</entry><entry>Yes</entry></row> 425   <row><entry>NULLIF</entry><entry>Yes</entry></row> 426   <row><entry>OCTET_LENGTH</entry><entry>Yes</entry></row> 427   <row><entry>PI</entry><entry>Yes</entry></row> 428   <row><entry>POWER</entry><entry>Yes</entry></row> 429   <row><entry>QUARTER</entry><entry>Yes</entry></row> 430   <row><entry>RADIANS</entry><entry>Yes</entry></row> 431   <row><entry>RAND</entry><entry>Yes</entry></row> 432   <row><entry>REPEAT</entry><entry>Yes</entry></row> 433   <row><entry>REPLACE</entry><entry>Yes</entry></row> 434   <row><entry>RIGHT</entry><entry>Yes</entry></row> 435   <row><entry>ROUND</entry><entry>Yes</entry></row> 436   <row><entry>RTRIM</entry><entry>Yes</entry></row> 437   <row><entry>SECOND</entry><entry>Yes</entry></row> 438   <row><entry>SIGN</entry><entry>Yes</entry></row> 439   <row><entry>SIN</entry><entry>Yes</entry></row> 440   <row><entry>SOUNDEX</entry><entry>Yes</entry></row> 441   <row><entry>SPACE</entry><entry>Yes</entry></row> 442   <row><entry>SQRT</entry><entry>Yes</entry></row> 443   <row><entry>SUBSTRING</entry><entry>Yes</entry></row> 444   <row><entry>TAN</entry><entry>Yes</entry></row> 445   <row><entry>TIMESTAMPADD</entry><entry>Yes</entry></row> 446   <row><entry>TIMESTAMPDIFF</entry><entry>Yes</entry></row> 447   <row><entry>TRUNCATE</entry><entry>Yes</entry></row> 448   <row><entry>UPPER UCASE</entry><entry>Yes</entry></row> 449   <row><entry>USER</entry><entry>Yes</entry></row> 450   <row><entry>WEEK</entry><entry>Yes</entry></row> 451   <row><entry>YEAR</entry><entry>Yes</entry></row> 452   <row><entry>DIGEST</entry><entry>Yes</entry></row> 453   <row><entry>LAST_INSERT_ID</entry><entry>Yes</entry></row> 454 */ 455 //#autogen end 456 457     static final Number toNumber(Value v) throws SQLException , ValueException { 458     Object obj = v.asJavaObject(); 459     if (obj instanceof Number ) { 460         return (Number )obj; 461     } else { 462         throw new ValueException("Not a number: " + v); 463     } 464     } 465 466     static final Value stringLength(Value v, int div, int mul) 467     throws ValueException, SQLException 468     { 469         if (Value.isNull(v)) { 470         return v; 471     } else { 472         int len; 473         if (v instanceof ValueOctets) { 474         len = ((ValueOctets)v).getLength() * mul; 475         } else if (v instanceof ValueString) { 476         len = ((ValueString)v).stringValue().length() * 16; 477         } else if (v instanceof ValueBlob) { 478         len = (int)(((ValueBlob)v).length() * 8 * mul); 479         } else { 480         throw new ValueException("BIT_LENGTH(" + v + 481                      "), wrong type"); 482         } 483         return new ValueInteger((len + div-1) / div); 484     } 485     } 486 487     static final String stringValue(Value v) throws ValueException { 488         if (!(v instanceof ValueString)) { 489             v = v.convert(TypeVarChar.typeVarChar); 490         } 491         return ((ValueString)v).stringValue(); 492     } 493 494     static final ValueString getString(Row values, int i) 495     throws ValueException, SQLException 496     { 497         Value v = values.item(i); 498         ValueString vs = null; 499         if (v instanceof ValueString) { 500             vs = (ValueString)v; 501         } else { 502             vs = (ValueString)(v.convert(TypeVarChar.typeVarChar)); 503         } 504         return vs; 505     } 506 507     static final Number getNumber(Row values, int i) 508     throws ValueException, SQLException 509     { 510     return toNumber(values.item(i)); 511     } 512      513     static final Timestamp getTimestamp(Value v, String fn) 514     throws ValueException, SQLException 515     { 516     if (!(v instanceof ValueTimestamp)) { 517         throw new ValueException("fn called with " + 518                      "non-TIMESTAMP argument: " + 519                      v.getType().getTypeName()); 520     } 521     Timestamp t = (Timestamp )((ValueTimestamp)v).asJavaObject(); 522     return t; 523     } 524 525     static final int getMonths(Timestamp t) { 526     Calendar c = Calendar.getInstance(); 527     c.setTime(t); 528     return c.get(Calendar.YEAR) * 12 + c.get(Calendar.MONTH); 529     } 530 531     /** 532      * Generic implementation of time field extraction from DATE, TIME, 533      * TIMESTAMP and INTERVAL components 534      */ 535     public Value getTimeComponent(Value v, int field, int offset, boolean time) 536     throws ValueException, SQLException 537     { 538     Value ret = null; 539     if (Value.isNull(v)) { 540         ret = v; 541     } else if (v instanceof ValueInterval) { 542             ret = ((ValueInterval)v).getTimeComponent(field); 543     } else { 544         Calendar c = Calendar.getInstance(); 545         boolean ok = v instanceof ValueTimestamp; 546         if (!ok) { 547         if (time) ok = v instanceof ValueTime; 548         else ok = v instanceof ValueDate; 549         } 550         if (!ok) { 551         throw new ValueException(uname + "() called with " + 552                      "non-" + (time ? "TIME" : "DATE") + 553                      " argument: " + 554                      v.getType().getTypeName() + 555                      "(" + v.getClass().getName() + ")"); 556         } 557         Date d = new Date (((ValueDateTime)v).getTime()); 558         c.setTime(d); 559         ret = new ValueInteger(c.get(field) + offset); 560     } 561     return ret; 562      563     } 564 565     public FunctionExpression() {} 566 567     public FunctionExpression(String name, VectorExpression args) { 568     this.name = name; 569     this.uname = name.toUpperCase(); 570     this.args = args; 571     } 572 573     public void invert() { 574     not = !not; 575     } 576 577     public int rank() { return 0; } 578 579     /*{fnvarExpression.xml-100} 580      * <section name="Functions"> 581      */ 582     /*{fnvarExpression.xml-990} 583      * </section> 584      */ 585      586        587     public Value getValue(Session session, Cursor cursor) throws SQLException { 588     Integer fi = (Integer )functions.get(uname); 589     Row values = args.getValues(session, cursor); 590     Value v = values.size() > 0 ? values.item(1) : null; 591     Value ret = null; 592     if (fi != null) switch (fi.intValue()) { 593     case fnABS: 594         /*{fnvarExpression.xml-105} 595          * <subsection name="ABS"> 596          * 597          * <h4>Syntax</h4> 598          * <code>ABS(<i>numeric value</i>)</code> 599          * 600          * <h4>Description</h4> 601          * <p><code>ABS</code> returns the <i>absolute value</i> of the 602          * numeric argument. The numeric argument may be of any integral, 603          * exact numeric or approximate numeric type, and the result will 604          * be of the same type.</p> 605              * </subsection> 606          */ 607         ret = v; 608         if (!Value.isNull(ret) && 609         Value.boolOp(Op.LT, ret, ValueInteger.ZERO)) { 610         ret = ret.unop(Op.MINUS); 611         } 612         break; 613 614     case fnASCII: 615         /*{fnvarExpression.xml-110} 616          * <subsection name="ASCII"> 617          * 618          * <h4>Syntax</h4> 619          * <code>ASCII(<i>character string</i>)</code> 620          * 621          * <h4>Description</h4> 622          * <p><code>ASCII</code> returns the integer ASCII code value 623          * of the first character in the string.</p> 624              * </subsection> 625          */ 626         if (Value.isNull(v)) { 627         ret = v; 628         } else { 629         ret = new ValueInteger(stringValue(v).charAt(0) & 0xff); 630         } 631         break; 632 633     case fnACOS: 634         /*{fnvarExpression.xml-120} 635          * <subsection name="ACOS"> 636          * 637          * <h4>Syntax</h4> 638          * <code>ACOS(<i>numeric value</i>)</code> 639          * 640          * <h4>Description</h4> 641          * <p><code>ACOS</code>returns the arc cosine of an angle 642          * expressed in radians.</p> 643              * </subsection> 644          */ 645         if (Value.isNull(v)) { 646         ret = v; 647         } else { 648         ret = new ValueDouble(Math.acos(toNumber(v).doubleValue())); 649         } 650         break; 651 652     case fnASIN: 653         /*{fnvarExpression.xml-130} 654          * <subsection name="ASIN"> 655          * 656          * <h4>Syntax</h4> 657          * <code>ASIN(<i>numeric value</i>)</code> 658          * 659          * <h4>Description</h4> 660          * <p><code>ASIN</code>returns the arc sine of an angle 661          * expressed in radians.</p> 662              * </subsection> 663          */ 664         if (Value.isNull(v)) { 665         ret = v; 666         } else { 667         ret = new ValueDouble(Math.asin(toNumber(v).doubleValue())); 668         } 669         break; 670 671     case fnATAN: 672         /*{fnvarExpression.xml-140} 673          * <subsection name="ATAN"> 674          * 675          * <h4>Syntax</h4> 676          * <code>ATAN(<i>numeric value</i>)</code> 677          * 678          * <h4>Description</h4> 679          * <p><code>ATAN</code>returns the arc tangent of an angle 680          * expressed in radians.</p> 681              * </subsection> 682          */ 683         if (Value.isNull(v)) { 684         ret = v; 685         } else { 686         ret = new ValueDouble(Math.atan(toNumber(v).doubleValue())); 687         } 688         break; 689 690     case fnATAN2: 691         /*{fnvarExpression.xml-150} 692          * <subsection name="ATAN2"> 693          * 694          * <h4>Syntax</h4> 695          * <code>ATAN2(<i>y</i>, <i>x</i>)</code> 696          * 697          * <h4>Description</h4> 698          * <p><code>ATAN2</code>converts the specified rectangular 699          * coordinates to polar and returns the theta component of the 700          * point (<i>r</i>, <i>theta</i>) in polar coordianate space 701          * that correspondes to the point (<i>x</i>, <i>y</i>) in 702          * rectangular space.</p> 703              * </subsection> 704          */ 705         if (Value.isNull(v)) { 706         ret = v; 707         } else { 708         double y = toNumber(v).doubleValue(); 709         double x = toNumber(values.item(2)).doubleValue(); 710         ret = new ValueDouble(Math.atan2(y, x)); 711         } 712         break; 713 714     case fnBIT_LENGTH: 715         /*{fnvarExpression.xml-160} 716          * <subsection name="BIT_LENGTH"> 717          * 718          * <h4>Syntax</h4> 719          * <code>BIT_LENGTH(<i>character or bit string</i>)</code> 720          * 721          * <h4>Description</h4> 722          * <p><code>BIT_LENGTH</code> returns the length of the string 723          * argument in bits.</p> 724              * </subsection> 725          */ 726         ret = stringLength(v, 1, 1); 727         break; 728 729         case fnCASE: 730         /*{fnvarExpression.xml-163} 731          * <subsection name="CASE"> 732          * 733          * <h4>Syntax</h4> 734          * <code>CASE ( WHEN <i>condition</i> THEN <i>value</i> )* 735              * ( ELSE <i>else-value</i> )? 736              * END 737              *</code> 738          * 739          * <h4>Description</h4> 740              * <p>The value of the <code>CASE</code> expression is the 741              * value of the '<code>THEN</code> <i>value</i>' clause 742              * for the first '<code>WHEN</code> <i>condition</i>' which 743              * evaluates to <code><b>TRUE</b></code>.</p> 744              * <p>If none of the <code>WHEN</code> clauses evaluate to 745              * true, the value returned is that of the '<code>ELSE</code> 746              * <i>else-value</i>' clause, or <code>NULL</code>.</p> 747              * </subsection> 748              */ 749             ret = null; 750             for (int i = 1; ret == null && i < values.size(); i += 2) { 751                 Value when = values.item(i); 752                 if (Value.isTrue(when)) { 753                     ret = values.item(i+1); 754                 } 755             } 756             if (ret == null && ((values.size() % 2) == 1)) { 757                 ret = values.item(values.size()); 758             } 759             if (ret == null) { 760                 ret = ValueNull.valueNull; 761             } 762             break; 763              764     case fnCAST: 765         /*{fnvarExpression.xml-165} 766          * <subsection name="CAST"> 767          * 768          * <h4>Syntax</h4> 769          * <code>CAST <i>expression</i> AS <i>datatype</i></code> 770          * 771          * <h4>Description</h4> 772          * <p>Converts <i>expression</i> to <i>datatype</i>, where 773          * <i>datatype</i> is one of the following types: 774          * <ul> 775          * <li>INT OR INTEGER</li> 776          * <li>SMALLINT</li> 777          * <li>TINYINT</li> 778          * <li>BIGINT</li> 779          * <li>VARCHAR</li> 780          * <li>CHAR</li> 781          * <li>DECIMAL</li> 782          * <li>DEC</li> 783          * <li>NUMERIC</li> 784          * <li>REAL</li> 785          * <li>DOUBLE</li> 786          * <li>FLOAT</li> 787          * <li>BOOLEAN</li> 788          * <li>BIT</li> 789          * <li>BINARY</li> 790          * <li>BIT VARYING</li> 791          * <li>VARBINARY</li> 792          * <li>BLOB</li> 793          * <li>BINARY LARGE OBJECT</li> 794          * <li>DATE</li> 795          * <li>TIME</li> 796          * <li>TIMESTAMP</li> 797          * <li>INTERVAL</li> 798          * </ul> 799              * </p> 800              * </subsection> 801          */ 802         if (Value.isNull(v)) { 803         ret = v; 804         } else { 805         ValueType vt = (ValueType)values.item(2); 806         Type type = vt.getType(); 807         if (type == null) { 808             throw new ValueException("Can't convert value to type: " + 809                          type); 810         } 811         ret = type.convert(v); 812         } 813         break; 814 815     case fnCEILING: 816         /*{fnvarExpression.xml-170} 817          * <subsection name="CEILING"> 818          * 819          * <h4>Syntax</h4> 820          * <code>CEILING(<i>numeric value</i>)</code> 821          * 822          * <h4>Description</h4> 823          * <p><code>CEILING</code>returns the least integer which is 824          * greater or equal to the specified numeric value.</p> 825              * </subsection> 826          */ 827         if (Value.isNull(v)) { 828         ret = v; 829         } else { 830         ret = new ValueDouble(Math.ceil(toNumber(v).doubleValue())); 831         } 832         break; 833 834     case fnCHAR: 835         /*{fnvarExpression.xml-180} 836          * <subsection name="CHAR"> 837          * 838          * <h4>Syntax</h4> 839          * <code>CHAR(<i>ascii code value</i>)</code> 840          * 841          * <h4>Description</h4> 842          * <p><code>CHAR</code>returns a string of length one, containing 843          * the character represented by the specified ASCII code value, 844          * as an integer in the range 0-255.</p> 845              * </subsection> 846          */ 847         if (Value.isNull(v)) { 848         ret = v; 849         } else { 850         int n = toNumber(v).intValue(); 851         if (n < 0 || n > 255) { 852             throw new ValueException("CHAR(" + n + 853                          "), not an ASCII value"); 854         } 855         char[] cv = new char[1]; 856         cv[0] = (char)n; 857         ret = new ValueString(new String (cv)); 858         } 859         break; 860 861     case fnCHAR_LENGTH: 862         /*{fnvarExpression.xml-190} 863          * <subsection name="CHARACTER_LENGTH"> 864          * 865          * <h4>Syntax</h4> 866          * <code>CHAR_LENGTH(<i>character or bit string</i>)</code> 867          * <code>CHARACTER_LENGTH(<i>character or bit string</i>)</code> 868          * 869          * <h4>Description</h4> 870          * <p>If the argument is a character string, 871          * <code>CHAR_LENGTH</code> and 872          * <code>CHARACTER_LENGTH</code> 873          * return the length of the string argument in characters, 874          * otherwise, 875          * they return the number of octets in the argument.</p> 876              * </subsection> 877          */ 878         ret = stringLength(v, 16, 2); 879         break; 880 881     case fnCOS: 882         /*{fnvarExpression.xml-200} 883          * <subsection name="COS"> 884          * 885          * <h4>Syntax</h4> 886          * <code>COS(<i>numeric value</i>)</code> 887          * 888          * <h4>Description</h4> 889          * <p><code>COS</code>returns the cosine of an angle 890          * expressed in radians.</p> 891              * </subsection> 892          */ 893         if (Value.isNull(v)) { 894         ret = v; 895         } else { 896         ret = new ValueDouble(Math.cos(toNumber(v).doubleValue())); 897         } 898         break; 899 900     case fnCOALESCE: 901         /*{fnvarExpression.xml-205} 902          * <subsection name="COALESCE"> 903          * 904          * <h4>Syntax</h4> 905          * <code>COS(<i>expression</i>, ...)</code> 906          * 907          * <h4>Description</h4> 908          * <p><code>COALESCE</code>returns the first non-null value in 909              * the expression list, or NULL if all values in the list are 910              * NULL.</p> 911              * </subsection> 912          */ 913             ret = ValueNull.valueNull; 914             for (int i = 1; i <= values.size(); i++) { 915                 Value vt = values.item(i); 916                 if (!Value.isNull(vt)) { 917                     ret = v; 918                     break; 919                 } 920             } 921         break; 922 923     case fnCOT: 924         /*{fnvarExpression.xml-210} 925          * <subsection name="COT"> 926          * 927          * <h4>Syntax</h4> 928          * <code>COT(<i>numeric value</i>)</code> 929          * 930          * <h4>Description</h4> 931          * <p><code>COT</code>returns the cotangent of an angle 932          * expressed in radians.</p> 933              * </subsection> 934          */ 935         if (Value.isNull(v)) { 936         ret = v; 937         } else { 938         double x = toNumber(v).doubleValue(); 939         ret = new ValueDouble(Math.cos(x) / Math.sin(x)); 940         } 941         break; 942 943     case fnCONCAT: 944         /*{fnvarExpression.xml-220} 945          * <subsection name="CONCAT"> 946          * 947          * <h4>Syntax</h4> 948          * <code>CONCAT(<i>string1</i>, <i>string2</i>)</code> 949          * 950          * <h4>Description</h4> 951          * <p><code>CONCAT</code> returns a string formed by concatenating 952          * <i>string1</i> with <i>string2</i>.</p> 953              * </subsection> 954          */ 955         if (Value.isNull(v) || Value.isNull(values.item(2))) { 956         ret = ValueNull.valueNull; 957         } else { 958         ValueString s1 = getString(values, 1); 959         ValueString s2 = getString(values, 2); 960         ret = new ValueString(s1.stringValue() + s2.stringValue()); 961         } 962         break; 963 964     case fnCURDATE: 965         /*{fnvarExpression.xml-230} 966          * <subsection name="CURDATE"> 967          * 968          * <h4>Syntax</h4> 969          * <code>CURDATE()</code> 970          * 971          * <h4>Description</h4> 972          * <p><code>CURDATE</code> returns the current date as a 973          * <code>DATE</code> value.</p> 974              * </subsection> 975          */ 976         ret = new ValueDate(System.currentTimeMillis()); 977         break; 978 979     case fnCURTIME: 980         /*{fnvarExpression.xml-240} 981          * <subsection name="CURTIME"> 982          * 983          * <h4>Syntax</h4> 984          * <code>CURTIME()</code> 985          * 986          * <h4>Description</h4> 987          * <p><code>CURTIME</code> returns the current local time as a 988          * <code>TIME</code> value.</p> 989              * </subsection> 990          */ 991         ret = new ValueTime(System.currentTimeMillis()); 992         break; 993 994     case fnDATABASE: 995         /*{fnvarExpression.xml-245} 996          * <subsection name="DATABASE"> 997          * 998          * <h4>Syntax</h4> 999          * <code>DATABASE()</code> 1000         * 1001         * <h4>Description</h4> 1002         * <p><code>DATABASE</code> returns the URL of the current 1003         * database.</p> 1004             * </subsection> 1005         */ 1006        ret = new ValueString(session.getDatabase().getURL()); 1007        break; 1008 1009    case fnDAYNAME: 1010        /*{fnvarExpression.xml-250} 1011         * <subsection name="DAYNAME"> 1012         * 1013         * <h4>Syntax</h4> 1014         * <code>DAYNAME(<i>date</i>)</code> 1015         * 1016         * <h4>Description</h4> 1017         * <p><code>DAYNAME</code> returns the a string representing 1018         * the day component of <i>date</i>.</p> 1019             * </subsection> 1020         */ 1021        if (Value.isNull(v)) { 1022        ret = v; 1023        } else { 1024        if (!(v instanceof ValueDate || 1025              v instanceof ValueTimestamp)) { 1026            throw new ValueException("DAYNAME() called with " + 1027                         "non-date argument"); 1028        } 1029        Date d = new Date (((ValueDateTime)v).getTime()); 1030                synchronized (dayNameFmt) { 1031                    ret = new ValueString(dayNameFmt.format(d)); 1032                } 1033        } 1034        break; 1035 1036    case fnDAYOFMONTH: 1037        /*{fnvarExpression.xml-260} 1038         * <subsection name="DAYOFMONTH"> 1039         * 1040         * <h4>Syntax</h4> 1041         * <code>DAYOFMONTH(<i>date</i>)</code> 1042         * 1043         * <h4>Description</h4> 1044         * <p><code>DAYOFMONTH</code> returns an integer in the range 1045         * 1-31 representing the day of the month in <i>date</i>.</p> 1046             * </subsection> 1047         */ 1048        ret = getTimeComponent(v, Calendar.DAY_OF_MONTH, 0, false); 1049        break; 1050 1051    case fnDAYOFWEEK: 1052        /*{fnvarExpression.xml-270} 1053         * <subsection name="DAYOFWEEK"> 1054         * 1055         * <h4>Syntax</h4> 1056         * <code>DAYOFWEEK(<i>date</i>)</code> 1057         * 1058         * <h4>Description</h4> 1059         * <p><code>DAYOFWEEK</code> returns an integer in the range 1060         * 1-7 representing the day of the week in <i>date</i>, 1061         * where '1' represents Sunday.</p> 1062             * </subsection> 1063         */ 1064        ret = getTimeComponent(v, Calendar.DAY_OF_WEEK, 0, false); 1065        break; 1066 1067    case fnDAYOFYEAR: 1068        /*{fnvarExpression.xml-280} 1069         * <subsection name="DAYOFYEAR"> 1070         * 1071         * <h4>Syntax</h4> 1072         * <code>DAYOFYEAR(<i>date</i>)</code> 1073         * 1074         * <h4>Description</h4> 1075         * <p><code>DAYOFYEAR</code> returns an integer in the range 1076         * 1-366 representing the day of the year in <i>date</i>.</p> 1077             * </subsection> 1078         */ 1079        ret = getTimeComponent(v, Calendar.DAY_OF_YEAR, 0, false); 1080        break; 1081 1082    case fnDEGREES: 1083        /*{fnvarExpression.xml-290} 1084         * <subsection name="DEGREES"> 1085         * 1086         * <h4>Syntax</h4> 1087         * <code>DEGREES(<i>numeric value</i>)</code> 1088         * 1089         * <h4>Description</h4> 1090         * <p><code>DEGREES</code>converts radians to degrees</p> 1091             * </subsection> 1092         */ 1093        if (Value.isNull(v)) { 1094        ret = v; 1095        } else { 1096        ret = new ValueDouble( 1097            Math.toDegrees(toNumber(v).doubleValue())); 1098        } 1099        break; 1100 1101    case fnDIFFERENCE: 1102        /*{fnvarExpression.xml-300} 1103         * <subsection name="DIFFERENCE"> 1104         * 1105         * <h4>Syntax</h4> 1106         * <code>DIFFERENCE(<i>string1</i>, <i>string2</i>)</code> 1107         * 1108         * <h4>Description</h4> 1109         * <p><code>DIFFERENCE</code> returns difference between the 1110         * soundex functions of the two arguments. It does so by 1111         * comparing the two soundex codes character by character 1112         * and returning the count of identical characters. Higher 1113         * values therefore indicate a closer match.</p> 1114             * </subsection> 1115         */ 1116        if (Value.isNull(v)) { 1117        ret = v; 1118        } else { 1119        String s1 = getString(values, 1).stringValue(); 1120        String s2 = getString(values, 2).stringValue(); 1121        ret = new ValueInteger(Soundex.difference(s1, s2)); 1122        } 1123        break; 1124 1125    case fnEXP: 1126        /*{fnvarExpression.xml-310} 1127         * <subsection name="EXP"> 1128         * 1129         * <h4>Syntax</h4> 1130         * <code>EXP(<i>numeric value</i>)</code> 1131         * 1132         * <h4>Description</h4> 1133         * <p><code>EXP</code>returns the exponential value, i.e. 1134         * <i>e</i> raised to the power of the argument.</p> 1135             * </subsection> 1136         */ 1137        if (Value.isNull(v)) { 1138        ret = v; 1139        } else { 1140        ret = new ValueDouble(Math.exp(toNumber(v).doubleValue())); 1141        } 1142        break; 1143 1144    case fnFLOOR: 1145        /*{fnvarExpression.xml-320} 1146         * <subsection name="FLOOR"> 1147         * 1148         * <h4>Syntax</h4> 1149         * <code>FLOOR(<i>numeric value</i>)</code> 1150         * 1151         * <h4>Description</h4> 1152         * <p><code>FLOOR</code>returns the greatest integer which is 1153         * less or equal to the specified numeric value.</p> 1154             * </subsection> 1155         */ 1156        if (Value.isNull(v)) { 1157        ret = v; 1158        } else { 1159        ret = new ValueDouble(Math.floor(toNumber(v).doubleValue())); 1160        } 1161        break; 1162 1163    case fnHOUR: 1164        /*{fnvarExpression.xml-330} 1165         * <subsection name="HOUR"> 1166         * 1167         * <h4>Syntax</h4> 1168         * <code>HOUR(<i>time</i>)</code> 1169         * 1170         * <h4>Description</h4> 1171         * <p><code>HOUR</code> returns an integer in the range 1172         * 0-23 representing the hour component of the specified 1173         * time value</p> 1174             * </subsection> 1175         */ 1176        ret = getTimeComponent(v, Calendar.HOUR_OF_DAY, 0, true); 1177        break; 1178 1179    case fnIFNULL: 1180        /*{fnvarExpression.xml-340} 1181         * <subsection name="IFNULL"> 1182         * 1183         * <h4>Syntax</h4> 1184         * <code> 1185         * IFNULL(<i>expression</i>, <i>value</i>) 1186         * </code> 1187         * 1188         * <h4>Description</h4> 1189         * <p><code>IFNULL</code> returns <i>expression</i>, unless 1190         * <i>expression</i> is <code>NULL</code>, in which case 1191         * it returns <i>value</i>.</p> 1192             * </subsection> 1193         */ 1194        if (Value.isNull(v)) { 1195        ret = values.item(2); 1196        } else { 1197        ret = v; 1198        } 1199        break; 1200 1201    case fnINSERT: 1202        /*{fnvarExpression.xml-345} 1203         * <subsection name="INSERT"> 1204         * 1205         * <h4>Syntax</h4> 1206         * <code> 1207         * INSERT(<i>string1</i>, <i>start</i>, 1208         * <i>length</i>, <i>string2</i>) 1209         * </code> 1210         * 1211         * <h4>Description</h4> 1212         * <p><code>INSERT</code> returns a new string formed by deleting 1213         * <i>length</i> characters from <i>string1</i> starting at 1214         * <i>start</i>, and inserting <i>string2</i> into <i>string1</i> 1215         * at <i>start</i>.</p> 1216             * </subsection> 1217         */ 1218    { 1219        try { 1220        String s1 = getString(values, 1).stringValue(); 1221        int start = getNumber(values, 2).intValue(); 1222        int len = getNumber(values, 3).intValue(); 1223        String s2 = getString(values, 4).stringValue(); 1224        StringBuffer sb = new StringBuffer (s1.substring(0, start)); 1225        sb.append(s2); 1226        sb.append(s1.substring(start+len)); 1227        ret = new ValueString(sb.toString()); 1228        } catch (ValueException e) { 1229        if (Value.isNull(v) || 1230            Value.isNull(values.item(2)) || 1231            Value.isNull(values.item(3)) || 1232            Value.isNull(values.item(4))) { 1233            ret = ValueNull.valueNull; 1234        } else { 1235            throw e; 1236        } 1237        } 1238        break; 1239    } 1240 1241    case fnLEFT: 1242        /*{fnvarExpression.xml-350} 1243         * <subsection name="LEFT"> 1244         * 1245         * <h4>Syntax</h4> 1246         * <code> 1247         * LEFT(<i>string</i>, <i>count</i>) 1248         * </code> 1249         * 1250         * <h4>Description</h4> 1251         * <p><code>LEFT</code> returns a new string consisting of 1252         * the <i>count</i> leftmost characters of <i>string</i>.</p> 1253             * </subsection> 1254         */ 1255        if (Value.isNull(v) || Value.isNull(values.item(2))) { 1256        ret = ValueNull.valueNull; 1257        } else { 1258        String s = getString(values, 1).stringValue(); 1259        int count = getNumber(values, 2).intValue(); 1260        ret = new ValueString(s.substring(0, count)); 1261        } 1262        break; 1263 1264    case fnLENGTH: 1265        /*{fnvarExpression.xml-360} 1266         * <subsection name="LENGTH"> 1267         * 1268         * <h4>Syntax</h4> 1269         * <code> 1270         * LENGTH(<i>string</i>) 1271         * </code> 1272         * 1273         * <h4>Description</h4> 1274         * <p><code>LENGTH</code> returns the number of characters 1275         * in <i>string</i>, not counting trailing spaces.</p> 1276             * </subsection> 1277         */ 1278        if (Value.isNull(v)) { 1279        ret = v; 1280        } else { 1281        String s = getString(values, 1).stringValue(); 1282        int len = s.length() - 1; 1283        while (len >= 0 && Character.isSpace(s.charAt(len))) len--; 1284        ret = new ValueInteger(len+1); 1285        } 1286        break; 1287 1288    case fnLOCATE: 1289        /*{fnvarExpression.xml-370} 1290         * <subsection name="LOCATE"> 1291         * 1292         * <h4>Syntax</h4> 1293         * <code> 1294         * LOCATE(<i>string1</i>, <i>string2</i>[, <i>start</i>]) 1295         * </code> 1296         * 1297         * <h4>Description</h4> 1298         * <p><code>LOCATE</code> returns the position of the first 1299         * occurrence of <i>string1</i> in <i>string2</i>. Searching 1300         * begins with the first character of <i>string2</i>, unless 1301         * <i>start</i> is specified, in which case searching begins with 1302         * the character specified by <i>start</i>. 0 is returned if 1303         * the search string is not found. The position of the first 1304         * character of the 1.</p> 1305             * </subsection> 1306         */ 1307        if (Value.isNull(v) || Value.isNull(values.item(2)) || 1308        (values.size() > 2 && Value.isNull(values.item(3)))) { 1309        ret = ValueNull.valueNull; 1310        } else { 1311        String s1 = getString(values, 1).stringValue(); 1312        String s2 = getString(values, 2).stringValue(); 1313        int pos = 0; 1314        if (values.size() > 2) { 1315            pos = getNumber(values, 3).intValue() - 1; 1316        } 1317        int idx = s2.indexOf(s1, pos) + 1; 1318        ret = new ValueInteger(idx); 1319        } 1320        break; 1321 1322    case fnLOG: 1323        /*{fnvarExpression.xml-380} 1324         * <subsection name="LOG"> 1325         * 1326         * <h4>Syntax</h4> 1327         * <code>LOG(<i>numeric value</i>)</code> 1328         * 1329         * <h4>Description</h4> 1330         * <p><code>LOG</code>returns the base <i>e</i> logarithm 1331         * of the argument.</p> 1332             * </subsection> 1333         */ 1334        if (Value.isNull(v)) { 1335        ret = v; 1336        } else { 1337        ret = new ValueDouble(Math.log(toNumber(v).doubleValue())); 1338        } 1339        break; 1340 1341    case fnLOG10: 1342        /*{fnvarExpression.xml-390} 1343         * <subsection name="LOG10"> 1344         * 1345         * <h4>Syntax</h4> 1346         * <code>LOG10(<i>numeric value</i>)</code> 1347         * 1348         * <h4>Description</h4> 1349         * <p><code>LOG</code>returns the base 10 logarithm 1350         * of the argument.</p> 1351             * </subsection> 1352         */ 1353        if (Value.isNull(v)) { 1354        ret = v; 1355        } else { 1356        ret = new ValueDouble(Math.log(toNumber(v).doubleValue()) / 1357                      Math.log(10.0)); 1358        } 1359        break; 1360 1361    case fnLOWER: 1362        /*{fnvarExpression.xml-400} 1363         * <subsection name="LOWER"> 1364         * 1365         * <h4>Syntax</h4> 1366         * <code>LOWER(<i>character string</i>)</code> 1367         * <code>LCASE(<i>character string</i>)</code> 1368         * 1369         * <h4>Description</h4> 1370         * <p><code>LOWER</code> and <code>LCASE</code> return a string with 1371         * each upper-case letter in 1372         * the string argument replaced by its corresponding lower-case 1373         * letter.</p> 1374             * </subsection> 1375         */ 1376        if (Value.isNull(v)) { 1377        ret = v; 1378        } else { 1379        String s = getString(values, 1).stringValue(); 1380        ret = new ValueString(s.toLowerCase()); 1381        } 1382        break; 1383 1384    case fnLTRIM: 1385        /*{fnvarExpression.xml-410} 1386         * <subsection name="LTRIM"> 1387         * 1388         * <h4>Syntax</h4> 1389         * <code>LTRIM(<i>string</i>)</code> 1390         * 1391         * <h4>Description</h4> 1392         * <p><code>LTRIM</code> removes leading whitespace from 1393         * <i>string</i>.</p> 1394             * </subsection> 1395         */ 1396        if (Value.isNull(v)) { 1397        ret = v; 1398        } else { 1399        String s = getString(values, 1).stringValue(); 1400        int i = 0; 1401        while (i < s.length() && Character.isWhitespace(s.charAt(i))) { 1402            i++; 1403        } 1404        if (i > 0) s = s.substring(i); 1405        ret = new ValueString(s); 1406        } 1407        break; 1408 1409    case fnMINUTE: 1410        /*{fnvarExpression.xml-420} 1411         * <subsection name="MINUTE"> 1412         * 1413         * <h4>Syntax</h4> 1414         * <code>MINUTE(<i>time</i>)</code> 1415         * 1416         * <h4>Description</h4> 1417         * <p><code>MINUTE</code> returns an integer in the range 1418         * 0-59 representing the minute component of the specified 1419         * time value</p> 1420             * </subsection> 1421         */ 1422        ret = getTimeComponent(v, Calendar.MINUTE, 0, true); 1423        break; 1424 1425    case fnMOD: 1426        /*{fnvarExpression.xml-430} 1427         * <subsection name="MOD"> 1428         * 1429         * <h4>Syntax</h4> 1430         * <code>MOD(<i>integer1</i>, <i>integer2</i>)</code> 1431         * 1432         * <h4>Description</h4> 1433         * <p><code>MOD</code> returns the remainder from the 1434         * division <i>integer1</i>/<i>integer2</i>.</p> 1435             * </subsection> 1436         */ 1437        if (Value.isNull(v)) { 1438        ret = v; 1439        } else { 1440        long x = toNumber(v).longValue(); 1441        long y = toNumber(values.item(2)).longValue(); 1442        long m = x % y; 1443        if (m < Integer.MAX_VALUE && m > Integer.MIN_VALUE) { 1444            ret = new ValueInteger((int)m); 1445        } else { 1446            ret = new ValueLong(m); 1447        } 1448        } 1449        break; 1450 1451    case fnMONTH: 1452        /*{fnvarExpression.xml-440} 1453         * <subsection name="MONTH"> 1454         * 1455         * <h4>Syntax</h4> 1456         * <code>MONTH(<i>date</i>)</code> 1457         * 1458         * <h4>Description</h4> 1459         * <p><code>MONTH</code> returns an integer in the range 1460         * 1-12 representing the month in <i>date</i>.</p> 1461             * </subsection> 1462         */ 1463        ret = getTimeComponent(v, Calendar.MONTH, 1, false); 1464        break; 1465 1466    case fnMONTHNAME: 1467        /*{fnvarExpression.xml-450} 1468         * <subsection name="MONTHNAME"> 1469         * 1470         * <h4>Syntax</h4> 1471         * <code>MONTHNAME(<i>date</i>)</code> 1472         * 1473         * <h4>Description</h4> 1474         * <p><code>MONTHNAME</code> returns the a string representing 1475         * the month component of <i>date</i>.</p> 1476             * </subsection> 1477         */ 1478        if (Value.isNull(v)) { 1479        ret = v; 1480        } else { 1481        if (!(v instanceof ValueDate || 1482              v instanceof ValueTimestamp)) { 1483            throw new ValueException("MONTHNAME() called with " + 1484                         "non-date argument"); 1485        } 1486        Date d = new Date (((ValueDateTime)v).getTime()); 1487                synchronized (monthNameFmt) { 1488                    ret = new ValueString(monthNameFmt.format(d)); 1489                } 1490        } 1491        break; 1492 1493    case fnNOW: 1494        /*{fnvarExpression.xml-460} 1495         * <subsection name="NOW"> 1496         * 1497         * <h4>Syntax</h4> 1498         * <code>NOW()</code> 1499         * 1500         * <h4>Description</h4> 1501         * <p><code>NOW</code> returns the current date and time as a 1502         * <code>TIMESTAMP</code> value.</p> 1503             * </subsection> 1504         */ 1505        ret = new ValueTimestamp(System.currentTimeMillis()); 1506        break; 1507 1508    case fnNULLIF: 1509        /*{fnvarExpression.xml-465} 1510         * <subsection name="NULLIF"> 1511         * 1512         * <h4>Syntax</h4> 1513         * <code> 1514         * NULLIF(<i>value1</i>, <i>value2</i>) 1515         * </code> 1516         * 1517         * <h4>Description</h4> 1518         * <p><code>NULLIF</code> returns <code>NULL</code> if 1519             * <i>value1</i> is equal to <i>value2</i>, otherwise 1520             * it returns <i>value1</i>.</p> 1521             * </subsection> 1522         */ 1523        if (Value.boolOp(Op.EQ, v, values.item(2))) { 1524        ret = ValueNull.valueNull; 1525        } else { 1526        ret = v; 1527        } 1528        break; 1529 1530    case fnOCTET_LENGTH: 1531        /*{fnvarExpression.xml-470} 1532         * <subsection name="OCTET_LENGTH"> 1533         * 1534         * <h4>Syntax</h4> 1535         * <code>OCTET_LENGTH(<i>character or bit string</i>)</code> 1536         * 1537         * <h4>Description</h4> 1538         * <p><code>OCTET_LENGTH</code> returns the length of the string 1539         * argument in octets.</p> 1540             * </subsection> 1541         */ 1542        ret = stringLength(v, 8, 1); 1543        break; 1544 1545    case fnPI: 1546        /*{fnvarExpression.xml-480} 1547         * <subsection name="PI"> 1548         * 1549         * <h4>Syntax</h4> 1550         * <code>PI()</code> 1551         * 1552         * <h4>Description</h4> 1553         * <p><code>PI</code> returns the constant <i>pi</i> as a 1554         * <code>DOUBLEd PRECISION</code> value.</p> 1555             * </subsection> 1556         */ 1557        ret = new ValueDouble(Math.PI); 1558        break; 1559 1560    case fnPOWER: 1561        /*{fnvarExpression.xml-490} 1562         * <subsection name="POWER"> 1563         * 1564         * <h4>Syntax</h4> 1565         * <code>POWER(<i>number1</i>, <i>number2</i>)</code> 1566         * 1567         * <h4>Description</h4> 1568         * <p><code>POWER</code> returns the <i>number1</i> raised to 1569         * the <i>number2</i> power.</p> 1570             * </subsection> 1571         */ 1572        if (Value.isNull(v) || Value.isNull(values.item(2))) { 1573        ret = ValueNull.valueNull; 1574        } else { 1575        double x = toNumber(v).doubleValue(); 1576        double y = toNumber(values.item(2)).doubleValue(); 1577        double res = Math.pow(x, y); 1578        if ((int)res == res) { 1579            ret = new ValueInteger((int)res); 1580        } else if ((long)res == res) { 1581            ret = new ValueLong((long)res); 1582        } else { 1583            ret = new ValueDouble(res); 1584        } 1585        } 1586        break; 1587 1588    case fnQUARTER: 1589        /*{fnvarExpression.xml-500} 1590         * <subsection name="QUARTER"> 1591         * 1592         * <h4>Syntax</h4> 1593         * <code>QUARTER(<i>time</i>)</code> 1594         * 1595         * <h4>Description</h4> 1596         * <p><code>QUARTER</code> returns an integer in the range 1597         * 1-4 representing the quarter (i.e. three-month period) 1598         * component of the date value.</p> 1599             * </subsection> 1600         */ 1601        ret = getTimeComponent(v, Calendar.MONTH, 0, false); 1602        ret = new ValueInteger(((((ValueInteger)ret).intValue()-1)/3)*4+1); 1603        break; 1604 1605    case fnRADIANS: 1606        /*{fnvarExpression.xml-510} 1607         * <subsection name="RADIANS"> 1608         * 1609         * <h4>Syntax</h4> 1610         * <code>RADIANS(<i>numeric value</i>)</code> 1611         * 1612         * <h4>Description</h4> 1613         * <p><code>RADIANS</code>converts degrees to radians.</p> 1614             * </subsection> 1615         */ 1616        if (Value.isNull(v)) { 1617        ret = v; 1618        } else { 1619        ret = new ValueDouble( 1620            Math.toRadians(toNumber(v).doubleValue()) 1621            ); 1622        } 1623        break; 1624 1625    case fnRAND: 1626        /*{fnvarExpression.xml-520} 1627         * <subsection name="RAND"> 1628         * 1629         * <h4>Syntax</h4> 1630         * <code>RAND([<i>seed</i>])</code> 1631         * 1632         * <h4>Description</h4> 1633         * <p><code>RAND</code> returns a pseudo-random, uniformly 1634         * distributed number between 0 and 1. If <i>seed</i> is 1635         * specified, the random number generator is first seeded with 1636         * the <i>seed</i> value. Each <code>Connection</code> has its 1637         * own random number generator.</p> 1638             * </subsection> 1639         */ 1640        if (values.size() > 0) { 1641        if (Value.isNull(v)) { 1642            ret = ValueNull.valueNull; 1643        } else { 1644            Random r = session.getRandom(); 1645            r.setSeed(toNumber(v).longValue()); 1646            ret = new ValueDouble(r.nextDouble()); 1647        } 1648        } else { 1649        ret = new ValueDouble(session.getRandom().nextDouble()); 1650        } 1651        break; 1652 1653    case fnREPEAT: 1654        /*{fnvarExpression.xml-530} 1655         * <subsection name="REPEAT"> 1656         * 1657         * <h4>Syntax</h4> 1658         * <code>REPEAT(<i>string</i>, <i>number</i>)</code> 1659         * 1660         * <h4>Description</h4> 1661         * <p><code>REPEAT</code> returns a string formed by concatenating 1662         * <i>number</i> occurrences of <i>string</i>.</p> 1663             * </subsection> 1664         */ 1665        if (Value.isNull(v) || Value.isNull(values.item(2))) { 1666        ret = ValueNull.valueNull; 1667        } else { 1668        String s = getString(values, 1).stringValue(); 1669        int cnt = getNumber(values, 2).intValue(); 1670        StringBuffer sb = new StringBuffer (); 1671        while (cnt-- > 0) { 1672            sb.append(s); 1673        } 1674        ret = new ValueString(sb.toString()); 1675        } 1676        break; 1677 1678    case fnREPLACE: 1679        /*{fnvarExpression.xml-540} 1680         * <subsection name="REPLACE"> 1681         * 1682         * <h4>Syntax</h4> 1683         * <code>REPLACE(<i>string1</i>, <i>string2</i>, <i>string3</i>)</code> 1684         * 1685         * <h4>Description</h4> 1686         * <p><code>REPLACE</code> returns a string formed by replacing 1687         * all occurrences of <i>string2</i> in <i>string1</i> with 1688         * <i>string3</i>.</p> 1689             * </subsection> 1690         */ 1691        if (Value.isNull(v) || Value.isNull(values.item(2)) || 1692        Value.isNull(values.item(3))) { 1693        ret = ValueNull.valueNull; 1694        } else { 1695        String s1 = getString(values, 1).stringValue(); 1696        String s2 = getString(values, 2).stringValue(); 1697        String s3 = getString(values, 3).stringValue(); 1698        StringBuffer sb = new StringBuffer (); 1699        int start = 0; 1700        while (start < s1.length()) { 1701            int pos = s1.indexOf(s2, start); 1702            if (pos < 0) { 1703            sb.append(s1.substring(start)); 1704            start = s1.length(); 1705            } else { 1706            sb.append(s1.substring(start, pos)); 1707            sb.append(s3); 1708            start = pos + s2.length(); 1709            } 1710        } 1711        return new ValueString(sb.toString()); 1712        } 1713        break; 1714 1715    case fnRIGHT: 1716        /*{fnvarExpression.xml-550} 1717         * <subsection name="RIGHT"> 1718         * 1719         * <h4>Syntax</h4> 1720         * <code> 1721         * RIGHT(<i>string</i>, <i>count</i>) 1722         * </code> 1723         * 1724         * <h4>Description</h4> 1725         * <p><code>RIGHT</code> returns a new string consisting of 1726         * the <i>count</i> rightmost characters of <i>string</i>.</p> 1727             * </subsection> 1728         */ 1729        if (Value.isNull(v) || Value.isNull(values.item(2))) { 1730        ret = ValueNull.valueNull; 1731        } else { 1732        String s = getString(values, 1).stringValue(); 1733        int count = getNumber(values, 2).intValue(); 1734        ret = new ValueString(s.substring(s.length() - count)); 1735        } 1736        break; 1737 1738    case fnROUND: 1739        /*{fnvarExpression.xml-560} 1740         * <subsection name="ROUND"> 1741         * 1742         * <h4>Syntax</h4> 1743         * <code>ROUND(<i>number</i>, <i>scale</i>)</code> 1744         * 1745         * <h4>Description</h4> 1746         * <p><code>ROUND</code> returns <i>number</i>, with the 1747         * number of digits to the right of the decimal point rounded 1748         * to <i>scale</i> places.</p> 1749             * </subsection> 1750         */ 1751        if (Value.isNull(v) || Value.isNull(values.item(2))) { 1752        ret = ValueNull.valueNull; 1753        } else { 1754        ValueScaledInteger si = 1755            (ValueScaledInteger)v.convert(TypeDecimal.typeDecimal); 1756        BigDecimal d = (BigDecimal )si.asJavaObject(); 1757        int scale = getNumber(values, 2).intValue(); 1758        d = d.setScale(scale, BigDecimal.ROUND_HALF_DOWN); 1759        ret = new ValueScaledInteger(d); 1760        } 1761        break; 1762 1763    case fnRTRIM: 1764        /*{fnvarExpression.xml-570} 1765         * <subsection name="RTRIM"> 1766         * 1767         * <h4>Syntax</h4> 1768         * <code>RTRIM(<i>string</i>)</code> 1769         * 1770         * <h4>Description</h4> 1771         * <p><code>RTRIM</code> removes trailing whitespace from 1772         * <i>string</i>.</p> 1773             * </subsection> 1774         */ 1775        if (Value.isNull(v)) { 1776        ret = v; 1777        } else { 1778        String s = getString(values, 1).stringValue(); 1779        int i = s.length() - 1; 1780        while (i > 0 && Character.isWhitespace(s.charAt(i))) { 1781            i--; 1782        } 1783        if (i < s.length() - 1) s = s.substring(0, i + 1); 1784        ret = new ValueString(s); 1785        } 1786        break; 1787 1788    case fnSECOND: 1789        /*{fnvarExpression.xml-580} 1790         * <subsection name="SECOND"> 1791         * 1792         * <h4>Syntax</h4> 1793         * <code>SECOND(<i>time</i>)</code> 1794         * 1795         * <h4>Description</h4> 1796         * <p><code>SECOND</code> returns an integer in the range 1797         * 0-59 representing the second component of the specified 1798         * time value.</p> 1799             * </subsection> 1800         */ 1801        ret = getTimeComponent(v, Calendar.SECOND, 0, true); 1802        break; 1803 1804    case fnSIGN: 1805        /*{fnvarExpression.xml-585} 1806         * <subsection name="SIGN"> 1807         * 1808         * <h4>Syntax</h4> 1809         * <code>SIGN(<i>numeric value</i>)</code> 1810         * 1811         * <h4>Description</h4> 1812         * <p><code>SIN</code>returns -1, 0, or 1 to indicates that the 1813         * number is respectively, negative, zero, or positive.</p> 1814             * </subsection> 1815         */ 1816        if (Value.isNull(v)) { 1817        ret = v; 1818        } else { 1819        double d = toNumber(v).doubleValue(); 1820        int sign = 0; 1821        if (d != 0) { 1822            if (d > 0) sign = 1; 1823            else sign = -1; 1824        } 1825        ret = new ValueInteger(sign); 1826        } 1827        break; 1828 1829    case fnSIN: 1830        /*{fnvarExpression.xml-590} 1831         * <subsection name="SIN"> 1832         * 1833         * <h4>Syntax</h4> 1834         * <code>SIN(<i>numeric value</i>)</code> 1835         * 1836         * <h4>Description</h4> 1837         * <p><code>SIN</code>returns the sine of an angle 1838         * expressed in radians.</p> 1839             * </subsection> 1840         */ 1841        if (Value.isNull(v)) { 1842        ret = v; 1843        } else { 1844        ret = new ValueDouble(Math.sin(toNumber(v).doubleValue())); 1845        } 1846        break; 1847 1848    case fnSOUNDEX: 1849        /*{fnvarExpression.xml-600} 1850         * <subsection name="SOUNDEX"> 1851         * 1852         * <h4>Syntax</h4> 1853         * <code>SOUNDEX(<i>character string</i>)</code> 1854         * 1855         * <h4>Description</h4> 1856         * <p><code>SOUNDEX</code> returns the four character SOUNDEX 1857         * code corresponding to the string argument.</p> 1858             * </subsection> 1859         */ 1860    { 1861        String s = getString(values, 1).stringValue(); 1862        ret = new ValueString(Soundex.soundex(s)); 1863        break; 1864    } 1865 1866    case fnSPACE: 1867        /*{fnvarExpression.xml-610} 1868         * <subsection name="SPACE"> 1869         * 1870         * <h4>Syntax</h4> 1871         * <code>SPACE(<i>number</i>)</code> 1872         * 1873         * <h4>Description</h4> 1874         * <p><code>SPACE</code> returns a string consisting of 1875         * <i>number</i> spaces.</p> 1876             * </subsection> 1877         */ 1878        if (Value.isNull(v)) { 1879        ret = ValueNull.valueNull; 1880        } else { 1881        int count = toNumber(v).intValue(); 1882        StringBuffer sb = new StringBuffer (); 1883        while (count-- > 0) sb.append(' '); 1884        ret = new ValueString(sb.toString()); 1885        } 1886        break; 1887 1888    case fnSQRT: 1889        /*{fnvarExpression.xml-620} 1890         * <subsection name="SQRT"> 1891         * 1892         * <h4>Syntax</h4> 1893         * <code>SQRT(<i>number</i>)</code> 1894         * 1895         * <h4>Description</h4> 1896         * <p><code>SQRT</code>returns square root of <i>number</i>.</p> 1897             * </subsection> 1898         */ 1899        if (Value.isNull(v)) { 1900        ret = v; 1901        } else { 1902        ret = new ValueDouble(Math.sqrt(toNumber(v).doubleValue())); 1903        } 1904        break; 1905 1906    case fnSUBSTRING: 1907        /*{fnvarExpression.xml-630} 1908         * <subsection name="SUBSTRING"> 1909         * 1910         * <h4>Syntax</h4> 1911         * <code> 1912         * SUBSTRING(<i>string1</i>, <i>start</i>, <i>length</i>) 1913         * </code> 1914         * 1915         * <h4>Description</h4> 1916         * <p><code>SUBSTRING</code> returns a string consisting 1917         * of the characters from <i>string</i> starting with the 1918         * character at position <i>start</i> and for <i>length</i> 1919         * characters. The first character in the source string 1920         * is a position 1.</p> 1921             * </subsection> 1922         */ 1923        if (Value.isNull(v) || Value.isNull(values.item(2)) || 1924        (values.size() > 2 && Value.isNull(values.item(3)))) { 1925        ret = ValueNull.valueNull; 1926        } else { 1927        String s = getString(values, 1).stringValue(); 1928        int len = s.length(); 1929        int end = len; 1930        int start = getNumber(values, 2).intValue() - 1; 1931        if (values.size() > 2) { 1932            end = start + getNumber(values, 3).intValue(); 1933        } 1934        if (end < start) { 1935            throw new SQLException ("SUBSTRING error, string = '" + 1936                       s + "', length = " + s.length() + 1937                       ", start = " + start + 1938                       ", end = " + end); 1939        } 1940        if (start >= len || end < 0) { 1941            s = ""; 1942        } else { 1943            if (start < 0) start = 0; 1944            if (end > len) end = len; 1945            s = s.substring(start, end); 1946        } 1947        ret = new ValueString(s); 1948        } 1949        break; 1950 1951    case fnTAN: 1952        /*{fnvarExpression.xml-640} 1953         * <subsection name="TAN"> 1954         * 1955         * <h4>Syntax</h4> 1956         * <code>TAN(<i>numeric value</i>)</code> 1957         * 1958         * <h4>Description</h4> 1959         * <p><code>TAN</code>returns the tangent of an angle expressed 1960         * in radians.</p> 1961             * </subsection> 1962         */ 1963        if (Value.isNull(v)) { 1964        ret = v; 1965        } else { 1966        ret = new ValueDouble(Math.tan(toNumber(v).doubleValue())); 1967        } 1968        break; 1969 1970    case fnTIMESTAMPADD: 1971        /*{fnvarExpression.xml-650} 1972         * <subsection name="TIMESTAMPADD"> 1973         * 1974         * <h4>Syntax</h4> 1975         * <code> 1976         * TIMESTAMPADD(<i>interval</i>, <i>count</i>, 1977         * <i>timestamp</i>) 1978         * </code> 1979         * 1980         * <h4>Description</h4> 1981         * <p><code>TIMESTAMPADD</code>returns the timestamp resulting 1982         * from adding <i>count</i> of type <i>interval</i> to 1983         * <i>timestamp</i>.</p> 1984         * 1985         * <p><i>interval</i> is one of the following keywords: 1986         * <ul> 1987         * <li>SQL_TSI_FRAC_SECOND</li> 1988         * <li>SQL_TSI_SECOND</li> 1989         * <li>SQL_TSI_MINUTE</li> 1990         * <li>SQL_TSI_HOUR</li> 1991         * <li>SQL_TSI_DAY</li> 1992         * <li>SQL_TSI_WEEK</li> 1993         * <li>SQL_TSI_MONTH</li> 1994         * <li>SQL_TSI_QUARTER</li> 1995         * <li>SQL_TSI_YEAR</li> 1996         * </ul></p> 1997             * </subsection> 1998         */ 1999        if (Value.isNull(v) || Value.isNull(values.item(2)) || 2000        Value.isNull(values.item(3))) { 2001        ret = ValueNull.valueNull; 2002        } else { 2003        int itype = toNumber(v).intValue(); 2004        int count = toNumber(values.item(2)).intValue(); 2005        Timestamp t = getTimestamp(values.item(3), "TIMESTAMPADD"); 2006        Calendar c = Calendar.getInstance(); 2007        c.setTime(t); 2008        if (itype >= 0) { 2009            c.add(itype, count); 2010                    t.setTime(c.getTime().getTime()); 2011        } else switch (itype) { 2012        case TSI_FRAC_SECOND: 2013            t.setNanos(t.getNanos() + count); 2014            break; 2015        case TSI_QUARTER: 2016            c.add(Calendar.MONTH, count*3); 2017                    t.setTime(c.getTime().getTime()); 2018            break; 2019        } 2020        return new ValueTimestamp(t); 2021 2022        } 2023        break; 2024 2025    case fnTIMESTAMPDIFF: 2026        /*{fnvarExpression.xml-660} 2027         * <subsection name="TIMESTAMPDIFF"> 2028         * 2029         * <h4>Syntax</h4> 2030         * <code> 2031         * TIMESTAMPDIFF(<i>interval</i>, <i>timestamp1</i>, 2032         * <i>timestamp2</i>) 2033         * </code> 2034         * 2035         * <h4>Description</h4> 2036         * <p><code>TIMESTAMPDIFF</code>returns the the difference between 2037         * <i>timestamp2</i> and <i>timestamp1</i>, expressed in 2038         * <i>interval</i> units.</p> 2039         * 2040         * <p><i>interval</i> is one of the following keywords: 2041         * <ul> 2042         * <li>SQL_TSI_FRAC_SECOND</li> 2043         * <li>SQL_TSI_SECOND</li> 2044         * <li>SQL_TSI_MINUTE</li> 2045         * <li>SQL_TSI_HOUR</li> 2046         * <li>SQL_TSI_DAY</li> 2047         * <li>SQL_TSI_WEEK</li> 2048         * <li>SQL_TSI_MONTH</li> 2049         * <li>SQL_TSI_QUARTER</li> 2050         * <li>SQL_TSI_YEAR</li> 2051         * </ul></p> 2052             * </subsection> 2053         */ 2054        if (Value.isNull(v) || Value.isNull(values.item(2)) || 2055        Value.isNull(values.item(3))) { 2056        ret = ValueNull.valueNull; 2057        } else { 2058        int itype = toNumber(v).intValue(); 2059        Timestamp t1 = getTimestamp(values.item(2), "TIMESTAMPDIFF"); 2060        Timestamp t2 = getTimestamp(values.item(3), "TIMESTAMPDIFF"); 2061        long secdiff = t2.getTime() / 1000 - t1.getTime() / 1000; 2062        long nanodiff = t2.getNanos() - t1.getNanos(); 2063        int monthdiff = getMonths(t2) - getMonths(t1); 2064        long diff; 2065        switch (itype) { 2066        case TSI_FRAC_SECOND: 2067            diff = secdiff * 1000000000L + nanodiff; 2068            break; 2069        case TSI_SECOND: 2070            diff = secdiff; 2071            break; 2072        case TSI_MINUTE: 2073            diff = secdiff / 60; 2074            break; 2075        case TSI_HOUR: 2076            diff = secdiff / 3600; 2077            break; 2078        case TSI_DAY: 2079            diff = secdiff / (3600*24); 2080            break; 2081        case TSI_WEEK: 2082            diff = secdiff / (3600*24*7); 2083            break; 2084        case TSI_MONTH: 2085            diff = monthdiff; 2086            break; 2087        case TSI_QUARTER: 2088            diff = monthdiff / 3; 2089            break; 2090        case TSI_YEAR: 2091            diff = monthdiff / 12; 2092            break; 2093        default: 2094            throw new SQLException ("Bad argument to TIMESTAMPDIFF"); 2095        } 2096        ret = new ValueLong(diff); 2097        } 2098        break; 2099 2100    case fnTRUNCATE: 2101        /*{fnvarExpression.xml-670} 2102         * <subsection name="TRUNCATE"> 2103         * 2104         * <h4>Syntax</h4> 2105         * <code>TRUNCATE(<i>number</i>, <i>scale</i>)</code> 2106         * 2107         * <h4>Description</h4> 2108         * <p><code>TRUNCATE</code> returns <i>number</i>, with the 2109         * number of digits to the right of the decimal point truncated 2110         * to <i>scale</i> places.</p> 2111             * </subsection> 2112         */ 2113        if (Value.isNull(v) || Value.isNull(values.item(2))) { 2114        ret = ValueNull.valueNull; 2115        } else { 2116        ValueScaledInteger si = 2117            (ValueScaledInteger)v.convert(TypeDecimal.typeDecimal); 2118        BigDecimal d = (BigDecimal )si.asJavaObject(); 2119        int scale = getNumber(values, 2).intValue(); 2120        d = d.setScale(scale, BigDecimal.ROUND_DOWN); 2121        ret = new ValueScaledInteger(d); 2122        } 2123        break; 2124 2125    case fnUPPER: 2126        /*{fnvarExpression.xml-680} 2127         * <subsection name="UPPER"> 2128         * 2129         * <h4>Syntax</h4> 2130         * <code>UPPER(<i>character string</i>)</code> 2131         * <code>UCASE(<i>character string</i>)</code> 2132         * 2133         * <h4>Description</h4> 2134         * <p><code>UPPER</code> and <code>UCASE</code> return a string with each 2135         * lower-case letter in the string argument replaced by 2136         * its corresponding upper-case letter.</p> 2137             * </subsection> 2138         */ 2139        if (Value.isNull(v)) { 2140        ret = v; 2141        } else { 2142        String s = getString(values, 1).stringValue(); 2143        ret = new ValueString(s.toUpperCase()); 2144        } 2145        break; 2146 2147    case fnUSER: 2148        /*{fnvarExpression.xml-690} 2149         * <subsection name="USER"> 2150         * 2151         * <h4>Syntax</h4> 2152         * <code>USER()</code> 2153         * 2154         * <h4>Description</h4> 2155         * <p><code>USER</code> returns the name of the current 2156         * database user.</p> 2157             * </subsection> 2158         */ 2159        ret = new ValueString(session.getConnection().getAuth()); 2160        break; 2161 2162    case fnWEEK: 2163        /*{fnvarExpression.xml-700} 2164         * <subsection name="WEEK"> 2165         * 2166         * <h4>Syntax</h4> 2167         * <code>WEEK(<i>date</i>)</code> 2168         * 2169         * <h4>Description</h4> 2170         * <p><code>WEEK</code> returns an integer in the range 2171         * 1-53 representing the week in <i>date</i>.</p> 2172             * </subsection> 2173         */ 2174        ret = getTimeComponent(v, Calendar.WEEK_OF_YEAR, 0, false); 2175        break; 2176 2177    case fnYEAR: 2178        /*{fnvarExpression.xml-710} 2179         * <subsection name="YEAR"> 2180         * 2181         * <h4>Syntax</h4> 2182         * <code>YEAR(<i>date</i>)</code> 2183         * 2184         * <h4>Description</h4> 2185         * <p><code>YEAR</code> returns an integer 2186         * representing the year component of <i>date</i>.</p> 2187             * </subsection> 2188         */ 2189        ret = getTimeComponent(v, Calendar.YEAR, 0, false); 2190        break; 2191 2192        case fnDIGEST: 2193            /*{fnvarExpression.xml-720} 2194             * <subsection name="DIGEST"> 2195             * 2196             * <h4>Syntax</h4> 2197             * <code>DIGEST(<i>string</i> [, <i>algorithm</i>])</code> 2198             * 2199             * <h4>Description</h4> 2200             * <p><code>DIGEST</code> performs a one-way hash function using 2201             * the specified digest algorithm. The default digest algorithm 2202             * is <b><code>"SHA"</code></b>. 2203             * </subsection> 2204             */ 2205            if (!Value.isNull(v)) { 2206                String algorithm = "SHA"; 2207                if (values.size() > 1) { 2208                    algorithm = getString(values, 1).stringValue(); 2209                } 2210                try { 2211                    MessageDigest md = MessageDigest.getInstance(algorithm); 2212                    byte[] b = md.digest(Value.bytes(v)); 2213                    ret = new ValueOctets(b); 2214                } catch (Exception e) { 2215                    throw new ValueException(e.toString()); 2216                } 2217            } 2218            break; 2219                 2220        case fnLAST_INSERT_ID: 2221            /*{fnvarExpression.xml-720} 2222             * <subsection name="LAST_INSERT_ID"> 2223             * 2224             * <h4>Syntax</h4> 2225             * <code>LAST_INSERT_ID()</code> 2226             * 2227             * <h4>Description</h4> 2228             * <p><code>LAST_INSERT_ID</code> returns the id of the last 2229             * auto-numbered (i.e., resulting from a <b>WITH IDENTITY</b> 2230             * constraint) row insertion.</p> 2231             * </subsection> 2232             */ 2233            ret = new ValueLong(session.getLastInsertId()); 2234            break; 2235                 2236    default: 2237        throw new SQLException ("scalar function not implemented: " + name); 2238    } 2239    if (ret == null) { 2240        throw new SQLException ("undefined function: " + name); 2241    } 2242    return ret; 2243    } 2244 2245    public Type getType(Session session, Cursor cursor) throws SQLException { 2246    Integer fi = (Integer )functions.get(uname); 2247        Expression e1 = args.size() > 0 ? args.get(0) : null; 2248        Type t = e1 != null ? e1.getType(session, cursor) : TypeAny.any; 2249    if (fi != null) switch (fi.intValue()) { 2250    case fnABS: 2251        break; 2252 2253    case fnASCII: 2254    case fnBIT_LENGTH: 2255    case fnCEILING: 2256    case fnCHAR_LENGTH: 2257    case fnDAYOFMONTH: 2258    case fnDAYOFWEEK: 2259    case fnDAYOFYEAR: 2260        case fnDIFFERENCE: 2261    case fnHOUR: 2262    case fnLENGTH: 2263    case fnLOCATE: 2264    case fnMINUTE: 2265    case fnMONTH: 2266    case fnOCTET_LENGTH: 2267    case fnQUARTER: 2268    case fnSECOND: 2269    case fnSIGN: 2270    case fnWEEK: 2271    case fnYEAR: 2272            t = TypeInt.typeInt; 2273        break; 2274 2275    case fnACOS: 2276    case fnASIN: 2277    case fnATAN: 2278    case fnATAN2: 2279    case fnCOS: 2280    case fnCOT: 2281    case fnDEGREES: 2282    case fnEXP: 2283    case fnFLOOR: 2284    case fnLOG: 2285    case fnLOG10: 2286    case fnPI: 2287    case fnPOWER: 2288    case fnRADIANS: 2289    case fnRAND: 2290    case fnSIN: 2291    case fnSQRT: 2292    case fnTAN: 2293            t = TypeReal.typeDouble; 2294        break; 2295 2296        case fnCASE: 2297            t = null; 2298            if ((args.size() % 2) == 1) { 2299                t = args.get(args.size() - 1).getType(session, cursor); 2300            } 2301            for (int i = 1; i < args.size(); i += 2) { 2302                Expression e = args.get(i); 2303                Type et = e.getType(session, cursor); 2304                if (t == null) { 2305                    t = et; 2306                } else if (t.getJDBCType() != et.getJDBCType()) { 2307                    t = TypeAny.any; 2308                } 2309            } 2310            break; 2311             2312    case fnCAST: 2313            t = args.get(1).getType(session, cursor); 2314            break; 2315 2316    case fnCHAR: 2317    case fnCONCAT: 2318    case fnDATABASE: 2319    case fnDAYNAME: 2320    case fnINSERT: 2321    case fnLEFT: 2322    case fnLOWER: 2323    case fnLTRIM: 2324    case fnMONTHNAME: 2325    case fnREPEAT: 2326    case fnREPLACE: 2327    case fnRIGHT: 2328    case fnRTRIM: 2329    case fnSOUNDEX: 2330    case fnSPACE: 2331    case fnSUBSTRING: 2332    case fnUPPER: 2333    case fnUSER: 2334            t = TypeVarChar.typeVarChar; 2335            break; 2336 2337    case fnCOALESCE: 2338            t = TypeAny.any; 2339            break; 2340 2341    case fnCURDATE: 2342            t = TypeDate.typeDate; 2343        break; 2344 2345    case fnCURTIME: 2346        t = TypeTime.typeTime; 2347        break; 2348 2349        case fnIFNULL: 2350    case fnNULLIF: 2351            // keep default t, type of first arg 2352 break; 2353 2354    case fnMOD: 2355    case fnTIMESTAMPDIFF: 2356        case fnLAST_INSERT_ID: 2357            t = TypeBigInt.typeBigInt; 2358            break; 2359 2360    case fnNOW: 2361    case fnTIMESTAMPADD: 2362            t = TypeTimestamp.typeTimestamp; 2363            break; 2364 2365    case fnROUND: 2366    case fnTRUNCATE: 2367            t = TypeDecimal.typeDecimal; 2368            break; 2369 2370        case fnDIGEST: 2371            t = TypeVarBinary.typeVarBinary; 2372            break; 2373                 2374    default: 2375        throw new SQLException ("scalar function not implemented: " + name); 2376    } 2377    return t; 2378    } 2379 2380    public void visitSubExpressions(ExpressionVisitor ev) { 2381    args.visitSubExpressions(ev); 2382    } 2383 2384    public String toString() { 2385    StringBuffer sb = new StringBuffer (name); 2386    sb.append('('); 2387    sb.append(args.toString()); 2388    sb.append(')'); 2389    return sb.toString(); 2390    } 2391 2392    public void readExternal(ObjectInput in) 2393    throws IOException , ClassNotFoundException 2394    { 2395    name = (String )in.readObject(); 2396    uname = name.toUpperCase(); 2397    args = (VectorExpression)in.readObject(); 2398    not = in.read() == 1; 2399    } 2400     2401    public void writeExternal(ObjectOutput out) throws IOException { 2402    out.writeObject(name); 2403    out.writeObject(args); 2404    out.write(not ? 1 : 0); 2405    } 2406} 2407

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags