[Gambas-bugtracker] Bug #3125: Support for passing Arrays in Connection.Subst() with dynamic filters

[<bugtracker@xxxxxxxxxxxxxx>]

http://gambaswiki.org/bugtracker/edit?object=BUG.3125&from=L21haW4-

Omar MORENO reported a new bug.

Summary
-------

Support for passing Arrays in Connection.Subst() with dynamic filters

Type             : Request
Priority         : Medium
Gambas version   : Master
Product          : Database component


Description
-----------

Hello, in Gambas3 the Connection.Subst() function only accepts individual parameters using .... This works fine when the number of parameters is fixed, but it becomes cumbersome when building queries with dynamic WHERE clauses.
In our case, filters are assigned at runtime to a vector, for example:
FiltroX.Add(cliente)
FiltroX.Add(fecha1)
FiltroX.Add(fecha2)…
Dim WHEREX As String = "Where cliente = &1 AND fecha BETWEEN &2 AND &3 "
Dim SqlX As String = "SELECT COUNT(*) FROM tabla ” &  WHEREX 
ResultX = Conn.Exec(DB.Subst(SqlX, FiltroX))

This would greatly simplify the code and avoid having to use Select Case for each possible number of parameters passed to Exec or other SQL commands. Currently, the code looks like this:
   Select Case FiltrosX.Length
     Case 0
       	TotReg = Conn.Exec(DB.Subst(SqlX))
     Case 1
      	TotReg = Conn.Exec(DB.Subst(SqlX, FiltroX[0]))
     Case 2
       	TotReg = Conn.Exec(DB.Subst(SqlX, FiltroX[0],FiltroX[1]))
     Case 3
   	TotReg = Conn.Exec(DB.Subst(SqlX, FiltroX[0],FiltroX[1],FiltroX[2]))
   End Select
The requested improvement would be the ability to send an array directly to DB.Subst, for example:
TotReg = Conn.Exec(DB.Subst(SqlX, FiltroX))
This feature would allow handling dynamic filters without repetitive logic for each possible number of parameters.
Is there any chance this improvement could be considered for future versions of Gambas?
Thank you for your attention and for all the work on this great project.

Attached is a small sample project that demonstrates the current limitation and how the proposed feature would simplify the code.


System information
------------------

[System]
Gambas=3.21.99 c3b73e2 (master)
OperatingSystem=Linux
Distribution=Linux Mint 22.3 (Zena)
Kernel=6.8.0-90-generic
Architecture=x86_64
Cores=4
Memory=7639M
Language=es_ES.utf-8
Platform=x11
Desktop=XFCE
DesktopResolution=96
DesktopScale=7
WidgetTheme=mint-l
Font=Ubuntu,10
DarkTheme=False
[Programs]
dpkg=Programa de gestión de paquetes de Debian `dpkg' versión 1.22.6 (amd64).
gcc=gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0
git=git version 2.43.0
msgmerge=msgmerge (GNU gettext-tools) 0.21
[Libraries]
Cairo=libcairo.so.2.11800.0
Curl=libcurl.so.4.8.0
DBus=libdbus-1.so.3.32.4
GDK2=libgdk-x11-2.0.so.0.2400.33
GDK3=libgdk-3.so.0.2409.32
GStreamer=libgstreamer-1.0.so.0.2402.0
GTK+2=libgtk-x11-2.0.so.0.2400.33
GTK+3=libgtk-3.so.0.2409.32
OpenGL=libGL.so.1.7.0
Poppler=libpoppler.so.134.0.0
QT5=libQt5Core.so.5.15.13
QT6=libQt6Core.so.6.4.2
RSvg=librsvg-2.so.2.50.0
SDL=libSDL2-2.0.so.0.3000.0
SQLite3=libsqlite3.so.0.8.6
[Environment]
CLUTTER_BACKEND=x11
COMPIZ_CONFIG_PROFILE=mint
DBUS_SESSION_BUS_ADDRESS=unix:path=/run/user/1000/bus
DESKTOP_SESSION=xfce
DISPLAY=:0.0
GB_GUI=gb.gtk3
GDMSESSION=xfce
GDM_LANG=es_ES
GTK3_MODULES=xapp-gtk3-module
GTK_MODULES=gail:atk-bridge
HOME=<home>
LANG=es_ES.utf-8
LANGUAGE=es_ES.utf-8
LC_ALL=es_ES.utf-8
LOGNAME=<user>
PATH=<home>/.cargo/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
PWD=<home>
QT_ACCESSIBILITY=1
QT_LOGGING_RULES=*.debug=false
SESSION_MANAGER=local/<hostname>:@/tmp/.ICE-unix/1675,unix/<hostname>:/tmp/.ICE-unix/1675
SHELL=/bin/bash
SHLVL=0
SSH_AGENT_PID=1813
SSH_AUTH_SOCK=/tmp/ssh-OzBru1TOUI52/agent.1812
TZ=:/etc/localtime
USER=<user>
XAUTHORITY=<home>/.Xauthority
XDG_CONFIG_DIRS=/etc/xdg/xdg-xfce:/etc/xdg
XDG_CURRENT_DESKTOP=XFCE
XDG_DATA_DIRS=/usr/share/xfce4:<home>/.local/share/flatpak/exports/share:/var/lib/flatpak/exports/share:/usr/local/share:/usr/share
XDG_GREETER_DATA_DIR=/var/lib/lightdm-data/<user>
XDG_MENU_PREFIX=xfce-
XDG_RUNTIME_DIR=/run/user/1000
XDG_SEAT=seat0
XDG_SEAT_PATH=/org/freedesktop/DisplayManager/Seat0
XDG_SESSION_CLASS=user
XDG_SESSION_DESKTOP=xfce
XDG_SESSION_ID=c2
XDG_SESSION_PATH=/org/freedesktop/DisplayManager/Session0
XDG_SESSION_TYPE=x11
XDG_VTNR=7



----[ Gambas bugtracker-list is hosted by https://www.hostsharing.net ]----