Perl ODBC complex query example needed

Discussion in 'Perl Misc' started by samregen, Mar 21, 2005.

  1. samregen

    samregen Guest

    Hi,

    I cannot find any examples of how to issue a multi-tabe query using
    perl ODBC methods.

    I am currently writing a perl script to issue queries generically, but
    I have no reference examples for queries against multiple tables.

    Specifically, I am used to using the form shown below, where a spcific
    sql connection is in scope for the query. My quer spans connections,
    so I am not sure how to approach this problem.

    #Next we can issue the SQL statement and trap on warnings if it fails
    if ($db{1}{connection}->Sql($passed_sql_statement)) {
    my ($err) = $db{1}{connection}->Error;
    warn "SQL() Error\n";
    warn "\t\$passed_sql_statement: $passed_sql_statement\n";
    warn "\t\$err: $err\n";
    $db{1}{connection}->DumpData();
    warn "\nThe problem SQL statment was: \n$passed_sql_statement\n";
    warn "\n\n";
    } else {
    print STDERR "The statement \"$passed_sql_statement\" \nran
    successfully\n\n";
    }

    I have been digging in the bit mines for 5 hours looking for examples
    for this sort of query, but nothing out there shows the use of ODBC
    methods.

    Thanks to anyone who can help.
    samregen, Mar 21, 2005
    #1
    1. Advertising

  2. samregen

    Bob Walton Guest

    samregen wrote:

    > Hi,
    >
    > I cannot find any examples of how to issue a multi-tabe query using
    > perl ODBC methods.


    Did you

    use DBI;

    with DBD::ODBC? If so:

    perldoc DBI

    at a command prompt will give you wonderful documentation
    including lots of examples. The "complex queries" you mention
    are all in SQL, which is outside the scope of this newsgroup -- a
    newsgroup about SQL would be better for that.

    Hint: It would be helpful if you would state what module you
    used to interface with your database so we don't have to guess,
    or just not know, like in my case.

    I am guessing that you probably want to access an ODBC database
    on Windoze. If so, you will need to create a user or system DSN
    as follows (maybe you already know that):

    (for Windoze XP, others may differ): Start..Control
    Panel..Administrative Tools..Data Sources (ODBC), then pick
    either the user or system DSN tab and add a DSN name for the
    database you want to connect to. That name goes in the DBI
    connect method.

    I don't know what method of accessing a database you were using
    below -- I don't recognize the code. Using the DBI module is
    definitely *the* way to go. The DBI code is independent of the
    database and the platform, so it will work the same if you decide
    you want to switch databases or OSes later, for example (some of
    the SQL might be slightly different, as SQL is not really very
    standard, at least if you use the more esoteric stuff, and, of
    course, the connection string will be different).

    >
    > I am currently writing a perl script to issue queries generically, but
    > I have no reference examples for queries against multiple tables.
    >
    > Specifically, I am used to using the form shown below, where a spcific
    > sql connection is in scope for the query. My quer spans connections,
    > so I am not sure how to approach this problem.


    With DBI, it is no problem at all to have connections to multiple
    databases -- just establish as many connections to as many
    databases as you need. However, to the best of my knowledge, a
    given SQL query is limited to a specific connection. That is a
    function of SQL -- the establishment of a connection is outside
    the scope of SQL -- the connection is established before SQL
    starts up. So I'm not sure what you mean when you say your
    "query spans connections". Could you clarify, please?

    >
    > #Next we can issue the SQL statement and trap on warnings if it fails
    > if ($db{1}{connection}->Sql($passed_sql_statement)) {
    > my ($err) = $db{1}{connection}->Error;
    > warn "SQL() Error\n";
    > warn "\t\$passed_sql_statement: $passed_sql_statement\n";
    > warn "\t\$err: $err\n";
    > $db{1}{connection}->DumpData();
    > warn "\nThe problem SQL statment was: \n$passed_sql_statement\n";
    > warn "\n\n";
    > } else {
    > print STDERR "The statement \"$passed_sql_statement\" \nran
    > successfully\n\n";
    > }
    >
    > I have been digging in the bit mines for 5 hours looking for examples
    > for this sort of query, but nothing out there shows the use of ODBC
    > methods.
    >
    > Thanks to anyone who can help.
    >

    HTH.
    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
    Bob Walton, Mar 22, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. news.amnet.net.au
    Replies:
    1
    Views:
    569
    =?UTF-8?b?TMSByrtpZSBUZWNoaWU=?=
    Apr 13, 2004
  2. Stanimir Stamenkov
    Replies:
    2
    Views:
    739
    Stanimir Stamenkov
    Oct 25, 2005
  3. Robert Mark Bram
    Replies:
    0
    Views:
    678
    Robert Mark Bram
    Feb 4, 2007
  4. Kottiyath

    How complex is complex?

    Kottiyath, Mar 18, 2009, in forum: Python
    Replies:
    22
    Views:
    753
  5. Wes Gamble
    Replies:
    1
    Views:
    159
    Gerardo Santana Gómez Garrido
    Apr 5, 2006
Loading...

Share This Page