MySQL prepared statement interface help

[Coachonko]

I am stuck with retrieving a prepared statement's result.

I do the following things:

1. Create a test database in a Percona Server container:

CREATE DATABASE prepStmtDb;
CREATE USER 'prepStmtUsr'@'%' IDENTIFIED BY 'prepStmtPwd';
GRANT ALL ON prepStmtDb.* TO 'prepStmtUsr'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
USE prepStmtDb;
CREATE TABLE countries (
  id VARCHAR(2) NOT NULL
  english_name VARCHAR(255) NOT NULL
  CONSTRAINT `PK_21d0fb76-3ac1-42bd-bba1-132f73c9cafa` PRIMARY KEY (id)
  );
CREATE TABLE cities (
  id INT,
  country VARCHAR(2) NOT NULL,
  name VARCHAR(255) NOT NULL,
  population INT NOT NULL,
  CONSTRAINT `PK_976eeb15-b32a-4428-ab6d-984b7d12aa16` PRIMARY KEY (id)
  );
ALTER TABLE cities
  ADD CONSTRAINT `FK_82c60424-2c5d-492e-9484-43e70768bbd7`
  FOREIGN KEY (country)
  REFERENCES countries(id)
  ON DELETE NO ACTION ON UPDATE NO ACTION;
INSERT INTO countries (id, english_name) VALUES ('BE', 'Belgium');
INSERT INTO cities (id, country, name, population) VALUES (0, 'BE', 'Brussels', 190000);
INSERT INTO cities (id, country, name, population) VALUES (1, 'BE', 'Ghent', 260000);
INSERT INTO cities (id, country, name, population) VALUES (2, 'BE', 'Antwerp', 500000);
EXIT;

2. Run the following V program

module main

import db.mysql

fn main() {
	mut mysql_conn := mysql.Connection{
		host: '127.0.0.1'
		port: 29300
		username: 'prepStmtUsr'
		password: 'prepStmtPwd'
		dbname: 'prepStmtDb'
	}
	defer {
		mysql_conn.close()
	}
	mysql_conn.connect() or { panic(err) }

	// Check if the cities table exists in the database
	mut stmt := mysql_conn.init_stmt('SELECT table_name FROM information_schema.tables WHERE table_schema = ?')
	stmt.prepare() or { panic(err) }
	stmt.bind_text('prepStmtDb') // TODO add support for varchar to vlib/db/mysql/stmt.c.v
	stmt.bind_params() or { panic(err) }
	stmt.execute() or { panic(err) }
	println(stmt) // res.len: 0
	// TODO get results
	// First try to get all rows, then try to get the one row with the table name cities

	// https://dev.mysql.com/doc/c-api/8.0/en/c-api-prepared-statement-interface-usage.html
	// 3. mysql_stmt_execute()
	// 4. skip getting metadata with mysql_stmt_result_metadata()
	// 5. mysql_stmt_bind_result()
	stmt.bind_result_buffer() or { panic(err) } // Runtime memory access error. Help.
	// stmt.store_result() or { panic(err) }
	// stmt.fetch_stmt() or { panic(err) }
	println(stmt)
}

Results in runtime memory access error.
How do I retrieve the query result set correctly?

[Coachonko]

In vlib/db/mysql/orm.v before bind_result_buffer, bind_res is invoked. Is this how it's supposed to be done?

To invoke bind_res, the parameter num_fields is required. The function get_field_count exists in vlib/db/mysql/stmt.c.v but it is not pub so users like me cannot use it

If invoking bind_res is not necessary, then I don't understand why bind_result_buffer causes a memory access error

[JalonSolov]

@walkingdevel ☝️

[walkingdevel]

Currently, I am only focused on ORM, so it's challenging for me to provide feedback on these libs. I have not reviewed their code yet, but I plan to do so after completing work on ORM. It would be best to ask the one who did it.

[Coachonko]

Currently, I am only focused on ORM

The commit history of vlib/db/mysql/orm.v is not clear, somebody must have understood how to make the C mysql wrapper work correctly there. Do you know who that was?

[walkingdevel]

As I know, the db module was created from copies of database modules that were not together. A better look at vlib/mysql/orm.v, it has whole git history.

[Coachonko]

Since nobody who worked on the mysql module replied to me, I tried to fix the problem in my fork by myself.
However, I do not think I understand V well enough to complete the task.