SQL Exception: Parameter metadata not available for the given statement

Poster Content
nk4um Moderator
Posts: 901
October 5, 2011 21:10

Very useful finding. I've never come across this before - but then we don't use MySQL. This will no doubt be useful to someone else at some point - glad you also discovered how to set the JDBC parameters on the config.

Thanks for reporting back on this. Glad its now working.

P.

Like · Post Reply
nk4um User
Posts: 39
October 5, 2011 17:50

Well that was an adventure, but I got to the bottom of it. The clue was in the MySQL JDBC driver docs, specifically the section on generateSimpleParameterMetadata:

Should the driver generate simplified parameter metadata for PreparedStatements when no metadata is available either because the server couldn't support preparing the statement, or server-side prepared statements are disabled?

This is set to false by default, which explained the exception I was getting. The solution was to set it in my ConfigRDBMS.xml:

<generateSimpleParameterMetadata>true&lt;/generateSimpleParameterMetadata&gt;

... and it's working now. Phew!

Glenn.

Like · Post Reply
nk4um Moderator
Posts: 901
October 5, 2011 15:38

If you're using MySQL it looks like this...

http://forums.mysql.com/read.php?46,188450,188450

Other google results suggest it could be the JDBC driver version you're using with the DB?

Being a prepared statement the parameter needs to match the Java object type expected for the inserted param. It may be that your column type is not compatible with the java.lang.String literal you are passing. In which case it would need some Java code to coerce the type.

Hope this helps - sorry I have to fly but will check back first thing in the morning.

P.

Like · Post Reply
nk4um User
Posts: 39
October 5, 2011 15:21

Posted by pjr (View)
I think this is the problem...

<argumentname="param">BRCA2&lt;/argument&gt;

Your saying BRCA2 is the name of resource - that will be sourced as the prepared statement parameter and will fail. What I think you want is that this is a literal...

<argumentname="param">
  <literaltype="string">BRCA2&lt;/literal&gt;
&lt;/argument&gt;

Which will now be sourceable and will be inserted into the PS.

P.

Thanks Peter - I had tried wrapping the string in a literal element before I posted, and have done so again, however I'm still getting the "Parameter metadata not available for the given statement" error.

Note that the exception is coming from JDBC rather than NetKernel; however I'm not sure what the error actually means!

Glenn.

Like · Post Reply
nk4um Moderator
Posts: 901
October 5, 2011 14:56

I think this is the problem...

<argumentname="param">BRCA2&lt;/argument&gt;

Your saying BRCA2 is the name of resource - that will be sourced as the prepared statement parameter and will fail. What I think you want is that this is a literal...

<argumentname="param">
  <literaltype="string">BRCA2&lt;/literal&gt;
&lt;/argument&gt;

Which will now be sourceable and will be inserted into the PS.

P.

Like · Post Reply
nk4um User
Posts: 39

Hi

I'm trying to execute a SQL prepared statement and I'm having problems passing parameters to it. The relevant snippet from my module.xml is:

<endpoint>
  <grammar>
    <group> res:/dbquery-arg/
      <groupname="gene">
        <regextype="anything" /&gt;
      &lt;/group&gt;
    &lt;/group&gt;
  &lt;/grammar&gt;
  <request>
    <identifier>active:sqlPSQuery&lt;/identifier&gt;
    <argumentname="operand">
      <literaltype="string"> SELECT x.display_label, gsi.stable_id, g.biotype, sr.name, g.seq_region_start, g.seq_region_end, g.seq_region_strand, g.description FROM seq_region sr, gene g, xref x, external_db e, gene_stable_id gsi WHERE gsi.gene_id=g.gene_id AND g.display_xref_id=x.xref_id AND x.external_db_id=e.external_db_id AND e.db_name='HGNC' AND sr.seq_region_id=g.seq_region_id AND x.display_label=? &lt;/literal&gt;
    &lt;/argument&gt;
    <argumentname="param">BRCA2&lt;/argument&gt;
  &lt;/request&gt;
&lt;/endpoint&gt;

Even when called like this, I get a java.sql.SQLException stating "Parameter metadata not available for the given statement". If I use a command-line tool to execute the same SQL, it works. My database config is OK - other endpoints that do active:sqlQuery requests in the same module work.

Can anyone shed more light on this error?

The next step is to take the "gene" part of the grammar and use it for the parameter value - is this just a case of replacing the hardcoded BRCA2 with

 [[arg:gene]]
?

Thanks

Glenn.

Like · Post Reply