BrianGodsend
commented
1 year ago NOTE: My described solution does NOT behave the same as the JDBC connector. Although the JDBC configuration dialogs have a separate input for the "Default SQL schema" and "Library list". For our purposes, I think we can consider the "Current Library" and the "Default SQL schema" as the same thing. When establishing the JDBC connection, the "Default SQL schema" will be pre-pended to the "Library list".
However, if the "Default SQL schema" also exists in the "Library list", the JDBC connection will not pre-pend the library to the library list. Instead, it will leave the library as positioned in the "Library list". In my solution, I was always pre-pending the current library (the Default SQL schema) and removing it from the library list, if found.
So, if you are attempting to replicate the behavior of the JDBC connection, you should only prepend the current library to the library list if it is (1) not the special value of *CRTDFT and (2) only if the current library is not in the library list.
worksofliam
ThePrez
It appears as-if the current library is being pre-pended to the user library list when starting a SQL job. As a result, the setting of the library list fails CPF2184. There are two problems:
The special value of *CRTDFT is being used by the SQL job as-if it were a valid library name; thus causing an error.
If the current library exists in the user portion of the library list, the resulting library list will contain the same library name twice; which is an error.
It should be noted that when the current library is set to a valid library name, the that library is added to the library list between the system (SYS) portion of the library list and the user (USR) portion of the library list. Despite IBM resolving the special value of CRTDFT to QGPL as needed, IBM does not* insert QGPL between the system and user portions of the library list.
Ideally, the setting of the current library should be done by explicitly setting the current library (CHGCURLIB) or the current library should be specified on the CURLIB() parameter of the CHGLIBL command; not included as the first library in the library list.
If the current library cannot be handled in either of the above manners and it must be added to the user library list, then the following guidelines should be considered:
1) If the current library is *CRTDFT, then do not pre-pend the user library list.
2) If the current library is valid library name (not the special value CRTDFT), pre-pend the user library list with the current library name and* remove the current library if it is found in the user library list.
Example 1: If the user library list on the connection is "MYLIB1,MYLIB2,QTEMP" and the current library is set to CRTDFT, the SQL job will try to establish the library list as "CRTDFT,MYLIB1,MYLIB2,QTEMP". Because *CRTDFT is not a valid library name, the setting of the library list will fail.
In this example, the library list should be set to "MYLIB1,MYLIB2,QTEMP". The library QGPL should simply not be included in the library list.
Example 2: If the user library list on the connection is "ALIB1,BLIB2,MYGPL,QTEMP" and the current library is set to MYGPL, the SQL job will try to establish the library list as "MYGPL,ALIB1,BLIB2,MYGPL,QTEMP". Because MYGPL appears twice in the library list, the setting of the library list will fail.
In this example, assuming the current library must be included in the library list, I would expect the library list to be set to "MYGPL,ALIB1,BLIB2,QTEMP".
Active extensions
``` AWS Toolkit (aws-toolkit-vscode): 1.81.0 C# (csharp): 1.26.0 COBOL (cobol): 9.7.23 Code Coverage for IBM i (code-coverage-ibmi): 0.1.7 Code for IBM i Walkthroughs (vscode-ibmi-walkthroughs): 0.3.1 Db2 for IBM i (vscode-db2i): 0.3.3 Debugger for Java (vscode-java-debug): 0.52.0 Dev Containers (remote-containers): 0.299.0 Docker (vscode-docker): 1.26.0 ESLint (vscode-eslint): 2.4.2 Emmet (emmet): 1.0.0 Error Lens (errorlens): 3.12.0 Git (git): 1.0.0 Git Base (git-base): 1.0.0 GitHub (github): 0.0.1 GitHub Authentication (github-authentication): 0.0.2 GitHub Pull Requests and Issues (vscode-pull-request-github): 0.68.1 GitLens — Git supercharged (gitlens): 14.1.1 HTML CSS Support (vscode-html-css): 1.13.1 IBM i Notebooks (vscode-ibmi-notebooks): 0.0.6 IntelliCode (vscodeintellicode): 1.2.30 JSON Language Features (json-language-features): 1.0.0 JavaScript Debugger (js-debug): 1.80.0 Merge Conflict (merge-conflict): 1.0.0 Microsoft Account (microsoft-authentication): 0.0.1 Node Debug Auto-attach (debug-auto-launch): 1.0.0 Nx Console (angular-console): 18.5.0 Path Intellisense (path-intellisense): 2.8.4 Prettier - Code formatter (prettier-vscode): 9.19.0 Pylance (vscode-pylance): 2023.7.30 Python (python): 2023.12.0 Server Ready Action (debug-server-ready): 1.0.0 TODO Highlight (vscode-todo-highlight): 1.0.5 Todo Tree (todo-tree): 0.0.226 TypeScript and JavaScript Language Features (typescript-language-features): 1.0.0 WSL (remote-wsl): 0.80.2 WSL: Recommender (remote-wsl-recommender): 0.0.19 ```Remote system
|Setting|Value| |-|-| |IBM i OS|V7R4M0| |Tech Refresh|7| |CCSID|37| |SQL|Enabled |Source dates|Disabled ### Enabled features |/QOpenSys/pkgs/bin|/usr/bin|/QSYS.lib/ILEDITOR.lib|/QSYS.LIB|/QIBM/ProdData/IBMiDebugService/bin| |-|-|-|-|-| |bash|attr|GENCMDXML.PGM|QZDFMDB2.PGM|startDebugService.sh| |tn5250|iconv|GETNEWLIBL.PGM||| ||ls|||| ||setccsid|||| ||tar||||Shell env
```bash ```Variants
```json { "american": "#@$", "local": "#@$" } ```Errors
```json [ { "command": "export BUILDLIB=\"QGPL\" && export CURLIB=\"QGPL\" && export USERNAME=\"BSLEETH3\" && export HOME=\"/home/BSLEETH3\" && export LIBLS=\"QHLPSYS IPMDPGM2 IPTSUTL IPWMPGM IPASPGM IPTSPGM IPTSPCH BSLIB IPSAPGM IPSRFIL3 IPDSFIL3 IPDSPGM3 DCXDTALIB DCXPGMLIB DBU11 IPPAFIL3 IPWMFIL3 IPSAFIL3 IPASFIL3 IPTSFIL3 IPTKLNK IPTSCHG033 IPTSPCH033 IPTSMOD3 QGPL QTEMP\" && env", "code": 1, "stderr": "bsh: BUILDLIB=QGPL: is not an identifier", "cwd": "/home/BSLEETH3" }, { "command": "export BUILDLIB=\"QGPL\" && export CURLIB=\"QGPL\" && export USERNAME=\"BSLEETH3\" && export HOME=\"/home/BSLEETH3\" && export LIBLS=\"QHLPSYS IPMDPGM2 IPTSUTL IPWMPGM IPASPGM IPTSPGM IPTSPCH BSLIB IPSAPGM IPSRFIL3 IPDSFIL3 IPDSPGM3 DCXDTALIB DCXPGMLIB DBU11 IPPAFIL3 IPWMFIL3 IPSAFIL3 IPASFIL3 IPTSFIL3 IPTKLNK IPTSCHG033 IPTSPCH033 IPTSMOD3 QGPL QTEMP\" && env", "code": 1, "stderr": "bsh: BUILDLIB=QGPL: is not an identifier", "cwd": "/home/BSLEETH3" }, { "command": "export BUILDLIB=\"QGPL\" && export CURLIB=\"QGPL\" && export USERNAME=\"BSLEETH3\" && export HOME=\"/home/BSLEETH3\" && export LIBLS=\"QHLPSYS IPMDPGM2 IPTSUTL IPWMPGM IPASPGM IPTSPGM IPTSPCH BSLIB IPSAPGM IPSRFIL3 IPDSFIL3 IPDSPGM3 DCXDTALIB DCXPGMLIB DBU11 IPPAFIL3 IPWMFIL3 IPSAFIL3 IPASFIL3 IPTSFIL3 IPTKLNK IPTSCHG033 IPTSPCH033 IPTSMOD3 QGPL QTEMP\" && env", "code": 1, "stderr": "bsh: BUILDLIB=QGPL: is not an identifier", "cwd": "/home/BSLEETH3" } ] ```