[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