public inbox for [email protected]  

help / color / mirror / Atom feed

From: Martin Handsteiner <[email protected]>
To: Dave Cramer <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: AW: stringtype=unspecified is null check problem
Date: Fri, 13 Jan 2023 09:54:34 +0000
Message-ID: <VI1PR1001MB14234D28E578CA1F8D7A00D5E8C29@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CADK3HHJbx5+DxndG--8FKDF3uAOLS3MtFGu++73TEvkFeABpqA@mail.gmail.com>
References: <VI1PR1001MB1423CDC3E737B4763436B730E8FF9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
	<CAKFQuwZA+9+FNovGXA6moj+sDzR6ShyzzP3H77m-05n3dXDv-w@mail.gmail.com>
	<VI1PR1001MB1423C002817FECB26B1AFB3DE8FC9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
	<CAKFQuwZrn0F9RyM=WL9Z=pioGX5pwH1+mbBe-jGNnjy_J2E+Jw@mail.gmail.com>
	<[email protected]>
	<CAKFQuwYtmLLkmjaKghYXPnBWwvUvsqhZR=9J8KDcLzavf8707w@mail.gmail.com>
	<[email protected]>
	<VI1PR1001MB1423F7050B1979EAF610D2C8E8FD9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
	<CADK3HHJbx5+DxndG--8FKDF3uAOLS3MtFGu++73TEvkFeABpqA@mail.gmail.com>

Whole Source is too much, as there is a lot of other things inside:
Null Replacement went into general DBAccess, not only in special PostgreSQLDBAccess like executeFunction or executeProcedure.
https://sourceforge.net/p/jvx/code/HEAD/tree/trunk/java/library/src/com/sibvisions/rad/persist/jdbc/...

Relevant Source with example:

String originalStatement = "select 1 where ? is null";
Object[] originalParams = new Object[] {null};
StringBuilder statementToBeChanged = new StringBuilder(originalStatement);

Object[] newParams = replaceNullParameter(statementToBeChanged, originalParams);
String newStatement = statementToBeChanged.toString();

Real usage would be:

List<Bean> result = dba.executeQuery("select 1 where ? is null or 1 = ?", null, null);



/**
* Replaces null parameters (?) directly with null, to avoid cast exceptions.
*
 * @param pStatement the original statement
* @param pParameters the parameters
* @return the new reduced parameters
*/
protected Object[] replaceNullParameter(StringBuilder pStatement, Object[] pParameters)
{
        int[] paramIndexes = getParameterIndexes(pStatement.toString());

        for (int i = paramIndexes.length - 1; i >= 0; i--)
        {
            Object param = pParameters[i];

            if (AbstractParam.getValue(param) == null)
            {
                int index = paramIndexes[i];

                if (index < pStatement.length() - 1 && !Character.isWhitespace(pStatement.charAt(index + 1)))
                {
                    pStatement.insert(index + 1, " ");
                }
                pStatement.replace(index, index + 1, "null");
                if (index > 0 && !Character.isWhitespace(pStatement.charAt(index - 1)))
                {
                    pStatement.insert(index, " ");
                }
                pParameters = ArrayUtil.remove(pParameters, i);
            }
        }

        return pParameters;
  }

    /**
     * Gets the indexes of parameters (?).
     *
     * @param pStatement the statement
     * @return the indexes of parameters (?)
     */
    protected int[] getParameterIndexes(String pStatement)
    {
        int[] result = new int[0];

        String sOpenQu = getOpenQuoteCharacter();
        String sCloseQu = getCloseQuoteCharacter();
        if (StringUtil.isEmpty(sOpenQu) || StringUtil.isEmpty(sCloseQu))
        {
            sOpenQu = null;
            sCloseQu = null;
        }

        String sCloseMark = null;

        for (int idx = 0, cnt = pStatement.length(); idx < cnt; idx++)
        {
            if (sCloseMark != null)
            {
                if (pStatement.startsWith(sCloseMark, idx))
                {
                    idx += sCloseMark.length() - 1;
                    sCloseMark = null;
                }
            }
            else
            {
                if (pStatement.startsWith("\'", idx))
                {
                    sCloseMark = "\'";
                }
                else if (pStatement.startsWith("\"", idx))
                {
                    sCloseMark = "\"";
                }
                else if (sOpenQu != null && pStatement.startsWith(sOpenQu, idx))
                {
                    idx += sOpenQu.length() - 1;
                    sCloseMark = sCloseQu;
                }
                else if (pStatement.startsWith("--", idx))
                {
                    idx++;
                    sCloseMark = "\n";
                }
                else if (pStatement.startsWith("/*", idx))
                {
                    idx++;
                    sCloseMark = "*/";
                }
                else if (pStatement.startsWith("?", idx))
                {
                    result = ArrayUtil.add(result, idx);
                }
            }
        }

        return result;
}

view thread (14+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: AW: stringtype=unspecified is null check problem
  In-Reply-To: <VI1PR1001MB14234D28E578CA1F8D7A00D5E8C29@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox