Loading ...
Sorry, an error occurred while loading the content.
 

Database Introspection with Jaybird

Expand Messages
  • Fidel Viegas
    Hello folks, I am doing a few database introspections using Jaybird, and I have ran into some problems. I am able to extract the metadata without any problems,
    Message 1 of 13 , Feb 6, 2008
      Hello folks,

      I am doing a few database introspections using Jaybird, and I have ran
      into some problems. I am able to extract the metadata without any
      problems, if the tables are created without delimiters. The minute I
      try to introspect a database which has table whose table names and
      column names are delimited with double quotes, then the whole thing
      fails. That is, I am able to get the table names, but I am unable to
      get the columns for that particular table. How do I write some code
      that will allow me to get table names and column names with or without
      delimiters?

      I am using the DatabaseMetadata.getColumns

      Another question is what sort of pattern shall I use to return only
      the columns for user defined tables?

      Thanks in advance,

      Fidel.
    • Roman Rokytskyy
      ... It s a bug which I missed when preparing 2.0.2 (http://tracker.firebirdsql.org/browse/JDBC-106). I just checked the history, the issue was fixed for
      Message 2 of 13 , Feb 6, 2008
        > I am doing a few database introspections using Jaybird, and I have ran
        > into some problems. I am able to extract the metadata without any
        > problems, if the tables are created without delimiters. The minute I
        > try to introspect a database which has table whose table names and
        > column names are delimited with double quotes, then the whole thing
        > fails. That is, I am able to get the table names, but I am unable to
        > get the columns for that particular table. How do I write some code
        > that will allow me to get table names and column names with or without
        > delimiters?
        >
        > I am using the DatabaseMetadata.getColumns

        It's a bug which I missed when preparing 2.0.2
        (http://tracker.firebirdsql.org/browse/JDBC-106).

        I just checked the history, the issue was fixed for indexes
        (DatabaseMetaData.getIndexInfo method), but remains for getColumns. As
        workaround, you can check whether the name has lower-case characters and
        then quote the table name (prepend and append the double-quote character).

        I will, very likely, prepare 2.0.3 this weekend.

        > Another question is what sort of pattern shall I use to return only
        > the columns for user defined tables?

        Pass as types parameter new String[]{"TABLE"}.

        Roman
      • Fidel Viegas
        On Feb 6, 2008 9:58 PM, Roman Rokytskyy wrote: Hi Roman, ... I figured that out after a lot of tests. What I did was check if the string
        Message 3 of 13 , Feb 6, 2008
          On Feb 6, 2008 9:58 PM, Roman Rokytskyy <rrokytskyy@...> wrote:

          Hi Roman,

          > It's a bug which I missed when preparing 2.0.2
          > (http://tracker.firebirdsql.org/browse/JDBC-106).
          >
          > I just checked the history, the issue was fixed for indexes
          > (DatabaseMetaData.getIndexInfo method), but remains for getColumns. As
          > workaround, you can check whether the name has lower-case characters and
          > then quote the table name (prepend and append the double-quote character).
          >
          > I will, very likely, prepare 2.0.3 this weekend.

          I figured that out after a lot of tests. What I did was check if the
          string was all uppercase, which ends up being the same.

          > Pass as types parameter new String[]{"TABLE"}.

          Got that one sorted as well, but that is for getTables method. Which
          patterns do you use for the getColumns? Let's say I want to get only
          the user defined tables using getColumns. I used % for both
          tableNamePattern and columnNamePattern, which obviously returns all
          the tables (including system tables), which is not exactly what I
          want.

          Anyway, I am using the getTables, make a query on that table and get
          the column names from the query.

          Thanks a lot and I will be waiting for the new release.

          All the best,

          Fidel.
        • Roman Rokytskyy
          ... There no such possibility in JDBC. The getColumns usually is used to fetch columns of one table. If you want to get all in one go, you have to fetch all
          Message 4 of 13 , Feb 6, 2008
            >> Pass as types parameter new String[]{"TABLE"}.
            >
            > Got that one sorted as well, but that is for getTables method. Which
            > patterns do you use for the getColumns? Let's say I want to get only
            > the user defined tables using getColumns. I used % for both
            > tableNamePattern and columnNamePattern, which obviously returns all
            > the tables (including system tables), which is not exactly what I
            > want.

            There no such possibility in JDBC. The getColumns usually is used to
            fetch columns of one table. If you want to get all in one go, you have
            to fetch all tables via getTables, cache the result set and then after
            getting all columns via getColumns check whether table is TABLE, SYSTEM
            TABLE or VIEW.

            Roman
          • Fidel Viegas
            ... Thanks!
            Message 5 of 13 , Feb 7, 2008
              On 2/7/08, Roman Rokytskyy <rrokytskyy@...> wrote:

              >
              > There no such possibility in JDBC. The getColumns usually is used to
              > fetch columns of one table. If you want to get all in one go, you have
              > to fetch all tables via getTables, cache the result set and then after
              > getting all columns via getColumns check whether table is TABLE, SYSTEM
              > TABLE or VIEW.

              Thanks!
            • Fidel Viegas
              Hi Roman, I really needed to use this feature, so I downloaded the source code for version 2.1.2 and fixed the bug. It is found in line 5529 of
              Message 6 of 13 , Feb 7, 2008
                Hi Roman,

                I really needed to use this feature, so I downloaded the source code
                for version 2.1.2 and fixed the bug. It is found in line 5529 of
                main/org/firebirdsql/jdbc/AbstractDatabaseMetaData.java

                It should be
                return pattern;

                instead of
                return pattern.toUpperCase();

                as described in the bug report http://tracker.firebirdsql.org/browse/JDBC-106

                Thanks for everything.

                All the best,

                Fidel.
              • Roman Rokytskyy
                Hi, ... That complete fix is a bit more complicated, but I think that this code can fit your needs. The issue is that it is quite possible that people will use
                Message 7 of 13 , Feb 7, 2008
                  Hi,

                  > I really needed to use this feature, so I downloaded the source code
                  > for version 2.1.2 and fixed the bug. It is found in line 5529 of
                  > main/org/firebirdsql/jdbc/AbstractDatabaseMetaData.java
                  >
                  > It should be
                  > return pattern;
                  >
                  > instead of
                  > return pattern.toUpperCase();
                  >
                  > as described in the bug report http://tracker.firebirdsql.org/browse/JDBC-106

                  That complete fix is a bit more complicated, but I think that this code
                  can fit your needs. The issue is that it is quite possible that people
                  will use lower-case notation for unqouted table names, in this case your
                  fix won't work. Just as a warning.

                  Roman
                • Fidel Viegas
                  ... I always create my tables in lowercase. The only time I am actually using mixed case quoted is when I use a tool called xplain2sql, which generates quoted
                  Message 8 of 13 , Feb 7, 2008
                    On Feb 7, 2008 1:39 PM, Roman Rokytskyy <rrokytskyy@...> wrote:

                    > That complete fix is a bit more complicated, but I think that this code
                    > can fit your needs. The issue is that it is quite possible that people
                    > will use lower-case notation for unqouted table names, in this case your
                    > fix won't work. Just as a warning.

                    I always create my tables in lowercase. The only time I am actually
                    using mixed case quoted is when I use a tool called xplain2sql, which
                    generates quoted identifiers.

                    I have run the following tests:

                    create table mytable (id integer not null primary key, val varchar(30));
                    CREATE TABLE MYNEWTABLE (ID INTEGER NOT NULL PRIMARY KEY, VAL VARCHAR(30));
                    create table "MyNewTable" ("Id" integer not null primary key, "value"
                    varchar (30));
                    create "mytable" ("id" integer not null primary key, "val" varchar(30));

                    I have created them using the API, not isql. And they all work. I am
                    able to extract the metadata from them, as well as data.

                    But, since you have implemented the code, you know better than me. I
                    will wait for the update. Just like you said, it is working for me.

                    Thanks in advance,

                    Fidel.
                  • Roman Rokytskyy
                    ... Check the CREATE TABLE mytable( id AS INTEGER); and then using the mytable string get the columns for the table. But this is quite legitimate test case.
                    Message 9 of 13 , Feb 7, 2008
                      > I always create my tables in lowercase. The only time I am actually
                      > using mixed case quoted is when I use a tool called xplain2sql, which
                      > generates quoted identifiers.
                      >
                      > I have run the following tests:

                      Check the

                      CREATE TABLE mytable( id AS INTEGER);

                      and then using the "mytable" string get the columns for the table. But
                      this is quite legitimate test case.

                      Roman
                    • Fidel Viegas
                      ... Hum, ok. I didn t think of that. Thanks!
                      Message 10 of 13 , Feb 7, 2008
                        On Feb 7, 2008 2:18 PM, Roman Rokytskyy <rrokytskyy@...> wrote:

                        > Check the
                        >
                        > CREATE TABLE mytable( id AS INTEGER);
                        >
                        > and then using the "mytable" string get the columns for the table. But
                        > this is quite legitimate test case.

                        Hum, ok. I didn't think of that.

                        Thanks!
                      • Fidel Viegas
                        ... I didn t notice you had the AS keyword there. Which version of Firebird uses this syntax? I am using Firebird 2.0.3, and that gives me a syntax error. But
                        Message 11 of 13 , Feb 7, 2008
                          On Feb 7, 2008 2:18 PM, Roman Rokytskyy <rrokytskyy@...> wrote:

                          > Check the
                          >
                          > CREATE TABLE mytable( id AS INTEGER);
                          >
                          > and then using the "mytable" string get the columns for the table. But
                          > this is quite legitimate test case.

                          I didn't notice you had the AS keyword there. Which version of
                          Firebird uses this syntax? I am using Firebird 2.0.3, and that gives
                          me a syntax error.

                          But if I do

                          CREATE TABLE mytable (id INTEGER);

                          That works.
                        • Roman Rokytskyy
                          ... sorry, typo. ... hmmm... shouldn t... i will check again. Roman
                          Message 12 of 13 , Feb 7, 2008
                            > Firebird uses this syntax? I am using Firebird 2.0.3, and that gives
                            > me a syntax error.

                            sorry, typo.

                            > But if I do
                            >
                            > CREATE TABLE mytable (id INTEGER);
                            >
                            > That works.

                            hmmm... shouldn't... i will check again.

                            Roman
                          • Fidel Viegas
                            ... Hi Roman, I hadn t noticed this properly. I was using the table name without quoting, that is why it was working. If you create the table above, and then
                            Message 13 of 13 , Feb 14, 2008
                              On Thu, Feb 7, 2008 at 2:18 PM, Roman Rokytskyy <rrokytskyy@...> wrote:
                              > Check the
                              >
                              > CREATE TABLE mytable( id AS INTEGER);
                              >
                              > and then using the "mytable" string get the columns for the table. But
                              > this is quite legitimate test case.

                              Hi Roman, I hadn't noticed this properly. I was using the table name
                              without quoting, that is why it was working. If you create the table
                              above, and then you try to call it using the string, it will obviously
                              fail, because firebird stores the table names in uppercase. This
                              doesn't even work if you use isql. Now, if you declare them using the
                              delimiters, then it should work. Was this what you were trying to
                              demonstrate?

                              Did you run any more tests on the driver?

                              I look forward to hearing from you.

                              All the best,

                              Fidel.
                            Your message has been successfully submitted and would be delivered to recipients shortly.