Knowledge Base

FireDAC error Not Enough values for host variables - running XML job

Hint Ref: 021608190014
Hint Date: 19/08/2016

Hint Details:

PROBLEM 

When running the SEPA script against SM V3 for the first time in their tests, a customer experienced the following error:

FireDac Phys ODBC Sybase ODBC Driver SQL Anywhere Not enough values for host variables

ISSUE

After some extensive diagnostics, the error was determined to be in a <SQLExec> statement where a SELECT was being redirected INTO a temporary table.

Specifically, a statement where a backslash character was being replaced with an empty string and the SQL contained one or more reference to a parameter thus (for example):

select 
 replace(isnull('accountname','fullname'),'\','') as dbtrtemp
 into #SEPA
from BACSTransaction 
where APSBatchnumber=:Thisbatchno

SOLUTION

Two solutions were identified;

1. To replace ALL parameters in the SQL with substitutions instead, thus (for example):

select 
 replace(isnull('accountname','fullname'),'\','') as dbtrtemp
 into #SEPA
from BACSTransaction 
where APSBatchnumber={Thisbatchno}

2. A better solution was to replace the backslash character with the ascii equivalent of char(92) thus:

select 
 replace(isnull('accountname','fullname'),char(92),'') as dbtrtemp
 into #SEPA
from BACSTransaction 
where APSBatchnumber=:Thisbatchno


(Please Note: This Procedure can be destructive and should only be used by Advanced Users.  RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure, XML Script or SQL command.  Responsibility resides solely with the user.  

IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)