Bind a date value in a database insert statement.

matteolovatti

Sorry, me again 🙁

Trying to insert data into a date type column in a Postgres DB

With

stmt.Bind(1, rs.Column("theDate").DateTimeValue)

I get: No overload of 'Bind' accepts arguments types (Integer, DateTime)

With

stmt.Bind(1, rs.Column("theDate").DateTimeValue.ToSQLDate())

I get an ExecuteSQL error: column "date" is of type date but expression is of type text.

Tried to parse the date in a date object and use that but no luck so far...

Any hint?

Thanks.

PaulS

Maybe changing ToSQLDate() into ToSQLDateTime() may help.

matteolovatti

Unfortunately not, as ToSQLDateTime() still gives a String

Garry

Thanks for reporting this. You’re right: PreparedStatement.Bind currently has no DateTime overload. Both ToSQLDate() and ToSQLDateTime() return a String, so changing between them does not resolve PostgreSQL’s text-versus-date error.

For a PostgreSQL date column, an explicit cast in the SQL should unblock you:

Var stmt As PreparedStatement = db.Prepare("INSERT INTO my_table (the_date) VALUES (CAST(? AS date))")
stmt.Bind(0, rs.Column("theDate").DateTimeValue.ToSQLDate())
stmt.ExecuteSQL()

Bind indexes start at 0; keep Bind(1, ...) if the date is your second placeholder. If the source column can be NULL, check IsNull and use BindNull to preserve it.

I’ve logged direct DateTime binding for a proper fix: Bind DateTime values in database statements.

matteolovatti

Thanks @Garry for the reply and the help!

Out of curiosity, are you going to consider something like Xojo DatabaseRow or Objo PreparedStatement will keep to be the only way to insert records in a database?

Garry

Good timing for this question! I'm implementing a DatabaseRow API for Objo, and it will sit alongside PreparedStatement rather than replacing it.

The shape:

Var row As New DatabaseRow
row.Column("Name") = "Penguins"
row.Column("Age") = 42
row.Column("Joined") = rs.Column("theDate").DateTimeValue
row.Column("Notes") = Nothing

db.AddRow("Team", row)

A few notes on the design:

Column assignments are statically typed: String, Integer, Double, Decimal, Boolean, DateTime, and blobs via MemoryBlock, so a DateTime binds as a real date parameter on every engine. The exact problem from this thread simply disappears: no casts, no ToSQLDate().
Only the columns you assign appear in the generated INSERT, so schema defaults still apply to the rest, and assigning Nothing inserts an explicit NULL.
AddRow works across all five engines (SQLite, PostgreSQL, MySQL/MariaDB, SQL Server, ODBC), with an AddRowAsync matching the other async database methods.
PreparedStatement stays exactly as it is - for hand-written SQL and bulk loads inside a transaction it remains the right tool.

It's logged and queued for implementation alongside the DateTime binding fix. Thanks for the nudge - this thread was a good prompt to get it moving.

https://feedback.objo.dev/feature/1564

matteolovatti

That's awesome @Garry ! Can't wait to see those features implemented!

Thanks for your work!

Garry

This one's now properly fixed - thanks for the report, it shaped two features that just landed on main and will ship in the next Studio release.

DateTime values now bind as real date/time parameters
You no longer need ToSQLDate() or any cast. PreparedStatement.Bind accepts a DateTime directly, and the parameterised SelectSQL/ExecuteSQL overloads accept them too:

Var stmt As PreparedStatement = db.Prepare("INSERT INTO games (team, played_on) VALUES (?, ?)")
stmt.Bind(0, "Penguins")
stmt.Bind(1, DateTime.Now)   # binds as a real date/timestamp parameter
stmt.ExecuteSQL()

Each provider maps this to its native types - PostgreSQL sends timestamp without time zone (which PostgreSQL assignment-casts into date and timestamptz columns), MySQL sends a native DATETIME, SQL Server uses DATETIME2, SQLite stores ISO-8601 text, and ODBC drivers get an SQL_TYPE_TIMESTAMP. The root cause of the original failure was that a ToSQLDate() result arrived as a String parameter, which Npgsql sends as text and PostgreSQL refuses for a date column without an explicit cast - that path is gone now. Reading back, DatabaseColumn.DateTimeValue converts date columns directly as well, including PostgreSQL date columns, so values round-trip without any conversion code.

There's also a new way to write inserts entirely without hand-building SQL.
The new DatabaseRow class pairs with AddRow on every database class, which covers this exact scenario:

Var row As New DatabaseRow
row.Column("Name") = "Penguins"
row.Column("Age") = 42
row.Column("Joined") = rs.Column("theDate").DateTimeValue
row.Column("Notes") = Nothing

Try
  db.AddRow("Team", row)
Catch e As DatabaseException
  MessageBox("DB Error: " + e.Message)
End Try

Each Column assignment is statically typed (String, Integer, Double, Decimal, Boolean, DateTime, MemoryBlock, or Nothing), so a mismatched value is a compile error rather than a runtime database error. The generated INSERT lists only the columns you assigned - unassigned columns keep their schema defaults - and Nothing inserts an explicit NULL. There's an AddRowAsync for awaited inserts too, and table/column identifiers are quoted per provider, so mixed-case and spaced names round-trip.

https://feedback.objo.dev/feature/1564