PowerShell script

This script can be used to automatically import sql files older than 20 minutes into the MySQL database. It is helpful if you enable SQL Text logging for high performance configurations. In order to execute this script, you have to save it with the .ps1 extension, and you must enable power shell script execution for windows in the group policy editor (or you must execute the script as Administrator).

Mastering PowerShell script automation (video tutorial)

The procedure of setting up an automatic MySQL batch job utilizing a PowerShell script to import SQL files into your database using Ozeki SMS Gateway is demonstrated in this video guide. You can easily manage your message records and keep a clean database for your SMS communications with the help of this solution. At the end of this video, you'll clearly understand how to use the included PowerShell script to automate the process of importing SQL files into your MySQL database, keeping an organized and effective SMS communication archive.

Create the following MySQL table

CREATE TABLE messagereport
(
    logid int not null auto_increment primary key,
    messageid varchar(64),
    fromconnection varchar(64),
    fromaddress varchar(64),
    requestedconnection varchar(64),
    toconnection varchar(64),
    toaddress varchar(64),
    messagetext varchar(1024),
    route varchar(64),
    datecreated datetime,
    datedeliverytoconnectionsuccess datetime,
    datedeliverytoconnectionfailed datetime,
    deliverytoconnectionreference varchar(256),
    deliverytoconnectionstatusmessage varchar(1024),
    datedeliverytorecipientsuccess datetime,
    datedeliverytorecipientfailed datetime,
    deliverytorecipientstatusmessage varchar(1024),
    status varchar(64),
    INDEX(messageid)
) charset = utf8;

The first step is to create the messagereport table in the database. All outgoing and incoming messages will be stored in this table and updated according to their status. If you have already created this table, you can skip this step. Copy the create table script above and execute it in your database as you can see it on the Figure 1.

Save the following powershell script as "dbimport.ps1"

The next step is to create the "dbimport.ps1" file in the C:\Ozeki folder as shown in Figure 2. This file will contain the script that periodically runs SQL queries in the database.

$ProcessedDir = "C:\Ozeki\Processed"
New-Item -ItemType Directory -Force -Path $ProcessedDir

#endless loop
while (1) {
	#list files older than 20 minutes
	#make sure the time is greater than the period you used in the reporting config
	Get-ChildItem "C:\Ozeki\Reporting_SQL" -Filter *.sql | 
		Where{$_.LastWriteTime -le (Get-Date).AddMinutes(-20)} |
	
	Foreach-Object {
		#load the contents of the file into the mysql database
		Write-Output $_.FullName
		$Text = Get-Content -Path $_.FullName
		Write-Output $Text | C:\'Program Files'\MySQL\'MySQL Server 5.7'\bin\mysql -uroot 
		-pmypass mydatabase
		
		#move the processed file into the processed directory
		Move-Item -Path $_.FullName -Destination $ProcessedDir
	}
	
	#sleep for 5 seconds
	Start-Sleep -s 5
}

Copy the power shell script above and paste it in the "dbimport.ps1" file as you can see it on the Figure 3.

Finally, modify the script according to your own MySQL database. In the path, change the version number of the MySQL database. Enter the username and password you want to use, and replace "mydatabase" with the name of your database (Figure 4).

Execute the script as administrator

powershell -ExecutionPolicy Bypass -File dbimport.ps1

Now execute the script in the power shell by running the above command as you can see it in the Figure 5.

You can see that the script executes all the SQL files what is older than 20 minutes in the 'C:\Ozeki\Reporting_SQL' folder one by one (Figure 6).

Every SQL file that the script has run is moved to the Processed folder so that no queries are lost (Figure 7). From here, you can delete them manually if you no longer need them.

Finally, if you look in the database you can see that all the messages have been inserted in the messsagereport table as the Figure 8 shows.