HyperSql Array Type - Doc Could Use Update

2012-02-19
2014-01-19
  • Michael Dever
    Michael Dever
    2012-02-19

    SQL:

    • Stores the last 12 passwords
    • The current password and the next 11
    • User cannot re-use passwords.

    CREATE TABLE UserPasswords
    (
    user_id varchar(32),
    password varchar(32) Array
    )

    INSERT into UserPasswords
    values
    'MIKEBIKE',
    ARRAY;

    INSERT into UserPasswords
    values
    'SEAN1000',
    ARRAY;

    INSERT into UserPasswords
    values
    'ASHLEY100',
    ARRAY;

    INSERT into UserPasswords
    values
    'MAUREEN123',
    ARRAY;


    SELECT *
      FROM UserPasswords

    User Id Password
    MIKEBIKE ARRAY


    Select password
      from userpasswords
    where user_id = 'MIKEBIKE'

    Password
    ARRAY


    SELECT password AS "Current Password"
      FROM USERPASSWORDS
    where user_id = 'SEAN1000'

    Current Password
    Superman1000


    SELECT password
      FROM USERPASSWORDS
    where user_id = 'SEAN1000'

    java.sql.SQLDataException: data exception: array element error

    - Sean does not have a Fifth password.


    SELECT cardinality( password )
      FROM USERPASSWORDS
    where user_id = 'SEAN1000'

    Password Count
    4


    SELECT MAX_CARDINALITY( password ) AS "Maximum Passwords"
      FROM USERPASSWORDS
    WHERE user_id = 'SEAN1000'

    Maximum Passwords
    12


    SELECT TRIM_ARRAY( password, 2 ) AS "Top 2 Passwords"
      FROM userpasswords
    WHERE user_id = 'SEAN1000'

    Top 2 Passwords
    ARRAY


    SELECT ARRAY_SORT( TRIM_ARRAY( password, 2 ) ) AS "Sorted Top 2 Passwords"
      FROM userpasswords
    WHERE user_id = 'SEAN1000'

    Sorted Top 2 Passwords
    ARRAY


    SELECT ARRAY_SORT( password ) AS "Sorted Passwords"
      FROM userpasswords
    WHERE user_id = 'SEAN1000'

    Sorted Passwords
    ARRAY


    Select Example Code:

    /**
    *
    * Select the current password for user_id.
    *
    * @param user - input the user_id
    * @return String = the Current Password, or NULL.
    * @throws SQLException
    */
    public String selectCurrentPassword( String user )
    throws SQLException
    {
    String userUpper = user.toUpperCase().trim();

    final String sql = "select password from userpasswords where user_id = ?";
    ResultSet rs;
    try
    {
    String currentPassword = null;

    PreparedStatement pStmt = conn.prepareStatement( sql );
    pStmt.setString( 1, userUpper );
    rs = pStmt.executeQuery();
    while( rs.next() )
    {
    String aPassword = rs.getString( 1 );
    if( !rs.wasNull() )
    {
    currentPassword = aPassword;
    }

    }

    return (currentPassword);
    }
    catch( SQLException se )
    {
    SQLException seOriginal = se;
    String sMsg = "";
    while( se != null )
    {
    sMsg += se.toString() + crlf;
    se = se.getNextException();
    }
    System.err.println( sMsg );
    throw seOriginal;
    }

    }

    /**
    *
    * Return an ArrayList of the last 12 known passwords for this user_id.
    *
    * @param user
    * @return ArrayList of the last 12 passwords for this user_id
    * @throws SQLException
    */
    public ArrayList<String> selectAllPasswords( String user )
    throws SQLException
    {
    String userUpper = user.toUpperCase().trim();

    final String sql = "select password from userpasswords where user_id = ?";
    ResultSet rs;
    try
    {
    String currentPassword = "";

    PreparedStatement pStmt = conn.prepareStatement( sql );
    pStmt.setString( 1, userUpper );
    rs = pStmt.executeQuery();

    ArrayList<String> alPasswords = new ArrayList<String>();

    while( rs.next() )
    {
    java.sql.Array arrayPasswords = rs.getArray( "password" );
    Object objPasswords = ( Object ) arrayPasswords.getArray();

    int objCount = objPasswords.length;
    String password;
    for( int i = 0; i < objCount; i++ )
    {
    password = ( String ) objPasswords;
    alPasswords.add( password );
    }
    }

    return (alPasswords);
    }
    catch( SQLException se )
    {
    SQLException seOriginal = se;
    String sMsg = "";
    while( se != null )
    {
    sMsg += se.toString() + crlf;
    se = se.getNextException();
    }
    System.err.println( sMsg );
    throw seOriginal;
    }

    }

     
  • Fred Toussi
    Fred Toussi
    2012-02-20

    Thanks. This is a good example. Will publish it.