#1406 Code Completion - SQuirreL hangs

[Claus]

Hi,
since SQuirreL 4.0.0 - Code Completion wont as suggested. Every time i use it - it hangs for multiple minutes. We use a current AS400 (7.3 and JDBC Driver 9.8).
Previous Release hadn't such a problem.

Greetings Claus

[Claus]

Hi, I should add: CPU Usage is 100% (one Core) at this time...

[Gerd Wagner]

Sorry, I can't reproduce the problem.

Could you give some details:


- The JDBC driver version you are using is this one
https://sourceforge.net/projects/jt400/files/JTOpen-full/9.8/
right?


- The AS 400 DB2 version  I have access to is 
Object tree --> Root Node --> getDatabaseProductVersion --> 07.03.0000 V7R3m0. Is this different from yours?


- Could you download a JDK (Java Development Kit) that matches the Java version you use to run SQuirreL and then do the following while Code Completion is hanging:
-- From the command line run 
<JDK_HOME>\bin\jstack <SQuirreL's Process ID> > stack_1.txt
several times and each time increase the number in stack_1 by one.
-- Each time you do this a stack_(...).txt file will be created.
-- When you send me these files I can see where SQuirreL hangs.

Gerd

[Sergio]

Hi Gerd,

I have the same problen Claus reported in DB2 for iSeries v7R2m0. I'm using the JTOpen 9.1.
In prior versions of Squirrel the plugin worked as a charm with same driver and database version.

Thanks in advance,
Sergio

[Gerd Wagner]

The time consuming part is column loading during Code completion. There are two things you may try:

1. Go to menu File -> New Session Properties -> tab SQL -> Section "Column loading" and check "Load columns in background".

2. By calling
   con.getMetaData().getColumns("<catalog>", "<schema>", "</schema></catalog>

   ", "%")
   (where con denotes a JDBC connection)
   and iterating over the ResultSet returned by this call the performance problem should be reproduceable. If so you may consult your JDBC / RDBMS vendor.

   Gerd

[Sergio]

Hello Gerd,

Setting "Load columns in background" option solved the problem and I can see now the proposed columns but if I use an alias for the tables the plugin does not show any column. It seems the '.' after the alias is causing the problem.

Thanks,
Sergio

[Gerd Wagner]

That was a bug: Column completion didn't work for aliases of schema/catalog qualified tables. It is fixed in our GIT repository and will be available in future snapshots and versions.

[Kevin Busick]

Hello,
I'm running version 3.9.1 and the auto-complete is working. I tried to update to 4.1.0 and even the most recent snapshot and it seems to still have this issue. Has the bugfix above been pushed yet?

Thanks
Kevin

[Gerd Wagner]

The only program change done for this issue was making code completion work for aliases of schema/catalog qualified tables.
No changes concerning hangs where done for this issue.

None the less there is a new function that may help with hangs. The according excerpt from our change log reads:
Option to keep Sessions from hanging or delaying when JDBC driver and/or database connection hangs or delays occur. See menu File --> New Session Properties --> tab SQL --> section "Meta data loading".

Please note: Hangs, as you describe, to my knowledge are due to database, JDBC driver or network related issues. When you use the above option and afterwards SQuirreL issues time out errors it is even more probable that the problems are database, JDBC driver or network related.

[Kevin Busick]

Gerd,
I did not see a "Meta Data Loading" section. I did check "Load Columns in background" and "Show SQL Results MetaData". When I did that, I went back to SQL and did the CTRL SPACE and I get a message that says "Encountered unexpected token: "." "." at line 3, column 8. Was expecting on of: "&" "&&" "::" ";" "<<" ">>" "AND" "COLLATE" "CONNECT" "EXCEPT" "FOR" "GROUP" "HAVING" "INTERSECT" "MINUS" "ORDER" "START" "UNION" "^" "|" "<eof>"</eof>

This is a JDBC connection. If I open V3.9.1 it works instantly - no issues.
If I put a coma instead of a period, it will let me get the columns but obviously doesnt execute

The SQL is
select *
from dba.HLCT_ACCOUNT a
where a.

[Gerd Wagner]

The "Meta Data Loading" section is a very recently added feature. That's why it is available in the latest snapshot only. But judging from your example and error message I do not believe anymore the feature would help.

Still I can't reproduce your problem. What database and JDBC driver are you using?

BTW the hint it worked in 3.9.1 can become helpful when I'm able to reproduce it.

[Sam Montgomery]

I am having the same issue as Kevin, and get the same "Encountered unexpected token" error. Auto-complete works when the table is not aliased, but does not work when aliased. Everything works correctly on version 3.9.1.

Squirrel SQL Version: 4.2.0
Driver: HP Vertica (vertica-jdbc-9.1.1-0.jar)

[Gerd Wagner]

Sam,
I just fixed the following bug (excerpt from change log):

Code completion didn't work for SQLs with aliased tables when a table was (catalog and/or schema)
  qualified and the upper/lower case spelling of the qualifier didn't match the catalog's and/or schema's name in the database.
  Possibly there are connections to more recent comments to bug #1406.

The fix is available in this snapshot:
https://sourceforge.net/projects/squirrel-sql/files/3-snapshots/snapshot-20210701_2344/

Please give it a try. If it doesn't solve your problem could you please post an example SQL including the DDLs for tables/catalogs/schemas used in the example.

Thanks
Gerd

[Sam Montgomery]

Hi Gerd,

Thanks for the reply, and sorry for the delay in responding. I have tested the shapshot and auto-complete works as desired. I also tested auto-complete when using proper schema case in the stable version (4.2.0), and auto-complete works there as well. Thank you for your help with this!

Thanks,
Sam