jRDC-MultiDB-Hikari/DBHandlerB4X.bas

B4J=true
Group=Default Group
ModulesStructureVersion=1
Type=Class
Version=10.3
@EndOfDesignText@
' Class module: DBHandlerB4X
' This generic handler is responsible for processing HTTP requests
' from B4A/B4i clients (using the DBRequestManager library).
' The database to use (DB1, DB2, etc.) is determined dynamically
' from the request URL.
' This version includes parameter validation and error handling.

Sub Class_Globals
	' --- Class global variables ---

	' The following section of constants and utilities is compiled conditionally
	' only if the #if VERSION1 directive is active. This is to support
	' an old version of the DBRequestManager communication protocol.
	'    #if VERSION1
	' Constants to identify data types in custom serialization (V1 protocol).
	Private const T_NULL = 0, T_STRING = 1, T_SHORT = 2, T_INT = 3, T_LONG = 4, T_FLOAT = 5 _
    ,T_DOUBLE = 6, T_BOOLEAN = 7, T_BLOB = 8 As Byte
	' Utilities for converting between data types and byte arrays.
	Private bc As ByteConverter
	' Utility for compressing/decompressing data streams (used in V1).
	Private cs As CompressedStreams
	'    #end if

	' Map to convert JDBC date/time column types to Java method names
	' to get the correct values from a ResultSet.
	Private DateTimeMethods As Map

	' Object that manages connections to a specific database's pool.
	' This RDCConnector instance will be assigned in the Handle method based on the request's dbKey.
	Private Connector As RDCConnector
End Sub

' Executes once when an instance of this class is created by the HTTP server.
Public Sub Initialize
	' Initializes the map that associates JDBC date/time column type codes
	' with the corresponding method names to read them correctly from a ResultSet.
	DateTimeMethods = CreateMap(91: "getDate", 92: "getTime", 93: "getTimestamp")
End Sub

' Main method that handles each HTTP request arriving at this handler.
' req: The ServletRequest object containing incoming request information.
' resp: The ServletResponse object for building and sending the response to the client.
Sub Handle(req As ServletRequest, resp As ServletResponse)
	' This section analyzes the request URL to determine which database
	' (DB1, DB2, etc.) the request is for. For example, if the URL is "/DB2/query",
	' the extracted 'dbKey' will be "DB2".
	Dim URI As String = req.RequestURI
	Dim dbKey As String ' Variable to store the database identifier.

	If URI.Length > 1 And URI.StartsWith("/") Then
		dbKey = URI.Substring(1) ' Remove the initial '/'.
		If dbKey.Contains("/") Then
			' If the URL has more segments (e.g., "/DB2/some_path"), take only the first segment as dbKey.
			dbKey = dbKey.SubString2(0, dbKey.IndexOf("/"))
		End If
	Else
		' If the URL is just "/", default to "DB1".
		dbKey = "DB1"
	End If
	dbKey = dbKey.ToUpperCase ' Normalize the dbKey to uppercase for consistency.

	' Check if the extracted dbKey corresponds to a database configured and loaded in Main.
	If Main.Connectors.ContainsKey(dbKey) = False Then
		Dim ErrorMsg As String = $"Invalid DB key specified in URL: '${dbKey}'. Valid keys are: ${Main.listaDeCP}"$
		Log(ErrorMsg)
		Main.LogServerError("ERROR", "DBHandlerB4X.Handle", ErrorMsg, dbKey, Null, req.RemoteAddress)
		SendPlainTextError(resp, 400, ErrorMsg)
		Return
	End If

'	Log("********************* " & dbKey & " ********************") ' Debug log to identify the database.

	Dim start As Long = DateTime.Now ' Record the request start time to calculate duration.

	' --- START: Active request count for this dbKey (Increment) ---
	' This block increments a global counter that tracks how many requests are
	' active for a specific database at any given time.
	Dim currentActiveRequests As Int = GlobalParameters.ActiveRequestsCountByDB.GetDefault(dbKey, 0).As(Int)
	GlobalParameters.ActiveRequestsCountByDB.Put(dbKey, currentActiveRequests + 1)
	' requestsBeforeDecrement is the counter value right after this request increments it.
	' This is the value that will be recorded in the 'query_logs' table.
	Dim requestsBeforeDecrement As Int = currentActiveRequests + 1
	' --- END: Active request count ---

	' Variable declarations with scope throughout the sub to ensure final cleanup.
	Dim q As String = "unknown_b4x_command" ' Command name for the log, with a default value.
	Dim con As SQL ' The DB connection, will be initialized later.
	Dim duration As Long ' The total request duration, calculated before logging.
	Dim poolBusyConnectionsForLog As Int = 0 ' Contains the number of busy connections from the pool.

	Try ' --- START: Try block wrapping the main Handler logic ---
		Dim in As InputStream = req.InputStream ' Get the HTTP request's input stream.
		Dim method As String = req.GetParameter("method") ' Get the 'method' parameter from the URL (e.g., "query2", "batch2").
		Connector = Main.Connectors.Get(dbKey) ' Assign the RDCConnector instance for this dbKey.

		con = Connector.GetConnection(dbKey) ' The DB connection is obtained here from the connection pool!

		' This block captures the number of connections currently busy in the pool
		' *after* this request has obtained its own.
		If Connector.IsInitialized Then
			Dim poolStats As Map = Connector.GetPoolStats
			If poolStats.ContainsKey("BusyConnections") Then
				poolBusyConnectionsForLog = poolStats.Get("BusyConnections").As(Int) ' We capture the value.
'				If Main.logger Then Log($">>>>>>>>>>  ${poolStats.Get("BusyConnections")} "$)
			End If
		End If

		Dim cachedStatsB4X As Map = Main.LatestPoolStats.Get(dbKey).As(Map)

		If cachedStatsB4X.IsInitialized Then
			' 1. Update Busy Connections and Active Requests
			cachedStatsB4X.Put("BusyConnections", poolBusyConnectionsForLog)
			cachedStatsB4X.Put("HandlerActiveRequests", requestsBeforeDecrement)

			' 2. Capture TotalConnections and IdleConnections (already available in poolStats)
			If poolStats.ContainsKey("TotalConnections") Then
				cachedStatsB4X.Put("TotalConnections", poolStats.Get("TotalConnections"))
			End If
			If poolStats.ContainsKey("IdleConnections") Then
				cachedStatsB4X.Put("IdleConnections", poolStats.Get("IdleConnections"))
			End If

			' 3. Re-write the map to the global cache (it's Thread-Safe)
			Main.LatestPoolStats.Put(dbKey, cachedStatsB4X)
		End If

'		Log("Metodo: " & method) ' Debug log to identify the request method.

		' --- Logic to execute different command types based on the 'method' parameter ---
		If method = "query2" Then
			' Execute a single query using V2 protocol (B4XSerializator).
			q = ExecuteQuery2(dbKey, con, in, resp)
			If q = "error" Then ' If ExecuteQuery2 returned a validation error.
				duration = DateTime.Now - start
				CleanupAndLog(dbKey, "error_in_" & method, duration, req.RemoteAddress, requestsBeforeDecrement, poolBusyConnectionsForLog, con)
				Return ' Early exit if there is an error.
			End If
			'        #if VERSION1
			' These branches are compiled only if #if VERSION1 is active (for old protocol).
		Else if method = "query" Then
			in = cs.WrapInputStream(in, "gzip") ' Decompress the input stream if it's V1 protocol.
			q = ExecuteQuery(dbKey, con, in, resp)
			If q = "error" Then
				duration = DateTime.Now - start
				CleanupAndLog(dbKey, "error_in_" & method, duration, req.RemoteAddress, requestsBeforeDecrement, poolBusyConnectionsForLog, con)
				Return
			End If
		Else if method = "batch" Then
			in = cs.WrapInputStream(in, "gzip") ' Decompress the input stream if it's V1 protocol.
			q = ExecuteBatch(dbKey, con, in, resp)
			If q = "error" Then
				duration = DateTime.Now - start
				CleanupAndLog(dbKey, "error_in_" & method, duration, req.RemoteAddress, requestsBeforeDecrement, poolBusyConnectionsForLog, con)
				Return
			End If
			'        #end if
		Else if method = "batch2" Then
			' Execute a batch of commands (INSERT, UPDATE, DELETE) using V2 protocol.
			q = ExecuteBatch2(dbKey, con, in, resp)
			If q = "error" Then
				duration = DateTime.Now - start
				CleanupAndLog(dbKey, "error_in_" & method, duration, req.RemoteAddress, requestsBeforeDecrement, poolBusyConnectionsForLog, con)
				Return ' Early exit if there is an error.
			End If
		Else
			Dim ErrorMsg As String = "Unknown method: " & method
			Log(ErrorMsg)
			Main.LogServerError("ERROR", "DBHandlerB4X.Handle", ErrorMsg, dbKey, method, req.RemoteAddress)
			SendPlainTextError(resp, 500, "unknown method")
			q = "unknown_method_handler"
			duration = DateTime.Now - start
			CleanupAndLog(dbKey, q, duration, req.RemoteAddress, requestsBeforeDecrement, poolBusyConnectionsForLog, con)
			Return
		End If

	Catch ' --- CATCH: Handle general execution or SQL errors ---
		Dim errorMessage As String = LastException.Message
		Private source As String = "DBHandlerB4X.Handle"
		If errorMessage.Contains("ORA-01002") Or errorMessage.Contains("recuperación fuera de secuencia") Then
			errorMessage = "SE USA EXECUTEQUERY EN LUGAR DE EXECUTECOMMAND: " & errorMessage
			source = "EXECUTEQUERY EN LUGAR DE EXECUTECOMMAND"
		else If errorMessage.Contains("ORA-17003") Or errorMessage.Contains("Índice de columnas no válido") Then
			errorMessage = "NUMERO DE PARAMETROS EQUIVOCADO: " & errorMessage
			source = "NUMERO DE PARAMETROS EQUIVOCADO"
		End If
'		Log(errorMessage) ' Log the full exception.
		Main.LogServerError("ERROR", source, errorMessage, dbKey, q, req.RemoteAddress)
		SendPlainTextError(resp, 500, errorMessage) ' Send a 500 error to the client.
		q = "error_in_b4x_handler" ' We ensure a value for 'q' in case of an exception.
		If source <> "DBHandlerB4X.Handle" Then q = source
	End Try ' --- END: Main Try block ---

	' --- Final logging and cleanup logic (for normal execution paths or after Catch) ---
	' This block ensures that, regardless of how the request ends (success or error),
	' the duration is calculated and the cleanup and logging subs are called.
	duration = DateTime.Now - start ' Calculate the total request duration.
'	Log($"${dbKey} - Command: ${q}, took: ${duration}ms, client=${req.RemoteAddress}"$) ' Log the command and duration.
	' Call the centralized subroutine to log performance and clean up resources.
	CleanupAndLog(dbKey, q, duration, req.RemoteAddress, requestsBeforeDecrement, poolBusyConnectionsForLog, con)

End Sub

' --- Subroutine: Centralizes performance logging and resource cleanup ---
' This subroutine is called by Handle at all exit points, ensuring
' that counters are decremented and connections are closed consistently.
Private Sub CleanupAndLog(dbKey As String, qName As String, durMs As Long, clientIp As String, handlerReqs As Int, poolBusyConns As Int, conn As SQL)
	' 1. Call the centralized subroutine in Main to log performance to SQLite.
	Main.LogQueryPerformance(qName, durMs, dbKey, clientIp, handlerReqs, poolBusyConns)

	' 2. Robustly decrement the active request counter for this dbKey.
	Dim currentCount As Int = GlobalParameters.ActiveRequestsCountByDB.GetDefault(dbKey, 0).As(Int)

	If currentCount > 0 Then
		' If the counter is positive, decrement it.
		GlobalParameters.ActiveRequestsCountByDB.Put(dbKey, currentCount - 1)
	Else
		' If the counter is already 0 or negative (which shouldn't happen with current logic,
		' but is handled for robustness), we log a warning and ensure it is 0.
'		Log($"ADVERTENCIA: Intento de decrementar ActiveRequestsCountByDB para ${dbKey} que ya estaba en ${currentCount}. Asegurando a 0."$)
		GlobalParameters.ActiveRequestsCountByDB.Put(dbKey, 0)
	End If

	' 3. Ensure the DB connection is always closed and returned to the connection pool.
	If conn <> Null And conn.IsInitialized Then conn.Close
End Sub

' --- Subroutines for handling query and batch execution (V2 Protocol) ---

' Executes a single query using V2 protocol (B4XSerializator).
' DB: Database identifier.
' con: The SQL connection obtained from the pool.
' in: Request InputStream.
' resp: ServletResponse to send the response.
' Returns the executed command name or "error" if it failed.
Private Sub ExecuteQuery2 (DB As String, con As SQL, in As InputStream, resp As ServletResponse) As String
	Dim ser As B4XSerializator ' Object to deserialize data sent from the client.
	' Convert the input stream to a byte array and then to a Map object.
	Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
	' Extract the DBCommand object (query name and its parameters) from the map.
	Dim cmd As DBCommand = m.Get("command")
	' Extract the row limit to return (for pagination).
	Dim limit As Int = m.Get("limit")

	' Get the SQL statement corresponding to the command name from config.properties.
	Dim sqlCommand As String = Connector.GetCommand(DB, cmd.Name)

	' <<< START VALIDATION: CHECK IF COMMAND EXISTS >>>
	' Check if the command was not found in the configuration file.
	If sqlCommand = Null Or sqlCommand = "null" Or sqlCommand.Trim = "" Then
		Dim errorMessage As String = $"El comando '${cmd.Name}' no fue encontrado en el config.properties de '${DB}'."$
		Log(errorMessage)
		Main.LogServerError("ERROR", "DBHandlerB4X.ExecuteQuery2", errorMessage, DB, cmd.Name, Null)
		' Send a 400 (Bad Request) error to the client informing them of the problem.
		SendPlainTextError(resp, 400, errorMessage)
		Return "error" ' Return a string for the log.
	End If
	' <<< END VALIDATION >>>

	' <<< START CENTRALIZED PARAMETER VALIDATION >>>
	' Convert the cmd.Parameters Object() array to a List for the validation utility.
	Dim paramsAsList As List
	paramsAsList.Initialize
	If cmd.Parameters <> Null Then
		For Each p As Object In cmd.Parameters
			paramsAsList.Add(p)
		Next
	End If

	Dim validationResult As ParameterValidationResult = ParameterValidationUtils.ValidateAndAdjustParameters(cmd.Name, DB, sqlCommand, paramsAsList, Connector.IsParameterToleranceEnabled)

	If validationResult.Success = False Then
		SendPlainTextError(resp, 400, validationResult.ErrorMessage)
		Return "error" ' Early exit if validation fails.
	End If

	' Execute the SQL query with the validated parameter list.
	Dim rs As ResultSet = con.ExecQuery2(sqlCommand, validationResult.ParamsToExecute)
	' <<< END CENTRALIZED PARAMETER VALIDATION >>>

	' If the limit is 0 or negative, set it to a very high value (max int).
	If limit <= 0 Then limit = 0x7fffffff 'max int

	' Get the underlying Java object from the ResultSet to access additional methods.
	Dim jrs As JavaObject = rs
	' Get the ResultSet metadata (information about the columns).
	Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
	' Get the number of columns in the result.
	Dim cols As Int = rs.ColumnCount

	Dim res As DBResult ' Create a DBResult object to package the response.
	res.Initialize
	res.columns.Initialize
	res.Tag = Null

	' Fill the column map with each column's name and its index.
	For i = 0 To cols - 1
		res.columns.Put(rs.GetColumnName(i), i)
	Next

	' Initialize the row list.
	res.Rows.Initialize

	' Iterate over each row in the ResultSet, up to the limit.
	Do While rs.NextRow And limit > 0
		Dim row(cols) As Object
		' Iterate over each column in the current row.
		For i = 0 To cols - 1
			' Get the column data type according to JDBC.
			Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
			' Handle different data types to read them correctly.
			If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then ' BLOB/binary types
				row(i) = rs.GetBlob2(i)
			Else If ct = 2005 Then ' CLOB type (long text)
				row(i) = rs.GetString2(i)
			Else if ct = 2 Or ct = 3 Then ' Numeric types that can have decimals
				row(i) = rs.GetDouble2(i)
			Else If DateTimeMethods.ContainsKey(ct) Then ' Date/Time types
				' Get the Java time/date object.
				Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
				If SQLTime.IsInitialized Then
					' Convert it to milliseconds (Long) for B4X.
					row(i) = SQLTime.RunMethod("getTime", Null)
				Else
					row(i) = Null
				End If
			Else ' For all other data types
				' Use getObject which works for most standard types.
				row(i) = jrs.RunMethod("getObject", Array(i + 1))
			End If
		Next
		' Add the complete row to the results list.
		res.Rows.Add(row)
		limit = limit - 1
	Loop
	' Close the ResultSet to free resources.
	rs.Close

	' Serialize the complete DBResult object to a byte array.
	Dim data() As Byte = ser.ConvertObjectToBytes(res)
	' Write the serialized data to the response stream.
	resp.OutputStream.WriteBytes(data, 0, data.Length)

	' Return the command name for the log.
	Return "query: " & cmd.Name
End Sub

' Executes a batch of commands (INSERT, UPDATE, DELETE) using V2 protocol.
' DB: Database identifier.
' con: The SQL connection obtained from the pool.
' in: Request InputStream.
' resp: ServletResponse to send the response.
' Returns a summary of the batch for the log, or "error" if it failed.
Private Sub ExecuteBatch2(DB As String, con As SQL, in As InputStream, resp As ServletResponse) As String
	Dim ser As B4XSerializator
	' Deserialize the map containing the list of commands.
	Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
	' Get the list of DBCommand objects.
	Dim commands As List = m.Get("commands")
	Dim totalAffectedRows As Int = 0 ' Counter to accumulate the total affected rows.

	' Prepare a DBResult object for the response (for batch, it doesn't return data, only confirmation).
	Dim res As DBResult
	res.Initialize
	res.columns = CreateMap("AffectedRows": 0) ' Symbolic column.
	res.Rows.Initialize
	res.Tag = Null

	Try
		' Start a transaction. All commands in the batch will be executed as one unit.
		con.BeginTransaction
		' Iterate over each command in the list.
		For Each cmd As DBCommand In commands
			' Get the SQL statement for the current command.
			Dim sqlCommand As String = Connector.GetCommand(DB, cmd.Name)

			' <<< START VALIDATION: CHECK IF COMMAND EXISTS WITHIN BATCH >>>
			If sqlCommand = Null Or sqlCommand = "null" Or sqlCommand.Trim = "" Then
				con.Rollback ' Rollback the transaction if a command is invalid.
				Dim errorMessage As String = $"El comando '${cmd.Name}' no fue encontrado en el config.properties de '${DB}'."$
				Log(errorMessage)
				Main.LogServerError("ERROR", "DBHandlerB4X.ExecuteBatch2", errorMessage, DB, cmd.Name, Null)
				SendPlainTextError(resp, 400, errorMessage)
				Return "error"
			End If
			' <<< END VALIDATION >>>

			' <<< START CENTRALIZED PARAMETER VALIDATION WITHIN BATCH >>>
			' Convert the cmd.Parameters Object() array to a List for the validation utility.
			Dim paramsAsList As List
			paramsAsList.Initialize
			If cmd.Parameters <> Null Then
				For Each p As Object In cmd.Parameters
					paramsAsList.Add(p)
				Next
			End If

			Dim validationResult As ParameterValidationResult = ParameterValidationUtils.ValidateAndAdjustParameters(cmd.Name, DB, sqlCommand, paramsAsList, Connector.IsParameterToleranceEnabled)

			If validationResult.Success = False Then
				con.Rollback ' Important: rollback if validation fails inside a transaction!
				SendPlainTextError(resp, 400, validationResult.ErrorMessage)
				Return "error" ' Early exit if validation fails.
			End If

			con.ExecNonQuery2(sqlCommand, validationResult.ParamsToExecute) ' Execute the command with the validated parameter list.
			
			totalAffectedRows = totalAffectedRows + 1 ' We accumulate 1 for each command executed without error.
			
			' <<< END CENTRALIZED PARAMETER VALIDATION WITHIN BATCH >>>
			
		Next

		res.Rows.Add(Array As Object(totalAffectedRows)) ' Add a symbolic row to the result to indicate success.
		con.TransactionSuccessful ' If all commands executed without error, commit the transaction.
	Catch
		' If any command fails, the error is caught.
		con.Rollback ' All changes made in the transaction are undone.
		Log(LastException) ' Log the exception.
		Main.LogServerError("ERROR", "DBHandlerB4X.ExecuteBatch2", LastException.Message, DB, "batch_execution_error", Null)
		SendPlainTextError(resp, 500, LastException.Message) ' Send a 500 error to the client.
	End Try

	' Serialize and send the response to the client.
	Dim data() As Byte = ser.ConvertObjectToBytes(res)
	resp.OutputStream.WriteBytes(data, 0, data.Length)

	' Return a summary for the log, including the query name if it's a batch of size 1.
	If commands.Size = 1 Then
		' Get the only command in the batch.
		Dim cmd As DBCommand = commands.Get(0)
		Return $"batch (size=1) - query: ${cmd.Name}"$
	Else
		' If the batch size is > 1, keep the summary by size.
		Return $"batch (size=${commands.Size})"$
	End If
End Sub

' --- Subroutines for handling query and batch execution (V1 Protocol - Conditional Compilation) ---
' This code is compiled only if #if VERSION1 is active, to maintain compatibility with old clients.
'#if VERSION1

' Executes a batch of commands using V1 protocol.
Private Sub ExecuteBatch(DB As String, con As SQL, in As InputStream, resp As ServletResponse) As String
'	Log($"ExecuteBatch ${DB}"$)
	' Read and discard the client version.
	Dim clientVersion As Float = ReadObject(in) 'ignore
	' Read how many commands are in the batch.
	Dim numberOfStatements As Int = ReadInt(in)
	Dim res(numberOfStatements) As Int ' Array for results (although not used).
	Dim singleQueryName As String = ""
	Dim affectedCounts As List
	Dim totalAffectedRows As Int
	affectedCounts.Initialize

	Try
		con.BeginTransaction
		' Iterate to process each command in the batch.
'		Log(numberOfStatements)
		For i = 0 To numberOfStatements - 1
'			Log($"i: ${i}"$)
			' Read the command name and parameter list using the V1 deserializer.
			Dim queryName As String = ReadObject(in)
			Dim params As List = ReadList(in)
'			Log(params)
			If numberOfStatements = 1 Then
				singleQueryName = queryName 'Capture the query name.
			End If
			Dim sqlCommand As String = Connector.GetCommand(DB, queryName)
'			Log(sqlCommand)
			' <<< START VALIDATION: CHECK IF COMMAND EXISTS (V1) >>>
			If sqlCommand = Null Or sqlCommand = "null" Or sqlCommand.Trim = "" Then
				con.Rollback ' Rollback the transaction if a command is invalid.
				Dim errorMessage As String = $"El comando '${queryName}' no fue encontrado en el config.properties de '${DB}'."$
				Log(errorMessage)
				Main.LogServerError("ERROR", "DBHandlerB4X.ExecuteBatch (V1)", errorMessage, DB, queryName, Null)
				SendPlainTextError(resp, 400, errorMessage)
				Return "error"
			End If
			' <<< END VALIDATION >>>

			' <<< START CENTRALIZED PARAMETER VALIDATION WITHIN BATCH (V1) >>>
			Dim validationResult As ParameterValidationResult = ParameterValidationUtils.ValidateAndAdjustParameters(queryName, DB, sqlCommand, params, Connector.IsParameterToleranceEnabled)

			If validationResult.Success = False Then
				con.Rollback ' Important: rollback if validation fails inside a transaction!
				SendPlainTextError(resp, 400, validationResult.ErrorMessage)
				Return "error" ' Early exit if validation fails.
			End If
			
'			Log(validationResult.ParamsToExecute)
			
			Dim affectedCount As Int = 1 ' Assume success (1) since the direct call is the only one that executes SQL without failing at runtime.

			con.ExecNonQuery2(sqlCommand, validationResult.ParamsToExecute) ' Execute the command with the validated parameter list.
			' <<< END CENTRALIZED PARAMETER VALIDATION WITHIN BATCH (V1) >>>
			affectedCounts.Add(affectedCount) ' Add the result (1) to the V1 response list
			totalAffectedRows = totalAffectedRows + affectedCount ' Accumulate the total for the log (even if it's symbolic 1)
			
		Next

		con.TransactionSuccessful ' Commit the transaction.
		
'		Log("Transaction succesfull")

		Dim out As OutputStream = cs.WrapOutputStream(resp.OutputStream, "gzip") ' Compress the output before sending it.
		' Write the response using the V1 serializer.
		WriteObject(Main.VERSION, out)
		WriteObject("batch", out)
		WriteInt(res.Length, out)
		For Each r As Int In affectedCounts
			WriteInt(r, out)
		Next
		out.Close

	Catch
		con.Rollback
		Log(LastException)
		Main.LogServerError("ERROR", "DBHandlerB4X.ExecuteBatch (V1)", LastException.Message, DB, "batch_execution_error_v1", Null)
		SendPlainTextError(resp, 500, LastException.Message)
	End Try

'	Return $"batch (size=${numberOfStatements})"$
	If numberOfStatements = 1 And singleQueryName <> "" Then
		Return $"batch (size=1) - query: ${singleQueryName}"$
	Else
		Return $"batch (size=${numberOfStatements})"$
	End If
End Sub

' Executes a single query using V1 protocol.
Private Sub ExecuteQuery(DB As String, con As SQL, in As InputStream, resp As ServletResponse) As String
	'    Log("====================== ExecuteQuery =====================")
	' Deserialize the request data using V1 protocol.
	Dim clientVersion As Float = ReadObject(in) 'ignore
	Dim queryName As String = ReadObject(in)
	Dim limit As Int = ReadInt(in)
	Dim params As List = ReadList(in)

	' Get the SQL statement.
	Dim theSql As String = Connector.GetCommand(DB, queryName)

	' <<< START VALIDATION: CHECK IF COMMAND EXISTS (V1) >>>
	If theSql = Null Or theSql ="null" Or theSql.Trim = "" Then
		Dim errorMessage As String = $"El comando '${queryName}' no fue encontrado en el config.properties de '${DB}'."$
		Log(errorMessage)
		Main.LogServerError("ERROR", "DBHandlerB4X.ExecuteQuery (V1)", errorMessage, DB, queryName, Null)
		SendPlainTextError(resp, 400, errorMessage)
		Return "error"
	End If
	' <<< END VALIDATION >>>

	' <<< START CENTRALIZED PARAMETER VALIDATION (V1) >>>
	Dim validationResult As ParameterValidationResult = ParameterValidationUtils.ValidateAndAdjustParameters(queryName, DB, theSql, params, Connector.IsParameterToleranceEnabled)

	If validationResult.Success = False Then
		SendPlainTextError(resp, 400, validationResult.ErrorMessage)
		Return "error" ' Early exit if validation fails.
	End If

	' Execute the query with the validated parameter list.
	Dim rs As ResultSet = con.ExecQuery2(theSql, validationResult.ParamsToExecute)
	' <<< END CENTRALIZED PARAMETER VALIDATION (V1) >>>

	If limit <= 0 Then limit = 0x7fffffff 'max int

	Dim jrs As JavaObject = rs
	Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
	Dim cols As Int = rs.ColumnCount

	Dim out As OutputStream = cs.WrapOutputStream(resp.OutputStream, "gzip") ' Compress the output stream.

	' Write the V1 response header.
	WriteObject(Main.VERSION, out)
	WriteObject("query", out)
	WriteInt(rs.ColumnCount, out)

	' Write the column names.
	For i = 0 To cols - 1
		WriteObject(rs.GetColumnName(i), out)
	Next

	' Iterate over the result rows.
	Do While rs.NextRow And limit > 0
		WriteByte(1, out) ' Write a '1' byte to indicate a row is coming.
		' Iterate over the row's columns.
		For i = 0 To cols - 1
			Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
			' Handle binary data types specially.
			If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
				WriteObject(rs.GetBlob2(i), out)
			Else
				' Write the column value.
				WriteObject(jrs.RunMethod("getObject", Array(i + 1)),  out)
			End If
		Next
		limit = limit - 1
	Loop

	' Write a '0' byte to indicate the end of rows.
	WriteByte(0, out)
	out.Close
	rs.Close

	Return "query: " & queryName
End Sub

' Writes a single byte to the output stream.
Private Sub WriteByte(value As Byte, out As OutputStream)
	out.WriteBytes(Array As Byte(value), 0, 1)
End Sub

' Main serializer for V1 protocol. Writes an object to the stream.
Private Sub WriteObject(o As Object, out As OutputStream)
	Dim data() As Byte
	' Write a type byte followed by the data.
	If o = Null Then
		out.WriteBytes(Array As Byte(T_NULL), 0, 1)
	Else If o Is Short Then
		out.WriteBytes(Array As Byte(T_SHORT), 0, 1)
		data = bc.ShortsToBytes(Array As Short(o))
	Else If o Is Int Then
		out.WriteBytes(Array As Byte(T_INT), 0, 1)
		data = bc.IntsToBytes(Array As Int(o))
	Else If o Is Float Then
		out.WriteBytes(Array As Byte(T_FLOAT), 0, 1)
		data = bc.FloatsToBytes(Array As Float(o))
	Else If o Is Double Then
		out.WriteBytes(Array As Byte(T_DOUBLE), 0, 1)
		data = bc.DoublesToBytes(Array As Double(o))
	Else If o Is Long Then
		out.WriteBytes(Array As Byte(T_LONG), 0, 1)
		data = bc.LongsToBytes(Array As Long(o))
	Else If o Is Boolean Then
		out.WriteBytes(Array As Byte(T_BOOLEAN), 0, 1)
		Dim b As Boolean = o
		Dim data(1) As Byte
		If b Then data(0) = 1 Else data(0) = 0
	Else If GetType(o) = "[B" Then ' If the object is a byte array (BLOB)
		data = o
		out.WriteBytes(Array As Byte(T_BLOB), 0, 1)
		' Write the data length before the data itself.
		WriteInt(data.Length, out)
	Else ' Treat everything else as a String
		out.WriteBytes(Array As Byte(T_STRING), 0, 1)
		data = bc.StringToBytes(o, "UTF8")
		' Write the string length before the string.
		WriteInt(data.Length, out)
	End If
	' Write the data bytes.
	If data.Length > 0 Then out.WriteBytes(data, 0, data.Length)
End Sub

' Main deserializer for V1 protocol. Reads an object from the stream.
Private Sub ReadObject(In As InputStream) As Object
	' Read the first byte to determine the data type.
	Dim data(1) As Byte
	In.ReadBytes(data, 0, 1)
	Select data(0)
		Case T_NULL
			Return Null
		Case T_SHORT
			Dim data(2) As Byte
			Return bc.ShortsFromBytes(ReadBytesFully(In, data, data.Length))(0)
		Case T_INT
			Dim data(4) As Byte
			Return bc.IntsFromBytes(ReadBytesFully(In, data, data.Length))(0)
		Case T_LONG
			Dim data(8) As Byte
			Return bc.LongsFromBytes(ReadBytesFully(In, data, data.Length))(0)
		Case T_FLOAT
			Dim data(4) As Byte
			Return bc.FloatsFromBytes(ReadBytesFully(In, data, data.Length))(0)
		Case T_DOUBLE
			Dim data(8) As Byte
			Return bc.DoublesFromBytes(ReadBytesFully(In, data, data.Length))(0)
		Case T_BOOLEAN
			Dim b As Byte = ReadByte(In)
			Return b = 1
		Case T_BLOB
			' Read the length, then read that amount of bytes.
			Dim len As Int = ReadInt(In)
			Dim data(len) As Byte
			Return ReadBytesFully(In, data, data.Length)
		Case Else ' T_STRING
			' Read the length, then read that amount of bytes and convert to string.
			Dim len As Int = ReadInt(In)
			Dim data(len) As Byte
			ReadBytesFully(In, data, data.Length)
			Return BytesToString(data, 0, data.Length, "UTF8")
	End Select
End Sub

' Ensures that exactly the requested amount of bytes is read from the stream.
Private Sub ReadBytesFully(In As InputStream, Data() As Byte, Len As Int) As Byte()
	Dim count = 0, Read As Int
	' Keep reading in a loop to fill the buffer, in case data arrives in chunks.
	Do While count < Len And Read > -1
		Read = In.ReadBytes(Data, count, Len - count)
		count = count + Read
	Loop
	Return Data
End Sub

' Writes an integer (4 bytes) to the stream.
Private Sub WriteInt(i As Int, out As OutputStream)
	Dim data() As Byte
	data = bc.IntsToBytes(Array As Int(i))
	out.WriteBytes(data, 0, data.Length)
End Sub

' Reads an integer (4 bytes) from the stream.
Private Sub ReadInt(In As InputStream) As Int
	Dim data(4) As Byte
	Return bc.IntsFromBytes(ReadBytesFully(In, data, data.Length))(0)
End Sub

' Reads a single byte from the stream.
Private Sub ReadByte(In As InputStream) As Byte
	Dim data(1) As Byte
	In.ReadBytes(data, 0, 1)
	Return data(0)
End Sub

' Reads a list of objects from the stream (V1 protocol).
Private Sub ReadList(in As InputStream) As List
	' First, read the number of elements in the list.
	Dim len As Int = ReadInt(in)
	Dim l1 As List
	l1.Initialize
	' Then, read each object one by one and add it to the list.
	For i = 0 To len - 1
		l1.Add(ReadObject(in))
	Next
	Return l1
End Sub

'#end If ' End of conditional compilation block for VERSION1

' Sends an error response in plain text format.
' This avoids the default HTML error page generated by resp.SendError.
' resp: The ServletResponse object to send the response.
' statusCode: The HTTP status code (e.g., 400 for Bad Request, 500 for Internal Server Error).
' errorMessage: The error message to be sent to the client.
' In B4X clients, an HTML or JSON response is not ideal. The IDE displays the full error text, and plain text is much easier to read than HTML or JSON.
Private Sub SendPlainTextError(resp As ServletResponse, statusCode As Int, errorMessage As String)
	Try
		' Set the HTTP status code (e.g., 400, 500).
		resp.Status = statusCode
		' Set the content type to plain text, with UTF-8 encoding to support accents.
		resp.ContentType = "text/plain; charset=utf-8"
		' Get the response OutputStream to write data directly.
		Dim out As OutputStream = resp.OutputStream
		' Convert the error message to a byte array using UTF-8.
		Dim data() As Byte = errorMessage.GetBytes("UTF8")
		' Write the bytes to the output stream.
		out.WriteBytes(data, 0, data.Length)
		' Close the stream to ensure all data is sent correctly.
		out.Close
	Catch
		' If something fails while trying to send the error response, log it
		' so the original cause of the problem is not lost.
		Dim ErrorMsg As String = "Error sending plain text error response: " & LastException
		Log(ErrorMsg)
		Main.LogServerError("ERROR", "DBHandlerB4X.SendPlainTextError", ErrorMsg, Null, Null, Null)
	End Try
End Sub