Not able to use Jooq-CodeGen

[Shekhar]

Please explain in your wiki/ userManual where to put the jars and how
to generate the code.

I am not able to generate the code with Command Line. Could someone
please explain with syntax...
How can i run jOOQ-codegen.jar for generating my model/domain classes?

[Lukas Eder]

Hello Shekhar

Do you think there is anything missing on this page:
http://sourceforge.net/apps/trac/jooq/wiki/Manual/META/Configuration

Cheers
Lukas

[Ravi Shekhar]

I have tried to use this

Run jOOQ code generation ¶

Code generation is currently supported by calling this class with the above property file as argument.

org.jooq.util.GenerationTool

Be sure that these elements are located on the classpath:

• The property file
• jOOQ.jar
• The JDBC driver you configured

I really don't know how to run org.jooq.util.GenerationTool

On a command line?? Or inside eclipse?? and exactly what will be the command syntax and where will be the command execution (i.e at the root of my project folder? or inside src? or inside bin folder??

--
Ravi.Shekhar
--------------------------------------------
Eklavya Technologies

Phone: 9312699455
www.eklavyatechnologies.com
---------------------------------------------

[Lukas Eder]

By "calling this class" I mean you can run it either in the command line or inside Eclipse. The property file has to be the first argument. A command-line example:

java -cp [path/to/jOOQ.jar];[path/to/

jOOQ-codegen.jar];[path/to/property/file];[path/to/jdbc-driver.jar] org.jooq.util.GenerationTool /property.file

Tell me if this helps. I will update the documentation page with some Eclipse screenshots, as this doesn't seem entirely clear...

[Lukas Eder]

Hi Ravi,

Glad it works for you and thanks for your feedback.

Lukas

2011/3/21 Ravi Shekhar <rshekhar.in@gmail.com>

Thanks for your help. I have generated the models by setting all these files into classpath. But yes, this should be documented inside documentation.

Now i am figuring out how to execute queries and parse result. Thanks for this utility.

On Mon, Mar 21, 2011 at 12:57 PM, Lukas Eder <lukas...@gmail.com> wrote:

By "calling this class" I mean you can run it either in the command line or inside Eclipse. The property file has to be the first argument. A command-line example:

java -cp [path/to/jOOQ.jar];[path/to/jOOQ-codegen.jar];[path/to/property/file];[path/to/jdbc-driver.jar] org.jooq.util.GenerationTool /property.file

Tell me if this helps. I will update the documentation page with some Eclipse screenshots, as this doesn't seem entirely clear...

--
Ravi.Shekhar
--------------------------------------------

[Lukas Eder]

I updated the documentation page with screenshots for Eclipse:
https://sourceforge.net/apps/trac/jooq/wiki/Manual/META/Configuration

I'll also add some Netbeans screenshots soon

Cheers
Lukas

2011/3/21 Lukas Eder <lukas...@gmail.com>

[Lukas Eder]

Hello Ravi

I am unable to recreate this query. Are you sure the syntax is correct?

Of course, you are right. It is a documentation error. Thanks for pointing that out. I fixed it

Cheers
Lukas

[Ravi Shekhar]

I have a mysql table "GAME" with structure as

CREATE TABLE GAME(
"GAME_ID" VARCHAR(20) PRIMARY KEY,
"GAME_START" TIMESTAMP,
"GAME_END" TIMESTAMP,
"PLAY_TIME" DECIMAL(20,0)
)

I am not able to perform 2 tasks

• Now at the time of new record, i want GAME_ID to auto populate to the max+1 i.e MAX(GAME_ID)+1 or COUNT(GAME_ID)+1 .

• Also, i am creating the new record at first and then updating the record at a later stage when the GAME is completed. At that time, i am updating GAME_END TIMESTAMP. Is there anyway, i can also update the PLAY_TIME = GAME_START-GAME_END (in seconds)--
  

Ravi.Shekhar
--------------------------------------------

[Lukas Eder]

Hello Ravi,

Note, in case of general SQL or MySQL-related questions, you will
probably get better answers on http://stackoverflow.com. As a general
rule of thumb, you can do everything with jOOQ, that you could do with
plain SQL. But you have to have an idea about the SQL statement
first...

>    - Now at the time of new record, i want GAME_ID to auto populate to the

>    max+1 i.e MAX(GAME_ID)+1 or COUNT(GAME_ID)+1 .

Use MySQL's AUTO_INCREMENT functionality on the GAME_ID column:
http://dev.mysql.com/doc/refman/5.5/en/create-table.html
http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

>    - Also, i am creating the new record at first and then updating the

>    record at a later stage when the GAME is completed. At that time, i am
>    updating GAME_END TIMESTAMP. Is there anyway, i can also update the
>    PLAY_TIME = GAME_START-GAME_END (in seconds)--

There are many ways to achieve this, both in SQL or in Java.

SQL:
- You can create a TRIGGER to always set PLAY_TIME = GAME_END -
GAME_START
- You can remove the PLAY_TIME field from the TABLE, and create a VIEW
that calculates PLAY_TIME on the fly
- You can calculate the difference at any insertion / update of data

Java:
- You can calculate the difference before insertion / update of data
and then insert / update the value as a constant. That might be the
easiest solution for you.

[Lukas Eder]

Hi Maxim,

The problem seems to lie here:

java.sql.SQLException: select command denied to user: '' for table

Your connection user might not be correctly configured.
Can you please provide me with the way you set up your JDBC
connection? What are the contents of your configuration properties
file?

Cheers
Lukas

2011/6/7 Maxim Usenko <uma.p...@gmail.com>:
> Hello Lukas!
> I apologize for my bad English..
>
> Help with this:
>
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger error
> SEVERE: Exception while fetching master data tables
> java.sql.SQLException: select command denied to user: '' for table
> 'TABLES'
>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
>        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
>        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
>        at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
>        at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
> 1137)
>        at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
> 1231)
>        at
> org.jooq.impl.AbstractResultProviderQuery.executeLazy(AbstractResultProviderQuery.java:
> 125)
>        at
> org.jooq.impl.AbstractResultProviderQuery.execute(AbstractResultProviderQuery.java:
> 68)
>        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:96)
>        at
> org.jooq.impl.AbstractResultProviderQuery.fetch(AbstractResultProviderQuery.java:
> 96)
>        at
> org.jooq.impl.AbstractDelegatingResultProviderQuery.fetch(AbstractDelegatingResultProviderQuery.java:
> 100)
>        at
> org.jooq.util.mysql.MySQLDatabase.getTables0(MySQLDatabase.java:197)
>        at
> org.jooq.util.AbstractDatabase.getMasterDataTables(AbstractDatabase.java:
> 202)
>        at
> org.jooq.util.DefaultGenerator.generate(DefaultGenerator.java:323)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:142)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:101)
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger error
> SEVERE: Error while fetching tables
> java.sql.SQLException: select command denied to user: '' for table
> 'TABLES'
>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
>        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
>        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
>        at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
>        at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
> 1137)
>        at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
> 1231)
>        at
> org.jooq.impl.AbstractResultProviderQuery.executeLazy(AbstractResultProviderQuery.java:
> 125)
>        at
> org.jooq.impl.AbstractResultProviderQuery.execute(AbstractResultProviderQuery.java:
> 68)
>        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:96)
>        at
> org.jooq.impl.AbstractResultProviderQuery.fetch(AbstractResultProviderQuery.java:
> 96)
>        at
> org.jooq.impl.AbstractDelegatingResultProviderQuery.fetch(AbstractDelegatingResultProviderQuery.java:
> 100)
>        at
> org.jooq.util.mysql.MySQLDatabase.getTables0(MySQLDatabase.java:197)
>        at
> org.jooq.util.AbstractDatabase.getTables(AbstractDatabase.java:169)
>        at
> org.jooq.util.DefaultGenerator.generate(DefaultGenerator.java:446)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:142)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:101)
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger info
> INFO: UDTs fetched             : 0
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger info
> INFO: ARRAYs fetched           : 0
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger error
> SEVERE: Error while fetching enums
> java.sql.SQLException: select command denied to user: '' for table
> 'COLUMNS'
>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
>        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
>        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
>        at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
>        at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
> 1137)
>        at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
> 1231)
>        at
> org.jooq.impl.AbstractResultProviderQuery.executeLazy(AbstractResultProviderQuery.java:
> 125)
>        at
> org.jooq.impl.AbstractResultProviderQuery.execute(AbstractResultProviderQuery.java:
> 68)
>        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:96)
>        at
> org.jooq.impl.AbstractResultProviderQuery.fetch(AbstractResultProviderQuery.java:
> 96)
>        at
> org.jooq.impl.AbstractDelegatingResultProviderQuery.fetch(AbstractDelegatingResultProviderQuery.java:
> 100)
>        at
> org.jooq.util.mysql.MySQLDatabase.getEnums0(MySQLDatabase.java:222)
>        at
> org.jooq.util.AbstractDatabase.getEnums(AbstractDatabase.java:229)
>        at
> org.jooq.util.DefaultGenerator.generate(DefaultGenerator.java:1029)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:142)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:101)
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger error
> SEVERE: Error while fetching procedures
> java.sql.SQLException: select command denied to user: '' for table
> 'proc'
>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
>        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
>        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
>        at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
>        at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
> 1137)
>        at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
> 1231)
>        at
> org.jooq.impl.AbstractResultProviderQuery.executeLazy(AbstractResultProviderQuery.java:
> 125)
>        at
> org.jooq.impl.AbstractResultProviderQuery.execute(AbstractResultProviderQuery.java:
> 68)
>        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:96)
>        at
> org.jooq.impl.AbstractResultProviderQuery.fetch(AbstractResultProviderQuery.java:
> 96)
>        at
> org.jooq.impl.AbstractDelegatingResultProviderQuery.fetch(AbstractDelegatingResultProviderQuery.java:
> 100)
>        at
> org.jooq.util.mysql.MySQLDatabase.executeProcedureQuery(MySQLDatabase.java:
> 313)
>        at
> org.jooq.util.mysql.MySQLDatabase.getProcedures0(MySQLDatabase.java:
> 280)
>        at
> org.jooq.util.AbstractDatabase.getProcedures(AbstractDatabase.java:
> 324)
>        at
> org.jooq.util.DefaultGenerator.generate(DefaultGenerator.java:1083)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:142)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:101)
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger error
> SEVERE: Error while fetching functions
> java.sql.SQLException: select command denied to user: '' for table
> 'proc'
>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
>        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
>        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
>        at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
>        at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
> 1137)
>        at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
> 1231)
>        at
> org.jooq.impl.AbstractResultProviderQuery.executeLazy(AbstractResultProviderQuery.java:
> 125)
>        at
> org.jooq.impl.AbstractResultProviderQuery.execute(AbstractResultProviderQuery.java:
> 68)
>        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:96)
>        at
> org.jooq.impl.AbstractResultProviderQuery.fetch(AbstractResultProviderQuery.java:
> 96)
>        at
> org.jooq.impl.AbstractDelegatingResultProviderQuery.fetch(AbstractDelegatingResultProviderQuery.java:
> 100)
>        at
> org.jooq.util.mysql.MySQLDatabase.executeProcedureQuery(MySQLDatabase.java:
> 313)
>        at
> org.jooq.util.mysql.MySQLDatabase.getFunctions0(MySQLDatabase.java:
> 299)
>        at
> org.jooq.util.AbstractDatabase.getFunctions(AbstractDatabase.java:340)
>        at
> org.jooq.util.DefaultGenerator.generate(DefaultGenerator.java:1112)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:142)
>        at org.jooq.util.GenerationTool.main(GenerationTool.java:101)
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger info
> INFO: Packages fetched         : 0
> 07.06.2011 15:23:27 org.jooq.impl.JooqLogger info
> INFO: GENERATION FINISHED!     : Total: 162.92ms, +141.504ms
>
> i'm too dumb to understand my fault \

[Lukas Eder]

When you use these connection properties in a standalone application:

#Configure the database connection here
jdbc.Driver=com.mysql.jdbc.Driver
jdbc.URL=jdbc:mysql://192.168.4.10/
jdbc.Schema=db_name
jdbc.User=db_user
jdbc.Password=db_password

will you then have access to the INFORMATION_SCHEMA tables?
Also, have you tried using

jdbc.URL=jdbc:mysql://192.168.4.10/[db_name]

I'm not sure, jOOQ will work correctly if no database is specified in
the connection URL

[Maxim Usenko]

I'm using mysql 4.0.20. Maybe in this my problem? There is no
INFORMATION_SCHEMA and, how i understand, it's was added in mysql
5.0+ ?!

I hope you understand me and my english (i'm reader, not writer) =)

[Lukas Eder]

Hi Maxim,

No worries about the English :)

That explains the missing tables.
Unfortunately, jOOQ does not support source code generation for MySQL
4.x. Is there a way you can get around that?

What is your use case for using jOOQ with MySQL 4.x?

Cheers
Lukas

2011/6/7 Maxim Usenko <uma.p...@gmail.com>:

[Maxim Usenko]

It's a billing, old and very ... agh.

I wrote some wrappers around its database. Now i want to rewrite my
ugly code and jooq is what i need. In 2 or 3 months we will upgrade it
and postgresql is what i whant in future.
What can i do now to generate classes?

thanks for helping

[Lukas Eder]

You could actually try to use the future Postgres schema for code
generation and run jOOQ queries with Postgres-generated classes
against your MySQL database. In principle, this could work, even if
using several database types is not yet fully supported by jOOQ (it
will be in 1.6.1 or 1.6.2).

In any case, as far as I remember MySQL 4.0, there wasn't even support
for foreign key relationships, so maybe you'll run into other problems
later on...

2011/6/7 Maxim Usenko <uma.p...@gmail.com>:

[Maxim Usenko]

Thanks for listening!
I will try to do classes from mysql 5 (create schema on it) and look
at the result.

On 7 июн, 16:56, Lukas Eder <lukas.e...@gmail.com> wrote:
> You could actually try to use the future Postgres schema for code
> generation and run jOOQ queries with Postgres-generated classes
> against your MySQL database. In principle, this could work, even if
> using several database types is not yet fully supported by jOOQ (it
> will be in 1.6.1 or 1.6.2).
>
> In any case, as far as I remember MySQL 4.0, there wasn't even support
> for foreign key relationships, so maybe you'll run into other problems
> later on...
>

> 2011/6/7 Maxim Usenko <uma.pro...@gmail.com>:

[Lukas Eder]

> I will try to do classes from mysql 5 (create schema on it) and look
> at the result.

That's probably even better!
Good luck

Lukas