[Haskell-cafe] Haskell SQL backends: How is data handed over?

[Brandon Allbery]

My observation about the "scientific" package is also relevant here:
most DBMSes have a different notion of "number" than most programming
languages, so unless there is specifically a binary number field type
it is probably necessary to send it as a string of some variety. The
"scientific" package abstracts over this for Haskell.

On Wed, Aug 10, 2022 at 11:22 AM Olaf Klinke <olf at aatal-apotheke.de> wrote:
>
> Thanks all for the replies.
> It seems in my specific setup values are indeed passed as strings, see
> below.
>
> Oliver Charles wrote:
> > I can't speak for SQL in general, but if you're targeting PostgreSQL note that the hasql library uses a binary wire format.
> Good to know, thanks. It might be that for my specific case the
> database choice ship has sailed, unfortunately.
>
> Anthony Clayden wrote:
> > Going via CSV isn't really saving anything: the "bulk insert" is again
> > parsing those commas and the bits in between. (A dedicated SQL engine is
> > probably more efficient than Haskell's string handling.)
> That was what I was thinking, too: The bulk insert must also parse the
> numbers, so it would be kind of a waste to have Haskell parse the
> numbers, format them as Strings in the CSV just to have the SQL server
> parse them again.
>
> Brandon Allbery wrote:
> > More specifically, a query or DML statement is a string with ?
> > placeholders for values, which is "prepare"d to turn it into internal
> > format and then executed one or more times with values supplied.
> Sending the SQL statement as string is probably fine. I guess it
> depends on the specific HDBC backend whether
>
> run :: Connection -> String -> [SqlValue] -> IO Integer
>
> also sends the SqlValue arguments as String.
> Specifically, I chose persistent with HDBC-odbc backend because
> (1) We're talking to a Microsoft SQL server from a Linux client via
> unixODBC,
> (2) We chose Microsoft SQL to ensure trouble-free integration into the
> Microsoft office world,
> (3) I expect a web application will emerge from this, hence persistent
> because it integrates well with Yesod.
>
> In [HDBC-odbc] it is implemented in sqlBindValue, sqlBindParameter
> which contains
>   CString   -- ^ Parameter value pointer
> The actual value (if not a string) is converted to ByteString (using
> the Convertible class) and placed into the CString, it seems. The
> Convertible SqlValue Bytestring instance uses fromString and
> Convertible SqlValue String which in turn uses show [HDBC].
> So yep, to ODBC the SqlValues are passed as Strings. HDBC-sqlite3 does
> the same. I opened [1].
>
> Olaf
>
> [HDBC-odbc] https://hackage.haskell.org/package/HDBC-odbc-2.5.0.1/docs/src/Database-HDBC-ODBC-Statement.html
> [HDBC] https://hackage.haskell.org/package/HDBC-2.4.0.4/docs/src/Database.HDBC.SqlValue.html#line-262
> [1] https://github.com/hdbc/hdbc/issues/56
>
> _______________________________________________
> Haskell-Cafe mailing list
> To (un)subscribe, modify options or view archives go to:
> http://mail.haskell.org/cgi-bin/mailman/listinfo/haskell-cafe
> Only members subscribed via the mailman list are allowed to post.



-- 
brandon s allbery kf8nh
allbery.b at gmail.com