db methods to prepare statements and execute prepared statements

[einar-hjortdal]

Describe the feature

Note: personally I use MySQL with db.mysql

Recently, the .exec methods were introduced, these solve a lot of problems in my use cases and I greatly appreciate them.
These methods prepare a statement and execute the statement right away. This is great because most of the times, this is how queries are or should be performed.
However, one use of prepared statements is to execute the same statement multiple times with different parameters. In my program I use this often to significantly and greatly improve performance. The .exec methods do not offer this feature.

In my opinion: the .exec methods should be used to execute statements that have already been prepared, preparing a statement should be done with a .prepare method. Preparing and executing a statement should be done with a .prep_and_exec method or something like that.
The reason is that the SQL has PREPARE and EXECUTE instructions, therefore .prepare and .execute methods would utilize the same keywords, or keywords similar enough to what they perform on the database.

Use Case

An example would be when inserting arrays in the database:
A statement should be prepared on the server and then executed a number of times equal to the length of an array, each time using the parameters of the index.
Only then, the statement is deallocated.
In this use case, first a PREPARE command is issued with the query, then EXECUTE n times, then DEALLOCATE.

Proposed Solution

DB.prepare method to prepare a statement

Using statement name as parameter

DB.execute method to execute a prepared statement with the given parameters. The name of the statement to execute is given to the method as a parameter.

DB.deallocate to deallocate a prepared statement once it is no longer needed. the name of the statement to deallocate is given to the method as a parameter.

DB.exec should be renamed to something else, because it doesn't just issue an EXECUTE command, but also a PREPARE and DEALLOCATE command.

Return a statement object

DB.prepare may return a statement struct. This struct should contain the name of the statement, the connection used to prepare the statement, and any other information required for the statement's execution and manipulation. New methods can be defined on this struct, instead of being defined on the DB type.
With this kind of implementation, current exec methods do not need to be renamed (although current naming is arguably confusing).

Acknowledgements

• I may be able to implement this feature request
• This feature might incur a breaking change

Version used

Current V version: V 0.4.3 55cac88, timestamp: 2023-11-13 18:12:54 +0200

Environment details (OS name and version, etc.)

V full version: V 0.4.3 95509cf.55cac88
OS: linux, Linux version 5.14.0-284.30.1.el9_2.x86_64 ([email protected]) (gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4), GNU ld version 2.35.2-37.el9) #1 SMP PREEMPT_DYNAMIC Fri Sep 15 08:06:33 UTC 2023
Processor: 8 cpus, 64bit, little endian, Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz

getwd: /home/coachonko
vexe: /home/coachonko/.local/lib64/v/v
vexe mtime: 2023-11-13 20:46:07

vroot: OK, value: /home/coachonko/.local/lib64/v
VMODULES: OK, value: /home/coachonko/.vmodules
VTMP: OK, value: /tmp/v_1000

Git version: git version 2.39.3
Git vroot status: weekly.2023.44-127-g55cac884
.git/config present: true

CC version: cc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4)
thirdparty/tcc status: thirdparty-linux-amd64 12f392c3

Note

You can use the 👍 reaction to increase the issue's priority for developers.

Please note that only the 👍 reaction to the issue itself counts as a vote.
Other reactions and those to comments will not be taken into account.