[Dxspider-support] Assistance with mariadb
Michael Serowik
maserowik at verizon.net
Fri Oct 11 23:59:26 BST 2024
So I have added the following to my DXVars.pm
# the SQL database DBI dsn
$dsn = "DBI:mysql:database=dxcluster;host=localhost;port=3306";
$dbuser = "sysopAdmin";
$dbpass = "503Di at bl0";
I have the database set up
MariaDB [dxcluster]> describe spot;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| rowid | int(11) | NO | PRI | NULL | auto_increment |
| freq | double | NO | | NULL | |
| spotcall | varchar(14) | NO | MUL | NULL | |
| time | int(11) | NO | MUL | NULL | |
| comment | varchar(255) | YES | | NULL | |
| spotter | varchar(14) | NO | MUL | NULL | |
| spotdxcc | smallint(6) | YES | | NULL | |
| spotterdxcc | smallint(6) | YES | | NULL | |
| origin | varchar(14) | YES | | NULL | |
| spotitu | tinyint(4) | YES | | NULL | |
| spotcq | tinyint(4) | YES | | NULL | |
| spotteritu | tinyint(4) | YES | | NULL | |
| spottercq | tinyint(4) | YES | | NULL | |
| spotstate | char(2) | YES | | NULL | |
| spotterstate | char(2) | YES | | NULL | |
| ipaddr | varchar(40) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
16 rows in set (0.034 sec)
I still cannot get dxspider to send data but I created a perl script and
that works.
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
# Database connection details
my $dsn = "DBI:mysql:database=dxcluster;host=localhost;port=3306";
my $dbuser = 'sysopAdmin';
my $dbpass = '503Di at bl0';
# Connect to the database
my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { RaiseError => 1, AutoCommit
=> 1 })
or die "Could not connect to database: $DBI::errstr";
# Prepare an SQL insert statement with two placeholders
my $sth = $dbh->prepare("INSERT INTO spot (comment, ipaddr, freq, spotcall,
time, spotter) VALUES (?, ?, ?, ?, ?, ?)");
# Execute the statement with two values
$sth->execute('John', 'Doe', 100, 101, 102, 103) or die "SQL Error:
$DBI::errstr";
# Insert multiple rows with two values
my @data = (
['Jane', 'Smith', 200, 201, 202, 203],
['Alice', 'Wonderland', 300, 301, 302, 302]
);
foreach my $row (@data) {
$sth->execute($row->[0], $row->[1], $row->[2], $row->[3], $row->[4],
$row->[5]) or die "SQL Error: $DBI::errstr";
}
# Finish the statement
$sth->finish();
# Disconnect from the database
$dbh->disconnect();
print "Data inserted successfully!\n";
MariaDB [dxcluster]> select * from spot;
+---------+------+----------+------+---------+---------+----------+---------
----+--------+---------+--------+------------+-----------+-----------+------
--------+------------+
| rowid | freq | spotcall | time | comment | spotter | spotdxcc |
spotterdxcc | origin | spotitu | spotcq | spotteritu | spottercq | spotstate
| spotterstate | ipaddr |
+---------+------+----------+------+---------+---------+----------+---------
----+--------+---------+--------+------------+-----------+-----------+------
--------+------------+
| 2598318 | 100 | 101 | 102 | John | 103 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Doe |
| 2598319 | 100 | 101 | 102 | John | 103 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Doe |
| 2598320 | 200 | 201 | 202 | Jane | 203 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Smith |
| 2598321 | 300 | 301 | 302 | Alice | 302 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Wonderland |
| 2598322 | 100 | 101 | 102 | John | 103 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Doe |
| 2598323 | 200 | 201 | 202 | Jane | 203 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Smith |
| 2598324 | 300 | 301 | 302 | Alice | 302 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Wonderland |
| 2598325 | 100 | 101 | 102 | John | 103 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Doe |
| 2598326 | 200 | 201 | 202 | Jane | 203 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Smith |
| 2598327 | 300 | 301 | 302 | Alice | 302 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Wonderland |
| 2598328 | 100 | 101 | 102 | John | 103 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Doe |
| 2598329 | 200 | 201 | 202 | Jane | 203 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Smith |
| 2598330 | 300 | 301 | 302 | Alice | 302 | NULL |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Wonderland |
+---------+------+----------+------+---------+---------+----------+---------
----+--------+---------+--------+------------+-----------+-----------+------
--------+------------+
13 rows in set (0.003 sec)
Anyone point me to where I may have screwed up?
Mike -N3bsq
--
This email has been checked for viruses by Avast antivirus software.
www.avast.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.tobit.co.uk/pipermail/dxspider-support/attachments/20241011/822fc7f8/attachment-0003.htm>
More information about the Dxspider-support
mailing list