Clarion. Clarion Application Migration Process to Microsoft SQL 2019

I continue to talk about life with Clarion. In this post I will describe my way of solving one of the most frequent tasks facing Clarion developers, it is the migration of Clarion programs to the Miscrosoft SQL DBMS.

It so happened that a few months ago, 2 programs based on Clarion technology were handed over to me for service, the reason is sad, the old generation is leaving, and this is what happened to my scientific advisor. For several years I worked with him as a programmer at Clarion, then I lost interest in this technology and our paths diverged. And now, after the lapse of years, I am faced with the need to support and occasionally develop 2 programs.

Problematic

Clarion, , , Update Insert Clarion , . :

       Access:Agent.Open 					! 
       Access:Agent.UseFile				! 
       clear(AGN:Record)					!     
       AGN:ID_AGENT = some_id 		!  
       set(AGN:BY_ID,AGN:BY_ID)		! ""    
       next(agent)								!     
       IF errorcode() or AGN:ID_AGENT <> some_id	!       
            RETVAL = '  '				! 
          ELSE													
            RETVAL = AGN:N_AGENT									!  
       .								
       Access:Agent.Close												  ! 
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , " " "" . . SQL :

select agent.name where id = some_id
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, " 1 ", , SQL, SQL . , SQL SQL.

: 80

: 250

: + ( )

:

3

5 -

• 
  
  
  
  

• DCT2SQL

  
  
  
  
  

• Cldump

  
  
  
  
  

• BULK insert

  
  
  
  
  

• UltimateSQL & Ultimate Debug

  
  
  
  
  

, , . . post dat .

DCT2SQL

Dictionary SQL, , foreign keys. , . .

Github

youtube . SQL.

CLDUMP

*.dat csv BULK. - . 10 15-20 . , Linux, debian. -, post , csv .

, "" , , , "" . " " " ". . , , "".

cldump debian :

apt-get install cldump
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

BULK insert

In a split second pulls a table from csv into SQL. In this case, because data bindings are already configured for existing IDs, but at the same time auto-increment needs to work, so it must be temporarily disabled, I also spent quite a lot of time to find suitable separators:

BULK INSERT dbo.%table_name%
FROM table_name.csv WITH ( 
FORMAT = 'CSV', 
FIELDQUOTE = '', 
FIRSTROW = 1, 
FIELDTERMINATOR = '0x3b', 
ROWTERMINATOR = '0x0a', 
CODEPAGE='65001',
TABLOCK, 
KeepIdentity)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

UltimateSQL & Ultimate Debug

These components allow you to load data from SQL into QUEUE like this:

SQL_Result = sql.query('
select id, path_to_result 
from dbo.export_tasks as et 
where 
(status_complete = 0 or status_complete = 2) 
and export_table_id = '& exp:id
,qexport_tasks)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Execute queries without return values:

sql.Query('Update export_tasks set status_complete = 2 where id = ' & qexport_tasks.id)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

There is a great description of how to use on youtube:

Also, when installing, inside the templates there is an "Easter egg" from the author, how to solve the quest is described by the link .