0

When I try to retrieve stored procedures from an SQL Server database using the SchemaCrawler API, I get this error:

12:28:07.427 [main] INFO  schemacrawler.crawl.SchemaCrawler - Retrieving routines
12:28:07.767 [main] WARN  schemacrawler.crawl.RoutineRetriever - JDBC driver does not support retrieving functions
java.lang.AbstractMethodError: null
    at net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData.getFunctions(JtdsDatabaseMetaData.java:3570) ~[jtds-1.3.1.jar:1.3.1]
    at schemacrawler.crawl.RoutineRetriever.retrieveFunctions(RoutineRetriever.java:175) ~[schemacrawler-14.02.02.jar:na]
    at schemacrawler.crawl.SchemaCrawler.crawlRoutines(SchemaCrawler.java:214) [schemacrawler-14.02.02.jar:na]
    at schemacrawler.crawl.SchemaCrawler.crawl(SchemaCrawler.java:564) [schemacrawler-14.02.02.jar:na]
    at schemacrawler.utility.SchemaCrawlerUtility.getCatalog(SchemaCrawlerUtility.java:49) [schemacrawler-14.02.02.jar:na]
    at schemacrawler.utility.SchemaCrawlerUtility.getCatalog(SchemaCrawlerUtility.java:57) [schemacrawler-14.02.02.jar:na]
    at com.expedia.cgs.db.ExportScripts.main(ExportScripts.java:41) [classes/:na]

The jtds driver supports DatabaseMetaData.getProcedures() but not DatabaseMetaData.getFunctions(). Is there a workaround?

UPDATE: Here is my code:

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.slf4j.bridge.SLF4JBridgeHandler;
import schemacrawler.schema.Catalog;
import schemacrawler.schema.Column;
import schemacrawler.schema.Routine;
import schemacrawler.schema.Schema;
import schemacrawler.schema.Table;
import schemacrawler.schema.View;
import schemacrawler.schemacrawler.DatabaseConnectionOptions;
import schemacrawler.schemacrawler.RegularExpressionInclusionRule;
import schemacrawler.schemacrawler.SchemaCrawlerException;
import schemacrawler.schemacrawler.SchemaCrawlerOptions;
import schemacrawler.schemacrawler.SchemaInfoLevelBuilder;
import schemacrawler.utility.SchemaCrawlerUtility;

public class ExportScripts {

    public static void main(String[] args) throws SchemaCrawlerException, SQLException {
        SLF4JBridgeHandler.removeHandlersForRootLogger();
        SLF4JBridgeHandler.install();

        // Create a database connection
        final DataSource dataSource = new DatabaseConnectionOptions("jdbc:sqlserver://myDatabase;appName=SchemaCrawler;useCursors=true");
        final Connection connection = dataSource.getConnection("username", "password");

        // Create the options
        final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
        // Set what details are required in the schema - this affects the
        // time taken to crawl the schema
        options.setSchemaInfoLevel(SchemaInfoLevelBuilder.standard());
        options.setRoutineInclusionRule(new RegularExpressionInclusionRule("ContentGeneration\\.dbo.*"));
        options.setSchemaInclusionRule(new RegularExpressionInclusionRule("ContentGeneration\\.dbo.*"));

        // Get the schema definition
        final Catalog catalog = SchemaCrawlerUtility.getCatalog(connection,
                options);

        for (final Schema schema : catalog.getSchemas()) {
            System.out.println(schema);
            for (final Routine routine : catalog.getRoutines()) {
                System.out.println("r--> " + routine);
                System.out.println("definition: " + routine.getDefinition());
            }
            for (final Table table : catalog.getTables(schema)) {
                System.out.print("o--> " + table);
                if (table instanceof View) {
                    System.out.println(" (VIEW)");
                } else {
                    System.out.println();
                }

                for (final Column column : table.getColumns()) {
                    System.out.println("     o--> " + column + " ("
                            + column.getColumnDataType() + ")");
                }
            }
        }
    }
}
Gili
  • 86,244
  • 97
  • 390
  • 689
  • Send a query instead of using SchemaCrawler, like `SELECT o.name, m.definition FROM sys.objects INNER JOIN sys.sql_modules...` – Aaron Bertrand Aug 17 '15 at 18:43
  • @AaronBertrand That worked, but I had to modify your query to `SELECT o.name, m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON m.object_id=o.object_id`. That said, can we make SchemaCrawler do this? Or does this functionality not exist? – Gili Aug 17 '15 at 20:03
  • Yeah my comment was just meant to be a general idea, not working code. And I have no idea about SchemaCrawler. I find that most helpers like this tend to get the simple stuff right, but fall apart whenever you need something exotic (and I hesitate to call functions exotic). – Aaron Bertrand Aug 17 '15 at 20:14
  • You can make SchemaCrawler use this query. Take a look at http://sualeh.github.io/SchemaCrawler/INFORMATION_SCHEMA.html – Sualeh Fatehi Aug 17 '15 at 20:16
  • Also, please provide more details, such as the command-line you are using. – Sualeh Fatehi Aug 17 '15 at 20:19
  • @SualehFatehi I am using the API, not command-line options. As such, I don't think that it makes sense to use `schemacrawler.config.properties`. How do I do this from the API? – Gili Aug 17 '15 at 20:20
  • This functionality is built into SchemaCrawler, for SQL Server. Please post your code. – Sualeh Fatehi Aug 17 '15 at 20:22
  • @SualehFatehi I have updated the question with my code. Please note that I am using schemacrawler from Maven, not a SQL-Server specific package (if such a thing exists). – Gili Aug 17 '15 at 20:24
  • Create a Config object, with host, database, username and password properties set. Then, use SqlServerDatabaseConnector.getDatabaseSystemConnector().newDatabaseConnectionOptions(Config) to obtain a connection. This will plugin to SchemaCrawler SQL Server functionality. In Maven, include http://search.maven.org/#artifactdetails%7Cus.fatehi%7Cschemacrawler-sqlserver%7C14.02.02%7Cjar – Sualeh Fatehi Aug 17 '15 at 20:32
  • @SualehFatehi Please post a formal answer containing example code for doing this. I am trying to get it working in parallel but haven't figured it out yet. – Gili Aug 17 '15 at 20:49

1 Answers1

0

Here is how to get full SchemaCrawler support for Microsoft SQL Server.

  1. Include the SchemaCrawler jar that has Microsoft SQL Server support, in your Maven project.

<dependency> <groupId>us.fatehi</groupId> <artifactId>schemacrawler-sqlserver</artifactId> <version>14.02.02</version> </dependency>

  1. Use code similar that this below.

    final DatabaseSystemConnector dbSystemConnector = new SqlServerDatabaseConnector().getDatabaseSystemConnector();

    final DatabaseSpecificOverrideOptions databaseSpecificOverrideOptions = dbSystemConnector.getDatabaseSpecificOverrideOptionsBuilder().toOptions();

    final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions(); schemaCrawlerOptions.setSchemaInfoLevel(InfoLevel.maximum.buildSchemaInfoLevel());

    final Catalog catalog = SchemaCrawlerUtility.getCatalog(getConnection(), databaseSpecificOverrideOptions, schemaCrawlerOptions);

  2. Loop over the routines, and use Routine.getDefinition() to get the definitions.

Sualeh Fatehi, SchemaCrawler

Sualeh Fatehi
  • 4,700
  • 2
  • 24
  • 28
  • In the next version of SchemaCrawler, you will simply need to do: final Catalog catalog = SchemaCrawlerUtility.getCatalog(connection, "sqlserver", options); – Sualeh Fatehi Aug 18 '15 at 01:02
  • This didn't work for me. I still get the `AbstractMethodError` and `Routine.getDefinition()` is empty. – Gili Aug 18 '15 at 15:29
  • The AbstractMethodError is a warning. Let me try to reproduce your situation, and see why the definitions are not being populated. – Sualeh Fatehi Aug 18 '15 at 16:50