Hi everyone... Could anyone please help me discover what is wrong with this code:

sql::SqlService db := SqlService.make("jdbc:mysql://127.0.0.1:3306/DbName","root")
sql := "Delete from TableName  Where Id=@id"

db.open
st := db.sql(sql)
st.prepare.execute(["id":a.Id])
db.close 

It throws :

java.sql.SQLException: Generated keys not requested. You need to specify  
Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or 
Connection.prepareStatement(). at the execute command.

What am I missing? Thanks for your help

I played around with my own MySql installation and can't duplicate that problem.

Does anyone else see that problem?

@ahhatem, can you email me some more information:

• full stack trace
• dump SqlService.meta and send that to me too

Sorry. I found this problem, but forget to report.

The bug at StatementPeer.java 238:

if (self.conn.peer.supportsGetGenKeys && FanStr.indexIgnoreCase(self.sql, "insert") != null)
{
  ResultSet rs = stmt.getGeneratedKeys();
  List keys = new List(Sys.IntType);
  while (rs.next()) keys.add(rs.getLong(1));
  if (!keys.isEmpty()) return keys;
}

I doubt the SqlServiceTest catch all error and make test passed.

Hi @ahhatem,

I didn't hear from you to see what the exactly stack trace was. But I think the line @go4 indicated might be the problem. I pushed a changeset which should fix it.

That code for handling auto-generated keys has definitely been troublesome getting it to work across various JDBC drivers.

Hi Brian,

I tried to pull the src and re-compile:

The steps I followed:

I set the JAVA_HOME to /usr/lib/jvm/java-6-openjdk/
I ran rel/bin/fan ../adm/bootstrap.fan

I have the following error:

Exception in thread "main" java.lang.NoClassDefFoundError: fanx/tools/Fan
Caused by: java.lang.ClassNotFoundException: fanx.tools.Fan
	at java.net.URLClassLoader$1.run(URLClassLoader.java:217)
 	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:321)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:266)
  Could not find the main class: fanx.tools.Fan. Program will exit.

it doesn't matter that you use rel/bin/fan. It gets classpath from environment variable FAN_HOME. fansubstitute is what you use on unix-likes.

In this case you don't need to go thru the pain of a full bootstrap build. You can just rebuild sql to get the patch.

Docs for bootstrap

Ok, I managed to recompile from the source with a bootstrap and using the new pod which fixed the error.

Now, I have a different problem... I run this to compile the pod: fan /pathToMyApp/TestingAround/build.fan ; which works, but running the pod using: fan TestingAround::Main.main; does not work.... and throws an

sql::SqlErr: No suitable driver found for jdbc:mysql://127.0.0.1:3306/ERP
  sql::ConnectionPeer.err (ConnectionPeer.java:317)
  sql::ConnectionPeer.open (ConnectionPeer.java:44)
  sql::Connection.open (Connection.fan)
  sql::SqlService.open (SqlService.fan:57)
  TestingAround::Db.getEmpty (Db.fan:128)
  TestingAround::Entity.getRow (Entity.fan:21)
  TestingAround::Entity.save (Entity.fan:111)
  TestingAround::Main.main (Main.fan:66)
  java.lang.reflect.Method.invoke (Method.java:616)
  fan.sys.Method.invoke (Method.java:536)
    fan.sys.Method$MethodFunc.callList (Method.java:182)
  fan.sys.Method.callList (Method.java:147)
  fanx.tools.Fan.callMain (Fan.java:135)
  fanx.tools.Fan.executeType (Fan.java:102)
  fanx.tools.Fan.execute (Fan.java:38)
  fanx.tools.Fan.run (Fan.java:236)
  fanx.tools.Fan.main (Fan.java:274)
Cause:
  sys::Err: java.sql.SQLException: No suitable driver found for     jdbc:mysql://127.0.0.1:3306/ERP
    java.sql.DriverManager.getConnection (DriverManager.java:640)
    java.sql.DriverManager.getConnection (DriverManager.java:200)
    sql::ConnectionPeer.open (ConnectionPeer.java:37)
    sql::Connection.open (Connection.fan)
    sql::SqlService.open (SqlService.fan:57)
      TestingAround::Db.getEmpty (Db.fan:128)
    TestingAround::Entity.getRow (Entity.fan:21)
    TestingAround::Entity.save (Entity.fan:111)
    TestingAround::Main.main (Main.fan:66)
    java.lang.reflect.Method.invoke (Method.java:616)
    fan.sys.Method.invoke (Method.java:536)
    fan.sys.Method$MethodFunc.callList (Method.java:182)
    fan.sys.Method.callList (Method.java:147)
    fanx.tools.Fan.callMain (Fan.java:135)
    fanx.tools.Fan.executeType (Fan.java:102)
    fanx.tools.Fan.execute (Fan.java:38)
    fanx.tools.Fan.run (Fan.java:236)
    fanx.tools.Fan.main (Fan.java:274)

which means It can't see the mysql-connector-java-...jar I have put in the {fan home}/lib/java But eclipse does run it! however whenever I run from eclipse it actually does not compile the source and runs the latest compile I did through the CLI

I am kinda lost here, first how do I run it from the CLI? and what could be the reason about eclipse? It used to run before the the bootstrap thing...

Thanks a lot for your help...

If you did a bootstrap build you have two fan's in your environment - so you just need to make sure you keep everything straight. Good techniques:

• "fan -version" to make sure fan in your path is one you think it is
• you can always add explicit debug in your scripts via Env.cur.homeDir

I managed to workout eclipse, but running the pod from the cli does not see the .jar file in lib/java/..

Do i need to pass any params for the fan runtime to see the .jar lib?

If I recall correctly, you put third-party jars in {FAN_HOME}/lib/java/ext/. See docs for further clarification.

The jar is visible through:

fan compilerJava::ClassPath

but still throws:

sql::SqlErr: No suitable driver found for jdbc:mysql://127.0.0.1:3306/ERP
  sql::ConnectionPeer.err (ConnectionPeer.java:317)
  sql::ConnectionPeer.open (ConnectionPeer.java:44)
  sql::Connection.open (Connection.fan)
  sql::SqlService.open (SqlService.fan:57)

I made sure that they both (from CLI and eclipse) work on the same Env.

Is there any configuration that should be made for it to run correctly? Anything needed for the jar to be used as the driver used?

$FAN_HOME/etc/sql/config.props? http://fantom.org/doc/sql/pod-doc.html can be of help. Otherwise I'm most likely run out of ideas.

I have the values set.

Besides, what I don't understand is why does it work from eclipse? I would understand that they both don't work... but runs from eclipse but not from the CLI... is amazing!

Ok, I found the error...

SqlService.make("jdbc:mysql://127.0.0.1:3306/DbName","root")

should be:

SqlService.make("jdbc:mysql://127.0.0.1:3306/DbName","root", "", MySqlDialect.make())

I assumed it was correct since no one commented on it...

Thanks a lot.. :)