Interactive shell - SQL Server: url setting for database and schemas doesn't connect to the right schemas

[aisbergde]

Bug Report

it looks like the only way to learn how to generate output files is to use examples on kataconca.com

I try to repeat the steps with my own SQL Server instances, to learn, how I could generate diagrams. But I get issues at the very beginning. I can't connect to the desired database and desired schemas.

Is it possible to learn the usage and commands without this kataconda examples, just from some documentation?
If it is not possible to use the interactive shell, I could use non-interactive. But where to find the required commands to generate output files, not using kataconda?

The following works when not interactive:

schemacrawler --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=WideWorldImporters; --schemas=WideWorldImporters\..* --info-level=standard --command=list --user=readonly --password=xxx

and I try similar in the interactive shell

connect --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=WideWorldImporters; --schemas=WideWorldImporters\..* --info-level=standard --command=list --user=readonly --password=xxx
load --info-level=standard
execute --command=schema --no-info --output-file=./share/schema.txt

but the generated schema.txt looks like a connection to the server ignoring --schemas, I get some objects from different databases, the same what I get when not interactively connected to the database without setting --schemas

I also tried this:

execute --command=schema --no-info --output-file=./share/schema2.txt --schemas=WideWorldImporters\..*

But with the same effect

Issue

• I want to learn how I can create diagram output
• I can't find the documentation how to do this not interactively
• I try to follow the learning examples, but with my own databases
• I can't connect to specific schemas the same way as I can do non-interactiv

Specify the

• version of SchemaCrawler that you are using 16.14.1
• version of Java that you are using
• operating system and version that you are using - Windows 10 10.0
• relational database and version that you are using - SQL Server 2017 and 2019
• JDBC driver and version that you are using - I would like to know, how can I know?

schemacrawler> help
SchemaCrawler 16.14.1
Database schema discovery and comprehension tool
https://www.schemacrawler.com/
Copyright (c) 2000-2021, Sualeh Fatehi <[email protected]>.

You can search for database schema objects using regular expressions,
and output the schema and data in a readable text format. You can find
potential schema design issues with lint. The output serves for
database documentation is designed to be diff-ed against other database
schemas. SchemaCrawler also generates schema diagrams.

System Information:
Windows 10 10.0
AdoptOpenJDK OpenJDK 64-Bit Server VM 11.0.10+9

Available SchemaCrawler database plugins:
 db2            IBM DB2
 hsqldb         HyperSQL DataBase
 mysql          MySQL
 offline        SchemaCrawler Offline Catalog Snapshot
 oracle         Oracle
 postgresql     PostgreSQL
 sqlite         SQLite
 sqlserver      Microsoft SQL Server


Available SchemaCrawler catalog loader plugins:
 weakassociationsloader
                Loader for weak associations
 oracleloader   Loader for Oracle databases
 offlineloader  Loader for offline databases
 attributesloader
                Loader for catalog attributes, such as remarks or tags
 countsloader   Loader for table row counts
 schemacrawlerloader
                Loader for SchemaCrawler metadata catalog

[sualeh]

@aisbergde - This could be better documented, I admit. This is an open source project, and I am only able to focus on a limited number of things at a time. If you need consulting effort, please see the consulting page on the website.

Your best bet is to use the interactive shell. Once you work out what options you need to use, you can string them together in a single command-line. The Katacoda tutorial on the interactive shell walks you through the following concepts:

1. connect: the first thing you need to do is to connect to your database - run help connect in the shell for more information.
2. load: loads the database schema metadata into memory - run help load in the shell for more information.
3. execute: executes a SchemaCrawler command, including commands to generate diagram - run help execute in the shell for more information.

The load command has many loaders, which run sequentially. You provide options for these loaders with the load command. The list of loaders is provided in the beginning of the help. You can get help for a loader by running, for example, help loader:weakassociationsloader. (I noticed that the help loaders option is broken, and I will fix that.)

The execute command has many command. You provide options for these command with the execute command. The list of commands is provided in the beginning of the help. You can get help for a command by running, for example, help command:schema. (I noticed that the help commands option is broken, and I will fix that.)

Similarly, for connections, you can run something like help server:sqlserver. (I noticed that the help servers option is broken, and I will fix that.)

I will add a help drivers option to point to documentation on available drivers.

In between the connect and the load step, you can:

1. limit: Limit the tables and routines loaded into memory - this is as if the table that are not loaded do not exist - see help limit for more information.
2. grep: Search for metadata using regular expression - see help grep for more information.
3. filter: Filter table - see help filter for more information.

Now that I have typed up all this information, I will add this to the Katacoda tutorial and the website too.

Hope this helps you.

[aisbergde]

@sualeh I know, this is an open source project. That's why I share my experience, because I hope this will help other users when they get similar problems. I want to use SchemaCrawler also for open source projects, where I also spent my time. But I will not buy consulting for the usage of open source software in my own open source projects. Consulting I could and would order in a paid project where the final customer could or should pay for this.

The idea of SchemaCrawler is very good, but it seems to be non-trivial to use in with any database, especially with SQL Server. And I realize that you can't expect the database developer to support everything for free.

Also the Katacoda is an excellent solution to learn the usage, I went through it, but only with the sqlite sample, which works fine and demonstrates all the features.

Your best bet is to use the interactive shell.

I wanted to try it with my own SQL Server databases. Because I hope to eventually be able to create diagrams for my own databases. And there I fail already at the first step. In the other issue #410 (which was unfortunately locked) I have also investigated exactly this issue and described what the reason for the problems could be and even presented a solution. But this solution does not work for interactive use.

And I don't know if SchemaCrawler has already been tested interactively with the SQL server, because I can hardly imagine that there should be such problems only with me.

So here are the options:

• It works immediately without problems
• There are difficulties with the use because something does not work as expected or because something is operated incorrectly. And now you can do the following:
  • Stop using it, because it doesn't work right away.
  • point out problems in the hope that there is a solution or that by fixing problems the product can be improved. I even posted a solution, but it is just "locked":

Repository owner locked as too heated and limited conversation to collaborators

If this is the way, then I don't know what to do. I spent hours, I found the reason for the issue and solutions to solve the issues and posted them. And I asked questions. And I get answers, like:

Please see the database support page - there is a link to the driver documentation

Of course, I did this. But maybe I am too stupid to find the answers there, otherwise I would not ask. I don't know from the documentation which driver is used at runtime, because I have many JDBC drivers on my PC and there is no information at runtime, which driver is used by SchemaCrawler. And I am not a Java developer.

Maybe my issues are related to the

load: loads the database schema metadata into memory - run help load in the shell for more information.

schemacrawler> help load
** Load database metadata into memory

  -i, --info-level=<infolevel>
         <infolevel> is one of unknown, minimum, standard, detailed, maximum
         The info level determines the amount of database metadata retrieved,
           and also determines the time taken to crawl the schema
         Optional, defaults to standard

There is no setting for the --schemas in the help.

help execute also doesn't say something about the --schemas

help connect also doesn't say something about the --schemas

But the issue with SQL server is: to make it work the database needs to used twice: when connecting and when using the --schemas (not interactively). But you just locked this issue. Although I think that's exactly where the problem is described. I think, the interactive shell doesn't work with SQL Server in some configurations, because it is not possible to define the --schemas. Or it is possible, but is not contained in the help.

I guess I'll give up for now then, because I have no idea how to interactively get SchemaCrawler to load the data of the right SQL Server database in the right way, so that all objects in that database are also displayed. And I'm afraid this issue here will then also just be locked.

So if my feedback is not perceived as something useful, but as intrusive and burdensome, and the issues just will be locked, then I might as well not bother giving detailed feedback. Maybe other users will get it right the first time with SQL Server.

[sualeh]

@aisbergde - I locked the other issue to encourage you to open targeted issues for each on of your problems, not one single catch-all issue. Your feedback is valuable, and I made a new release just for you. I am currently working on improving documentation based on some of your suggestions. If you want to have a discussion, please have it in the discussion section, not in the issues section.

Please read the message above carefully. I am repeating it here:

In between the connect and the load step, you can:

• limit: Limit the tables and routines loaded into memory - this is as if the table that are not loaded do not exist - see help limit for more information.
• grep: Search for metadata using regular expression - see help grep for more information.
• filter: Filter table - see help filter for more information.

If you run help limit in the interactive shell, it will show you how to limit schemas.

[sualeh]

And I don't know if SchemaCrawler has already been tested interactively with the SQL server, because I can hardly imagine that there should be such problems only with me.

SchemaCrawler runs tests with SQL Server with every build, and has a large community of users who use it with SQL Server.

[sualeh]

@aisbergde - Converting to a discussion.

[sualeh]

If this works:
schemacrawler --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=WideWorldImporters; --schemas=WideWorldImporters\..* --info-level=standard --command=list --user=readonly --password=xxx

Then try this in the interactive shell

connect --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=WideWorldImporters; --user=readonly --password=xxx
limit --schemas=WideWorldImporters\..* 
load --info-level=standard
execute --command=list --no-info --output-file=./share/schema.txt

[aisbergde]

I got it. The limit does the magic, is doesn't "limit" in my case, but it "extends" the selection :-)
There are still issues with the regex, I will open separate issues.

[sualeh]

@aisbergde - please mark this question as answered, if you are done.

[aisbergde]

@sualeh the issue is not solved. In the shell the regex doesn't work correctly. limit works as expected outside the shell, but the same limit delivers more objects when used inside the shell. see #439