SQLExecDirect not inserting records

[Yannis]

Hello,
I have an app in VDF19.1 from which I have to create new records in a couple of tables (not in the filelist), and these tables are located in a different database, same server (SQLServer 2019 - using Mertcech Drivers)
The problem is that I have tried to use SQLExecDirect to insert these new records, but while I am not getting any error message during the process, no records are created in the destination table. Just as a side note, this destination table is always empty when the process starts.

Here is the code I am using to loop through a table called CLIENTS, and I just want to send all these records to the other table called USTRADERS.
For each record of the CLIENTS table, I create the query string by gathering the fields needed.
The number of filed headers is the same as the number of values that I am using.
I copied a sample of what the query looks like in the watches window (the 'weird' characters are from the copy/paste of the watch window, but the strings are in Greek and are displayed correctly in the watches window)

The display I have showing on the view the Client ID and his name do show me correct values while the loop is executed, but at the end, no records are inserted.
The process is not placed within a transaction block, either.

        String CS_QUERY_WRITE_CLIENTS_FIELDS CS_QUERY_WRITE_CLIENTS_VALUES


        #Replace CS_CONNECT "SERVER=HERMES\SQL2109;USR=Yannis;PWD='123';Trusted_Connection=no;Database=dbSuperBeton"
        
        Move "INSERT INTO USTRADERS (CODE, NAME, Address,Number,AREA,TK,CITY,PHONE,GLACCOUNT,AFM,SOURCE,VATCATEGORY_1,CUSTOMERSUPPLIER,DOYCODE,VATCATEGORY_2) " ;
                to CS_QUERY_WRITE_CLIENTS_FIELDS 


        Move "" to CS_QUERY_WRITE_CLIENTS_VALUES  
    
        Integer hdbc
        Integer hstmt
        Integer iFetchResult
        
        Set_Argument_Size 17000
        
        String  sID
        String  sName
        String  sEmail
        String  sAddress


        Object oSQLHandler is a cSQLHandleManager
            Move Self to hoSQL
        End_Object // oSQLHandler


        Send SQLSetConnect of hoSQL "SQL_DRV" CS_CONNECT


        Get SQLConnect     of hoSQL "" "" to hdbc
        If (hdbc <> 0) Begin
            
            //*** Open a statement
            Get SQLOpen of hdbc to hstmt
            If (hstmt <> 0) Begin


                Clear CLIENTS
                Find GE CLIENTS by Index.3
                While (Found)
                    String sClientName 
                    Move (Trim(CLIENTS.CLIENT_NAME)) to sClientName
                    Move (Replaces('""',sClientName," "))   to sClientName
                    Move (Replaces("'" ,sClientName," "))   to sClientName
                    
                    Move (Trim(CLIENTS.CLIENT_ADDRESS)) to sAddress
                    Move (Replaces(",",sAddress," "))   to sAddress 
                    
                    Move " VALUES (" to CS_QUERY_WRITE_CLIENTS_VALUES
                    
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_ID)     +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(sClientName)           +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(sAddress)              +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_ADD_NO) +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
        
                    Clear AREAS
                    Move CLIENTS.CLIENT_AREA to AREAS.AREA_ID
                    Find Eq AREAS by Index.1
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(AREAS.AREA_DESCR)      +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
        
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_ZIPCODE)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
        
                    Clear CITIES
                    Move CLIENTS.CLIENT_CITY_ID to CITIES.CITY_ID
                    Find Eq CITIES by Index.1
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CITIES.CITY_DESCR)     +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
        
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_PHONE)  +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_GLCODE) +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_AFM)    +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.IS_EKSOTER)    +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
        
                    String sVATStatus
                    Move 0 to sVATStatus // 0=Ypoxreos, 1=MH Ypoxreos
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(sVATStatus)            +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    
                    If      (Trim(CLIENTS.IS_CLIENT      )="1") Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim("CUSTOMER")+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Else If (Trim(CLIENTS.IS_PROMHTHEYTHS)="1") Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim("SUPPLIER")+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_DOY)    +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                    Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.SUM_PCT_TPCL)  +"')")) to CS_QUERY_WRITE_CLIENTS_VALUES
                   
                    Send SQLExecDirect of hstmt (CS_QUERY_WRITE_CLIENTS_FIELDS+CS_QUERY_WRITE_CLIENTS_VALUES)
                    
                    Set Label of btnCount to ("EXPORTING TABLE CLIENTS... "+Trim(CLIENTS.CLIENT_ID)+"  "+Trim(CLIENTS.CLIENT_NAME))
        
                    Find GT CLIENTS by Index.3
                Loop
            End
            Send SQLClose of hstmt
        End
        Send SQLDisconnect of hdbc


        Send Destroy of hoSQL


        Set_Argument_Size 256

And here is a sample of the query from the watches window for 1 Clients record.
Note that when I take the exact same sentence with copy/paste from the watches window of the Dataflex Studio, and I paste it in the SQL Manager query window, the record is created without any problem

INSERT INTO USTRADERS (CODE, NAME, Address,Number,AREA,TK,CITY,PHONE,GLACCOUNT,AFM,SO URCE,VATCATEGORY_1,CUSTOMERSUPPLIER,DOYCODE,VATCAT EGORY_2) VALUES ('4F4690802DA3','EXPIM-KYKNOS ÊÁÓÉÌÁÔÇ ÅÕÁÃÃ. ÁÍÍÁ','×ÏÕÑÆÏÕÌÇ 402','','ÁÈÇÍÁ','19300','ÁÃÍÙÓÔÇ','210-4813396','','137504213','','0','ÐÑÏÌÇÈÅÕÔÇÓ','','2 4')

All the fields in the receiving database are strings allowing nulls.

Any idea as to why the records are not inserted?

Thank you
Yannis

P.S. I also tried SQLPrepare and SQLExecute, same issue there too, code tested is below

                Clear CLIENTS
                Find GE CLIENTS by Index.3
                While (Found)


                    //*** Open a statement
                    Get SQLOpen of hdbc to hstmt
                    If (hstmt <> 0) Begin
                    
                    
                        String sClientName 
                        Move (Trim(CLIENTS.CLIENT_NAME)) to sClientName
                        Move (Replaces('"',sClientName," "))   to sClientName
                        Move (Replaces("'" ,sClientName," "))   to sClientName
                        
                        Move (Trim(CLIENTS.CLIENT_ADDRESS)) to sAddress
                        Move (Replaces(",",sAddress," "))   to sAddress 
                        
                        Move "VALUES (" to CS_QUERY_WRITE_CLIENTS_VALUES
                        
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_ID)     +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(sClientName)           +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(sAddress)              +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_ADD_NO) +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
            
                        Clear AREAS
                        Move CLIENTS.CLIENT_AREA to AREAS.AREA_ID
                        Find Eq AREAS by Index.1
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(AREAS.AREA_DESCR)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
            
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_ZIPCODE)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
            
                        Clear CITIES
                        Move CLIENTS.CLIENT_CITY_ID to CITIES.CITY_ID
                        Find Eq CITIES by Index.1
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CITIES.CITY_DESCR)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
            
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_PHONE)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_GLCODE)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_AFM)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.IS_EKSOTER)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
            
                        String sVATStatus
                        Move 0 to sVATStatus // 0=Ypoxreos, 1=MH Ypoxreos
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(sVATStatus)+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        
                        If      (Trim(CLIENTS.IS_CLIENT      )="1") Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim("ΠΕΛΑΤΗΣ")+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Else If (Trim(CLIENTS.IS_PROMHTHEYTHS)="1") Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim("ΠΡΟΜΗΘΕΥΤΗΣ")+"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.CLIENT_DOY)    +"',")) to CS_QUERY_WRITE_CLIENTS_VALUES
                        Move (Append(CS_QUERY_WRITE_CLIENTS_VALUES, "'"+Trim(CLIENTS.SUM_PCT_TPCL)  +"')")) to CS_QUERY_WRITE_CLIENTS_VALUES
            
    
                        Send SQLPrepare of hstmt  (CS_QUERY_WRITE_CLIENTS_FIELDS+CS_QUERY_WRITE_CLIENTS_VALUES) //  "delete from customer"
                        Send SQLExecute of hstmt
                        
                        //Send SQLExecDirect of hstmt (CS_QUERY_WRITE_CLIENTS_FIELDS+CS_QUERY_WRITE_CLIENTS_VALUES)
                        
                        
                        Set Label of btnCount to ("ΕΞΑΓΩΓΗ ΦΟΡΕΩΝ... "+Trim(CLIENTS.CLIENT_ID)+"  "+Trim(CLIENTS.CLIENT_NAME))
                    End
                    
                    Send SQLClose of hstmt
        
                    Find GT CLIENTS by Index.3
                Loop