FAQ
Hi,
I am using perl DBI to access an sql server database.
One table has component part numbers that have failed, keyed in by
operators. I need to read in and count these which is no problem but I also
need to compare them with an approved parts table to make sure that they are
valid part numbers. Currently I am doing this by reading the failed parts
into a hash and then using the hash keys as placeholders to query the parts
valid table.
Is it possible with DBI to do something like
SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
table2.valid_parts

thanks Willie


Willie McSweeney
Memory Component Engineer
EMC,
Ovens,
Co.Cork, Ireland.
Tel +00353-21-4281412
Fax +00353-21-4281898
Email <mcsweeney_william@emc.com >

Search Discussions

  • Brett W. McCoy at May 1, 2001 at 2:15 pm

    On Tue, 1 May 2001 McSweeney_William@emc.com wrote:

    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but I also
    need to compare them with an approved parts table to make sure that they are
    valid part numbers. Currently I am doing this by reading the failed parts
    into a hash and then using the hash keys as placeholders to query the parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts
    You can do this, it's pretty basic SQL, but the problem is that you have
    no way of enforcing this 'referential integrity' rule.

    The correct way to do this is to use a foreign key constraint in your
    failed parts table. This enforces the rule that a failed part number
    cannot be entered into the database unless that part number exists in the
    approved parts table. Some databases (like MySQL) don't support foreign
    keys, but all of the commercial ones do, as well as PostgreSQL.

    You would have something like this:

    failed_part integer references parts(part_num)

    in your table creation statement. There are more options for creating
    foreign key constraints, refer to your server documentation for the
    specifics. They can make your life a lot easier.

    -- Brett
    http://www.chapelperilous.net/btfwk/
    ------------------------------------------------------------------------
    Hey, Jim, it's me, Susie Lillis from the laundromat. You said you were
    gonna call and it's been two weeks. What's wrong, you lose my number?
  • Sterin, Ilya at May 1, 2001 at 2:19 pm
    You sure can, that's just a simple join, but you have to also use the table2
    in the from clause. Like this...

    SELECT table1.failed_parts FROM table1, table2 WHERE table1.failed_parts =
    table2.valid_parts;

    Ilya Sterin



    -----Original Message-----
    From: McSweeney_William@emc.com
    To: dbi-users@perl.org
    Sent: 05/01/2001 8:04 AM
    Subject: sql table joins

    Hi,
    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but I
    also
    need to compare them with an approved parts table to make sure that they
    are
    valid part numbers. Currently I am doing this by reading the failed
    parts
    into a hash and then using the hash keys as placeholders to query the
    parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts

    thanks Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >
  • Enrique Marcote Peña at May 1, 2001 at 2:31 pm
    Hi:

    Isn't table2 needed? Like:

    SELECT table1.failed_parts
    FROM table1, table2
    WHERE table1.failed_parts = table2.valid_parts

    Joins work fine to me accessing an Oracle DB vía DBI.

    my $sql = qq{
    SELECT cdg, nmb, dsc, info, figProd1, figProd2
    FROM Secc S, Dvde D
    WHERE D.tnda = ?
    AND S.cdg = D.secc
    AND ((S.oscur IS NULL) OR (S.oscur NOT LIKE '%erdad%'))
    };
    $Secc::sth = $dbh->prepare( $sql );
    $Secc::sth->bind_param( 1, $tnda, SQL_INTEGER );
    $Secc::sth->execute();
    $Secc::sth->bind_columns( undef,
    \$self->{cdg},
    \$self->{nmb},
    \$self->{dsc},
    \$self->{info},
    \$self->{figProd1},
    \$self->{figProd2}
    );

    Greetings

    Quique

    McSweeney_William@emc.com wrote:
    Hi,
    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but I also
    need to compare them with an approved parts table to make sure that they are
    valid part numbers. Currently I am doing this by reading the failed parts
    into a hash and then using the hash keys as placeholders to query the parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts

    thanks Willie

    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >
  • Mcsweeney_william at May 1, 2001 at 2:47 pm
    Hi IIya,
    Thanks for the reply. I know that your suggestion will work but how do I
    construct the prepare statement in DBI
    If I do something like
    $dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
    "could not CONNECT $!";
    $dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die "could
    not CONNECT $!";

    $sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
    AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
    AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die "Couldn't
    prepare statement: " . $dbh->errstr;

    I get an error "invalid object name AMLMaster". This is I guess because $dbh
    is the connection to TotalBoards and knows nothing about the AMLMaster
    table. I have another $dhh_aml connecting to AMLMaster. My question is how
    do you write a prepare statement as above that works. Can you get $dbh to
    connect to both tables at the same time???
    Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: Sterin, Ilya [SMTP:Isterin@ciber.com]
    Sent: Tuesday, May 01, 2001 3:19 PM
    To: 'McSweeney_William@emc.com '; 'dbi-users@perl.org '
    Subject: RE: sql table joins

    You sure can, that's just a simple join, but you have to also use the
    table2
    in the from clause. Like this...

    SELECT table1.failed_parts FROM table1, table2 WHERE table1.failed_parts =
    table2.valid_parts;

    Ilya Sterin



    -----Original Message-----
    From: McSweeney_William@emc.com
    To: dbi-users@perl.org
    Sent: 05/01/2001 8:04 AM
    Subject: sql table joins

    Hi,
    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but I
    also
    need to compare them with an approved parts table to make sure that they
    are
    valid part numbers. Currently I am doing this by reading the failed
    parts
    into a hash and then using the hash keys as placeholders to query the
    parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts

    thanks Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >
  • Michael A. Chase at May 1, 2001 at 3:10 pm
    Please run 'perldoc DBI' and read the fine manual. There are several
    examples in there. "Programming the Perl DBI" from Oreilly would be another
    good investment, if you read it.

    One hint: $! does _not_ contain the DBI error message, use $DBI::errstr
    instead.
    --
    Mac :})
    ** I normally forward private database questions to the DBI mail lists. **
    Give a hobbit a fish and he'll eat fish for a day.
    Give a hobbit a ring and he'll eat fish for an age.
    ----- Original Message -----
    From: <McSweeney_William@emc.com>
    To: <Isterin@ciber.com>
    Cc: <dbi-users@perl.org>
    Sent: Tuesday, May 01, 2001 7:47 AM
    Subject: RE: sql table joins

    Thanks for the reply. I know that your suggestion will work but how do I
    construct the prepare statement in DBI
    If I do something like
    $dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
    "could not CONNECT $!";
    $dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die "could
    not CONNECT $!";

    $sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
    AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
    AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die "Couldn't
    prepare statement: " . $dbh->errstr;

    I get an error "invalid object name AMLMaster". This is I guess because $dbh
    is the connection to TotalBoards and knows nothing about the AMLMaster
    table. I have another $dhh_aml connecting to AMLMaster. My question is how
    do you write a prepare statement as above that works. Can you get $dbh to
    connect to both tables at the same time???
    Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: Sterin, Ilya [SMTP:Isterin@ciber.com]
    Sent: Tuesday, May 01, 2001 3:19 PM
    To: 'McSweeney_William@emc.com '; 'dbi-users@perl.org '
    Subject: RE: sql table joins

    You sure can, that's just a simple join, but you have to also use the
    table2
    in the from clause. Like this...

    SELECT table1.failed_parts FROM table1, table2 WHERE table1.failed_parts
    =
    table2.valid_parts;

    Ilya Sterin



    -----Original Message-----
    From: McSweeney_William@emc.com
    To: dbi-users@perl.org
    Sent: 05/01/2001 8:04 AM
    Subject: sql table joins

    Hi,
    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but I
    also
    need to compare them with an approved parts table to make sure that they
    are
    valid part numbers. Currently I am doing this by reading the failed
    parts
    into a hash and then using the hash keys as placeholders to query the
    parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts

    thanks Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >
  • Michael A. Chase at May 1, 2001 at 2:59 pm
    DBI is just a way to call SQL. If you will read it, a book on basic SQL
    would be a good investment.
    --
    Mac :})
    ** I normally forward private database questions to the DBI mail lists. **
    Give a hobbit a fish and he'll eat fish for a day.
    Give a hobbit a ring and he'll eat fish for an age.
    ----- Original Message -----
    From: <McSweeney_William@emc.com>
    To: <dbi-users@perl.org>
    Sent: Tuesday, May 01, 2001 7:04 AM
    Subject: sql table joins

    Hi,
    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but I also
    need to compare them with an approved parts table to make sure that they are
    valid part numbers. Currently I am doing this by reading the failed parts
    into a hash and then using the hash keys as placeholders to query the parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts

    thanks Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

  • Mcsweeney_william at May 1, 2001 at 3:11 pm
    Hello IIya,
    I forgot one important thing the tables are in two different databases (that
    is why I have two connect statements). This is why I can't figure out how to
    write the prepare statement. If they were on the same database your
    suggestions would work fine.

    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: McSweeney, William
    Sent: Tuesday, May 01, 2001 3:47 PM
    To: 'Sterin, Ilya'
    Cc: 'dbi-users@perl.org '
    Subject: RE: sql table joins

    Hi IIya,
    Thanks for the reply. I know that your suggestion will work but how do I
    construct the prepare statement in DBI
    If I do something like
    $dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
    "could not CONNECT $!";
    $dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die "could
    not CONNECT $!";

    $sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
    AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
    AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die
    "Couldn't prepare statement: " . $dbh->errstr;

    I get an error "invalid object name AMLMaster". This is I guess because
    $dbh is the connection to TotalBoards and knows nothing about the AML
    database . I have another $dhh_aml connecting to AMLMaster. My question is
    how do you write a prepare statement as above that works. Can you get $dbh
    to connect to both tables at the same time???
    Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >


    -----Original Message-----
    From: Sterin, Ilya [SMTP:Isterin@ciber.com]
    Sent: Tuesday, May 01, 2001 3:19 PM
    To: 'McSweeney_William@emc.com '; 'dbi-users@perl.org '
    Subject: RE: sql table joins

    You sure can, that's just a simple join, but you have to also use
    the table2
    in the from clause. Like this...

    SELECT table1.failed_parts FROM table1, table2 WHERE
    table1.failed_parts =
    table2.valid_parts;

    Ilya Sterin



    -----Original Message-----
    From: McSweeney_William@emc.com
    To: dbi-users@perl.org
    Sent: 05/01/2001 8:04 AM
    Subject: sql table joins

    Hi,
    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but
    I
    also
    need to compare them with an approved parts table to make sure that
    they
    are
    valid part numbers. Currently I am doing this by reading the failed
    parts
    into a hash and then using the hash keys as placeholders to query
    the
    parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts

    thanks Willie


    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >
  • Michael A. Chase at May 1, 2001 at 4:24 pm
    That wee fact does make a bit of a difference...

    Unless you have more than a few hundred rows in the error table, you are
    probably doing the best you can.

    If the tables are fairly large, it might be worth using an ORDER BY clause
    in both queries so the rows are fetched in the same order. You skip rows in
    either query that don't have a matching row in the other query. You end up
    with a full-table scan of both tables, but that is usually faster than a
    bunch of key lookups if you need to look at more than around 10% of the
    rows, especially if the lookup is across a network.
    --
    Mac :})
    ** I normally forward private database questions to the DBI mail lists. **
    Give a hobbit a fish and he'll eat fish for a day.
    Give a hobbit a ring and he'll eat fish for an age.
    ----- Original Message -----
    From: <McSweeney_William@emc.com>
    To: <McSweeney_William@emc.com>; <Isterin@ciber.com>
    Cc: <dbi-users@perl.org>
    Sent: Tuesday, May 01, 2001 8:12 AM
    Subject: RE: sql table joins

    Hello IIya,
    I forgot one important thing the tables are in two different databases (that
    is why I have two connect statements). This is why I can't figure out how to
    write the prepare statement. If they were on the same database your
    suggestions would work fine.

    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: McSweeney, William
    Sent: Tuesday, May 01, 2001 3:47 PM
    To: 'Sterin, Ilya'
    Cc: 'dbi-users@perl.org '
    Subject: RE: sql table joins

    Hi IIya,
    Thanks for the reply. I know that your suggestion will work but how do I
    construct the prepare statement in DBI
    If I do something like
    $dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
    "could not CONNECT $!";
    $dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die
    "could
    not CONNECT $!";

    $sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
    AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
    AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die
    "Couldn't prepare statement: " . $dbh->errstr;

    I get an error "invalid object name AMLMaster". This is I guess because
    $dbh is the connection to TotalBoards and knows nothing about the AML
    database . I have another $dhh_aml connecting to AMLMaster. My question
    is
    how do you write a prepare statement as above that works. Can you get
    $dbh
    to connect to both tables at the same time???

    -----Original Message-----
    From: McSweeney_William@emc.com
    To: dbi-users@perl.org
    Sent: 05/01/2001 8:04 AM
    Subject: sql table joins

    I am using perl DBI to access an sql server database.
    One table has component part numbers that have failed, keyed in by
    operators. I need to read in and count these which is no problem but
    I
    also
    need to compare them with an approved parts table to make sure that
    they
    are
    valid part numbers. Currently I am doing this by reading the failed
    parts
    into a hash and then using the hash keys as placeholders to query
    the
    parts
    valid table.
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts
  • Jason Waugh at May 1, 2001 at 3:21 pm
    William,
    Is it possible with DBI to do something like
    SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
    table2.valid_parts

    DBI is really just a way of executing SQL against your database... so
    anything supported by your DB will work.

    Try this:

    SELECT failed_parts FROM table 1
    WHERE failed_parts IN ( SELECT valid_parts FROM table2)

    I don't know what you are going to do to figure out what to do with
    failed_parts that are invalid, but I suppose that's for the next step in
    your project...h


    Thanks,

    Jason Waugh
    Systems Development
    COLLECTCORP, INC.
    (416)935-2883
  • Mcsweeney_william at May 1, 2001 at 3:37 pm
    Hi Sumit,
    The databases are both microsoft sql. As you say it looks like DBI does not
    support what I would like to do when the tables are in different databases.
    I will just have to continue what I have right now which is read in from
    one, store in a hash and the use the hash keys to query the second database.
    the only problem with this is that it ran a little slow and I felt getting
    SQL to do this compare would be faster.Thanks any way
    Willie

    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: Sumit_Babu@i2.com [SMTP:Sumit_Babu@i2.com]
    Sent: Tuesday, May 01, 2001 4:24 PM
    To: McSweeney_William@emc.com
    Subject: RE: sql table joins






    Hello Willie,
    If I do something like
    $dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
    "could not CONNECT $!";
    $dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die "could
    not CONNECT $!";

    $sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
    AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
    AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die "Couldn't
    prepare statement: " . $dbh->errstr;

    I get an error "invalid object name AMLMaster". This is I guess because $dbh
    is the connection to TotalBoards and knows nothing about the AMLMaster
    table. I have another $dhh_aml connecting to AMLMaster. My question is how
    do you write a prepare statement as above that works. Can you get $dbh to
    connect to both tables at the same time???
    Willie
    If I guess it correctly, the table AMLMaster is in the AML Database/DSN
    and
    TotalBoards Database/DSN, right. If so you will have to have a database
    link created between the 'TotalBoards' and 'AML' databases. I don't think
    you can do what you are doing with out a database link or some thing like
    that between 'TotalBoards' and 'AML'. Also i don't think ODBC supports
    such
    a thing.

    What databases are these two? If it's oracle then you can create a
    database
    link using the command 'CREATE DATABASE LINK ...' in SQL Plus.

    Hope this helps.

    Regards,

    Sumit.
    **************************************************************************
    ******************************

    Just because something doesn't do what you planned it to do doesn't mean
    it's useless.
    -Thomas A. Edison
    **************************************************************************
    ******************************
  • Brett W. McCoy at May 1, 2001 at 3:44 pm

    On Tue, 1 May 2001 McSweeney_William@emc.com wrote:

    The databases are both microsoft sql. As you say it looks like DBI does not
    support what I would like to do when the tables are in different databases.
    I will just have to continue what I have right now which is read in from
    one, store in a hash and the use the hash keys to query the second database.
    the only problem with this is that it ran a little slow and I felt getting
    SQL to do this compare would be faster.Thanks any way
    Is there a reason they are in different schemas altogether?

    -- Brett
    http://www.chapelperilous.net/btfwk/
    ------------------------------------------------------------------------
    Lizzie Borden took an axe,
    And plunged it deep into the VAX;
    Don't you envy people who
    Do all the things ___YOU want to do?
  • Michael Peppler at May 1, 2001 at 3:49 pm

    McSweeney_William@emc.com writes:
    Hi Sumit,
    The databases are both microsoft sql. As you say it looks like DBI does not
    support what I would like to do when the tables are in different databases.
    I will just have to continue what I have right now which is read in from
    one, store in a hash and the use the hash keys to query the second database.
    the only problem with this is that it ran a little slow and I felt getting
    SQL to do this compare would be faster.Thanks any way
    Are they in different databases, or different servers?

    If the former you can do it at the sql level.

    Also, doesn't MS-SQL support some sort of proxy tables? I know that
    with Sybase you can define a proxy table on a remote server and query
    it as if it's a local table. It's not necessarily *fast*, but at least
    the functionality is there.

    Michael
  • Steve Howard at May 2, 2001 at 3:39 am
    If the tables are in two different databases on the same server, the naming
    format is:

    Database.Owner.Table.

    therefore you can do a join in MS like this:

    SELECT value1 FROM database1.dbo.table1 l
    JOIN database2.dbo.table2 r ON l.somecolumn = r.somecolumn.

    (dbo is the default owner. if dbo is the owner, you can reference it as:

    Database..Table)
    If they are on two different servers, there are two ways to do this in MS
    SQL (If you are usnig 7.0 or later, one way using 6.5)

    The first way is to create a linked server definition. This is not
    difficult, but is a little more involved than I want to go into for this
    message. (Read the SQL Server Books Online that are installed with MS SQL to
    get the instructions for creating Linked server definitions). Once the
    Linked server definition is created, you can to a distributed query using a
    4 part naming convention that is SERVER.DATABASE.OWNER.TABLE and it is a
    simple as

    SELECT Value1 FROM Myserver.Database1..Table1 l
    JOIN Otherserver.Database2..Table2 r on l.somecolumn = r.somecolumn.

    If you are working ad-hoc in 7.0 or later, you can use an openrowset
    function and join to the results in the openrowset function. You can do that
    from PERL, but if it is something you will use frequently, just create a
    linked server rather than use the OPENROWSET.

    All of these things are contained in MS SQL Server Books Online, but I'll
    lend a hand if you need more help getting started.

    Sincerely,

    Steve Howard

    PS. Don't use the old SQL Syntax for joins - it limits what you can do with
    SQL too much.



    -----Original Message-----
    From: McSweeney_William@emc.com
    Sent: Tuesday, May 01, 2001 10:38 AM
    To: Sumit_Babu@i2.com
    Cc: dbi-users@perl.org
    Subject: RE: sql table joins


    Hi Sumit,
    The databases are both microsoft sql. As you say it looks like DBI does not
    support what I would like to do when the tables are in different databases.
    I will just have to continue what I have right now which is read in from
    one, store in a hash and the use the hash keys to query the second database.
    the only problem with this is that it ran a little slow and I felt getting
    SQL to do this compare would be faster.Thanks any way
    Willie

    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: Sumit_Babu@i2.com [SMTP:Sumit_Babu@i2.com]
    Sent: Tuesday, May 01, 2001 4:24 PM
    To: McSweeney_William@emc.com
    Subject: RE: sql table joins






    Hello Willie,
    If I do something like
    $dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
    "could not CONNECT $!";
    $dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die "could
    not CONNECT $!";

    $sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
    AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
    AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die "Couldn't
    prepare statement: " . $dbh->errstr;

    I get an error "invalid object name AMLMaster". This is I guess because $dbh
    is the connection to TotalBoards and knows nothing about the AMLMaster
    table. I have another $dhh_aml connecting to AMLMaster. My question is how
    do you write a prepare statement as above that works. Can you get $dbh to
    connect to both tables at the same time???
    Willie
    If I guess it correctly, the table AMLMaster is in the AML Database/DSN
    and
    TotalBoards Database/DSN, right. If so you will have to have a database
    link created between the 'TotalBoards' and 'AML' databases. I don't think
    you can do what you are doing with out a database link or some thing like
    that between 'TotalBoards' and 'AML'. Also i don't think ODBC supports
    such
    a thing.

    What databases are these two? If it's oracle then you can create a
    database
    link using the command 'CREATE DATABASE LINK ...' in SQL Plus.

    Hope this helps.

    Regards,

    Sumit.
    **************************************************************************
    ******************************

    Just because something doesn't do what you planned it to do doesn't mean
    it's useless.
    -Thomas A. Edison
    **************************************************************************
    ******************************
  • Mcsweeney_william at May 1, 2001 at 4:02 pm
    different databases on different servers

    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: Michael Peppler [SMTP:mpeppler@peppler.org]
    Sent: Tuesday, May 01, 2001 4:49 PM
    To: McSweeney_William@emc.com
    Cc: dbi-users@perl.org
    Subject: RE: sql table joins

    McSweeney_William@emc.com writes:
    Hi Sumit,
    The databases are both microsoft sql. As you say it looks like DBI does not
    support what I would like to do when the tables are in different
    databases.
    I will just have to continue what I have right now which is read in from
    one, store in a hash and the use the hash keys to query the second database.
    the only problem with this is that it ran a little slow and I felt getting
    SQL to do this compare would be faster.Thanks any way
    Are they in different databases, or different servers?

    If the former you can do it at the sql level.

    Also, doesn't MS-SQL support some sort of proxy tables? I know that
    with Sybase you can define a proxy table on a remote server and query
    it as if it's a local table. It's not necessarily *fast*, but at least
    the functionality is there.

    Michael
  • Brett W. McCoy at May 1, 2001 at 5:18 pm

    On Tue, 1 May 2001 McSweeney_William@emc.com wrote:

    different databases on different servers
    Yikes! That does introduce extra problems for you! Yeah, the only way
    you can really handle that is via external scripting, unless your servers
    support some kind of replication|mirroring|distributed querying or
    something along those lines.

    I guess your big problem is that you can't verify your data integrity
    until after the data has already been entered. How big of a gap in time
    does this occur, between data entry and data validation?

    -- Brett
    http://www.chapelperilous.net/btfwk/
    ------------------------------------------------------------------------
    "Ninety percent of baseball is half mental."
    -- Yogi Berra
  • Mcsweeney_william at May 1, 2001 at 4:26 pm
    I am not sure to be honest, there may be a historial reason.
    I do know the AML database is a "LIVE" database where updates can occur at
    anytime while the database containing the failing parts only gets updated on
    a nightly basis from an AS400 system so it only contains data up to
    yesterday. This may be the reason why they exist in different databases.

    Willie McSweeney
    Memory Component Engineer
    EMC,
    Ovens,
    Co.Cork, Ireland.
    Tel +00353-21-4281412
    Fax +00353-21-4281898
    Email <mcsweeney_william@emc.com >

    -----Original Message-----
    From: Brett W. McCoy [SMTP:bmccoy@chapelperilous.net]
    Sent: Tuesday, May 01, 2001 4:48 PM
    To: McSweeney_William@emc.com
    Cc: Sumit_Babu@i2.com; dbi-users@perl.org
    Subject: RE: sql table joins
    On Tue, 1 May 2001 McSweeney_William@emc.com wrote:

    The databases are both microsoft sql. As you say it looks like DBI does not
    support what I would like to do when the tables are in different
    databases.
    I will just have to continue what I have right now which is read in from
    one, store in a hash and the use the hash keys to query the second database.
    the only problem with this is that it ran a little slow and I felt getting
    SQL to do this compare would be faster.Thanks any way
    Is there a reason they are in different schemas altogether?

    -- Brett
    http://www.chapelperilous.net/btfwk/
    ------------------------------------------------------------------------
    Lizzie Borden took an axe,
    And plunged it deep into the VAX;
    Don't you envy people who
    Do all the things ___YOU want to do?
  • De Simone, Andrew (CAP, FGI) at May 1, 2001 at 8:31 pm
    Even if the servers don't support replication you could take data from the one database and populate a table on the other by creating your own replica. You could then add a
    foreign key constraint to this table or use a query to check the data. Just remember that if it is being used as a foreign key, you can't drop the data and replace it, you will
    need to do update/inserts. This could give the app a way of validating the data entered or even give them a parts lookup.

    -----Original Message-----
    From: Brett W. McCoy
    Sent: Tuesday, May 01, 2001 1:21 PM
    To: McSweeney_William@emc.com
    Cc: mpeppler@peppler.org; dbi-users@perl.org
    Subject: RE: sql table joins

    On Tue, 1 May 2001 McSweeney_William@emc.com wrote:

    different databases on different servers
    Yikes! That does introduce extra problems for you! Yeah, the only way
    you can really handle that is via external scripting, unless your servers
    support some kind of replication|mirroring|distributed querying or
    something along those lines.

    I guess your big problem is that you can't verify your data integrity
    until after the data has already been entered. How big of a gap in time
    does this occur, between data entry and data validation?

    -- Brett
    http://www.chapelperilous.net/btfwk/
    ------------------------------------------------------------------------
    "Ninety percent of baseball is half mental."
    -- Yogi Berra

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedMay 1, '01 at 2:05p
activeMay 2, '01 at 3:39a
posts18
users9
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase