"Error Creating Station" - Parameter index out of range

[emiliom]

A couple of months ago there was an error (issue #11) posted with roughly this same title ( Error Creating Station - Parameter index out of range). I'm getting pretty much the same error, except in my case it's Parameter index out of range (4 > number of parameters, which is 3). From @shane-axiom 's response back then, I know the error is in get_station_sensors.sql, not get_stations.sql.

I'm running sos-injector-db in -Dmock mode. get_stations.sql returns 8 stations, with unique station_database_id values, and checked that station_database_id is used as the field name of only one field. I'm using named parameters. I've run the get_station_sensors.sql code on my database (MySQL), manually entering individual station_database_id values in the WHERE statements. Every one executed correctly; 2 or 3 returned no records, but that's expected and I assume it wouldn't cause an error with sos-injector-db.

Here's my error listing, including the sos-injector-db statement:

$ java -Dmock -Dstart_date=2014-09-13T00:00:00Z -jar sos-injector-db.jar
2014-09-17 09:45:06,514  INFO [main] - Mock SosInjector initialized
2014-09-17 09:45:06,517  INFO [main] - Updating mock-database-sos-injector
2014-09-17 09:45:08,199 ERROR [main] - Sos injection failed after 1 seconds
com.axiomalaska.sos.exception.StationCreationException: Error creating station
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStations(DatabaseStationRetriever.java:246)
    at com.axiomalaska.sos.SosInjector.update(SosInjector.java:191)
    at com.axiomalaska.sos.injector.db.DatabaseSosInjector.main(DatabaseSosInjector.java:44)
Caused by: java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3796)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3778)
    at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4599)
    at com.jolbox.bonecp.PreparedStatementHandle.setString(PreparedStatementHandle.java:1013)
    at com.axiomalaska.jdbc.NamedParameterPreparedStatement.setString(NamedParameterPreparedStatement.java:214)
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStations(DatabaseStationRetriever.java:212)
    ... 2 more

I could post my get_station_sensors.sql code, but it's fairly complex so I wanted to spare you first.

Thanks!

[emiliom]

I forgot to add: I'm using the latest stable sos-injector-db (1.0.1) and i52n-sos (0.8.1).

[emiliom]

More information. The error still occurs when I set get_stations.sql to only return station_database_id values for which get_station_sensors.sql returns one or more records.

And if it helps, here's my get_station_sensors.sql code. It's fairly tricky, but I won't try to explain it yet. The main point is that it works as expected when run directly on the mysql shell client, when the named parameter :station_database_id is replaced with a valid station string value.

SELECT
  CONCAT(a.measurement_label, ':', n.depth_idx+1, ':', 
     TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.measurement_depth, ',', n.depth_idx+1), ',', -1)) )
     AS sensor_database_id
  ,CONCAT(a.measurement_label, '_', n.depth_idx+1)
     AS sensor_short_name
  ,CONCAT(a.measurement_label, '_', n.depth_idx+1, ' (', 
     TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.measurement_depth, ',', 
     n.depth_idx+1), ',', -1)), ')')
     AS sensor_long_name
  ,CAST(TRIM(TRAILING 'm' FROM TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.measurement_depth, ',', 
     n.depth_idx+1), ',', -1))) AS DECIMAL(6,1) )
     AS sensor_height_meters
FROM
  vizer_nvs.platform_data_details AS a
  INNER JOIN
    -- Hardwires a maximum of 12 depth elements
    (SELECT 0 AS depth_idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
     UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11)
     AS n
  ON LENGTH(REPLACE(a.measurement_depth, ',' , '')) <= LENGTH(a.measurement_depth)-n.depth_idx
WHERE a.platform_label = :station_database_id
  AND a.measurement_depth != 'VARIABLE'
  AND a.mode in ('all','live')
  AND a.download_opt = 'yes'
ORDER BY
   a.platform_label
  ,a.measurement_label
  ,n.depth_idx

[emiliom]

Sorry to bug, @shane-axiom ... Any thoughts on this problem?

[srstsavage]

Hi @emiliom, sorry, I'm out of the office until the end of next week so my responses won't be prompt. Judging from the stack trace, which is complaining about only three parameters existing in a query instead of four, I think your problem is probably is your get_observations query. It's definitely not get_station_sensors.sql, since the SQL you posted contains only a single parameter.

I'm guessing that you're using a single get_observation.sql query for all observations and not a getobservations{cf_standard_name}.sql per observed property? Can you check that query to make sure that all four parameters are present (station_database_id, sensor_database_id, phenomenon_database_id, start_date)? If that doesn't solve your problem, please post that query here.

[srstsavage]

Added https://github.com/ioos/sos-injector-db/issues/14 to refine error message specificity.

[emiliom]

@shane-axiom , thanks for taking the time to respond while you're out of the office! I was stuck, based on a previous comment, thinking that this error message involved get_station_sensors.sql. So pointing out get_observations.sql was very helpful. BTW, I am in fact using a single get_observations.sql query for all observations. I ended up finding a couple of mistakes in my query, but I've tested the query on the mysql client shell, and now I'm confident it's fine. The 4 named parameters were present all along, but I've triple-checked the spelling. All seems fine. But I'm still running into exactly the same error.

I know you can't get to this soon, but here's my get_observations.sql query. With the help you've already provided, I'll try to think about this some more and run more tests.

SET time_zone = '+00:00';
SELECT
   FROM_UNIXTIME(a.measurement_time, '%Y-%m-%dT%H:%i:%sZ')
     AS observation_time
  ,a.value AS observation_value
  ,NULL AS observation_height_meters
  -- I also tested omitting the observation_height_meters assignment
FROM vizer_nvs.platform_data a
WHERE a.platform_label = :station_database_id
  AND a.measurement_label = :phenomenon_database_id
  AND a.measurement_depth = SUBSTRING_INDEX(:sensor_database_id, ':', -1)
  AND a.measurement_time > UNIX_TIMESTAMP(:start_date)
ORDER BY 
  a.measurement_time DESC, a.measurement_label

[srstsavage]

Hmm, that's weird. That query looks ok, and the parsing class used by sos-injector-db to parse the named parameters should be smart enough to ignore the : characters in quotes. I'll check next week when I'm back if you haven't figured it out.

[emiliom]

Thanks, @shane-axiom . FYI, I use a : character in every query except get_stations.sql; it's a delimeter I set in get_station_sensors.sql, I could easily change it. But if those other queries aren't failing on that character, seems like this isn't the problem. : is also found in the datetime string formatting code in from_unixtime(), and that I can't change.

[emiliom]

@shane-axiom : a ping, in case you're back Friday (as opposed to Monday!). Sorry, I know you're out, but I'm itching to be able to get past this. Thanks!

[srstsavage]

@emiliom Can you give this new release with improved error logging (1.0.2) a try?

[emiliom]

Thanks, @shane-axiom ! The error log now has more information. See the middle block below; for completeness I included the blocks (at the start and end) that look largely unchanged.

It'd be really helpful to know which query this new message is pointing to:

Error setting sensorPhenomenaStatement named parameter: station_database_id

Seems like it's get_sensor_phenomena.sql, but I can't tell; could be get_observations.sql. Assuming it's one of those two, can I then assume that get_stations.sql and get_station_sensors.sql are executing properly?

Here's the complete error log. This initial error block is pretty much the same as before.

java -Dmock -Dstart_date=2014-09-27T00:00:00Z -jar sos-injector-db.jar
2014-09-29 11:43:51,195  INFO [main] - Mock SosInjector initialized
2014-09-29 11:43:51,199  INFO [main] - Updating mock-database-sos-injector
2014-09-29 11:43:53,061 ERROR [main] - Sos injection failed after 1 seconds
com.axiomalaska.sos.exception.StationCreationException: Error creating station
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStations(DatabaseStationRetriever.java:67)
    at com.axiomalaska.sos.SosInjector.update(SosInjector.java:191)
    at com.axiomalaska.sos.injector.db.DatabaseSosInjector.main(DatabaseSosInjector.java:44)

This error message block is new:

Caused by: com.axiomalaska.sos.injector.db.exception.DatabaseSosInjectorStationCreationException: Error setting sensorPhenomenaStatement named parameter: station_database_id
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStationsInternal(DatabaseStationRetriever.java:320)
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStations(DatabaseStationRetriever.java:65)
    ... 2 more

The rest looks the same as before:

Caused by: java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3796)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3778)
    at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4599)
    at com.jolbox.bonecp.PreparedStatementHandle.setString(PreparedStatementHandle.java:1013)
    at com.axiomalaska.jdbc.NamedParameterPreparedStatement.setString(NamedParameterPreparedStatement.java:214)
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStationsInternal(DatabaseStationRetriever.java:318)
    ... 3 more

[srstsavage]

@emiliom Yeah, it's definitely complaining about get_sensor_phenomena.sql. I took a look at that query in the zip you sent and saw several potential problems. There are named parameters in the SQL comments, and also "normal" JDBC parameters (the '?' character) in comments at the bottom. The query parsing library wasn't set up to ignore named parameters in SQL comments. I'm unsure how normal ? parameters are treated in SQL comments by the JDBC engine, but they're probably ok.

I updated the jdbc-named-parameters library to ignore named parameters in SQL comments and made a new sos-injector-db release based on that. This may solve your problem straight away. If not, there are a few things to try.

One thing to note is that your error was complaining about expecting four parameters and finding only three, which is odd because the non-comment-ignoring version of the query parser was finding five named parameters. So, not totally sure that this fix will solve all your problems. If not, some further query cleanup will probably help.

[emiliom]

Thanks, @shane-axiom . Since you pointed out that characters within comments could be a problem, I went ahead and removed every single comment from all 4 queries. Between that and the updates you pushed into sos-injector-db-1.0.3 (which I downloaded), I have great progress! Awesome. New errors, new challenges. I like it :)

I have two kinds of errors now that are much more well constrained.

The first and main problem looks like this

2014-09-29 15:22:59,295  INFO [main] - Harvesting station 1 of 1.
2014-09-29 15:22:59,425  INFO [main] - Retrieving observations for urn:ioos:sensor:nanoos:fhl_friharbor1:a1_airtemp_1, phenomenon http://mmisw.org/ont/cf/parameter/air_temperature, start time 1970-01-01T00:00:00.000Z
2014-09-29 15:22:59,459 ERROR [main] - Sos injection failed after 2 seconds
java.lang.RuntimeException: Error getting observations for sensor urn:ioos:sensor:nanoos:fhl_friharbor1:a1_airtemp_1, phenomenon http://mmisw.org/ont/cf/parameter/air_temperature, start date 2014-09-27T00:00:00.000Z
    at com.axiomalaska.sos.injector.db.DatabaseObservationRetriever.getObservationCollection(DatabaseObservationRetriever.java:183)
    at com.axiomalaska.sos.SosInjector$2.update(SosInjector.java:151)
    at com.axiomalaska.sos.SosInjector.update(SosInjector.java:207)
    at com.axiomalaska.sos.injector.db.DatabaseSosInjector.main(DatabaseSosInjector.java:44)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT
   FROM_UNIXTIME(a.measurement_time, '%Y-%m-%dT%H:%i:%sZ')
     AS observ' at line 2

I think the core problem is at the end, which is from get_observations.sql. Is it possible that sos-injector-db is incorrectly interpreting as the start of named parameters the ":" characters within the FROM_UNIXTIME function? When I run a manual version of the query at the mysql shell, everything is fine. I ran a test involving just one simple station, and failed at the same point. FYI, I listed the complete get_observations.sql statement on an earlier comment; the query is still the same, except for the use of index value 3 instead of -1 in SUBSTRING_INDEX().

The second, smaller problem involves the string to use for dimensionless (or unitless) phenomena. When I use a single blank character or a single dash (as a test), I got this: Error creating phenomenon unit:. The error went away when I replaced that by a dummy string ("unitless"). What's a proper string to use for these cases, that will keep sos-injector-db happy?

Thank you!! Almost there.

[srstsavage]

Ok, progress is good.

For your get_observations.sql query, the parsing library is parsing it fine. For furture reference, you can double check this by downloading the jdbc-named-parameters jar and running it with your query file as an argument:

java -jar jdbc-named-parameters-1.0.1.jar ./src1/get_observations.sql

This will show you the original query, the parsed query, and the detected named parameters.

The problem with that query is that JDBC statements don't allow multiple queries in the same statement. With most database platforms you can wrap statements in BEGIN .. END commands to make them execute as a single statement, but I've had trouble getting MySQL to work properly with this approach recently. Your mileage may vary. Also, if you're able to rework your query to eliminate the separate SET command, that should work too.

The second problem with dimensionless phenomena is a little more involved. I was working through it a little, but from the get go it looks like the OGC SWE 2.0 schema doesn't have a way to elegantly represent this concept. swe:Quantity requires a swe:uom element, but that swe:uom element is not nillable. I think that forces us to come up with a string constant convention to represent dimensionlessness ( or something). Not very satisfying, especially when there are two potential ways to cleanly represent it in the XML that are disallowed (omit swe:Quantity's swe:uom or set swe:uom's xsi:nil to true).

Just to verify, does your need for dimensionless units match this CF recommendation?

http://mailman.cgd.ucar.edu/pipermail/cf-metadata/2011/054296.html

(i.e.do you need to represent a dimensionless quantity which isn't "fractions or parts of a whole"?).

[srstsavage]

Actually, you might be able to use multiple queries in a single statement with MySql by adding ?allowMultiQueries=true to your jdbc.url:

jdbc.url=jdbc:mysql:///sos-db?allowMultiQeries=true

See this StackOverflow answer. Worth a try anyway.

[srstsavage]

@emiliom After thinking more about this, I think it probably makes sense to always use a unit of "1" for dimensionless phenomena. SWE basically forces us to provide a unit, and using "1" for dimensionless quantities matches up with the CF standard names. Does that work for you?

[emiliom]

I can now report success on all counts now! (at least with -Dmock)

@shane-axiom, thanks so much for all your help, and for providing options and some R&D on these last two issues.

On the "multiple queries" / SET time_zone = "+00:00" issue: I should've realized that this was liable to cause problems. After looking at your suggestion to use ?allowMultiQueries=true in the jdbc.url, I concluded that probably wouldn't work, at least in part b/c the session time_zone setting might not be persisted (but really, I should've just tried it!.). The solution I found was passing this session setting via the jdbc url: ?sessionVariables=time_zone='%2B00:00'. The "+" character led to java/jdbc errors that stumped me, until I found the solution to use its url encoded equivalent (%2B). This replaces the SQL statement SET time_zone = "+00:00" I was using in get_observations.sql.

On the dimentionless / unitless units string issue: I gave up and used "1" for all cases where I might have used something different or have qualms about its implications. Seems like there are tightly intertwined issues here of CF standard names, udunits and SWE that leave little room for options or arguments, and would be a digression from this github issue.

Again, thank you!!!