7 Replies Latest reply on Aug 25, 2008 9:47 AM by Bir Adam

    SQL query missinterperted in JBOSS. How can I correct it?

    Bir Adam Newbie

      Hi everyone

      This is really an embarassing issue. I am using JBOSS & Sybase. In the following statement:

      Statment stmt = connection.createStatement();
      boolean results= stmt.execute(sqlCommand);


      When I ran the query directly from sql client, it ran correctly. But when ran it from the application, it missed interperted the sql statement. Any concatenated parameter name with white spaces was wrongly read.

      Is there anyone has any idea?

      Best Regards
      badam571

        • 1. Re: SQL query missinterperted in JBOSS. How can I correct it
          Peter Johnson Master

          What do you meant by "Any concatenated parameter name with white spaces was wrongly read." An example would be helpful (the code you posted uses a variable name for the sql statement so we have no idea what that statement contains).

          • 2. Re: SQL query missinterperted in JBOSS. How can I correct it
            Bir Adam Newbie

            Hi PeterJ

            Here is the sql statment passed in the parameter called sqlcommand


            SELECT convert(varchar(12), pdl_site.last_updt_tmsp, 101) + " " + convert(varchar(8), pdl_site.last_updt_tmsp, 108) + ":" + right("00" + datename(ms,pdl_site.last_updt_tmsp), 3) last_updt_tmsp, pdl_site.last_updt_usr, pdl_site.appl_version, pdl_site.default_archive_days, pdl_site.default_fund_type, pdl_site.email_protocol, pdl_site.general_info, pdl_site.license_key, pdl_site.id, pdl_site.password_expir_days, pdl_site.reg_instr, pdl_site.smtp_account, pdl_site.smtp_host, pdl_site.site_ip_addr, pdl_site.pd_version FROM pdl_site


            this sql statment working for long time with the pervious sliverstream server as is, now we are trying to migrate to JBOSS, it is no longer working.

            You can see that, there are ":", "", "00" in the parameter names. The error generated is the following:

            Invalid column name ' '.
            Invalid column name ':'.
            Invalid column name '00'.


            On the server console, the connection is open, data-source is fine, but the above execute(sqlcommand) is returning false.

            Best Regards
            badam571

            • 3. Re: SQL query missinterperted in JBOSS. How can I correct it
              Bir Adam Newbie

              Hi PeterJ

              Here are the parameters in table:

              last_updt_tmsp

              last_updt_user

              appl_version

              default_archive_days

              default_fund_type

              email_protocol

              general_info

              license_key

              id

              password_expir_days

              smtp_account

              smtp_host

              reg_instr

              site_ip_addr

              pd_version


              the first one which causing the troube appears in the table like this:
              last_updt_tmsp: 06/10/2008 21:51:52:610. Also some of the parameters are null or zero.

              Best Regards
              Badam571

              • 4. Re: SQL query missinterperted in JBOSS. How can I correct it
                Peter Johnson Master

                The three error lines you posted, where are those appearing? As part of an exception stack trace in console log? If so, please post the full stack trace for one of the error messages.

                • 5. Re: SQL query missinterperted in JBOSS. How can I correct it
                  Bir Adam Newbie

                  Hi PeterJ

                  Here is stacktrace for error:

                  2008-08-22 12:06:22,427 DEBUG [org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService] Created Connection proxy for invoker=jboss:service=invoker,type=jrmp, targetName=jboss.jca:service=DataSourceBinding,name=aco_code, cacheID=25803197

                  2008-08-22 12:06:22,427 INFO [STDOUT] data-source: org.jboss.invocation.jrmp.interfaces.JRMPInvokerProxy@191c263

                  2008-08-22 12:06:22,427 INFO [STDOUT] connection: 25803197

                  2008-08-22 12:06:22,427 DEBUG [org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService] Created Statement proxy for invoker=jboss:service=invoker,type=jrmp, targetName=jboss.jca:service=DataSourceBinding,name=aco_code, cacheID=1031493

                  2008-08-22 12:06:22,447 DEBUG [com.base.SqlEngine] debug2 ====>com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ' '.

                  2008-08-22 12:06:22,447 INFO [STDOUT] debug2 ====>com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ' '.
                  2008-08-22 12:06:22,447 DEBUG [com.base.SqlEngine] debug2 ====>com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ':'.

                  2008-08-22 12:06:22,447 INFO [STDOUT] debug2 ====>com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ':'.
                  2008-08-22 12:06:22,447 DEBUG [com.base.SqlEngine] debug2 ====>com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name '00'.

                  2008-08-22 12:06:22,447 INFO [STDOUT] debug2 ====>com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name '00'.

                  2008-08-22 12:06:22,457 DEBUG [org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService] Closed Statement=1031493

                  2008-08-22 12:06:22,457 INFO [STDOUT] results: false


                  You can see that results is equal to: false .. results is the output of: stmt.execute(sqlcommand)

                  do I need to paste all the server long here?

                  Best Regards
                  badam571

                  • 6. Re: SQL query missinterperted in JBOSS. How can I correct it
                    Peter Johnson Master

                    Note this line:

                    2008-08-22 12:06:22,447 DEBUG [com.base.SqlEngine] debug2 ====> com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ' ' .

                    This means that the Sybase JDBC driver is complaining about the syntax of the SQL statement. Have you asked the Sybase support team to help you with this? (Note that JBossAS has nothing to do with this error.)

                    • 7. Re: SQL query missinterperted in JBOSS. How can I correct it
                      Bir Adam Newbie

                      Hi PeterJ

                      Great point! .. I reached that conclusion also, I replaced jConnect2.jar (version 5) file with jConnect3.jar (version 6) in order to give me more debugging power. Now I know the error code for issue and it is purely a database issue.

                      later on, when I reach a solution I will post it here for the benefit of our community.

                      Best Regards
                      badam571