CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
storing values to mySQL - with DBI - doable for this too!?

 



dilbert
User

Jun 8, 2014, 10:30 AM

Post #1 of 6 (6281 views)
storing values to mySQL - with DBI - doable for this too!? Can't Post

 
helllo dear perl-commmunity


new to Ruby - i need some advices -

i plan to do some requests in osm-files. (openstreetmap)

Question - how can i store the results on a Database -
eg mysql or - (if you prefer postgresql) -

note: my favorite db - at least at the moment is mysql


here the code


Code
 
require 'open-uri'
require "net/http"
require 'rexml/document'

def query_overpass(object_type, left,bottom,right,top, key, value)
base_url = "http://www.overpass-api.de/api/xapi?"
query_string = "#{object_type}[bbox=#{left},#{bottom},#{right},#{top}][#{key}=#{value}]"
url = "#{base_url}#{URI.encode(query_string)}"
resp = Net::HTTP.get_response(URI.parse(url))
data = resp.body
return data
end

overpass_result = REXML::Document.new(query_overpass("node", 7.1,51.2,7.2,51.3,"amenity","restaurant|pub|ice_cream|food_court|fast_food|cafe|biergarten|bar|bakery|steak|pasta|pizza|sushi|asia|nightclub"))

overpass_result.elements.each('osm/node') {|x|
if !x.elements["tag[@k='name']"].nil?
print x.elements["tag[@k='name']"].attributes["v"]
end
print " | "

if !x.elements["tag[@k='addr:postcode']"].nil?
print x.elements["tag[@k='addr:postcode']"].attributes["v"]
print ", "
end
if !x.elements["tag[@k='addr:city']"].nil?
print x.elements["tag[@k='addr:city']"].attributes["v"]
print ", "
end
if !x.elements["tag[@k='addr:street']"].nil?
print x.elements["tag[@k='addr:street']"].attributes["v"]
print ", "
end
if !x.elements["tag[@k='addr:housenumber']"].nil?
print x.elements["tag[@k='addr:housenumber']"].attributes["v"]
end
print " | "
print x.attributes["lat"]
print " | "
print x.attributes["lon"]
print " | "
if !x.elements["tag[@k='website']"].nil?
print x.elements["tag[@k='website']"].attributes["v"]
end
print " | "ll
if !x.elements["tag[@k='amenity']"].nil?
print x.elements["tag[@k='amenity']"].attributes["v"]
print " | "
end
puts
}


look forward to hear from you

again - i would love to store it on a mysql - database - if possible. If you would prefer postgresql - then i would
takte this one.... ;-)

well - i guess that the answer to this will be the same no matter what language we are using.
If the db is a sql database we need to design the database schema and create the tables in the database.

The first step in accessing a db in our code is to get a connection to it.
If ruby is our choice of language, a search for "ruby sql connector" will give us
lots of options to read about.

Well - we also can do it in PHP. What do you think!?

Next, based on the schema we have designed, we need to create queries suitable for storing the data.
We will likely need to consider our transactional model.

Again, searching "ruby sql transactional model" will give us plenty of food for thought. Finally, we may want or need to close the connection to the database.


FishMonger
Veteran / Moderator

Jun 8, 2014, 1:10 PM

Post #2 of 6 (6176 views)
Re: [dilbert] storing values to mySQL - with DBI - doable for this too!? [In reply to] Can't Post

This is a Perl form, not Ruby.


dilbert
User

Jun 8, 2014, 2:02 PM

Post #3 of 6 (6142 views)
Re: [FishMonger] storing values to mySQL - with DBI - doable for this too!? [In reply to] Can't Post

 


well if we would work with php this task would be split into several parts.


for example the creation of a Database Connection in PHP wuld be not that difficult : i would d o i t like this.




Code
 

<?php
if(basename(__FILE__) == basename($_SERVER['PHP_SELF'])) send_404();
$dbHost = "localhost"; //Location Of Database usually its localhost
$dbUser = "xxxx"; //Database User Name
$dbPass = "xxxx"; //Database Password
$dbDatabase = "xxxx"; //Database Name

$db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");

# This function will send an imitation 404 page if the user
# types in this files filename into the address bar.
# only files connecting with in the same directory as this
# file will be able to use it as well.
function send_404()
{
header('HTTP/1.x 404 Not Found');
print '<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">'."n".
'<html><head>'."n".
'<title>404 Not Found</title>'."n".
'</head><body>'."n".
'<h1>Not Found</h1>'."n".
'<p>The requested URL '.
str_replace(strstr($_SERVER['REQUEST_URI'], '?'), '', $_SERVER['REQUEST_URI']).
' was not found on this server.</p>'."n".
'</body></html>'."n";
exit;
}

# In any file you want to connect to the database,
# and in this case we will name this file db.php
# just add this line of php code (without the pound sign):
# include"db.php";
?>


we could do it like so... .


Laurent_R
Veteran / Moderator

Jun 9, 2014, 9:43 AM

Post #4 of 6 (5653 views)
Re: [dilbert] storing values to mySQL - with DBI - doable for this too!? [In reply to] Can't Post

You've been told already that this is Perl forum, neither a forum on Ruby nor a forum on PHP.

Wink


FishMonger
Veteran / Moderator

Jun 9, 2014, 10:00 AM

Post #5 of 6 (5652 views)
Re: [dilbert] storing values to mySQL - with DBI - doable for this too!? [In reply to] Can't Post

This is quoted from your other related thread.

Quote
it is very very simple - in PHP. i am going to see if we can do such an easy solution in perl - which is in general a bit more difficult than php - (my opinion)


IMO, PHP is messy and noisy (referring to your send_404 sub) when compared with Perl.


Code
# doing this test doesn't make any sense, 
# but it's what you're doing so I left it in
send_404() if basename(__FILE__) eq basename($ENV{SCRIPT_NAME});

my $db_host = 'localhost';
my $db_user = 'xxxxx';
my $db_pass = 'xxxxx';
my $database = 'xxxxx';

my $dbh = DBI->connect("DBI:mysql:$database:$db_host", $db_user, $db_pass,
{ RaiseError => 1})
or die 'Error connecting to database.';

sub send_404 {
print header(-status => '404 Not Found'),
start_html('404 Not Found'),
h1('Not Found'),
p("The requested URL $ENV{REQUEST_URI} was not found in this server"),
end_html();
exit;
}


You should be aware that PHP's mysql_connect function is depreciated and will be removed in the next PHP release.

All mysql_xxxx functions are being replaced with mysqli_xxxx functions.


dilbert
User

Jun 9, 2014, 3:24 PM

Post #6 of 6 (5511 views)
Re: [FishMonger] storing values to mySQL - with DBI - doable for this too!? [In reply to] Can't Post

hi deear Laurent hello dear Fishmonger

thx for all your answers - i will have a closer look at all - and i try to go ahead with the perl-solution-.....

many many thanks for all you did!

greetings dilbert

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives