Getting information about the tables and the corresponding fields.

[Kamal raj]

Hi, 

Imagine , the user types the below sql string into the browser of our web application:

show tables from employees

Now using Jooq I connect to the user's database and I run the following code:

Result<Record> result = context.fetch(sql);

System.out.println(result.size());

for(Record a : result) {

    System.out.println(a.toString());

    System.out.println("*********Fileds***********");

    Field<?>[] fields = a.fields();

    for(Field field : fields)

        System.out.println(field.toString());

    System.out.println("\n\n");

}

Here I want to get all the data like table name and its corresponding column names , types etc so that I can show that back to the user. However the line:

a.fields()

is returning :

Tables_in_employees

I was expecting to return the fields of the corresponding table (ie Record details). May be I'm wrong here. 

[Lukas Eder]

Hi Kamal,

Thank you very much for your message.

First off, a little disclaimer (I have to!):

2017-06-28 3:23 GMT+02:00 Kamal raj <kamalra...@gmail.com>:

Hi, 

Imagine , the user types the below sql string into the browser of our web application:

show tables from employees

Now using Jooq I connect to the user's database and I run the following code:

Result<Record> result = context.fetch(sql);

I'm sure you know what you're doing here and that you give your users the full power over your database, far beyond what's possible through SQL injection. This is usually fine when you write a database administration tool. So, I'm assuming you're writing such a database administration tool.

 

System.out.println(result.size());

for(Record a : result) {

    System.out.println(a.toString());

    System.out.println("*********Fileds***********");

    Field<?>[] fields = a.fields();

    for(Field field : fields)

        System.out.println(field.toString());

    System.out.println("\n\n");

}

Here I want to get all the data like table name and its corresponding column names , types etc so that I can show that back to the user. However the line:

a.fields()

is returning :

Tables_in_employees

I was expecting to return the fields of the corresponding table (ie Record details). May be I'm wrong here. 

Yes of course, the behaviour is correct. What happens if you run your query in some other database administration tool, such as SQL Workbench? You'll get exactly that column. Why would you expect jOOQ to do more than that?

Perhaps, the query you meant to execute was:

SELECT * FROM information_schema.columns WHERE table_schema = 'employees'

[Kamal raj]

Thanks for the response. 

On Wed, Jun 28, 2017 at 12:58 PM, Lukas Eder <lukas...@gmail.com> wrote:

Hi Kamal,

Thank you very much for your message.

First off, a little disclaimer (I have to!):

2017-06-28 3:23 GMT+02:00 Kamal raj <kamalra...@gmail.com>:

Hi, 

Imagine , the user types the below sql string into the browser of our web application:

show tables from employees

Now using Jooq I connect to the user's database and I run the following code:

Result<Record> result = context.fetch(sql);

I'm sure you know what you're doing here and that you give your users the full power over your database, far beyond what's possible through SQL injection. This is usually fine when you write a database administration tool. So, I'm assuming you're writing such a database administration tool.

Yes, basically I'm allowing the user to control their configured database. Basically its going to be a tool like MetaBase (https://github.com/metabase/metabase).

 

 

System.out.println(result.size());

for(Record a : result) {

    System.out.println(a.toString());

    System.out.println("*********Fileds***********");

    Field<?>[] fields = a.fields();

    for(Field field : fields)

        System.out.println(field.toString());

    System.out.println("\n\n");

}

Here I want to get all the data like table name and its corresponding column names , types etc so that I can show that back to the user. However the line:

a.fields()

is returning :

Tables_in_employees

I was expecting to return the fields of the corresponding table (ie Record details). May be I'm wrong here. 

Yes of course, the behaviour is correct. What happens if you run your query in some other database administration tool, such as SQL Workbench? You'll get exactly that column. Why would you expect jOOQ to do more than that?

Perhaps, the query you meant to execute was:

SELECT * FROM information_schema.columns WHERE table_schema = 'employees'

Yeah, I got it now. Thanks. 

 

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/TiP_Pj5kN68/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kamal raj]

Hi Luke, 

May be this is a novice question. I did the following code:

String sql = "SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE table_schema = 'employees'";

Result<Record> result = context.fetch(sql);

System.out.println(result.size());

for(Record a : result) {

    System.out.println(a.valuesRow().toString());

}

The result looks like:

('current_dept_emp')

('departments')

('dept_emp')

I'm wondering why the parenthesis and quotes comes with the string. Because remember then after this call I need to do another sql query for getting the table and its column informations, which might look like:

SELECT DISTINCT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'current_dept_emp'

So in this case, I want just a string without the parenthesis etc. Yes, I can remove them, but wondering why it is returning so in the first place.

Kamal.

[Lukas Eder]

Hi Kamal,

2017-06-28 16:48 GMT+02:00 Kamal raj <kamalra...@gmail.com>:

Hi Luke, 

May be this is a novice question. I did the following code:

String sql = "SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE table_schema = 'employees'";

May I suggest using this query instead, then:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'employees';

It'll certainly run faster for large schemas.

Result<Record> result = context.fetch(sql);

System.out.println(result.size());

for(Record a : result) {

    System.out.println(a.valuesRow().toString());

}

The result looks like:

('current_dept_emp')

('departments')

('dept_emp')

I'm wondering why the parenthesis and quotes comes with the string.

Yes, that's how an org.jooq.Row renders itself as a SQL string. The Row type corresponds to the SQL row value expression (i.e. when you want to work with tuples, such as row(A, B).eq(1, 2) in jOOQ). All jOOQ QueryPart types generally produce a workable SQL string when you call toString() on them.

What you wanted is simply a.get(0), instead:

Result<Record> result = context.fetch(sql);

System.out.println(result.size());

for(Record a : result) {

    System.out.println(a.get(0, String.class));

}

Notice that I think a much better solution would be to fetch everything in a single query and turn that into a Map<String, Result> as follows:

Map<?, Result<Record>> result = context.resultQuery(sql).fetchGroups("table_name");

for (Entry<?, Result<Record>> entry : result.entrySet()) {

    System.out.println(entry.getKey());

    System.out.println(entry.getValue());

    System.out.println();

}

[Kamal raj]

Hi Luke, 

Thanks a lot. It really helps.

Kamal.