Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
The short answer is "yes you can use DBI with DBD::AnyData to treat your data as a database and run SQL queries on it".

The somewhat longer answer is that you'll want to convert your data from the hash to a text format such as CSV (Comma Separated Values) or XML or a Fixed-length record. AnyData can work with those and others. For the example below, I'll assume you will use CSV.

A second issue is the names of your fields. AnyData tries to stick to the rules of SQL which define fieldnames as starting with an alphabetic character and containing only alphanumerics and underscores. There are ways to circumvent that by quoting the field names, but that adds a level of complexity you don't really need. It would be much simpler to rename your fields to be valid SQL field names. I've done that in the example below (e.g. _SESSION_ID loses the underscore and image.x gets an underscore instead of a period).

With those caveats, here's a working example that takes a hash like you've shown, inserts it into a database and then queries that database.

#!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect('dbi:AnyData(RaiseError=1):'); $dbh->ad_catalog('Login','CSV','Login.csv'); my $data = {Password=>'raman',Session_ID=>'8ada23',Image_X=>55}; my @cols = qw(Password Session_ID Image_X); my @vals = @$data{@cols}; my $colStr = join ',', @cols; my $paramStr = join ',', ('?')x@cols; $dbh->do("DROP TABLE IF EXISTS Login"); $dbh->do("CREATE TABLE Login (Password TEXT,Session_ID TEXT, Image_X I +NT)"); $dbh->do("INSERT INTO Login ($colStr) VALUES ($paramStr)",{},@vals); my $sth=$dbh->prepare("SELECT Password FROM Login WHERE Image_X=?"); $sth->execute(55); $sth->dump_results; $sth->{Active}=0; # temporary workaround for DBD::File bug

There are a couple of perl "tricks" here - I use a hash slice to extract the values from your hash and I use join to create strings of placeholders. In actual practice you probably won't create, insert, and query all in one script (or at least not in the same part of the script) but I've combined them here so you can see the full process.

I hope this helps.

In reply to Re: dbi::anydata by jZed
in thread dbi::anydata by rjsaulakh

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    [vedagiri89]: how do fix this issue
    [choroba]: This usually happens when you have more than one version of Perl, and you mix the libraries
    [hippo]: Which O/S?
    [vedagiri89]: i have searched many forum...not getting resolutions
    [vedagiri89]: centos
    [Corion]: vedagiri89: Either what choroba said, or maybe you misspelled some module name (on Windows), like use Strict; or something like that.
    [vedagiri89]: @choroba, what to do for that
    [Corion]: Ah, you are not on Windows, so disregard my opinion.
    [vedagiri89]: in centos, how to fix this.
    [choroba]: You have to find out why it happened. Was there an OS update?

    How do I use this? | Other CB clients
    Other Users?
    Others studying the Monastery: (13)
    As of 2018-06-19 11:21 GMT
    Find Nodes?
      Voting Booth?
      Should cpanminus be part of the standard Perl release?

      Results (113 votes). Check out past polls.