Hello,
i would like to know if it is possible to use magic parser with Postgresql database ?
i already use magic parser for import csv and xml to mysql database
regards,
Marcel
David,
this is part of the code:
mysql_connect("host","user","password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
$counter = 0;
unset($record);
unset($sDate);
function myRecordHandler($record)
{
$sDate = date("Y-m-d H:i:s");
global $counter;
$counter++;
$sql = "INSERT INTO mz_weerstations (stationcode, stationnaam, datum, luchtvochtigheid, temperatuurgc, windsnelheidms, windsnelheidbf, windrichtinggr, windrichting, luchtdruk, zichtmeters, windstotenms, regenmmpu, datumtijd)
VALUES (
'".mysql_real_escape_string($record["STATIONCODE"])."',
'".mysql_real_escape_string($record["STATIONNAAM"])."',
'".mysql_real_escape_string($record["DATUM"])."',
'".mysql_real_escape_string($record["LUCHTVOCHTIGHEID"])."',
'".mysql_real_escape_string($record["TEMPERATUURGC"])."',
'".mysql_real_escape_string($record["WINDSNELHEIDMS"])."',
'".mysql_real_escape_string($record["WINDSNELHEIDBF"])."',
'".mysql_real_escape_string($record["WINDRICHTINGGR"])."',
'".mysql_real_escape_string($record["WINDRICHTING"])."',
'".mysql_real_escape_string($record["LUCHTDRUK"])."',
'".mysql_real_escape_string($record["ZICHTMETERS"])."',
'".mysql_real_escape_string($record["WINDSTOTENMS"])."',
'".mysql_real_escape_string($record["REGENMMPU"])."',
'".mysql_real_escape_string($sDate)."')";
mysql_query($sql);
print "<p>Executing: [".$sql."]</p>";i think i should translate mysql to postgresql but not sure about the syntax and special the mysql_real_escape_string part of it
regards,
Marcel
Hello Marcel,
The equivalents in this case are
mysql_connect -> pg_connect
mysql_real_escape_string > pg_escape_string
mysql_query -> pg_query
pg_connect() differs somewhat from how mysql_connect works, based on a connection string (which includes database selection) rather than individual values - have a go with something like:
$connection = pg_connect("host=host user=user password=password dbname=dbname");
$counter = 0;
unset($record);
unset($sDate);
function myRecordHandler($record)
{
$sDate = date("Y-m-d H:i:s");
global $counter;
$counter++;
$sql = "INSERT INTO mz_weerstations (stationcode, stationnaam, datum, luchtvochtigheid, temperatuurgc, windsnelheidms, windsnelheidbf, windrichtinggr, windrichting, luchtdruk, zichtmeters, windstotenms, regenmmpu, datumtijd)
VALUES (
'".pg_escape_string($record["STATIONCODE"])."',
'".pg_escape_string($record["STATIONNAAM"])."',
'".pg_escape_string($record["DATUM"])."',
'".pg_escape_string($record["LUCHTVOCHTIGHEID"])."',
'".pg_escape_string($record["TEMPERATUURGC"])."',
'".pg_escape_string($record["WINDSNELHEIDMS"])."',
'".pg_escape_string($record["WINDSNELHEIDBF"])."',
'".pg_escape_string($record["WINDRICHTINGGR"])."',
'".pg_escape_string($record["WINDRICHTING"])."',
'".pg_escape_string($record["LUCHTDRUK"])."',
'".pg_escape_string($record["ZICHTMETERS"])."',
'".pg_escape_string($record["WINDSTOTENMS"])."',
'".pg_escape_string($record["REGENMMPU"])."',
'".pg_escape_string($sDate)."')";
global $connection;
pg_query($connection,$sql);
print "<p>Executing: [".$sql."]</p>";Hope this helps!
Cheers,
David
--
MagicParser.com
David,
i had a go at it and almost works like a charm.
one issue i have is the error-handling on the xml file
sometimes a value is empty, then a minus sign (-) is placed.
this is no problem but the column in postgres doesn't accept this.
i could change the column to text or so, but i would prefer to solve this.
the error i get:
Warning: pg_query(): Query failed: ERROR: invalid input syntax for type numeric: "-" LINE 14: '-', ^ in D:\xampp\htdocs\pg\pg_buienradarxml.php on line 33
Executing: [INSERT INTO mz_weerstations (stationcode, stationnaam, lat, lon, datum, luchtvochtigheid, temperatuurgc, windsnelheidms, windsnelheidbf, windrichtinggr, windrichting, luchtdruk, zichtmeters, windstotenms, regenmmpu, datumtijd) VALUES ( '6391', 'Meetstation Arcen', '51.30', '6.12', '12/18/2014 13:50:00', '96', '11.9', '5.46', '3', '243.6', 'WZW', '-', '-', '9.0', '0.168', '2014-12-18 14:03:12')]
any idea on how to solve this in the code without changing the database fields?
regards,
Marcel
Hello Marcel,
You could convert any "-" value into "0" by adding the following code at the top of your myRecordHandler function:
foreach($record as $k => $v)
{
if ($v == "-") $record[$k] = "0";
}Hope this helps!
Cheers,
David
--
MagicParser.com
David,
that works. but thinking some more. isn't it possible to have no value at all, so column remains empty ?
because value 0 in a column with temperature could be interpreted as temperature is 0
Hi Marcel,
I'm not familiar with PostgreSQL handling of null / default / empty values being passed to a numeric field but you could try in the first instance:
foreach($record as $k => $v)
{
if ($v == "-") $record[$k] = "";
}If that results in an error, as long as the field has been specified to accept NULL (no) value then it would be possible to modify the SQL construction so that NULL is used instead of an empty field for "-" values...
Cheers,
David
perhaps this can do some good ?
http://www.postgresql.org/docs/9.1/static/functions-conditional.html
the nullif part (see 9.16.3)
could this be a solution ?
http://www.postgresql.org/docs/9.3/static/functions-conditional.html
9.17.3. NULLIF
NULLIF(value1, value2)
The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:
SELECT NULLIF(value, '(none)') ...
In this example, if value is (none), null is returned, otherwise the value of value is returned.
Hello Marcel,
I'm afraid not sorry, that really only applies to SELECTion rather than INSERTing.
You would need to change table structure to ensure that any fields that could be "-" in your feed are permitted to be NULL, or have a default value, so that they do not need to appear in the INSERT query. Once that is in place, then you can construct your query using an array of fields, adding each one to the $sql if not "-", for example:
$fields["stationcode"] = "STATIONCODE";
$fields["stationnaam"] = "STATIONNAAM";
$fields["datum"] = "DATUM";
$fields["luchtvochtigheid"] = "LUCHTVOCHTIGHEID";
$fields["temperatuurgc"] = "TEMPERATUURGC";
$fields["windsnelheidms"] = "WINDSNELHEIDMS";
$fields["windsnelheidbf"] = "WINDSNELHEIDBF";
$fields["windrichtinggr"] = "WINDRICHTINGGR";
$fields["windrichting"] = "WINDRICHTING";
$fields["luchtdruk"] = "LUCHTDRUK";
$fields["zichtmeters"] = "ZICHTMETERS";
$fields["windstotenms"] = "WINDSTOTENMS";
$fields["regenmmpu"] = "REGENMMPU";
$sql = "INSERT INTO mz_weerstations SET ";
foreach($fields as $field => $key)
{
if ($record[$key] <> "-")
{
$sql .= $field." = '".pg_escape_string($record[$key])."', ";
}
}
$sql .= "datumtijd = '".pg_escape_string($sDate)."'";Hope this helps!
Cheers,
David
Hello Marcel,
There's nothing database specific about Magic Parser so you would be able to use any database library functions you wish since that is entirely down to your code as used within the myRecordHandler function of your application.
There are analogous postgreSQL functions to all mysql function, for example pg_connect (in place of mysql_connect), pg_query (in place of mysql_query) and pg_fetch_assoc (in place of mysql_fetch_assoc) but subtleties of the query language - the actual SQL itself - would also need to be considered in case your application uses anything MySQL specific (for example type=MYISAM within the CREATE TABLE sql).
If you're no sure at all, if you would like to post an example from your code that uses MySQL functions I'll have a look and see if I can point you in the right direction...
Best regards,
David
--
MagicParser.com