Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

phpMyAdmin and column addition

Posted on 2009-07-07
4
Medium Priority
?
572 Views
Last Modified: 2012-05-07
Hi, i currently have a script (see snippet below)...

The script is currently returning the following error message:

bash-3.2# perl stub_uniprot_interpro.pl
DBD::mysql::st execute failed: Unknown column 'domain_type' in 'field list' at stub_uniprot_interpro.pl line 122, <GEN0> line 652.
could not enter data at stub_uniprot_interpro.pl line 122, <GEN0> line 652.


So i'm missing the 'domain_type' column.

I want to open up phpMyAdmin and add a new column... named the same as in the script error 'domain_type' and sync it with the script above to clear the error message.

Thanks.

Stephen.

I was wondering if anybody could help me with this as i haven't used phpMyAdmin before. I'm not even sure if i have it installed...

So i need to manually sync the column into
#!/usr/bin/perl
 
# Stephen McGowan
 
use strict;
use warnings;
 
use Bio::SeqIO;
use Data::Dumper;
use DBI;
use DBD::mysql;
use mysql;
 
# The point of this script is to grab some useful identifiers from a SwissProt .dat file, and pass
# them to a MySQL database with the BioSQL schema.
# These identifiers are UniProt name, Uniprot accession number, InterPro accession number, InterPro domain type.
# The standard script load_seqdatabase.pl does not fetch the InterPro domain name.
 
 
# connect to file and create object
 
my $file = 'load_seqdb_test_dat.txt';
my $DEBUG = 0;
# my $file = 'YEAST.dat';
my $inseq = Bio::SeqIO->new(-file   => "<$file", -format => "swiss");
my @parsed_entries = (); # storage for fields of interest
 
 
# connect to mysql db, localhost
 
my $dbh = DBI->connect('DBI:mysql:biosql:host=localhost:port=3306','root') or die "Could not connect.";
 
# get primary sequence tags
 
while ( my $seq = $inseq->next_seq ) {
        my $protein_ids = {
                uniprot_number => $seq->accession_number,
                uniprot_id         => $seq->id,
                interpro       => [],   # the InterPro refs are in the dblink annotations, which is in an array of hashes, accessed below
        };                                                      # this array will hold an anonymous reference to those hashes
 
        # Pull the dblink annotations from the file
        my @annotations_dblink = $seq->annotation->get_Annotations( 'dblink' );
 
        foreach my $annotation ( @annotations_dblink ) {
                my @dblinks = $annotation->hash_tree();
 
                foreach my $dblink ( @dblinks ) {
 
                        if ( $dblink->{database} eq 'InterPro' ) {  # Here the is the InterPro hash
                                push @{$protein_ids->{interpro}}, {             # We pass the reference to the array
                                        entry  => $dblink->{primary_id},        # using the keys of entry or domain to
                                        domain => $dblink->{optional_id},   # hold the id values
                                }
                        }
            }
        }
        push @parsed_entries, $protein_ids;     # moving the data to an array for passing below
        print Dumper $protein_ids if $DEBUG;    # Debug
        last if scalar @parsed_entries == 500;  # an bad character in the file located between 500 and 1000 lines in, needs troubleshooting
}
 
 
########## Enter data in MySQL ##############
 
my $bdb_biodatabase_id = '1';
my $bdb_name = "SwissProt";
 
$dbh->do("truncate dbxref");
$dbh->do("truncate bioentry");
$dbh->do("truncate biodatabase");
 
my $sth = $dbh->prepare("insert into biodatabase(biodatabase_id, name)
values ('$bdb_biodatabase_id', '$bdb_name')");
 
$sth->execute() or die "could not enter data";
 
 
# bioentry table
 
my $be_biodatabase_id = 1;      # must agree with DB or DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails
my $be_bioentry_id = '';        # must be unique or produces the error: DBD::mysql::st execute failed: Duplicate entry
my $be_taxon_id = '';           # must agree with DB or DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails
my $be_name = '';                       # not essential, but if empty can cause trouble in retrieval
my $be_accession = '';          # if this is duplicate, produces the error: DBD::mysql::st execute failed: Duplicate entry
# my $be_identifier = '';       # must be unique or produces the error: DBD::mysql::st execute failed: Duplicate entry, set to $be_bioentry_id below
# my $be_division = '';         # not essential, and not used here
# my $be_description = '';      # not essential, and not used here
my $be_version = '';            # not essential
 
 
# dbxref table
 
my $dbx_dbxref_id = '';
my $dbx_dbname = 'InterPro';
my $dbx_accession = '';
my $dbx_domain_type = '';  # new field addeded manual to BioSQL schema to hold the InterPro domain type
my $dbx_version = '';      # used in info.php to bind bioentry to dbxref with a common numerical entry, see below dbxref for loop
                                
# pass the extracted fields to the appropriate tables in BioSQL
                                        
for ( my $i=0; $i < @parsed_entries; $i++ ){
        $be_bioentry_id = $i + 1;
        $be_name = $parsed_entries[$i] {uniprot_id};
        print "$be_name\n" if $DEBUG;
        $be_accession = $parsed_entries[$i] {uniprot_number};
        print "$be_accession\n" if $DEBUG;
        
        $sth = $dbh->prepare("insert into bioentry(bioentry_id, biodatabase_id, name, accession, identifier, version)
        values ('$be_bioentry_id', '$be_biodatabase_id', '$be_name', '$be_accession', '$be_bioentry_id', '$be_version')");
 
        $sth->execute() or die "could not enter data";
 
        for ( my $j = 0; $j < @{$parsed_entries[$i]->{interpro}}; $j++ ) {
                $dbx_accession = $parsed_entries[$i]{interpro}->[$j]->{entry};
                print "$dbx_accession\n" if $DEBUG;
                $dbx_domain_type = $parsed_entries[$i]{interpro}->[$j]->{domain};
                print "$dbx_domain_type\n" if $DEBUG;
 
                $sth = $dbh->prepare("insert into dbxref(dbxref_id, dbname, accession, domain_type, version)
                values (NULL, '$dbx_dbname', '$dbx_accession', '$dbx_domain_type', '$be_bioentry_id')");
 
                $sth->execute() or die "could not enter data";
        }
}
exit;

Open in new window

0
Comment
Question by:StephenMcGowan
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
Fairlight2cx earned 2000 total points
ID: 24800325
Okay, I don't know what data type domain_type needs to be, or I'd give you -exact- syntax.  But forget phpMyAdmin.  I know you've got the mysql command line client handy, as you used it earlier today in another question.

Here's how you'd add a column to that table.

$ mysql
mysql> use biosql;
mysql> alter table dbxref add domain_type varchar(20) after accession;

This is going off memory that the column name in the actual table was named just "accession", from the other question this morning.  The reason we're putting in the "after accession" is so that domain_type is inserted into the right order, so your arguments in the calls against that table for the INSERT actually match up and you don't have to re-order the variables in the code.  Normally it would just be added last.  This fixes that issue.

Change varchar(20) to whatever data type you need in the above definition.
0
 

Author Comment

by:StephenMcGowan
ID: 24807776
the data type i think will be "varchar", so would the sytax stay the same?

i.e.

$ mysql
mysql> use biosql;
mysql> alter table dbxref add domain_type varchar(20) after accession;

?

Thanks again FairL

Ste.
0
 
LVL 7

Expert Comment

by:Fairlight2cx
ID: 24815371
Yup.  that'd give you a 20char max field.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question