Solved

phpMyAdmin and column addition

Posted on 2009-07-07
4
563 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
4 Comments
 
LVL 7

Accepted Solution

by:
Fairlight2cx earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use md5 hashing 3 25
Php pie charts 3 26
PHP - AJAX and MySQL it works only if the value is a number 12 33
How to convert my query to the proper format? 5 15
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now