Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

DBI Substring index query and passing value

by SriniK (Beadle)
on Dec 10, 2012 at 13:36 UTC ( #1008099=perlquestion: print w/replies, xml ) Need Help??
SriniK has asked for the wisdom of the Perl Monks concerning the following question:


i using the following query, here im using "substring_index, and some special variables like ( )$..." and passing value to the query "$name"
$Agent1=$dbh->prepare('select substring_index(substring_index(substrin +g_index(Name,"$$$",1),"$$",1),"\$",-1) as Phone_no, substring_index(s +ubstring_index(Name,"$$$",1),"$$",-1) as Mail_Id from CustomFieldValu +es where CustomField =291 and Name like "$name%";') || die "$DBI::er +rstr"; $Agent1->execute() || die "$DBI::errstr" ; ( $phone_no,$mail )= $Agent1->fetchrow_array; print "Phone:\t$phone,Mail:\t$mail\n";
but im not getting the required output. its giving just empty.
Phone: ,Mail:
i have changed it like placeholder.... but its not working.
please help me on this

Replies are listed 'Best First'.
Re: DBI Substring index query and passing value
by marto (Archbishop) on Dec 11, 2012 at 11:27 UTC

    Since you seem to be missing the point(s) AnonymousMonk has explained, I'll try another method. Your code isn't doing what you think it does. Add error checking to your code (don't assume things work), actually use placeholders and use DBIs powerful Trace mechanism to find out what SQL you're actually running (don't assume that the SQL being executed is what you think it is). Also review your previous question DBI Query insert issue.....

Re: DBI Substring index query and passing value
by Anonymous Monk on Dec 11, 2012 at 09:29 UTC

    And what does your query produce when run straight against the database with an SQL command prompt?

    How many records are there in the database that begin with '$name'? Does fetchrow_array return false? (If so, your resultset is zero rows.)

    Also, I'm quite confused by the query. Do you have fields that are named $$$? (Double quotes are meant to quote table and field names in the SQL dialects I'm familiar with.)

    Perhaps you mean:

    my $sql = sprintf("... where CustomField = ? and Name like %s", $dbh-> +quote($name . '%')); $agent1 = $dbh->prepare($sql) || die $DBI::errstr; $Agent1->execute(291) || die $DBI::errstr ; ( $phone_no,$mail )= $Agent1->fetchrow_array or die "no results :(";

    May I recommend you put RaiseError into your connection string? Less explicit dying.

      Thanks for your replay.

      $name will be like "Christin". the Direct result in sql will be
      phone_no Mail_ID +00 00 0000 0000 ********.*****@******.com
      In that query the actual result will be +00 00 0000 0000$$********.*****@******.com$$$******, in that im splitting the required phone no and mail id using sub string in sql

      hope you understand.
      The result is there in SQL. But while running in perl it not showing result must be some special charcter issue.
      I tried various methods but not able to get the answer. As of now i have changed the query and splitting done in script.

        $name will be like "Christin".

        Well, currently, $name is $name; single quotes don't interpolate. Do check how I modified your query code. (Also worth looking is 7548)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1008099]
Approved by moritz
and all is quiet...

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

    Results (126 votes). Check out past polls.