CODEWITHSUNDEEP
javasqlxmljdbc
lincolnadym
lincolnadym

Reputation: 1000

Java/XML/SQL - SQL in XML won't run in JDBC (Sybase) driver

I've checked a number of posts on Stack overflow and tried a number of things. For whatever reason, the following SQL when stored in an XML attribute and then processed thru Java/JDBC will not run against a Sybase database. I've verified that the SQL is correct, as running it by hand in an SQL query tool does work. But placing it in an XML attribute (encoded or not) and running it thru keeps throwing the same error/exception from the Sybase JDBC driver : com.sybase.jdbc4.jdbc.SybSQLException: Invalid column name ' '.

Original SQL : 

1> begin tran
2> update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, ";"," ") where charindex(';',nme_unit_org_cust_chc) > 0
3> go
(8 rows affected)

XML Structure : 

<item key="task.300.remove.semi.colon"           value="com.lmig.lbpf.tasks.RemoveSemiColon" />
  <item key="item.300.key.context"               value="sql.results"/>
  <item key="item.300.search.text"               value=";"/> 
  <!-- 
  <item key="item.300.sql.statement"             value="update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, &quot;;&quot;,&quot; &quot;) where charindex(&apos;;&apos;,nme_unit_org_cust_chc) > 0"/>
  <item key="item.300.sql.statement"             value="update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, &quot;;&quot;,&quot; &quot;) where charindex(';',nme_unit_org_cust_chc) > 0"/>
  <item key="item.300.sql.statement"             value="<![CDATA[update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, ";" ," ") where charindex(';',nme_unit_org_cust_chc) > 0]]>"/>
  <item key="item.300.sql.statement"             value="update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, &quot;;&quot;, &quot; &quot;) where charindex(&apos;;&apos;, nme_unit_org_cust_chc) &gt; 0"/>
   -->
  <item key="item.300.sql.statement"             value='update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, ";" ," ") where charindex(&apos;;&apos;,nme_unit_org_cust_chc) &gt; 0'/>

Things I've tried : 

  <!-- 
  <item key="item.300.sql.statement"             value="update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, &quot;;&quot;,&quot; &quot;) where charindex(&apos;;&apos;,nme_unit_org_cust_chc) > 0"/>
  <item key="item.300.sql.statement"             value="update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, &quot;;&quot;,&quot; &quot;) where charindex(';',nme_unit_org_cust_chc) > 0"/>
  <item key="item.300.sql.statement"             value="<![CDATA[update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, ";" ," ") where charindex(';',nme_unit_org_cust_chc) > 0]]>"/>
  <item key="item.300.sql.statement"             value="update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, &quot;;&quot;, &quot; &quot;) where charindex(&apos;;&apos;, nme_unit_org_cust_chc) &gt; 0"/>
   -->
  <item key="item.300.sql.statement"             value='update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, ";" ," ") where charindex(&apos;;&apos;,nme_unit_org_cust_chc) &gt; 0'/>

Error from Console : 

2016-04-01 08:32:01,202 DEBUG  RemoveSemiColon.runTask - -------------------------------------------------
2016-04-01 08:32:01,202 DEBUG  RemoveSemiColon.runTask - update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, ";" ," ") where charindex(';',nme_unit_org_cust_chc) > 0
2016-04-01 08:32:01,202 DEBUG  RemoveSemiColon.runTask - -------------------------------------------------
2016-04-01 08:32:01,281 ERROR  RemoveSemiColon.runTask - com.sybase.jdbc4.jdbc.SybSQLException: Invalid column name ' '.

com.sybase.jdbc4.jdbc.SybSQLException: Invalid column name ' '.

    at com.sybase.jdbc4.tds.Tds.a(Unknown Source)
    at com.sybase.jdbc4.tds.Tds.nextResult(Unknown Source)
    at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.execute(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.execute(Unknown Source)
    at com.lmig.lbpf.tasks.RemoveSemiColon.runTask(RemoveSemiColon.java:152)
    at com.lmig.rapid.util.model.AbstractRapidProcess.runProcess(AbstractRapidProcess.java:237)
    at com.lmig.lbpf.model.AbstractProcess._main(AbstractProcess.java:696)
    at com.lmig.lbpf.process.RunSemiColonQuery.main(RunSemiColonQuery.java:129)

The runTask() method : 

public void runTask(IContext theContext) throws Exception {
    getLog().info ( SHORT_NAME + ".runTask() - Start" );

    ResultSet   ldtbResults     = null;
    Statement   ldtbStatement   = null;
    String      lstrSQL         = null;
    int         lrowCount       = 0;

    if ( getConnection() != null ) { 
        try {
            // STATEMENT : Create the database statement...
            //
            ldtbStatement = getConnection().createStatement ();

            // SQL : Resolve and fetch the SQL...
            //
            lstrSQL = getProperty( getTaskItemFilter()+"."+getTaskItemPrefix()+".sql.statement" );

            // RUN : Run the SQL against the database...
            //
            getLog().debug( SHORT_NAME + " SQL " );
            getLog().debug( "-------------------------------------------------" );
            getLog().debug( lstrSQL );
            getLog().debug( "-------------------------------------------------" );
            //ldtbStatement.executeQuery ( lstrSQL );
            boolean lblnResults = ldtbStatement.execute ( lstrSQL );
            lrowCount = ldtbStatement.getUpdateCount ();

            getLog().debug( SHORT_NAME + "...Query Complete...SQL [" + lstrSQL + "]" );
            getLog().info( SHORT_NAME + ".runTask() - Row Count................................[" + lrowCount + "]" );

            // RESULTS : Grab the results...
            //
            //ldtbResults = ldtbStatement.getResultSet();
            getLog().debug( SHORT_NAME + "...Resultset fetched...SQL [" + lstrSQL + "]" );

        } 
        catch ( Exception ltheXcp ) {
            //TODO: Need to copy the ResultSet somehow and allow
            // the database/resources to close...
            //ldtbResults.close();
            //ldtbStatement.close();
            //ldtbConnection.close();
            ltheXcp.printStackTrace ();
            getLog().error ( ltheXcp );
            //throw ltheXcp;
        } 
        finally {
            //TODO: Need to copy the ResultSet somehow and allow
            // the database/resources to close...
            if ( ldtbResults != null ) { 
                try { ldtbResults.close(); } catch ( Exception ltheXcp ) { getLog().error ( ltheXcp ); } 
            }
            if ( ldtbStatement != null ) { 
                try { ldtbStatement.close(); } catch ( Exception ltheXcp ) { getLog().error ( ltheXcp ); } 
            }

            getConnection().close();

        }  //end finally

    }
    else { 
        throw new Exception ( 
            SHORT_NAME 
            + ".runTask() - Encountered an error/exception : " 
            + "The Database connection is NULL!  Please check " 
            + "the log for any errors/exceptions." 
        );

    }
    getLog().info ( SHORT_NAME + ".runTask() - Complete" );
}

Two other things I tried to isolate the issue : Neither worked...same error :

Upvotes: 0

Views: 248

Answers (2)

Adrian Silveri
Adrian Silveri

Reputation: 31

Have your tried replacing double quotes with single quotes? 

update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, ';',' ') where charindex(';',nme_unit_org_cust_chc) > 0
go

If this is not possible due to legacy code try altering the connection string and add "set quoted_identifier off" as follows: 

jdbc:sybase:Tds:DB_SERVER:DB_PORT/DB_NAME?SQLINITSTRING=set quoted_identifier off

When the option is on Sybase allows table, view, and column names to be delimited by double quotes. If you turn it off then it is treated the same as single quote.

Upvotes: 0

lincolnadym
lincolnadym

Reputation: 1000

The issue appears to be two things :

  • There was a space after the quoted semi-colon and before the comma : A co-worker explained this as a standard SQL syntax, although this is my first time ever hitting this.

    str_replace(nme_unit_org_cust_chc, ";" , " ")

  • The quotes were replaced by single-quotes in the str_replace() : 

    str_replace(nme_unit_org_cust_chc, ';', ' ')

The final XML (or SQL) that runs thru the XML/Java/JDBC : 

  <item key="item.300.sql.statement"             value="update dbo.cond_loss_detail_t set nme_unit_org_cust_chc = str_replace(nme_unit_org_cust_chc, &apos;;&apos;, &apos;&#032;&apos;) where charindex(&apos;;&apos;,nme_unit_org_cust_chc) &gt; 0"/>

Upvotes: 1

Related Questions