Clojurians Log v2

What is the preferred way to keep your sql statements organized with jbdc.next?

I enjoyed using conman and keeping my sql in a .sql file with the previous iteration of the library, but it doesn't look like conman supports jdbc.next

nevermind, looks like it is right in the docs: https://github.com/seancorfield/next-jdbc/blob/4d7940a5d346f9caa01d77aaf4f32bcbd61ce65d/doc/friendly-sql-functions.md

HugSQL?

I'll give a :thumbsup::skin-tone-2: to HugSQL as it has built-in support for next.jdbc -- and it's also documented as part of the next.jdbc documentation https://cljdoc.org/d/seancorfield/next.jdbc/CURRENT/doc/getting-started/friendly-sql-functions#hugsql-quick-start

hi -- does anyone have a working example of a db-spec that connects to ms sql server? I'm on a linux box trying to connect using clojure.java.jdbc and keep getting authentication failures. I've tried setting :user to "DOMAIN\\username" and just "username", as well as something simillar in :subname. what's the syntax for using domain AD authentication in jdbc?

This is how next.jdbc connects to MS SQL Server: https://github.com/seancorfield/next-jdbc/blob/master/test/next/jdbc/test_fixtures.clj#L38-L42

The clojure.java.jdbc tests also have code for that https://github.com/clojure/java.jdbc/blob/master/src/test/clojure/clojure/java/jdbc_test.clj#L50-L54 and https://github.com/clojure/java.jdbc/blob/master/src/test/clojure/clojure/java/jdbc_test.clj#L102-L107

^ @mbarillier Use the :dbtype/`:dbname` approach instead of trying to construct things with :subname etc.

next.jdbc assumes default host/port locally, clojure.java.jdbc uses :host/`:port` because that's an artifact of the different ways I test those two libraries (with clojure.java.jdbc I used a SQL Server Express instance running on an old Windows XP VM on my Mac; with next.jdbc I used Microsoft's official SQL Server Docker image based on Linux and exposed the host/port as 127.0.0.1:1433

Not sure how you'd do AD authentication if that's different from regular user/password.

@seancorfield lol ... neither do I 🙂

The alternative is to construct the full JDBC URL yourself, based on whatever information you can find out there for how to connect via AD auth to SQL Server, and give that to c.j.j (or next.jdbc).

AD is integrated security and based on the current logged in user right?

Been a while since my .NET days

@dpsutton yes, there's an integratedSecurity=true option on the connect string, but works only on windows (I'm on linux)

Can you try using that connection string from a more traditional client? I’m guessing it won’t work from Linux at all

I got it to work using a connect string, something like: "jdbc:<sqlserver://HOST>:PORT;databaseName=DBNAME;user=ID;password=PWD". I don't understand how this active directory authentication works, but apparently the service account I used doesn't require a domain, but mine does. microsoft products are a nightmare (IMHO).

@mbarillier Given that URL works, I would expect a db-spec map of

{:dbtype "sqlserver"
 :dbname "DBNAME"
 :host "HOST"
 :port PORT
 :user "ID"
 :password "PWD"}

(and that should work with both clojure.java.jdbc or next.jdbc -- and I'd encourage you to use the latter if you are just getting started)