0

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;

user2101
  • 145
  • 1
  • 11
  • Sorry, I need some more details - which DBMS? MS SQL? Oracle? MySQL? How do you call the sql script file on each server? – BitLauncher Feb 25 '21 at 22:03
  • Postgres.This script will create sequence, drop table if exists and create all the tables. This sql script will be run on each of the servers as sudo user. – user2101 Feb 25 '21 at 23:15
  • sudo user means, you work on linux. So I see different solution possibilities: a) if Postgres has similar features like Microsoft SQL you can code all the configuration values inside SQL and with IF etc. call the right SQL commands and/or set SQL variables to the right values, there are scripting variables as well b) before you call the command in a console to execute the sql, somehow create/modify it with other linux commands (e. g. sed) and a config file.Then it is not SQL problem c) a lot more possibilities... – BitLauncher Mar 04 '21 at 20:30
  • I could do that as below, posting my answer. – user2101 Mar 05 '21 at 22:09

1 Answers1

0

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;
user2101
  • 145
  • 1
  • 11