Installation for Mac

[jfyu]

Hi,

The sqlwbconsole.sh is not included in the standard mac installation of SQLWorkbench J. So instead, I did

1. download the shell scripts from the full, generic package zip
   http://www.sql-workbench.net/downloads.html

2)unzip, get the shell scripts

3)move the shell scripts to /Applications/SQLWorkbenchJ.app/Contents/Java/

now this plugin works, sort of. I still cannot choose a profile. CtrlP does not work. I can attempt to execute a command, but it takes forever so I am assuming it didn't work (although I get a query time)

I am on MacVim 8.0.

And it seems that I can only launch the BufferConnect inside the Java folder. When I do that I get:
=SQL 1
readlink: illegal option -- f
usage: readlink [-n] [file ...]
�SQL Workbench/J�
SQL Workbench/J (121) console interface started.
Enter exit to quit.
Enter WbHelp for a list of SQL Workbench/J specific commands
Config directory: /Users/vlu747/.sqlworkbench

But if I launch vim in another folder, I see nothing.
Possibly having to edit the shell scripts?

Any insights would be appreciated..

[cosminadrianpopescu]

I don't have any Mac to test, but it might be because of the jline library. Try to disable it in your SQL Workbench/J config file:

workbench.console.use.jline=false

This should be in the user profile and then .workbench folder by default.

[jfyu]

Edit: previously stated error in this post was due to permissions as well as the use of terminal vim, which I'm not sure why. MacVim still gives the same response as described in the initial post even when added the jline library disabling command.

Edit again: I am currently using an company in-house package to connect to my SQL database. So this issue is not urgent and I may not be following this up very quickly. But would be nice for Mac users to be able to use this plugin, it looks very useful. Thanks for developing it.

[cosminadrianpopescu]

Can you access from the terminal the sql workbench/j and send queries and get responses?

Can you try in the terminal to run:

sqlwbconsole.sh -feedback=true -showProgress=false -showTiming=true -nosettings

If this goes ok, then try to do a wbconnect <your-profile> and see what this returns.

Thank you.

[edwarddlbrown]

jfyu, as you suggested, the script needs to be modified. This is because OSX's implementation of readline does not have a -f option... the simplest solution for this is to just hardcode the SCRIPT_PATH variable to wherever you unzipped the files e.g. (SCRIPT_PATH="~/sqlworkbench/") and to comment out the SCRIPT_PATH="$(dirname "$(readlink -f "$0")")" line
A more robust solution can be found here: https://stackoverflow.com/questions/1055671/how-can-i-get-the-behavior-of-gnus-readlink-f-on-a-mac

However, I'm also having troubles. The commands you suggest above work fine and I am able to query my database, however, when I try to execute SWDbExplorer sahm I get an error that seems to indicate it cannot find the profile I supplied. The path to the config file seems to be set and works fine when specified in the sqlwbconsole.sh console.

Is there a way for me to specify a jdbc connection string in vim, to try it that way?

Thanks for any help! Error below:

Error detected while processing function sw#dbexplorer#show_panel[16]..sw#dbexplorer#open_dbexplorer[3]..sw#server#execute_sql[19]..<SNR>52_trigger_event[8]..sw#report#profile_changed:
line    7:
E716: Key not present in Dictionary: sahm
E116: Invalid arguments for function has_key(profiles[profile]['props'], 'report') && profiles[profile]['props']['report'] == 'true'
E15: Invalid expression: has_key(profiles[profile]['props'], 'report') && profiles[profile]['props']['report'] == 'true'
Error detected while processing function sw#dbexplorer#show_panel[16]..sw#dbexplorer#open_dbexplorer[3]..sw#server#execute_sql:
line   44:
E631: ch_sendraw(): write failed

[cosminadrianpopescu]

@jfyu: if the problem is the readline, you could try this in the config file:

workbench.console.use.jline=false

I think jline is using readline

@edwarddlbrown:

Do you have your profile in a group? If yes, you need to add the group before SWDbExplorer group\sahm

[nikisix]

Mac user here, having same issue as @edwarddlbrown . Prepending group\ before profile name doesn't seem to do anything.

Profile file for reference:

<?xml version="1.0" encoding="UTF-8"?>
<java version="10.0.1" class="java.beans.XMLDecoder">
 <object class="java.util.ArrayList">
  <void method="add">
   <object class="workbench.db.ConnectionProfile">
    <void property="driverName">
     <string>Amazon Redshift</string>
    </void>
    <void property="driverclass">
     <string>com.amazon.redshift.jdbc.Driver</string>
    </void>
    <void property="name">
     <string>*********</string>
    </void>
    <void property="password">
     <string></string>
    </void>
    <void property="storeExplorerSchema">
     <boolean>true</boolean>
    </void>
    <void property="url">
     <string>******************************************************</string>
    </void>
    <void property="useSeparateConnectionPerTab">
     <boolean>true</boolean>
    </void>
    <void property="username">
     <string>****************</string>
    </void>
   </object>
  </void>
 </object>
</java>

I think the parsing is off b/c it picks up double quotes when specifying the group and does not when there is no group present:

E716: Key not present in Dictionary: my_profile" -profileGroup="group
E116: Invalid arguments for function has_key(profiles[profile]['props'], 'report') && profiles[profile]['props']['report'] == 'true'
E15: Invalid expression: has_key(profiles[profile]['props'], 'report') && profiles[profile]['props']['report'] == 'true'

E716: Key not present in Dictionary: my_profile
E116: Invalid arguments for function has_key(profiles[profile]['props'], 'report') && profiles[profile]['props']['report'] == 'true'
E15: Invalid expression: has_key(profiles[profile]['props'], 'report') && profiles[profile]['props']['report'] == 'true'

[cosminadrianpopescu]

Sorry for the late reply. Holidays...

I cannot reproduce the issue. Can you be more specific? And are you sure that you have the latest version. Because this sounds like an older issue. Unfortunately I don't have a Mac (or should I say fortunately, but that's another discussion :D) and on a GNU/Linux machine I don't have this issue.

[ttm436]

Ha! I have been working on this problem for the last two weeks too but haven't been able to find this thread till now.

Using MacOS: the readline -f problem can be solved many ways, but I worked around by using the Perl fix from the StackOverflow thread that edwarddlbrown posted.

>head -n 5 sqlwbconsole.sh
#!/bin/sh
# Start SQL Workbench/J in console mode
readlinkf(){ perl -MCwd -e 'print Cwd::abs_path shift' "$1";}
READLINKF="$(readlinkf "$0")"
SCRIPT_PATH="$(dirname "$READLINKF")"

I was also getting the mentioned "Key not present in Dictionary" error until I added the following to my .vimrc:

if !exists("g:ctrlp_extensions")
let g:ctrlp_extensions=[]
endif
call add(g:ctrlp_extensions, "sw_profiles")

Following this error, I was able to connect with a profile, but trying to query would give me an error like:

Command not recognized: -- @wbresult select * from staging;.  Encountered "<EOF>" 

I was able to fix this by adding the following line to my .vimrc:

let g:sw_sql_name_result_tab = 0

Risking hijacking this thread with yet another MacOS-based error, now I am finding that none of the following commands in a result buffer are doing anything at all, including showing an error:

:SWSqlShowColumn 
:SWSqlHideColumn 
:SWSqlShowOnlyColumns 
:SWSqlShowAllColumns

I have yet to find a fix for this one.

Thanks for the great work Cosmin :)

[cosminadrianpopescu]

I was also getting the mentioned "Key not present in Dictionary" error until I added the following to my .vimrc:

I removed my g:ctrlp_extensions variable from my vimrc and I don't have any issue. Anyway, release 8.2.1 should solve the error in other cases.

:SWSqlShowColumn :SWSqlHideColumn :SWSqlShowOnlyColumns :SWSqlShowAllColumns

These commands work if you have the report activated. Do you have the report activated (set in the connection profile in the GUI, the extended property report with the value true.

[ttm436]

Hmm. When confirming in the GUI, I am greeted by "Unrecognized property-name report" and loading vim gives the same error message.

Edit: I am guessing that my JDBC driver does not support the report extended property. Darn.

[cosminadrianpopescu]

Are you sure you are adding to Extended properties? I am using the build 124 (latest one of SQL Workbench/J) and I can add the extended properties without any problem. See the screen shot.

[ttm436]

Yeah, I am able to add the property without a hitch, but when connecting to the DB, the JDBC driver does not recognize the extended property "report" (with "Copy to system properties" on or off). I am using a company-custom driver, not the oracle one you show in the screenshot. Do you know what the OracleDriver is doing with the report property?

[cosminadrianpopescu]

The report property is mine. :D This is why is not recognized. Is used by this plugin. If you cannot set the property, you can do manually :call sw#report#get('<your-profile>').

<your-profile> is the name of your profile. If the profile is part of a group, it should be prefixed by the group. For example sw#report#get('MyGroup\MyProfile')

The report property set is supposed to do this automatically.

The command can last even more than 7, 8 minutes, depending on the size of your database. The problem is that since is supposed to run in the background, you won't have any output to see when it's finished. In order to check that it finished, you can either follow the log, either check the cache folder (see the g:sw_cache variable) for when a file named MyGroup-MyProfile.vim is created there.

What this is doing is launching a wbschemareport command (see https://www.sql-workbench.eu/manual/wb-info-commands.html#command-schema-report) and gathers the information about the schema metadata. Based on this information then you have very powerful intellisense auto complete, you have SWSqlReferencedBy and SWSqlReferences commands (very powerful for checking the foreign keys), and you have the SWSqlFilter and other niceties.

Hope this helps.

[ttm436]

After running sw#report#get(<Profile>), I am able to see the generated .vim file in the cache, and I have waited until it is definitely completed, but the autocomplete and commands still aren't working for column names. Though I have not had an opportunity to try the SWSqlReferencedBy or SWSqlReferences commands yet.

Looking through the generated file, I notice that not all schemas are included in the report. Only some tables are included for the schemas that are included. Even so, autocomplete and :*Column commands do not work on result buffers that are querying the included schema+tables.

I have a nagging phobia that this is an issue on my end... Thanks for the support so far.

[cosminadrianpopescu]

So I understand that the autocomplete work for tables, but not for columns? If so, if it's not sensitive, can you upload the cache .vim file?

If it's confidential, you can check it. It should contain for each table columns rows like this:

let b:schema_report['<table-name>']['columns']['<column-name>']['name'] = '<column-name>';

Are these rows there?

If not, it means that the connector that you are using does not return this info. The sw#report#get command will fetch this info executing a wbschemareport via jdbc. If your jbdc driver does not provide this info, I am afraid there is nothing I can do. You will have to change your connector. As I've already mentioned, The standard connector should have no issue.

If the rows are there, let me know and we will try to debug it somehow. Again, if you could send me the file would be great because I could simulate autocomplete's based on it and it would be great.

Another option would be to use a version previous to 6.3 of this plugin, which was not basing the autocomplete and the :*Column commands on the report, but that is much slower and less performant. And it does not have the SWSqlReferencedBy or SWSqlRefereces commands.

[ttm436]

Hmm, the data is not specifically sensitive, but I don't want to even brush up against corporate policy. Thanks for understanding.

The autocomplete does not work for either tables or columns.

There are many lines formatted like you mention:

let b:schema_report['tablename'] = {}
        let b:schema_report['tablename']['type'] = 'table'
        let b:schema_report['tablename']['schema'] = 'schema1'
        let b:schema_report['tablename']['catalog'] = ''
        let b:schema_report['tablename']['name'] = 'tablename'
        let b:schema_report['tablename']['columns'] = {}
        
        let b:schema_report['tablename']['columns']['columnname'] = {}
        let b:schema_report['tablename']['columns']['columnname']['name'] = 'columnname'
        let b:schema_report['tablename']['columns']['columnname']['type'] = -9
        let b:schema_report['tablename']['columns']['columnname']['dbms-type'] = "string"
        let b:schema_report['tablename']['columns']['columnname']['primary-key'] = 0
        let b:schema_report['tablename']['columns']['columnname']['nullable'] = 1
        let b:schema_report['tablename']['columns']['columnname']['references'] = {}

Though these seem incomplete. Looking through the file, not all columns, tables, or schemas from the database are represented. Even so, autocomplete or the commands don't work for even the names that are in the report.

I'll try out 6.3, perhaps it will work for my application at this moment.

[cosminadrianpopescu]

The 6.3 would be just a workaround. I think that there is a problem with your connector. Can you try, to see if it works the oracle provided connector?

If so, please let me know of the results. If not, maybe you can just get one table (for which the autocomplete is not working) from the cache file, change the table and column names and send me that to try some debugging.