Clojurians Log v2

Hello! I'm using jdbc.next to work with a PostgreSQL table with a column containing JSON. When I select from the table, I get data like #object[org.postgresql.util.PGobject 0xb1fe4db ... . Any recommendations for working with JSON using jdbc.next? Do people just "convert to maps" when running the select query?

I'm aware that I can use str with a normal JSON parser, but I'm suspecting that ad-hoc json/string conversion all around the codebase might not be desirable:

(with-open [conn (jdbc/get-connection db-datasource)]
  (-> (jdbc.sql/query conn ["SELECT * from my_table LIMIT 1"])
      first
      :my_table/data
      str
      json/read-str))

I haven’t tried this, but perhaps it’ll work / be useful. I’d extend ReadableColumn to PGobject as described here https://github.com/seancorfield/next-jdbc/blob/master/doc/result-set-builders.md#readablecolumn When the type of the object as reported by .`getType` is "jsonb" or "json" call json/read-str on the result of .getValue, otherwise return the result fo .getValue

Found an article that seems to cover insertion as well, but it's for clojure.jdbc and not jdbc.next: https://web.archive.org/web/20161024231548/http://hiim.tv/clojure/2014/05/15/clojure-postgres-json/

https://github.com/seancorfield/next-jdbc/blob/master/doc/prepared-statements.md#prepared-statement-parameters

This says how to do the reverse

That's right, the article documents how to do it both ways. Both articles rely on switching on the object type before converting to PGObjects. Thanks.

Does anyone have an example of importing data from csv into postgres or any other db?

At least psql can output to CSV, but I haven't found a way to input. From man psql:

-F separator
       --field-separator=separator
           Use separator as the field separator for unaligned output. This is equivalent to \pset
           fieldsep or \f.

In psql...

\copy my-funky-table from '/home/foo/this-is-my.csv' with delimiter as ',' csv header

you can remove the header if you csv doesn't include a header.

Thanks

Instead of passing a file can I do it with csv data in memory?

hello all, about jdbc.next what is the way to use jdbc driver that is not in list of available

?

you provide jdbc drivers via the classpath, which should be getting it via project.clj or deps.edn depending on how you manage your project

I got

Unhandled clojure.lang.ExceptionInfo
   Unknown dbtype:

OK - that's either an issue with the classpath where the db driver wasn't loaded, or you misconfigured the db spec itself - I can't help with the latter

can you show more of the message / context?

Unhandled clojure.lang.ExceptionInfo
   Unknown dbtype:
   {:class "dbmaker.jdbc.ws.client.Driver",
    :dbname "DEFAULT",
    :user "SYSADMIN",
    :password "1234",
    :host "127.0.0.1",
    :port 1054}
            connection.clj:  201  next.jdbc.connection/spec->url+etc
            connection.clj:  141  next.jdbc.connection/spec->url+etc
            connection.clj:  267  next.jdbc.connection/eval959/fn
             protocols.clj:   14  next.jdbc.protocols/eval723/fn/G
            connection.clj:  283  next.jdbc.connection/eval979/fn
             protocols.clj:   24  next.jdbc.protocols/eval753/fn/G
                  jdbc.clj:  134  next.jdbc/get-connection
                  jdbc.clj:  117  next.jdbc/get-connection
                      REPL:   12  app.core/eval13801
                      REPL:   12  app.core/eval13801
             Compiler.java: 7177  clojure.lang.Compiler/eval
             Compiler.java: 7132  clojure.lang.Compiler/eval
                  core.clj: 3214  clojure.core/eval
                  core.clj: 3210  clojure.core/eval
                  main.clj:  437  clojure.main/repl/read-eval-print/fn
                  main.clj:  437  clojure.main/repl/read-eval-print
                  main.clj:  458  clojure.main/repl/fn
                  main.clj:  458  clojure.main/repl
                  main.clj:  368  clojure.main/repl
               RestFn.java:  137  clojure.lang.RestFn/applyTo
                  core.clj:  665  clojure.core/apply
                  core.clj:  660  clojure.core/apply
                regrow.clj:   18  refactor-nrepl.ns.slam.hound.regrow/wrap-clojure-repl/fn
               RestFn.java: 1523  clojure.lang.RestFn/invoke
    interruptible_eval.clj:   79  nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:   55  nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:  142  nrepl.middleware.interruptible-eval/interruptible-eval/fn/fn
                  AFn.java:   22  clojure.lang.AFn/run
               session.clj:  171  nrepl.middleware.session/session-exec/main-loop/fn
               session.clj:  170  nrepl.middleware.session/session-exec/main-loop
                  AFn.java:   22  clojure.lang.AFn/run
               Thread.java:  748  java.lang.Thread/run

for

(jdbc/get-connection
 {:class "dbmaker.jdbc.ws.client.Driver"
 :dbname "DEFAULT"
 :user "SYSADMIN"
 :password "1234"
 :host "127.0.0.1"
 :port 1054})

@fabrao Did you read https://cljdoc.org/d/seancorfield/next.jdbc/1.0.13/api/next.jdbc.connection#dbtypes ?

You must provide :dbtype.

Per the docs:

If you want to use a database that is not in this list, you can specify a new :dbtype along with the class name of the JDBC driver in :classname. You will also need to specify :port. For example:

{:dbtype "acme" :classname "com.acme.JdbcDriver" ...}

Add :dbtype "dbmaker" to your hash map and it should work.

next.jdbc caches certain aspects of driver handling based on the db type so you have to provide a unique value for each different type of database (driver) you want to use.

sorry about that. I only read the get-datasource help

FWIW, Getting Started links to that documentation in the https://cljdoc.org/d/seancorfield/next.jdbc/1.0.13/doc/getting-started#the-db-spec-hash-map section.

any other advice? :dbtype "dbmaker" didn´t work

Oh :class is wrong. Should be :classname

get-datasource says

In the first format, these keys are required:

:dbtype -- a string indicating the type of the database

and

:classname -- if you need to override the default for the :dbtype (or you want to use a database that next.jdbc does not know about!)

Let me know if I can make that clearer.

I guess I could link from the get-datasource docstring to the #dbtypes section of the next.jdbc.connection namespace...

oh, you right, I´m dumb*ss , that was the problem, :classname

the docs of driver says that we have to include other parameter to it

Class.forName("dbmaker.jdbc.ws.client.Driver");
Connection conn =
DriverManager.getConnection("jdbc:dbmaker:<type3://127.0.0.1:8083/JDBCTES>
T", "SYSADM", abc");

what is the way to include :type3 ?

or the best way is to provide the url directly?

:dbtype "dbmaker:type3" should work.

That's what the DB type is: the driver-specific segment of the JDBC URL.

Best regards Sean, thank you, now is working

have you heard about this database?

Nope, never heard of it.

BTW, going back to the docstring for that dbtypes Var:

The value of :dbtype should be the string that the driver is associated with in the JDBC URL, i.e., the value that comes between the jdbc: prefix and the ://<host>... part. In the above example, the JDBC URL that would be generated would be jdbc:acme://<host>:<port>/<dbname>.

(I wanted to double-check I was describing the relationship between :dbtype and the JDBC URL somewhere -- and I would have added that if it wasn't already present!)

my customer is using COBOL in JVM, 🙂

and this database

the old new technology

the Cobol code is transpiled to Java and is compiled

You are taking to the absolutely perfect person then :)