How can I change the connection string in my configuration file (config.yml) using a batch file?

Question

The connection string is as follows in the file config.yml:

"DRIVER={SQL Server};SERVER=my_server;DATABASE=myDBName;UID=username;PWD=password"

There are also a bunch of other lines.

The connection string with the database login credentials which are defined in the batch file should be written by the batch file into the file config.yaml:

"DRIVER={SQL Server};SERVER=%server_name%;DATABASE=%DBname%;UID=%user%;PWD=%password%"

The rest of the file config.yml file should remain unmodified.

How can this be achieved?

Please edit *relevant section(s)* of what you have tried into your question along with appropriate representative data (use cut/paste) & say what your actual and expected results are. Is it important that the edit applied to your file maintains the new data in the same position, or can it be moved to the beginning or end of the file? — Magoo, Feb 02 '21 at 05:27

Mofi · Answer 1 · 2023-08-12T18:25:15.957

The Windows Command Processor cmd.exe interpreting a batch file is the worst choice of all script interpreters installed by default on Windows to modify data in a text file. It is designed for executing commands and executables. It is not designed for text file editing purposes.

However, this batch file code makes the job without using another script interpreter or executable not installed by default on Windows.

@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "SourceName=config.yaml"
set "SourceFile=%~dp0%SourceName%"
if not exist "%SourceFile%" (
    echo ERROR: File "%SourceFile%" not found.
    goto EndBatch
)

rem These environment variables are defined for completness.
set "server_name=MyServerName"
set "DBname=MyDatabaseName"
set "user=MyUserName"
set "password=MyPassword"

set "TempFile=%SourceFile%.tmp"
set "LineUpdated="

(for /F delims^=^ eol^= %%I in ('%SystemRoot%\System32\findstr.exe /N "^" "%SourceFile%" 2^>nul') do (
    set "Line=%%I"
    setlocal EnableDelayedExpansion
    if defined LineUpdated (
        echo(!Line:*:=!
        endlocal
    ) else (
        if "!Line:;SERVER=!" == "!Line!" (
            echo(!Line:*:=!
            endlocal
        ) else (
            if not "!Line:~1,-1!" == "DRIVER={SQL Server};SERVER=!server_name!;DATABASE=!DBname!;UID=!user!;PWD=!password!" (
                echo "DRIVER={SQL Server};SERVER=!server_name!;DATABASE=!DBname!;UID=!user!;PWD=!password!"
                endlocal
                set "LineUpdated=1"
            ) else (
                endlocal
                set "LineUpdated=2"
                goto ProcessResult
            )
        )
    )
))>"%TempFile%"

:ProcessResult
if not exist "%TempFile%" (
    echo ERROR: Failed to create a temporary file in the folder:
    echo        "%~dp0"
    goto EndBatch
)
if not defined LineUpdated (
    echo ERROR: Failed to find the line to update in the file:
    echo        "%SourceFile%"
    del "%TempFile%" 2>nul
    goto EndBatch
)
if %LineUpdated% == 2 (
    rem The source file contains already exactly the specified data.
    del "%TempFile%" 2>nul
    goto EndBatch
)
del /A /F "%SourceFile%" 2>nul
ren "%TempFile%" "%SourceName%" 2>nul
if exist "%TempFile%" (
    echo ERROR: Failed to overwrite the configuration file:
    echo        "%SourceFile%"
    del "%TempFile%" 2>nul
    goto EndBatch
)

:EndBatch
endlocal

This batch file is written for exactly the line posted in the question. The line to update must start with " and must end with ". The line to update is identified by containing case-insensitive the string ;SERVER. So this batch file does not work as designed if any other line contains also ;SERVER in any case or the line to update contains more data.

Please read my answer on How to read and print contents of text file line by line? for the main issues to solve on processing a text file with only internal commands of cmd.exe and external Windows Commands in Windows system directory.

There is also How can you find and replace text in a file using the Windows command-line environment? with lots of alternatives to do a search and replace in a text file. My personal favorite tool is JREPL.BAT. But this tool is not used here as who knows which string is entered as password. It could contain for example $1 which would be interpreted as regular expression and so the password string would not be written into the file as defined by the user of the batch file.

There is usually used the following command line to replace the source file by the temporary file:

move /Y "%TempFile%" "%SourceFile%" >nul 2>nul

But there is a problem with this line if the source file has the read-only attribute set. In this case the command MOVE prompts the user despite using option /Y for confirmation on overwriting the read-only file. This prompt is not visible because of >nul. It would be possible to remove >nul to see the prompt, but than a successful file movement results in output of the text 1 file(s) moved. which does not look good. For that reasons DEL is used to delete the source file even on read-only attribute set and REN to rename the temporary file to name of source file if that is not prevented by NTFS permissions or a file sharing access violation.

To understand the commands used and how they work, open a command prompt window, execute there the following commands, and read the displayed help pages for each command, entirely and carefully.

call /? ... explains %~dp0 ... full batch file path ending always with a backslash.
del /?
echo /?
endlocal /?
findstr /?
for /?
goto /?
if /?
rem /?
ren /?
set /?
setlocal /?

How can I change the connection string in my configuration file (config.yml) using a batch file?

1 Answers1