SQL for QSL status and to update QSL status from third party tool

[ac9hp]

After doing some research I found the QSL data in the qslconfirmations field. The Data is stored as JSON and I can pull it out.

I updated the MariaDB to v10.6 so I could JSON_TABLE feature in sql.

Code: Select all

select qsoid, callsign, qsodate, email, band, mode, rstsent,name, j.* 
from log,JSON_TABLE(log.qsoconfirmations,'$[*]'
COLUMNS (
	ct VARCHAR(10) PATH '$.CT', S VARCHAR(10) PATH '$.S',
    R VARCHAR(10) PATH '$.R', 
      SV VARCHAR(100) PATH '$.SV',
      RV VARCHAR(100) PATH '$.RV',
      SD VARCHAR(100) PATH '$.SD',
      RD VARCHAR(100) PATH '$.RD' ) ) as j where j.ct = 'QSL' and callsign = 'N9DBJ' and j.S <> '';
 

I am lazy and I do not want to pull the field out and parse it into JSON just so I can update the values. Is there a way to update the JSON in the field with a raw SQL statement? I tried this and it does not work:

Code: Select all

update log,JSON_TABLE(log.qsoconfirmations,'$[*]'
COLUMNS (
	ct VARCHAR(10) PATH '$.CT', S VARCHAR(10) PATH '$.S',
    R VARCHAR(10) PATH '$.R', 
      SV VARCHAR(100) PATH '$.SV',
      RV VARCHAR(100) PATH '$.RV',
      SD VARCHAR(100) PATH '$.SD',
      RD VARCHAR(100) PATH '$.RD' ) ) as j set j.S = 'Yes' where qsoid = '20210208015823435' and j.S <> 'Yes' and j.ct = 'QSL'; 

If anyone has any tips or tricks, I am open to learn.

73 for now de AC9HP

[ac9hp]

I got impatient and created a routine to read out the structure, update it and save it back.


Data Structure to hold the qsoconfirmations :

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Log4OMQSLEmailer
{
    public class QSLConfirmation
    {

        public string CT { get; set; }
        public string S { get; set; }
        public string R { get; set; }
        public string SV { get; set; }

        public string RV { get; set; }
        public string SD { get; set; }
        public string RD { get; set; }

    }
}

Code to update the structure:

Code: Select all

public int LookupQSLConformation(string qsoid)
        {

            MySqlConnector.MySqlConnectionStringBuilder b = new MySqlConnector.MySqlConnectionStringBuilder
            {
                Server = Properties.Settings.Default.DBHost,
                UserID = Properties.Settings.Default.DBUser,
                Password = Properties.Settings.Default.DBPassword,
                Database = Properties.Settings.Default.DBDatabase

            };
            MySqlConnector.MySqlConnection sqlcon = new MySqlConnector.MySqlConnection(b.ConnectionString);

            sqlcon.Open();
            string mysql = "select qsoconfirmations from log where qsoid = ?qsoid";
           
            MySqlConnector.MySqlCommand com = new MySqlConnector.MySqlCommand();
            com.Connection = sqlcon;
            com.CommandText = mysql;
            com.Parameters.Add("?qsoid", MySqlConnector.MySqlDbType.VarChar).Value = qsoid;
            
            MySqlConnector.MySqlDataReader reader = com.ExecuteReader();

            if (reader.Read())
            {
                string ret = reader["qsoconfirmations"].ToString();

                QSLConfirmation[] qsl = JsonConvert.DeserializeObject<QSLConfirmation[]>(ret);
                reader.Close();
                for (int idx = 0; idx<qsl.Count();idx++)
                { 

                    if (qsl[idx].ct.Equals("QSL"))
                    {
                        qsl[idx].S = "Yes";
                        qsl[idx].SV = "Electronic";
                        qsl[idx].SD = DateTime.Now.ToString("MM-dd-yyyy hh:mm:ss tt");
                        break;
                    }
                }

                //update code here 
                string qstr = JsonConvert.SerializeObject(qsl);

                mysql = "update log set qsoconfirmations = '" + qstr + "' where qsoid = ?qsoid2" ;
                com.Connection = sqlcon;
                
                com.CommandText = mysql;
                com.Parameters.Add("?qsoid2", MySqlConnector.MySqlDbType.VarChar).Value = qsoid;
                int rc = com.ExecuteNonQuery();

                sqlcon.Close();

                return rc;
            }
            else
            {

                return 0;
            }
        }

I worry that the date format may turn into a problem but so far it has not impacted log4om2.

[ac9hp]

I made two version of a QSL emailer - one that remotes Corel Draw and one that uses a PNG.

This is the PNG version - create your PNG with your favorite tool and this can overlay your QSO data, email it and update your QSL status in Log4OM2. Uses a mysql database for the log.

I would call this an "Alpha version". if you decide to try it out, let me know how it goes and what you think.

https://github.com/wrmichael/Log4OMQSLEmailerPNG