dxcc_ref

17 posts / 0 new
Last post
km4hqe
dxcc_ref

I tried a filter to see what I could come up with on the DXCC entities I've worked and sent a QSL to but haven't received one back. With the goal being to re-work the ones I need in order to get closer to making Challenge.

What column/field in cqrlog_main is dxcc_ref? I tried this from the SQL console and it fails since there is no column/field named dxcc_ref

select qsodate,time_on,callsign,band, dxcc_ref, lotw_qsls, lotw_qslr from cqrlog_main where lotw_qslr=""

I'm sure there is a way to get more specific with a where statement and eliminate all of the US QSOs, but I need to learn SQL a little better first. An alternative would be to import it into a spreadsheet and remove all of the US QSOs that way.

73

Stan
KM4HQE

oh1kh
dxcc_ref

Hi!
Could it be "adif" you are looking for?

select adif,callsign,qsl_r,qsl_s from cqrlog_main where adif <> 0 and qsl_r='' order by adif;

--
Saku
OH1KH

km4hqe
dxcc_ref

Hi Saku,

No, not exactly. Is there a field for prefix and country or does the ADIF code get translated to a country name? Just wondering how or what the columns "dxcc_ref" and "country" are on the QSO list window. Those two columns along with the date, callsign, band, qsl_s, and qsl_r are what I want to make a filter for.

Thanks

73

Stan
KM4HQE

ok7an
ok7an's picture
Re: dxcc_ref

Hi Stan,

dxcc_ref is in views defined in cqrlog datase.
The SQL query should be something like:

select qsodate, callsign, band, qsl_s, qsl_r, dxcc_id.country from cqrlog_main
left join dxcc_id on cqrlog_main.adif = dxcc_id.adif

The main view is view_cqrlog_main_by_qsodate and there is also country name:

select * from view_cqrlog_main_by_qsodate

73 Petr

oh1kh
dxcc_ref

Hi!

There is another table telling what adif is. To get idea give:
select * from dxcc_id where adif=100;

You have to JOIN queries from cqrlog_main telling you the worked status and dxcc_id to get country names and prefixes for adif nubers you get from cqrlog_main query.

--
Saku
OH1KH

km4hqe
dxcc_ref

Hi Saku,

Oh, Ok. I thought the adif and prefix might be in the cqrlog_main table too. I'm just learning some SQL, so I'll take a look at the JOIN command and see what I can come up with.

Thanks!

73

Stan
KM4HQE

oh1kh
dxcc_ref

HI!

To make your SQL learning easier do your logs to localhost:3306 mysql server. So you do not need to have cqrlog open to access the database
and you can do mysql commands from sql console window.

Uncheck "Save data to local machine" and set server port to 3306 and create new log. (you need to have mysql/maridb-server installled)
You actually still save data to local machine, but now you can access it without running cqlog first.


[saku@hamtpad ~]$ mysql -ucqrlog -pXXXXX cqrlog001
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.2.14-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [cqrlog001]> select * from dxcc_id where adif=100;
+---------+------+----------+-----------+
| id_dxcc | adif | dxcc_ref | country |
+---------+------+----------+-----------+
| 54565 | 100 | LU | Argentina |
+---------+------+----------+-----------+
1 row in set (0.00 sec)

MariaDB [cqrlog001]>

File: 

--
Saku
OH1KH

ok1rr
ok1rr's picture
Re: dxcc_ref

...does the ADIF code get translated to a country name?

The ADIF specification might be useful:
http://www.adif.org/308/ADIF_308.htm

The country files set contains the ADIF country numbers as well.

73,
Martin, OK1RR

km4hqe
Re: dxcc_ref

Hi Saku, Petr and Martin,

Thanks for your help.

I tried connecting to the database like Saku outlined, but was unable to login. When I try it from the cli, it gives me an access denied error. It tried what's in the attached screenshot, but the results weren't quite what I expected. In fact it displays the same thing without the join clause. I thought it would display the country, but no such luck. I'll keep trying.

73

Stan
KM4HQE

File: 

ok7an
ok7an's picture
Re: dxcc_ref

Hi Stan,

you forget to add dxcc_id.country field.

Did you read my post? I wrote that counrty name is also in the view_cqrlog_main_by_qsodate.

73 Petr

oh1kh
Re: dxcc_ref

HI!

I think you should be able to connect cqrlog database (without setting log to 3306 port of mysql server) by starting cqrlog, and when it is running
open console and give:
mysql -uXXXXXX -pYYYYYY -Pzzzzzz cqrlogNNN

where XXXX is the same user name, YYYYY is same password and zzzzz is same port as you have in "Database connection" window. (I think the defauts were X=cqrlog, Y=cqrlog and z=64000)
NNN is the log number you want to work with.

But it works only if cqrlog is open.

I prefer accessing mysql from linux command console. It is handy as you can scroll and edit command history and long outputs with arrow keys and execute them by just pressing return.
But you have to remember to add ; at the end of line, that is not needed in cqrlog's SQL console.

When logs are in mysql server (port 3306) you can access them when cqrlog is not running. But you need to create and grant user name for cqrlog before trying to create logs to server.
See : https://dev.mysql.com/doc/refman/8.0/en/adding-users.html

And when you have logs on the server side and the PC is connected to internet with public-IP you can access logs remote with SSH tunnel.

On remote machine console star SSH connect with:
ssh -L 13306:localhost:3306 your_username@your_server.IP

Then opening cqrlog on remote machine with port 13306 will connect you to logs at your mysql server. (use 13306 if remote PC has own mysql server running that reserves 3306 port)

--
Saku
OH1KH

ok7an
ok7an's picture
Re: dxcc_ref

If the log is running on local computer with "Save data to local machine" checked, you can use existing socked to connect to the database:

mysql -S ~/.config/cqrlog/database/sock

that's all, no username or password.

73 Petr

km4hqe
Re: dxcc_ref

Hi Petr,

I was able to connect to the database using the socket. I refined the SQL statement a little and may refine it some more. But, this is really close to what I want.

select qsodate, callsign, band, qsl_s, qsl_r, lotw_qsls, lotw_qslr, dxcc_id.country from cqrlog_main left join dxcc_id on cqrlog_main.adif = dxcc_id.adif where lotw_qslr='' AND dxcc_id.country != 'United States';

Thanks again for everyone's help!

73

Stan
KM4HQE

ok7an
ok7an's picture
Re: dxcc_ref

select qsodate, callsign, band, qsl_s, qsl_r, lotw_qsls, lotw_qslr, dxcc_id.country from cqrlog_main left join dxcc_id on cqrlog_main.adif = dxcc_id.adif where lotw_qslr='' AND dxcc_id.country <> 'United States';

As I noticed before, you don't need left join and you can use view_cqrlog_main_by_qsodate:

view_cqrlog_main_by_qsodate select qsodate, callsign, band, qsl_s, qsl_r, lotw_qsls, lotw_qslr, country where country <> 'United States';

or use dxcc_ref <> 'W'; There are many ways to do it.

km4hqe
Re: dxcc_ref

What is view_cqrlog_main_by_qsodate ? I thought it might be a stored procedure, but I get an error when I try "show procedure status" or "show function status"

What is the field qso_dxcc?

73

Stan
KM4HQE

km4hqe
Re: dxcc_ref

Hi Saku,

Yes, but I was unsure what you meant at first. I tried this and it works

select qsodate, callsign, band, qsl_s, qsl_r, lotw_qsls, lotw_qslr, dxcc_id.country from cqrlog_main left join dxcc_id on cqrlog_main.adif = dxcc_id.adif where lotw_qslr=''

So, it would appear the select command will filter on fields in either table when one does a join? Seems like I tried it without the join and I got an error since that field isn't in cqrlog_main.

Thanks again for all your help.

73

Stan
KM4QHE

km4hqe
Re: dxcc_ref

Ooops, I meant Petr...