Re: [Haskell-cafe] Haskell SQL backends: How is data handed over?
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.
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
Anthony Clayden wrote: 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... [HDBC] https://hackage.haskell.org/package/HDBC-2.4.0.4/docs/src/Database.HDBC.SqlV... [1] https://github.com/hdbc/hdbc/issues/56
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
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.
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
Anthony Clayden wrote: 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... [HDBC] https://hackage.haskell.org/package/HDBC-2.4.0.4/docs/src/Database.HDBC.SqlV... [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@gmail.com
participants (2)
-
Brandon Allbery -
Olaf Klinke