Fork me on GitHub
#sql
<
2019-11-14
>
geek-draven11:11:28

Hi @seancorfield we're having an 'interesting' issue using next and java.jdbc with Snowflake. They've made some decisions which are causing us some pain. Unfortunately, they don't support prepared statements with all sql commands, as a hack, what they do is not return an error to the connection but instead re-run the sql in the background. Usually this isn't much of an issue, but when we're running commands with upstream deps, we're having issues where either the old data is being ingested, or the table has been dropped. Is there a way we could execute a statement not as a prepared statement? I was hoping that as execute-one was meant to be used for DDLs that it might the one to use. More information on the original issue that was reported to Snowflake can be found here - https://github.com/snowflakedb/snowflake-jdbc/issues/7

valerauko14:11:59

Thanks for the eastwood fix! Do you have any release date idea for 1.0.10?

byrongibby16:11:04

Hi. Does anyone know how I would run the mssql stored procedure "EXECUTE pc_ListMyTimeseriesObservations @TimeseriesCodes='EACM026A', @StartDate='19600101', @EndDate='20190630'", my db being given by mssql-db, using java.jdbc?

seancorfield16:11:47

@vale I consider your PostgreSQL issue blocking for 1.0.10 (nearly everything else since 1.0.9 is documentation updates).

seancorfield16:11:34

@byrongibby Have you tried running that EXECUTE statement with clojure.java.jdbc/execute! ?

seancorfield16:11:50

(I never use stored procs so I have no idea beyond suggesting that)

byrongibby16:11:56

@seancorfield I haven't I've been trying db-do-prepared with no luck - SQL noob here. Will try your suggestion, thanks!

seancorfield16:11:20

@geek-draven I'll take a look at that when I get time later today. There may be paths through clojure.java.jdbc that do not use PreparedStatement but I suspect they won't return result sets. As for next.jdbc, no, there's no path through that without PreparedStatement right now.

geek-draven16:11:01

Thanks @seancorfield, for now we're resorting to using java interop

seancorfield16:11:07

@byrongibby Did you try query first, by the way?

seancorfield16:11:29

(if that stored proc returns a result set, that would be my first attempt)

byrongibby16:11:48

(jdbc/execute! mssql-db "EXEC pc_ListMyTimeseriesObservations @TimeseriesCodes='EACM026A', @StartDate='20190101', @EndDate='20190630'") returns

Execution error (SQLServerException) at com.microsoft.sqlserver.jdbc.SQLServerException/makeFromDriverError (SQLServerException.java:234).
A result set was generated for update.
Yep, I did try query.

seancorfield16:11:21

Neither clojure.java.jdbc nor next.jdbc support multiple result sets BTW which is common for stored procs I gather. Just FYI.

seancorfield16:11:30

Oh, and query failed how?

byrongibby16:11:07

query returns an empty collection ()

seancorfield17:11:21

Sorry, I have no idea then... ¯\(ツ)

byrongibby17:11:03

I'll keep on trying, maybe try solve it in Java first. 🙂

byrongibby17:11:02

So it turns out there were no observations for the date range I gave and j/query works a charm. It's difficult being a noob some days. Thanks for the help.

4
seancorfield17:11:21

Thanks for reporting back on that!

seancorfield23:11:15

@geek-draven I'm looking at Statement/`PreparedStatement` stuff now... it looks relatively easy to add support for Statement https://github.com/seancorfield/next-jdbc/issues/75 but it will require use of a tiny bit of interop (calling .createStatement yourself on a java.sql.Connection).