Results 1 to 10 of 10

Thread: SQLExecDirect not inserting records

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Athens - Greece
    Posts
    282

    Default SQLExecDirect not inserting records

    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.



    Code:
            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
    Code:
                    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
    Last edited by Yannis; 22-Mar-2023 at 07:17 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •