InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors

Hello,

Firstly thanks for your great work on instantDB. It's really kind to have an 
easy-to-install database like this.

i notice some problems using 3.26 idb...


1) Between : between clause doesn't work very kindly with negative numbers. 
Tries
 
"select myvalue from mytable where myvalue between -1 and 2" generate an 
SQLException.

we must put single quote like this :
"select myvalue from mytable where myvalue between '-1' and 2"

but its not really the right syntax for a integer value, isn't it ?


2) null values : relating to general comportment of any database like sybase, 
oracle or my sql

doing such sql in an empty table
"select max(id) from mytable" 
should return null value. and instantDB does it. ok.

but doing such sql with the same empty table
"select max(id)+1 from mytable"
should although return a null value to.
but instantDB return -2147483647 !


3) again null values :

we can't do this : "select null from mytable"  (generate SQLException)
that would be fine if we could.


4) i give you a sql function ifnull(int, int, int) i made. Work's like 
isNull(int, int) sybase function but with an added 3rd parameters.
i think this is usefull in managing identity columns like the following sql :

select ifnull(max(id), 1, max(id)+1) from mytable

to use it like isnull(int, int) sybase function, put the 1rst param into the 
3rd param, like
select ifnull(max(id), 1, max(id)) from mytable

if you want, extends it with long values fonctionality.


5) your management of 'sql' function doesn't really work with aggregate 
functions...

example on a table with n rows (n>=2):

"select mod(6,2) from mytable" return n rows with a value of 0. ok
"select max(id) from mytable"   return one row with a value of 5 for example. 
ok
but
"select mod(max(id),2) from mytable" return n rows with a value of 1. which 
is wrong.
it should return me one row with a value of 1.




Again, thanks for your work.
yours faithfully. 

/*-----------------------------------------------------------------------------
 *
 * Enhydra InstantDB
 * The Initial Developer of the Original Code is Lutris Technologies Inc.
 * Portions created by Lutris are Copyright 1997-2000 Lutris Technologies Inc.
 * All Rights Reserved.
 *
 * The contents of this file are subject to the Enhydra Public License
 * Version 1.1 (the "License"); you may not use this file except in compliance
 * with the License. You may obtain a copy of the License at
 * www.enhydra.org/license/epl.html
 *
 * Software distributed under the License is distributed on an "ASIS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 *
 * -----------------------------------------------------------------------------
 * $Id: ifnull.java,v 1.3 22/03/2001 12:51:28 Christophe Exp $
 * -----------------------------------------------------------------------------
 */
package org.enhydra.instantdb.db;

import org.enhydra.instantdb.db.SqlFunction;
import java.sql.SQLException;

/**
 * This class is a SQL-function class which is used for InstantDB
 * to use in SQL.
 * ifnull implements the expression function:
 *
 *      IFNULL (int expr1, int expr2, int expr3)
 *
 * Example :
 *
 * select ifnull(Max(id), 1, Max(id) + 1) from table1
 *
 * gives
 *
 *
 * 18/2/2000 Christophe NIGAUD, atrap@club-internet.fr
 */

class ifnull implements SqlFunction {
  private String usage = "usage IFNULL (integer, integer, integer)";

  public int checkParameters (int[] parameterTypes) throws SQLException {
    int paramCount = parameterTypes.length;				// get number of parameters

    // parameters controls...
    if (paramCount != 3) { throw new SQLException (usage);}
    if (parameterTypes[0] != TYPE_INTEGER) {throw new SQLException (usage);}
    if (parameterTypes[1] != TYPE_INTEGER) {throw new SQLException (usage);}
    if (parameterTypes[2] != TYPE_INTEGER) {throw new SQLException (usage);}
    return TYPE_INTEGER;									// our return type
  } // checkParameters

  public void setSpecialValue (int type, Object value){
  }

  public Object getSpecialValue (int type){
    return null;
  }

  public Object evaluate(Object[] parameters) {
    int retValue;

    if (((Number)parameters[0]).intValue() == Integer.MIN_VALUE){
      retValue = ((Number)parameters[1]).intValue();
    } else {
      retValue = ((Number)parameters[2]).intValue();
    }

    return new Integer(retValue);
  }
}