Skip site navigation (1) Skip section navigation (2)

Re: Binary protocol support for JDBC

From: Radosław Smogura <rsmogura(at)softperience(dot)eu>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Binary protocol support for JDBC
Date: 2010-07-20 20:43:06
Message-ID: 201007202243.07075.rsmogura@softperience.eu (view raw or flat)
Thread:
Lists: pgsql-jdbc
I searched something about this, but I culdn't find :)

It looks like this what I've done, so I will only send BigDeciaml read code.

Below :) some Sysout trashes left, but works, I've tested

public BigDecimal getBigDecimal(int columnIndex, int scale) throws 
SQLException
    {
        checkResultSet(columnIndex);
        if (wasNullFlag)
            return null;
        final int column = columnIndex - 1;
        if (fields[column].getFormat() == Field.BINARY_FORMAT) {
            //TODO Extract this do getBinaryBigDeciaml to support NaN
            if (fields[column].getOID() != Oid.NUMERIC)
                throw new PSQLException("Conversion in binary form not fully 
implemented yet.", PSQLState.NOT_IMPLEMENTED);
            
            byte[] number = this_row[column];
            
            short ndigits = (short) (((number[0] & 0xff) << 8) | (number[1] & 
0xff));
            short weight =  (short) (((number[2] & 0xff) << 8) | (number[3] & 
0xff));
            short sign =    (short) (((number[4] & 0xff) << 8) | (number[5] & 
0xff));
            short dscale =  (short) (((number[6] & 0xff) << 8) | (number[7] & 
0xff));

            if (sign == (short) 0xC000) {
                //Numeric NaN - BigDecimal doesn't support this
                throw new PSQLException("The numeric value is NaN - can't 
convert to BigDecimal", 
                        PSQLState.NUMERIC_VALUE_OUT_OF_RANGE);
            }

            final int bigDecimalSign = sign == 0x4000 ? -1 : 1;

//            System.out.println("ndigits=" + ndigits
//                    +",\n wieght=" + weight
//                    +",\n sign=" + sign
//                    +",\n dscale=" + dscale);
////            for (int i=8; i < number.length; i++) {
//                System.out.println("numer[i]=" + (int) (number[i] & 0xff));
//            }

            int tail = ndigits % 4;
            int bytesToParse = (ndigits - tail) * 2 + 8;
//            System.out.println("numberParseLength="+numberParseLength);
            int i;
            BigInteger unscaledValue = BigInteger.ZERO;
            final BigInteger nbase = getNBase();
            final BigInteger nbasePow2 = getNBasePow2();
            final BigInteger nbasePow4 = getNBasePow4();

            final long nbaseLong = AbstractJdbc2ResultSet.nbaseLong;
            final long nbaseLongPow2 = AbstractJdbc2ResultSet.nbaseLongPow2;
            final int nbaseInt = (int) AbstractJdbc2ResultSet.nbaseInt;
            
            //final long nbasePow2Long = nbaseLong * nbaseLong;

            byte[] buffer = new byte[8];

//            System.out.println("tail = " + tail + " bytesToParse = " + 
bytesToParse);
            
            for (i=8; i < bytesToParse; i+=8) {
                //This Hi and Lo aren't bytes Hi Li, but decimal Hi Lo!!! (Big 
& Small)
                long valHi = (((number[i] & 0xff) << 8) | (number[i+1] & 0xff)) 
* 10000
                        + (((number[i+2] & 0xff) << 8) | (number[i+3] & 0xff));
                long valLo = (((number[i+4] & 0xff) << 8) | (number[i+5] & 
0xff)) * 10000
                        + (((number[i+6] & 0xff) << 8) | (number[i+7] & 0xff));
                long val = valHi * nbaseLongPow2 + valLo;
                buffer[0] = (byte)(val >>> 56);
                buffer[1] = (byte)(val >>> 48);
                buffer[2] = (byte)(val >>> 40);
                buffer[3] = (byte)(val >>> 32);
                buffer[4] = (byte)(val >>> 24);
                buffer[5] = (byte)(val >>> 16);
                buffer[6] = (byte)(val >>> 8);
                buffer[7] = (byte)(val >>> 0);

                BigInteger valBigInteger = new BigInteger(bigDecimalSign, 
buffer);
                unscaledValue = 
unscaledValue.multiply(nbasePow4).add(valBigInteger);
//                System.out.println("Value (8) = " + val + ", unscaled =" +  
unscaledValue
//                        +", valBI = "+ valBigInteger);
            }
            tail = tail % 2;
            bytesToParse = (ndigits - tail) * 2 + 8;
            //System.out.println("tail = " + tail + " bytesToParse = " + 
bytesToParse);
            
            buffer = new byte[4];
            for (;i < bytesToParse; i+=4) {
                int val = (((number[i] & 0xff) << 8) | (number[i+1] & 0xff)) * 
nbaseInt
                        + (((number[i+2] & 0xff) << 8) | (number[i+3] & 0xff));
                buffer[0] = (byte)(val >>> 24);
                buffer[1] = (byte)(val >>> 16);
                buffer[2] = (byte)(val >>> 8);
                buffer[3] = (byte)val;
                BigInteger valBigInteger = new BigInteger(bigDecimalSign, 
buffer);
                unscaledValue = 
unscaledValue.multiply(nbasePow2).add(valBigInteger);
//                System.out.println("Value (4) = " + val + ", unscaled =" +  
unscaledValue
//                        +", valBI = "+ valBigInteger);
            }

            //Add the rest of number
            //System.out.println("tail = " + tail + " bytesToParse = " + 
bytesToParse);
            if (tail % 2 == 1){
                buffer = new byte[2];
                buffer[0] = number[number.length - 2];
                buffer[1] = number[number.length - 1];
                BigInteger valBigInteger = new BigInteger(buffer);
                unscaledValue = 
unscaledValue.multiply(nbase).add(valBigInteger);
//                System.out.println("Value (2)  unscaled =" +  unscaledValue
//                        +", valBI = "+ valBigInteger);
            }

            //System.out.println("Final unscaled value " + unscaledValue);

            //if (sign == 0x4000)
            //    unscaledValue = unscaledValue.negate();
            
            //Calculate scale offset
            final int databaseScale = (ndigits - weight - 1)*4; // Number of 
digits in nabse
            //TODO This number of digits should be calculeted depending on 
nbase (getNbase());
            
            BigDecimal result = new BigDecimal(unscaledValue, databaseScale);
            //System.out.println("Final result " + result);
            if (scale == -1)
                return result;
            else
                return result.setScale(scale);
            
        }else {
            Encoding encoding = connection.getEncoding();
            if (encoding.hasAsciiNumbers()) {
                try {
                    return getFastBigDecimal(columnIndex);
                } catch (NumberFormatException ex) {
                }
            }
            return toBigDecimal( getFixedString(columnIndex), scale );
        }        
    }
> On Tue, 20 Jul 2010, Rados?aw Smogura wrote:
> > I partially, and for "test", implemented retrieval data in binary mode
> > (instead of text mode) for some Jdbc2 types, because I see great
> > performance boost (25% - 50%) on implemented types I think about
> > including this work to main JDBC branch.
> 
> Are you aware of the existing work in this area?
> 
> http://wiki.postgresql.org/wiki/JDBC-BinaryTransfer
> 
> > This should be done without problem, because I added binary parameter to
> > Connection and Datasources, so user can decide to use binary mode
> > retrieve or current text mode (default). Currently I implemented
> > retrieve of short, int, long, date and BigDecimal. Other simple and
> > basic types, used in typically application I will implement shortly.
> 
> One of the difficulties in the existing patch is knowing when to request
> binary transfer and when to request text transfer because for the first
> execution the datatypes are not known.  How have you addressed
> this problem?
> 
> Kris Jurka

In response to

pgsql-jdbc by date

Next:From: Radosław SmoguraDate: 2010-07-20 20:45:54
Subject: Re: Binary protocol support for JDBC
Previous:From: Florence CousinDate: 2010-07-20 20:40:21
Subject: Patch for the documentation (PGResultSetMetaData)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group