Does odbc-api support prepare statement with args context?

Currently, I just find prepare function

odbc-api/odbc-api/src/guide.rs

Lines 481 to 505 in 4770340

fn insert_birth_years(conn: &Connection, names: &[&str], years: &[i16]) -> Result<(), Error> {

// All columns must have equal length.

assert_eq!(names.len(), years.len());

let prepared = conn.prepare("INSERT INTO Birthdays (name, year) VALUES (?, ?)")?;

// Create a columnar buffer which fits the input parameters.

let buffer_description = [

BufferDescription {

kind: BufferKind::Text { max_str_len: 255 },

nullable: false,

},

BufferDescription {

kind: BufferKind::I16,

nullable: false,

},

];

// The capacity must be able to hold at least the largest batch. We do everything in one go, so

// we set it to the length of the input parameters.

let capacity = names.len();

// Allocate memory for the array column parameters and bind it to the statement.

let mut prebound = prepared.into_any_column_inserter(capacity, buffer_description)?;

// Length of this batch

prebound.set_num_rows(capacity);

If prepare can be executed, binding parameters are required,

Does support step-by-step execution now?

First execute prepare fooplan

> PREPARE fooplan (int, text, bool, numeric) AS  INSERT INTO foo VALUES($1, $2, $3, $4);
> EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

then execute fooplan, because the parameters are obtained later, not immediately

ref link

https://www.postgresql.org/docs/current/sql-prepare.html

How many parameters are obtained later?

One set of parameters (i.e. Please insert this one row)
A whole bulk of them (i.e. please insert these 1000 rows at once)

Both are supported.

To insert one row at a time using a prepared statement. Just use the Prepared::execute which allows for specifying a set of parameters. See: https://docs.rs/odbc-api/latest/odbc_api/guide/index.html#executing-prepared-queries and https://docs.rs/odbc-api/latest/odbc_api/struct.Prepared.html#method.execute
For bulk inserts convert the prepared query into a bulk inserter first (to save roundtrips to the database). See: https://docs.rs/odbc-api/latest/odbc_api/struct.Prepared.html#method.into_any_column_inserter

Did this answer your question?

At present, it is not enough. I will write the implementation process in detail later

Interessting. It seems I do not understand your use case yet. I assumed you want to execute the same query several times using different parameters; Reusing the execution plan the database provides for performance.

I found through packet capture that the prepare function of odbc-api did not send sql to the database,

https://github.com/pacman82/odbc-api/blob/019247c574/odbc-api/src/guide.rs#L296

But I found through sqlx that the prepare function is to send sql to the database.

https://github.com/launchbadge/sqlx/blob/0823e1139c/tests/postgres/postgres.rs#L792

odbc-api seems to lack the function of two-phase commit. It must bind parameters to execute the execute method, and it cannot split prepare and execute for two executions.

Now, I hope the prepare function of odbc-api can send sql to database feature.

It is up to the Postgres ODBC driver though then and how to exactly prepare the SQL. The prepare function however communicates that usecase very clearly to the driver.

While it is possible for a driver to be conform with ODBC and not do anything on prepare I am confident the Postgres ODBC driver actually does something. At least it is able to tell the types of the result set columns and parameters after you perpare a statement.

Never looked at the packages, but nowehre it says that SQL has to be communicated in clear text. It's actually quite likely some of the parsing already happens in the driver.

Closing this issue. Either I am not understanding it. Or I am understanding it. In that case preparing queries with ODBC already works.

Either way it is not actionable.

Cheers, Markus

Hi @pacman82 . Your answer inspired me, very helpful. Thank you.

	fn insert_birth_years(conn: &Connection, names: &[&str], years: &[i16]) -> Result<(), Error> {

	// All columns must have equal length.
	assert_eq!(names.len(), years.len());

	let prepared = conn.prepare("INSERT INTO Birthdays (name, year) VALUES (?, ?)")?;

	// Create a columnar buffer which fits the input parameters.
	let buffer_description = [
	BufferDescription {
	kind: BufferKind::Text { max_str_len: 255 },
	nullable: false,
	},
	BufferDescription {
	kind: BufferKind::I16,
	nullable: false,
	},
	];
	// The capacity must be able to hold at least the largest batch. We do everything in one go, so
	// we set it to the length of the input parameters.
	let capacity = names.len();
	// Allocate memory for the array column parameters and bind it to the statement.
	let mut prebound = prepared.into_any_column_inserter(capacity, buffer_description)?;
	// Length of this batch
	prebound.set_num_rows(capacity);