Perl ODBC complex query example needed

[samregen]

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.

 

[Bob Walton]

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.