Retrieval of DArT Data From ICIS
From ICISWiki
Contents |
PostgreSQL
Steps in Retrieving genotype and phenotype study
- Retrieve the Genotype Study
SELECT get_dataset ('DArT_Genotype', 'genotype'); - Create an array implementation of the Genotype Study
SELECT create_dataset_array ('genotype', genoarray') - Retrieve the Phenotype Study
SELECT get_dataset ('UWS0F2','phenodata') - Join the phenotype and Genotype Study
SELECT * INTO phenogeno FROM genoarray, phenodata where genoarray.gid = phenodata."GID"
PostgreSQL Functions/Stored Procedures
Below are the codes for the procedures used in retrieving the phenotypic and genotypic data
get_dataset (studyname, tablename)
This query calls the get_dataset function.
-- Function: get_dataset(text, text)
-- DROP FUNCTION get_dataset(text, text);
CREATE OR REPLACE FUNCTION get_dataset(text, text)
RETURNS int4 AS
$BODY$
DECLARE
ntable ALIAS FOR $1;
var_sname ALIAS FOR $2;
n INT;
var_studyid INT;
rec RECORD;
vrec RECORD;
rec2 RECORD;
rec_factor RECORD;
rec_var RECORD;
BEGIN
SELECT studyid INTO var_studyid FROM study where sname = var_sname;
SELECT factorid INTO n FROM factor
INNER JOIN study on study.studyid = factor.studyid
WHERE study.studyid = var_studyid and factor.scaleid = 134;
execute 'CREATE TABLE ' ||quote_ident(ntable) ||'( ounitid INT )';
--ADD columns for factor names
FOR rec in
execute 'SELECT distinct factor.factorid,factor.fname, factor.labelid, factor.ltype
FROM factor
LEFT JOIN study on factor.studyid = study.studyid
WHERE study.studyid = '||var_studyid ||'
and factor.factorid != (SELECT factorid FROM factor inner join study on
study.studyid = factor.studyid
WHERE study.studyid = '||var_studyid||' and factor.labelid ='||n||')'
LOOP
IF rec.ltype = 'N' THEN
execute 'ALTER TABLE ' ||quote_ident(ntable) ||
' ADD COLUMN `' || quote_ident(rec.fname) || '` DOUBLE PRECISION';
ELSE
execute 'ALTER TABLE ' ||quote_ident(ntable) ||
' ADD COLUMN `' || quote_ident(rec.fname) || '` VARCHAR';
END IF;
END LOOP;
--ADD columns for the variate names
FOR vrec in
execute 'SELECT distinct variate.variatid,variate.vname, variate.dtype
FROM variate
LEFT JOIN study on variate.studyid = study.studyid
WHERE study.studyid = '||var_studyid
LOOP
IF vrec.dtype = 'N' THEN
execute 'ALTER TABLE ' ||quote_ident(ntable) ||
' ADD COLUMN `' || quote_ident vrec.vname) || '` DOUBLE PRECISION';
ELSE
execute 'ALTER TABLE ' ||quote_ident(ntable) ||
' ADD COLUMN `' || quote_ident(vrec.vname) || '` VARCHAR';
END IF;
END LOOP;
-- Populate table by ounitid except the global ounitid of the study
FOR rec2 in
EXECUTE 'SELECT distinct ounitid from oindex
LEFT JOIN factor ON factor.factorid = oindex.factorid
LEFT JOIN study on study.studyid = factor.studyid
WHERE study.studyid = '|| var_studyid || ' and
factor.labelid != '||n
LOOP
execute 'INSERT INTO ' || quote_ident(ntable)||
' (ounitid) VALUES ('||rec2.ounitid||')';
END LOOP;
--*EXECUTE 'ALTER TABLE' || quote_ident(ntable)||
' ADD CONSTRAINT p_key PRIMARY KEY (ounitid)';
--GET ALL FACTOR values
OR rec_factor IN
execute 'SELECT distinct factor.factorid,factor.fname,
factor.labelid, factor.ltype
FROM factor
LEFT JOIN study on factor.studyid = study.studyid
WHERE study.studyid = '||var_studyid ||' and factor.factorid != '||n
LOOP
IF rec_factor.ltype = 'N' THEN
EXECUTE 'UPDATE '||quote_ident(ntable)||'
SET ' || quote_ident(rec_factor.fname) || ' = x.lvalue
FROM(
(SELECT d.lvalue, oindex.ounitid
FROM oindex
INNER JOIN
(SELECT study.studyid, factor.factorid,factor.fname,
level_n.labelid,level_n.levelno,level_n.lvalue
FROM level_n
INNER JOIN factor
ON factor.labelid = level_n.labelid
INNER JOIN study
ON study.studyid = factor.studyid
WHERE study.studyid = '|| var_studyid || '
order by levelno
) as d on d.levelno = oindex.levelno
WHERE
d.fname = '||quote_literal(rec_factor.fname) ||'
and d.labelid = '||rec_factor.labelid ||'
and oindex.factorid = '||rec_factor.factorid ||')
) as x
WHERE
'||quote_ident(ntable) ||'.ounitid = x.ounitid ';
ELSE
EXECUTE 'UPDATE '||quote_ident(ntable)||' SET '
|| quote_ident(rec_factor.fname) || ' = x.lvalue
FROM(
(SELECT d.lvalue, oindex.ounitid
FROM oindex
INNER JOIN
(SELECT study.studyid, factor.factorid,factor.fname,
level_c.labelid,level_c.levelno,level_c.lvalue
FROM level_c
INNER JOIN factor
ON factor.labelid = level_c.labelid
INNER JOIN study
ON study.studyid = factor.studyid
WHERE study.studyid = '|| var_studyid ||
'order by levelno ) as d
on d.levelno = oindex.levelno
WHERE
d.fname = `'||quote_literal(rec_factor.fname) ||'` and
d.labelid = '||rec_factor.labelid ||'
and oindex.factorid = '||rec_factor.factorid ||')
) as x
WHERE
'||quote_ident(ntable) ||'.ounitid = x.ounitid ';
END IF;
END LOOP;
--- GET ALL VARIATES
FOR rec_var IN
execute 'SELECT distinct variate.variatid,variate.vname, variate.dtype
FROM variate
LEFT JOIN study on variate.studyid = study.studyid
WHERE study.studyid = '||var_studyid
LOOP
IF rec_var.dtype = 'N' THEN
EXECUTE 'UPDATE '||quote_ident(ntable)||
' SET `' || quote_ident(rec_var.vname) || '` = x.dvalue
FROM(
SELECT dvalue, ounitid FROM data_n
WHERE data_n.variatid = '||rec_var.variatid||
') as x
WHERE '||quote_ident(ntable)||'.ounitid = x.ounitid ';
ELSE
EXECUTE 'UPDATE '||quote_ident(ntable)||
'SET `' || quote_ident(rec_var.vname) || '` = x.dvalue
FROM(
SELECT dvalue, ounitid from data_c WHERE data_c.variatid
'||rec_var.variatid||') as x
WHERE '||quote_ident(ntable)||'.ounitid = x.ounitid ';
END IF;
END LOOP;
RETURN n;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
ALTER FUNCTION get_dataset(text, text) OWNER TO postgres;
create_array_dataset (tablename,studyname)
This procedure creates an array representation of the genotyping dataset.
-- Function: create_array_dataset(text,text)
-- DROP FUNCTION create_array_dataset(text,text);
CREATE OR REPLACE FUNCTION create_array_dataset(text,text)
RETURNS void AS
$BODY$
DECLARE
var_sname ALIAS FOR $1;
var_tablename ALIAS FRO $2;
var_studyid INT;
var_gid INT;
var_mdid INT;
rec_gid RECORD;
rec_data RECORD;
BEGIN
FOR rec_gid IN
SELECT DISTINCT "GID" as gid from genodata
LOOP
FOR rec_data IN
SELECT distinct ounitid, "MARKER" as mdid, "ALLELE" as alleleid
FROM genodata WHERE genodata."GID" = rec_gid.gid
LOOP
IF (SELECT m FROM geno_array WHERE gid = rec_gid.gid) IS NULL THEN
UPDATE geno_array SET m = (
ARRAY[[(SELECT "MARKER" FROM genodata
where ounitid = rec_data.ounitid),
(SELECT "ALLELE" FROM genodata
where ounitid = rec_data.ounitid)]])
WHERE geno_array.gid = rec_gid.gid;
ELSE
UPDATE geno_array SET m = (
SELECT array_cat(
(select m from geno_array where gid = rec_gid.gid),
(ARRAY[[(SELECT "MARKER" FROM genodata
WHERE ounitid = rec_data.ounitid),
SELECT "ALLELE" FROM genodata
WHERE ounitid = rec_data.ounitid)]]
)
)
)
WHERE geno_array.gid = rec_gid.gid;
END IF;
END LOOP;
END LOOP;
--select distinct marker from level_n table
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
MySQL
The Following is the MySQL script equivalent to the PostgreSQL script create_dataset_array.
create_allele_string()
DELIMITER $$;
DROP PROCEDURE IF EXISTS `genotyping_scripts`.`create_allele_string`$$
CREATE PROCEDURE `create_allele_string`()
BEGIN
DECLARE done1 BOOL default FALSE;
DECLARE var_gid INT;
DECLARE new_gid INT;
DEClARE var_marker INT;
DECLARE var_allele INT;
DECLARE m1 TEXT ;
DECLARE m_cursor CURSOR FOR
SELECT DISTINCT marker FROM gm_table ORDER BY marker;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = TRUE;
INSERT INTO geno_string (gid) SELECT DISTINCT gid FROM geno_table ORDER by gid;
OPEN m_cursor;
loop1: LOOP
FETCH m_cursor INTO var_marker;
IF done1 THEN
CLOSE m_cursor;
LEAVE loop1;
END IF;
UPDATE geno_string
INNER JOIN (select gid as g, max(allele) AS a
FROM gm_table where marker = var_marker GROUP BY g
) AS x
SET m = IF (m IS NULL,x.a,concat(geno_string.m,'|',x.a))
WHERE x.g = geno_string.gid;
END LOOP;
END$$
DELIMITER ;$$
create_table`(IN var_ntable VARCHAR(25),IN var_sname1 VARCHAR(25), IN var_sname2 VARCHAR(25) )
Create_table routine combines all markers and gids used in two genotyping studies and stores it in a table (geno_table).
DELIMITER $$;
DROP PROCEDURE IF EXISTS `genotype-myisam`.`create_table`$$
CREATE PROCEDURE `create_table`(IN var_ntable VARCHAR(25),IN var_sname1 VARCHAR(25), IN var_sname2 VARCHAR(25) )
BEGIN
DECLARE var_studyid1 INT;
DECLARE var_studyid2 INT;
DECLARE var_gfac1 INT;
DECLARE var_gfac2 INT;
DECLARE var_mfac1 INT;
DECLARE var_mfac2 INT;
DECLARE var_varid1 INT;
DECLARE var_varid2 INT;
DECLARE var_mscaleid INT DEFAULT -16 ;
DECLARE var_gscaleid INT DEFAULT 91 ;
DECLARE var_vscaleid INT DEFAULt -19;
DECLARE var_ounitid INT;
DECLARE m1 TEXT;
-- GET the study ids for the two Studies
SELECT studyid INTO var_studyid1 FROM study WHERE study.sname = var_sname1;
SELECT studyid INTO var_studyid2 FROM study WHERE study.sname = var_sname2;
-- GET the marker factorids
SELECT factorid INTO var_mfac1 FROM factor
WHERE factor.studyid = var_studyid1
AND factor.scaleid = var_mscaleid;
SELECT factorid INTO var_mfac2 FROM factor
WHERE factor.studyid = var_studyid2
AND factor.scaleid = var_mscaleid;
-- GET the gid factorids
SELECT factorid INTO var_gfac1 FROM factor
WHERE studyid = var_studyid1
AND factor.scaleid = var_gscaleid;
SELECT factorid INTO var_gfac2 FROM factor
WHERE studyid = var_studyid2
AND factor.scaleid = var_gscaleid;
-- GET the variate IDs
SELECT variatid INTO var_varid1 FROM variate
WHERE studyid = var_studyid1
AND variate.vname = 'ALLELEID'
AND variate.scaleid = var_vscaleid;
SELECT variatid INTO var_varid2 FROM variate
WHERE studyid = var_studyid2
AND variate.vname = 'ALLELEID'
AND variate.scaleid = var_vscaleid;
CREATE TABLE gid_marker1
(
glevel INT,
gid INT,
mlevel INT,
marker INT
) ;
CREATE TABLE gid_marker2
(
glevel INT,
gid INT,
mlevel INT,
marker INT
) ;
CREATE TABLE gid_marker
(
glevel INT,
gid INT,
mlevel INT,
marker INT
) ;
INSERT INTO gid_marker1 (glevel,gid,mlevel,marker)
SELECT x.levelno AS glevel, x.gid , y.levelno AS mlevel, y.marker
FROM
(
select distinct level_n.levelno,level_n.lvalue as gid FROM study
INNER JOIN factor ON study.studyid = factor.studyid
INNER JOIN level_n ON level_n.factorid = factor.factorid
AND (study.studyid = var_studyid1)
AND (factor.scaleid = var_gscaleid)
AND level_n.labelid = factor.labelid
ORDER BY gid
)AS x,
(
SELECT DISTINCT level_n.levelno,level_n.lvalue AS marker FROM study
INNER JOIN factor ON study.studyid = factor.studyid
INNER JOIN level_n ON level_n.factorid = factor.factorid
AND study.studyid = var_studyid1
AND factor.scaleid = var_mscaleid
AND level_n.labelid = factor.labelid
ORDER BY marker
) AS y;
INSERT INTO gid_marker2
SELECT x.levelno AS glevel, x.gid , y.levelno AS mlevel, y.marker
FROM
-- GET distinct GID levels
(SELECT DISTINCT level_n.levelno,level_n.lvalue as gid FROM study
INNER JOIN factor ON study.studyid = factor.studyid
INNER JOIN level_n ON level_n.factorid = factor.factorid
AND (study.studyid= var_studyid2)
AND (factor.scaleid = var_gscaleid)
AND level_n.labelid = factor.labelid
ORDER BY gid
) AS x,
(SELECT DISTINCT level_n.levelno,level_n.lvalue as marker FROM study
INNER JOIN factor ON study.studyid = factor.studyid
INNER JOIN level_n ON level_n.factorid = factor.factorid
AND study.studyid= var_studyid2
AND factor.scaleid = var_mscaleid
AND level_n.labelid = factor.labelid
ORDER BY marker
) AS y;
INSERT INTO gid_marker
SELECT DISTINCT x.glevel,x.gid,y.mlevel, y.marker
FROM
(
SELECT DISTINCT gid,glevel FROM gid_marker1
WHERE gid NOT IN (SELECT DISTINCT gid FROM gid_marker2)
) AS x,
(
SELECT distinct marker, mlevel FROM gid_marker2
) AS y;
-- GET GIDS in study2 NOT in study1 and pair it with the markers in study1
INSERT INTO gid_marker
SELECT DISTINCT x.glevel,x.gid,y.mlevel, y.marker
FROM
(
SELECT DISTINCT gid,glevel FROM gid_marker2
WHERE gid NOT IN (SELECT distinct gid from gid_marker1)
) AS x,
(
SELECT distinct marker, mlevel FROM gid_marker1
) AS y;
INSERT INTO gid_marker select * FROM gid_marker1;
INSERT INTO gid_marker select * FROM gid_marker2;
DROP TABLE gid_marker1;
DROP TABle gid_marker2;
CREATE TABLE exist_table
(
ounitid INT,
m INT,
g INT,
allele INT
) ;
INSERT INTO exist_table (ounitid,m,g,allele)
SELECT DISTINCT mtable.ounitid, mtable.levelno AS m, gtable.levelno AS g,mtable.allele
FROM
(SELECT data_n.ounitid, factorid, levelno,dvalue AS allele FROM oindex,data_n
WHERE data_n.ounitid = oindex.ounitid
AND (data_n.variatid = var_varid1 or data_n.variatid = var_varid2)
AND (factorid = var_mfac1 or factorid = var_mfac2)
) AS mtable
LEFT OUTER JOIN
(SELECT ounitid, factorid, levelno FROM oindex
WHERE (factorid = var_gfac1 or factorid = var_gfac2)
) AS gtable
ON gtable.ounitid = mtable.ounitid;
-- CREATE geno_table
CREATE TABLE geno_table
(
glevel INT,
gid INT,
mlevel INT,
marker INT,
ounitid INT,
allele INT
);
INSERT INTO geno_table (glevel,gid, mlevel,marker, ounitid, allele)
SELECT glevel,gid, mlevel,marker, ounitid, allele
FROM gid_marker
LEFT OUTER JOIN exist_table
ON exist_table.g = gid_marker.glevel AND exist_table.m = gid_marker.mlevel;
-- UPDATE NULL ALLELES into 0;
UPDATE geno_table SET allele = 0 WHERE ounitid IS NULL;
END$$
DELIMITER ;$$
get_dataset(IN var_studyid INT, IN ntable VARCHAR(25))
This routine is the MySQL equivalent of the PostgreSQL get_dataset procedure.
DELIMITER $$;
DROP PROCEDURE IF EXISTS `genotyping_scripts`.`get_dataset`$$
CREATE PROCEDURE `get_dataset`(IN var_studyid INT, IN ntable VARCHAR(25))
BEGIN
DECLARE done1 BOOL default FALSE;
DECLARE var_fname VARCHAR(25);
DECLARE var_labelid INT;
DECLARE var_ltype CHAR;
DECLARE var_vname VARCHAR(50);
DECLARE var_factorid INT;
DECLARE var_variatid INT;
DECLARE var_dtype INT;
DECLARE var_slabelid INT;
DECLARE factor_cursor CURSOR FOR
SELECT distinct factor.factorid,factor.fname, factor.labelid, factor.ltype
FROM factor
LEFT JOIN study on factor.studyid = study.studyid
WHERE study.studyid = var_studyid
and factor.factorid != (SELECT factorid FROM factor
INNER JOIN study ON study.studyid = factor.studyid
WHERE study.studyid = var_studyid
AND factor.scaleid =134);
DECLARE variate_cursor CURSOR FOR
SELECT distinct variate.variatid,variate.vname, variate.dtype
FROM variate
LEFT JOIN study on variate.studyid = study.studyid
WHERE study.studyid = var_studyid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = TRUE;
-- SELECT studyid INTO var_studyid FROM study where sname = var_sname;
-- SELECT factorid INTO n FROM factor INNER JOIN study on study.studyid = factor.studyid
-- WHERE study.studyid = var_studyid and factor.scaleid = 134;
SELECT factorid INTO var_slabelid FROM factor
INNER JOIN study ON study.studyid = factor.studyid
WHERE study.studyid = var_studyid AND factor.scaleid =134;
SET @q1 = concat('CREATE TABLE ', ntable,' (ounitid INT )');
PREPARE stmnt1 FROM @q1;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
-- ADD columns for factor names
OPEN factor_cursor;
factor_loop: LOOP
FETCH factor_cursor INTO var_factorid,var_fname, var_labelid, var_ltype;
IF done1 THEN
CLOSE factor_cursor;
LEAVE factor_loop;
END IF;
IF var_ltype = 'N' THEN
SET @q2 = concat('ALTER TABLE ', ntable , ' ADD COLUMN ',var_fname, ' INT');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
ELSE
SET @q2 = concat('ALTER TABLE ', ntable ,' ADD COLUMN ',var_fname, ' VARCHAR(25)');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
END IF;
END LOOP;
-- ADD columns for the variate names
SET done1 = FALSE;
OPEN variate_cursor;
variate_loop: LOOP
FETCH variate_cursor INTO var_variatid,var_vname, var_dtype;
IF done1 THEN
CLOSE variate_cursor;
LEAVE variate_loop;
END IF;
IF var_dtype = 'N' THEN
SET @q2 = concat('ALTER TABLE ',ntable,' ADD COLUMN ',var_vname, ' INT');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
ELSE
SET @q2 = concat('ALTER TABLE ',ntable,' ADD COLUMN ',var_vname,
' VARCHAR(25)');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
END IF;
END LOOP;
-- Insert OUNITIDs into the new table
SET @q2 = concat('INSERT INTO ', ntable , '(ounitid)
SELECT distinct ounitid from oindex
LEFT JOIN factor ON factor.factorid = oindex.factorid
LEFT JOIN study ON study.studyid = factor.studyid
WHERE study.studyid = ', var_studyid,'
AND factor.labelid != ', var_slabelid);
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
-- GET ALL FACTOR values
SET done1 = FALSE;
OPEN factor_cursor;
fval_loop: LOOP
FETCH factor_cursor INTO var_factorid,var_fname, var_labelid, var_ltype;
IF done1 THEN
CLOSE factor_cursor;
LEAVE fval_loop;
END IF;
IF var_ltype = 'C' THEN
SET @q2 = concat('UPDATE ', ntable, 'INNER JOIN
(SELECT study.studyid, factor.factorid,factor.fname,
level_c.labelid,level_c.levelno,level_c.lvalue, oindex.ounitid
FROM oindex
INNER JOIN level_c ON level_c.levelno = oindex.levelno
INNER JOIN factor ON factor.labelid = level_c.labelid
INNER JOIN study ON study.studyid = factor.studyid
WHERE study.studyid = ',var_studyid,
' AND factor.fname = \, var_fname,
'\' AND level_c.labelid = ' ,var_labelid,
' AND oindex.factorid = ' ,var_factorid,
' AND level_c.levelno = oindex.levelno
ORDER BY levelno
) as d
SET ',ntable,'.',var_fname,' = d.lvalue
WHERE ', ntable,'.ounitid = d.ounitid');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
ELSE
SET @q2 = concat('UPDATE ', ntable ,' INNER JOIN
(SELECT study.studyid, factor.factorid,factor.fname,
level_n.labelid,level_n.levelno,level_n.lvalue, oindex.ounitid
FROM oindex
INNER JOIN level_n ON level_n.levelno = oindex.levelno
INNER JOIN factor ON factor.labelid = level_n.labelid
INNER JOIN study ON study.studyid = factor.studyid
WHERE study.studyid = ',var_studyid ,
' AND factor.fname = \, var_fname ,
'\' AND level_n.labelid = ' , var_labelid ,
' AND oindex.factorid = ' , var_factorid ,
' AND level_n.levelno = oindex.levelno
order by levelno
) as d
SET ',ntable,'.',var_fname,' = d.lvalue
WHERE ', ntable,'.ounitid = d.ounitid');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
END IF;
END LOOP;
-- GET ALL VARIATES
SET done1 = FALSE;
OPEN variate_cursor;
var_loop: LOOP
FETCH variate_cursor INTO var_variatid,var_vname, var_dtype;
IF done1 THEN
CLOSE variate_cursor;
LEAVE var_loop;
END IF;
IF var_dtype = 'N' THEN
SET @q2 = concat( 'UPDATE ', ntable ,' INNER JOIN
(SELECT dvalue, ounitid FROM data_n
WHERE data_n.variatid = \,var_vname,'\'
) as x
SET ',var_vname,' = x.dvalue
WHERE', ntable,'.ounitid = x.ounitid ');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
ELSE
SET @q2 = concat( 'UPDATE ', ntable ,' INNER JOIN
(SELECT dvalue, ounitid FROM data_c
WHERE data_n.variatid = \,var_vname,'\'
) as x
SET ',var_vname,' = x.dvalue
WHERE', ntable,'.ounitid = x.ounitid ');
PREPARE stmnt FROM @q2;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
END IF;
END LOOP;
END$$
DELIMITER ;$$
Java
This is a simple java program for retrieving the dataset from the PostgreSQL database. The program connects to the database via JDBC.
import java.sql.*;
import java.util.Arrays;
import java.lang.*;
public class LoadPostgresDriver {
public static void main(String[] args) {
double arr[];
Array x;
int i;
String str;
String dbURL = "jdbc:postgresql://localhost/databasename";
try{
Class c = Class.forName("org.postgresql.Driver");
System.out.println("Loaded "+c);
System.out.println("Getting Connection");
Connection conn = DriverManager.getConnection(dbURL,"username","password");
SQLWarning warn = conn.getWarnings();
while (warn!=null){
System.out.println("SQLState : "+ warn.getSQLState());
warn = warn.getNextWarning();
}
Statement stmnt = conn.createStatement();
ResultSet rs = stmnt.executeQuery(" select * from genotype");
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()){
str = rs.getString("gid");
System.out.println(str +"\t");
x = rs.getArray(2);
arr = (double []) x.getArray();
for (i=0; i < arr.length; i++)
{
System.out.print(arr[i]+ " \t" );
}
for (i = 2;i<=rsmd.getColumnCount();i++)
{
System.out.print(rs.getString(i)+"\t");
}
System.out.println();
}
rs.close();
stmnt.close();
conn.close();
}catch(ClassNotFoundException ex){
System.out.println("can't load driver " + ex);
}catch(SQLException e){
System.out.println("Database access failed " + e);
}
}
}
