Connecting to MS SQL Server DB

There are different ways to connect to Microsoft SQL server and they all have their own advantages.

For administrative purposes the SSMS (SQL Server Management Studio) is one of the most common ways. It offers GUI with lots of functionalities directly exposed to the GUI. Great for advanced tasks and also for inexperienced users with the autocompletes, error highlitning and the intuitive use. The disadvantages are- requires install and works on MS Windows.

Another tool is the Azure Data Studio, which runs on Windows and Linux and also on x86/64 and ARM processors. It is lightweight and not all SSMS functionalities are exposed. It offers some GUI advantages, but most of the tasks have to berformed via the query screens.

There are drivers that allow PHP to communicate with all kinds of DBs, but this approach is for web pages and not for users.

In CMD the sqlcmd.exe can be used (download and instructions link) but this might still require approval.

If there are installed drivers, then some script can be used to connect to SQL DB. I have the MS drivers, but I do not know if they are build in or additionally instaled. Whatever… they are there and I will use them.

When speaking for scripting in the Microsoft world, the PowerShell scripting is usually one of the first choices. Following script can be used to send the sylables to the server’s database. The PowerShell SQL module is also an option, but I don’t have it installed, yet.

function Invoke-SQL {
    param(
        [Parameter(Mandatory=$true)] [String] $connectionString ,
        [Parameter(Mandatory=$true)] [String] $query ,
        [Parameter(Mandatory=$true)] [String] $database        
      )

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($query,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet)  | Out-Null
    $dataset.Tables
    $connection.Close()
}


foreach ($sylable in $sylablesTextFile) {
#write-host $sylable

    $query = "INSERT INTO [sylables] ([syl_text]) VALUES (N'"+($sylable)+"')"
    $return = Invoke-SQL -query $query -database $DB -connectionString $connectionString

}

As the conversion is triggered from external Windows Task Sheduler invoked PowerShell script, the request rate has to be throttled.

The above script will later be changed to open connection, insert the data and afterwards close the connection, but as it is local connection and performance optimization will be performed when the proof of concept is ready, it stays like this for a while.

The script gets the sylables from the DB, pushes them back to the server, triggers the conversion and than waits for set time (until the conversion processing should be completed).

$query = 'select  [syl_text] from [dictate_test].[dbo].[sylables] order by [syl_text] asc'
$return = Invoke-SQL -query $query -database $DB -connectionString $connectionString

foreach ($sylable in $return) {
#Write-Host $sylable.syl_text
$reqWord = $sylable.syl_text -replace '\s',''
$connuri = "$somePath/texttofile.php?req=$reqWord&rate=0&voice=Vocalizer%20Expressive%20Daria%20Harpo%2022kHz"
try {$response = Invoke-WebRequest -Uri $connuri -Headers $hDict -Method GET}
    catch {$webRequestError = $_.Exception}

$connuri = "$someOtherPath/convert.php"
try {$response = Invoke-WebRequest -Uri $connuri -Headers $hDict -Method GET}
    catch {$webRequestError = $_.Exception}
Start-Sleep -Seconds 3
Copy-Item "$someOtherPath2\audiooutput.wav" -Destination "$someOtherPath3\$reqWord.wav"
}

The copy-rename part will be moved to the PHP script at a later stage. It is still under developement 🙂

Despite beeing nonsense to script something locally, then to push it via web server to local web server, that starts PHP script, this approach is chosen, because it is planned everything to be started from internet, when ready (scripts, DBs, audio files, etc.)

Daria voice by Harpo is used for the developement, because it is the best Bulgarian voice that I have found.

p.s. It’s my page and I write whatever I want. And also change it whenever I want (and have time of course).

p.p.s. Parts of the scripts are taken from StackOverflow, but I do not have the exact paths to quote the original authors.