C# .Net OleDbCommand with SQL request using "substring" word not working with DB2/400 database

Question

I'm working on Rest API that uses a DB2/400 database ( IBMi ).

I'm using this string to connect to database:

//  Defaut connexion string           
ConnectString = "Provider=IBMDA400;" + "Password=xxxxxxx;" +"User ID=Bxxxxxxxx;" +"Data Source=XXX.XX.XX.XX;";

The connection to the system is working well :

ConnectLst = new OleDbConnection(ConnectString);
try
{
    ConnectLst = new OleDbConnection(ConnectString);

    ConnectLst.Open();

    ResCnx = true;
}
catch (Exception ex)
{
    ResCnx = false;
}

finally
{ }

return ResCnx;

My problem is that a simple SQL request with a substring doesn't works:

string W_RqtSql = "SELECT SUBSTRING(CDMATN,2,3) " +                
                 " FROM " + @_Schema + ".CSALAR CSAL " +                     
                 " where CSAL.NOPPHN = '" + @_NumPers + "'";

I have the following error message :

• $exception {"SQL0104: Syntactic element ,2 is not correct. Possible elements: , FROM.\r\nCause . . . . .: A syntax error was detected at element ,2. , 2 is not a valid element. , FROM is a partial list of valid elements.

I have also tried SUBSTR, but doesn't work either.

The same SQL request without "substring" word works very well :

string W_RqtSql = "SELECT CDMATN " +                
                 " FROM " + @_Schema + ".CSALAR CSAL " +                     
                 " where CSAL.NOPPHN = '" + @_NumPers + "'";

How can I select a substring of my column value?

Answer 1

The solution is :

syntaxe with spaces between commas and numbers like this : SUBSTRING(CDMATN, 2, 3)

Many thanks for your help :)