Fork me on GitHub
#sql
<
2020-03-18
>
Mitch13:03:36

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

Mitch13:03:42

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

orestis14:03:13

HugSQL?

๐Ÿ‘ 8
seancorfield18:03:41

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

๐Ÿ˜„ 4
mbarillier19:03:09

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?

seancorfield19:03:41

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

seancorfield19:03:39

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

seancorfield19:03:22

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

mbarillier19:03:12

@seancorfield lol ... neither do I ๐Ÿ™‚

seancorfield19:03:57

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).

dpsutton20:03:06

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

dpsutton20:03:45

Been a while since my .NET days

mbarillier20:03:21

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

dpsutton20:03:42

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

mbarillier21:03:31

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).

seancorfield21:03:51

@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"}
to work also

seancorfield21:03:20

(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)