I hope someone can help.
I have never used (custom) SQL searches, so I wonder if anyone can give a simple example that I might use as a basis, or point me to a tutorial web site. I have tried www.w3schools.com/sql/, but can't relate the examples given to Log4OM.
Sorry for my lack of knowledge.
Thanks again
73 Martin, G4DUL
Help/Advice on using Custom searches
- G4POP
- Log4OM Alpha Team
- Posts: 10815
- Joined: 21 Jan 2013, 14:55
- Location: Burnham on Crouch, Essex UK
Re: Help/Advice on using Custom searches
Martin,G4DUL wrote:I hope someone can help.
I have never used (custom) SQL searches, so I wonder if anyone can give a simple example that I might use as a basis, or point me to a tutorial web site. I have tried http://www.w3schools.com/sql/, but can't relate the examples given to Log4OM.
Sorry for my lack of knowledge.
Thanks again
73 Martin, G4DUL
If you want to use the "Custom update query" in the "Field update" area of the QSO Archive manager I must warn you that unless you are very familiar with that sort of query it can be very dangerous and has the potential to destroy your logbook!
However I will assume that you want to do a custom query in the "Search Parameters" of Log4OM? and this is quiet harmless as far as your data is concerned so here is a how to for you to experiment with.
EG: I have several QSO's where 'Claude' is part of the name - Like John Claude Pasquale but I also have other QSO's where Claude is the only name - I need to display all QSO's where the name Claude appears somewhere in the 'Name' field.
Type:
Name='Claude' and you will only get those QSO's where Claude is the only name in the field
Equals (=) works for EQUALS (Exact match) only
Type:
Name LIKE '%Claude%' OR Name like '%claude%' OR Name like '%CLAUDE%' - results in all QSO's where 'Claude' is somewhere in the Name text
LIKE is "similar to"
Where % means everything so %Claude matches everything that ends with Claude
Claude% matches everything that starts with Claude
%Claude% matches everything that contains (or start/end) with Claude
73 Terry G4POP
Re: Help/Advice on using Custom searches
Thank you Terry, that gives me a basis to start from.
73 Martin, G4DUL
73 Martin, G4DUL
Re: Help/Advice on using Custom searches
All,
I'm also trying to make use of custom MySQL queries, but I'm a bit stuck.
Here's an Example of what I want to do: I want to run a query to find my status for the 2m VUCC Award, it's about working distinct locator Grids (Squares).
I have built a simple MySQL query, running it directly on the Log4OM MySQL database from the MySQL Workbench works fine;
Query:
SELECT
COUNT(DISTINCT LEFT(GridSquare,4)) AS "Squares"
FROM
log
WHERE
Band = '2m'
Result:
Squares
--------
237
--------------------------
Can I integrate this search somehow in Log4OM ? Ideally this should be a new Award definition.
73 de Marco HB9CAT
I'm also trying to make use of custom MySQL queries, but I'm a bit stuck.
Here's an Example of what I want to do: I want to run a query to find my status for the 2m VUCC Award, it's about working distinct locator Grids (Squares).
I have built a simple MySQL query, running it directly on the Log4OM MySQL database from the MySQL Workbench works fine;
Query:
SELECT
COUNT(DISTINCT LEFT(GridSquare,4)) AS "Squares"
FROM
log
WHERE
Band = '2m'
Result:
Squares
--------
237
--------------------------
Can I integrate this search somehow in Log4OM ? Ideally this should be a new Award definition.
73 de Marco HB9CAT
- G4POP
- Log4OM Alpha Team
- Posts: 10815
- Joined: 21 Jan 2013, 14:55
- Location: Burnham on Crouch, Essex UK
Re: Help/Advice on using Custom searches
To add that as an award you need a CSV file of grid squares related to DXCC countries and references
73 Terry G4POP
Re: Help/Advice on using Custom searches
Ok then forget the award, can I just place that as log query ?
HB9CAT
HB9CAT
- G4POP
- Log4OM Alpha Team
- Posts: 10815
- Joined: 21 Jan 2013, 14:55
- Location: Burnham on Crouch, Essex UK
Re: Help/Advice on using Custom searches
hb9cat wrote:Ok then forget the award, can I just place that as log query ?
HB9CAT
Yes save it as a search parameter for future use
73 Terry G4POP
Re: Help/Advice on using Custom searches
As the title above the data entry line says, it accepts SQL update queries only. I have tried things like
Select Count(*) From Log
but the result always shows a figure 0. Update queries however are working ok. Let's assume I did a SOTA activation today and forgot to enter "My Sota Ref":
Update Log Set MySotaRef = 'HB/SH-002' Where QsoDate = 20150507
The same result can be achieved more easily using Log4OM's field update feature.
The primary index of the Log table is called QsoId and contains a time stamp showing when the record (QSO) was added to the database. As soon as you have to access this primary key you need an external tool such as the free Database Browser from http://www.etl-tools.com.
If you stay with the default SQLite database, there is another very nice freeware tool for browsing the database or running queries: DB Browser for SQLite from http://sqlitebrowser.org/.
73, Markus HB9BRJ
Select Count(*) From Log
but the result always shows a figure 0. Update queries however are working ok. Let's assume I did a SOTA activation today and forgot to enter "My Sota Ref":
Update Log Set MySotaRef = 'HB/SH-002' Where QsoDate = 20150507
The same result can be achieved more easily using Log4OM's field update feature.
The primary index of the Log table is called QsoId and contains a time stamp showing when the record (QSO) was added to the database. As soon as you have to access this primary key you need an external tool such as the free Database Browser from http://www.etl-tools.com.
If you stay with the default SQLite database, there is another very nice freeware tool for browsing the database or running queries: DB Browser for SQLite from http://sqlitebrowser.org/.
73, Markus HB9BRJ
- IW3HMH
- Site Admin
- Posts: 2927
- Joined: 21 Jan 2013, 14:20
- Location: Quarto d'Altino - Venezia (ITA)
- Contact:
Re: Help/Advice on using Custom searches
When you set the query parameters, available in every grid, you must simply switch the radio button to custom query.
Here you can type, as example:
DXCC <> '291' AND BAND = '20m'
to see QSO not made with USA in 20m band
So:
1) select CUSTOM QUERY in the SEARCH PARAMETERS
2) type your WHERE (without WHERE statement)
3) press the + button in the top right part of the search screen
4) apply with V button
Here you can type, as example:
DXCC <> '291' AND BAND = '20m'
to see QSO not made with USA in 20m band
So:
1) select CUSTOM QUERY in the SEARCH PARAMETERS
2) type your WHERE (without WHERE statement)
3) press the + button in the top right part of the search screen
4) apply with V button
Daniele Pistollato - IW3HMH
Re: Help/Advice on using Custom searches
Why the custom search show me all countrys not only Japan as requested.
My query: Country = 'Japan' AND Mode = 'JT9' OR Mode = 'JT65'
What is wrong with my search query ?
As soon as I combine two modes and the country, search result show me all country from the log not only Japan ?
If I delete one mode I get only Japan and the one requested mode !
Could you give me please a few example of query`s, I could modify my self, mainly I have problems to combine more than two parameter in the query !
Thanks for your Support !
73 Hans DF2MC
My query: Country = 'Japan' AND Mode = 'JT9' OR Mode = 'JT65'
What is wrong with my search query ?
As soon as I combine two modes and the country, search result show me all country from the log not only Japan ?
If I delete one mode I get only Japan and the one requested mode !
Could you give me please a few example of query`s, I could modify my self, mainly I have problems to combine more than two parameter in the query !
Thanks for your Support !
73 Hans DF2MC