I have develped a Perl script that can format data into CSV format, and want to save the data into a Postgres database table.
I'm following this tutorial as a guideline for how to interface to Postgres. I verified in the PPM that I have the same versions installed as the tutorial: 1.634 version of DBI and the 3.5.3 version of DBD::Pg installed in Perl 5.22.1 in Windows 10 x64. The database is Postgres 12 on Windows Server 2008r2.
The first part of the Perl script reads, parses, and formats one data record as CSV. This is an example of one CSV data record:
"2020-05-10 20:39:16+0","0.528239011764526","15:39 05/10/2020","0x1c","LOW STATUS","0x85","Normal","73.8","32","29.11","29.31","61.2","29","80","0.7","2.5","22.6","378.64","3009","7","0.00","0.00","0.97","0.97","11.96"
This is stored in $SQLstring before entering the database interface snippet below: This is the code I have modified from the tutorial, which compiles and runs without errors.
# ------------- Postgres Database Connection --------------
# Connection config
my $dbname = 'MyDatabase';
my $host = '192.168.1.1';
my $port = 5432;
my $username = 'myuser';
my $password = 'mypassword';
# Create DB handle object by connecting
my $dbh = DBI -> connect("dbi:Pg:dbname=$dbname;host=$host;port=$port",
$username,
$password,
{AutoCommit => 0, RaiseError => 1}
) or die $DBI::errstr;
# Trace to a file
$dbh -> trace(1, 'tracelog.txt');
# Copy from STDIN into the table
my $SQL = "COPY AmbientWeather (
datetimestamp ,
CurrTime ,
IndoorID ,
inBattSta ,
Outdoor1ID ,
outBattSta1 ,
inTemp ,
inHumi ,
AbsPress ,
RelPress ,
outTemp ,
outHumi ,
windir ,
avgwind ,
gustspeed ,
dailygust ,
solarrad ,
uv ,
uvi ,
rainofhourly ,
rainofdaily ,
rainofweekly ,
rainofmonthly ,
rainofyearly
)
FROM STDIN WITH DELIMITER ',' CSV HEADER";
my $sth = $dbh->do($SQL);
# putcopy data from saved line in CSV format
$dbh->pg_putcopydata($SQLstring);
$dbh->pg_putcopyend(); # finished with one line
$dbh->commit or die $DBI::errstr;
exit;
This runs without errors, but the database is unchanged. No record is created.
This is the trace log, which does not show any notable errors, but I am not really familiar with the syntax:
DBI::db=HASH(0x3c62268) trace level set to 0x0/1 (DBI @ 0x0/0) in DBI 1.634-ithread (pid 19436)
<- DESTROY(DBI::db=HASH(0x3c62268))= ( undef ) [1 items] at Ambient_Parser.pl line 158
DBI::db=HASH(0x3c76ca8) trace level set to 0x0/1 (DBI @ 0x0/0) in DBI 1.634-ithread (pid 2108)
<- do('COPY AmbientWeather (
datetimestamp ,
CurrTime ,
IndoorID ,
inBattSta ,
Outdoor1ID ,
outBattSta1 ,
inTemp ,
inHumi ,
AbsPress ,
RelPress ,
outTemp ,
outHumi ,
windir ,
avgwind ,
gustspeed ,
dailygust ,
solarrad ,
uv ,
uvi ,
rainofhourly ,
rainofdaily ,
rainofweekly ,
rainofmonthly ,
rainofyearly
...')= ( -1 ) [1 items] at Ambient_Parser.pl line 177
<- pg_putcopydata('"2020-05-10 20:35:59+0","0.547099113464355","15:35 05/10/2020","0x1c","LOW STATUS","0x85","Normal","73.6","32","29.11","29.31","61.3","24","193","3.8","4.9","22.6","380.54","3082","7","0.00","0.00","0.97","0.97","11.96"
')= ( 1 ) [1 items] at Ambient_Parser.pl line 182
<- pg_putcopyend= ( 1 ) [1 items] at Ambient_Parser.pl line 183
<- commit= ( 1 ) [1 items] at Ambient_Parser.pl line 184
<- DESTROY(DBI::db=HASH(0x3c76ca8))= ( undef ) [1 items] at Ambient_Parser.pl line 193
For reference line 193 is the final exit; in the file.
I must be missing something, but I don't see what it is. Can you point out my error?
Edit: I compared the options in the tutorial's command in my $SQL = "COPY.... with Postgres COPY command documentation. The tutorial adds options CSV HEADER, which are not seen in Postres docs. I'm not sure why those options are used in the tutorial, or why they cause a silent failure. I removed them and now I am getting errors.
Code from above now looks like this:
rainofyearly
)
FROM STDIN WITH DELIMITER ','";
These errors are now being output:
DBD::Pg::db pg_putcopyend failed: ERROR: invalid input syntax for type real: ""0.520319223403931""
CONTEXT: COPY ambientweather, line 1, column fetchelapsed: ""0.520319223403931"" at Ambient_Parser.pl line 186.
DBD::Pg::db pg_putcopyend failed: ERROR: invalid input syntax for type real: ""0.520319223403931""
CONTEXT: COPY ambientweather, line 1, column fetchelapsed: ""0.520319223403931"" at Ambient_Parser.pl line 186.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Pg::db handle dbname=MyDatabase;host=192.168.1.1;port=5432 at Ambient_Parser.pl line 186.
I'm investigating why the real value is being seen as double quoted. This is the same CSV format I have used from the PSQL command line with COPY FROM , and reals were accepted as shown above.
You told it the first line would be an ignored header. But you only sent it one line. So there were no data lines sent.
CSV and HEADER are separate options. These are both present (separately) in the docs you link to. You need to keep CSV, otherwise the quoting is not understood.