sqlsequencemulti-master-replication

reading from config file in a sql script


I have a sql script where I create all the sequence used in the application as below. These values will be different for different servers. Instead of giving these hard coded values, I want to specify these in some config file Is there a way that I can read all these values from the config file in my sql script

CREATE SEQUENCE tbl1_tbl1id_seq AS INT START WITH 1 INCREMENT BY 2 MAXVALUE 50000;

CREATE SEQUENCE tbl2_tb21id_seq AS INT START WITH 1 INCREMENT BY 2 MAXVALUE 50000;

CREATE SEQUENCE tbl3_tbl3id_seq AS INT START WITH 1 INCREMENT BY 2 MAXVALUE 50000;


Solution

  • I could do that in the below fashion. I wrote a shell script which would read from the config file line by line, and generate sql statement writing to a new file. In the end, I excecute the new sql script from the script itself

    INPUT=config.txt
    OLDIFS=$IFS
    IFS=','
    [ ! -f $INPUT ] && { echo "$INPUT file not found"; exit 99; }
    
    
    while read sequencename start_value increment_by maxvalue
    do
            echo "SequenceName : $sequencename"
            echo "start value: $start_value"
            echo "increment by : $increment_by"
            echo "max_value : $maxvalue"
    echo "CREATE SEQUENCE $sequencename  AS INT START WITH $start_value INCREMENT BY $increment_by MAXVALUE $maxvalue;" >> createsequence.sql
    #done < $INPUT
    done < <(tail -n +2 $INPUT) #This skips reading the header
    IFS=$OLDIFS
    

    The config.txt contains the below lines,

    sequencename,start_value,increment_by,maxvalue
    tbl1_tbl1id_seq , 1,2 ,50000;
    tbl2_tbl2id_seq , 1,2, 50000;
    tbl3_tbl3id_seq , 1, 2,50000;