1,234
edits
Changes
no edit summary
==Java==
==PostgreSQL Specifications==
* PostgreSQL 8Installation (v 9.9.1 for J2EE/JDBC applications -1): [http://wikiwww.postgresql.org/images/a/acdownload/Pg_8.1_J2EE_v1.0.pdf]* Installation: -> for [http://www.postgresqlenterprisedb.orgcom/downloadproducts/pgdownload.do#windows windows]
* Tutorial @ [http://www.postgresql.org/docs/9.0/static/index.html PostgreSQl Documentation]
* Guide to report problems [http://wiki.postgresql.org/wiki/Guide_to_reporting_problems]
* PostgreSQL 8.1 for J2EE/JDBC applications [http://wiki.postgresql.org/images/a/ac/Pg_8.1_J2EE_v1.0.pdf]
* [http://www.postgresql.org/files/documentation/books/aw_pgsql/node200.html Reference Manual]
===Connection to the driver===
* JDBC Interface
: 1- XA JDBC driver for Postgre (needed for NexJ Express Model)
:: XA Data Source, as for what they mean: XA are for distributed transactions (as per the Open Group specificitions) and non-XA are not (transactions must be single-database). [http://www.theserverside.com/discussions/thread.tss?thread_id=21385]
:: Hierarchy for package [http://jdbc.postgresql.org/development/privateapi/org/postgresql/xa/package-tree.html org.postgresql.xa] - src of the hirearchy [http://grepcode.com/file/repository.jboss.com/maven2/postgresql/postgresql/8.3-603.jdbc3/org/postgresql/core/ConnectionFactory.java?av=f]
:: [http://jdbc.postgresql.org/todo.html Bugs]
:: Postgresql Limited XA Support[http://www.atomikos.com/Documentation/KnownProblems#Postgresql_Limited_XA_Support] - PostgreSQL XA support is limited in what it can do; using Postgresql is not recommended and not supported. If you do choose to use it, see this forum post for some of the problems.
: [http://www.postgresql.org/docs/8.4/static/app-psql.html psql]
: [http://www.postgresql.org/docs/9.0/interactive/app-pg-ctl.html working with server]
: psql [http://www.postgresonline.com/special_feature.php?sf_name=postgresql83_psql_cheatsheet&outputformat=html cheatsheet]
* GUI
: PGAdimn [http://www.pgadmin.org/visualtour.php Visual tour]
::A binary string is a sequence of bytes. Unlike character strings, which usually contain text data, binary strings are used to hold non-traditional data such as pictures, voice, or mixed media. [http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0008472.htm] & [http://www.enterprisedb.com/docs/en/8.3R2/jdbc/Postgres_Plus_Advanced_Server_JDBC_Connector_Guide-24.htm]
:: PostgreSQL can not store values of more than several thousands bytes within any data-type except large objects, nor can binary data be easily entered within single quotes. Instead, large objects (BLOB) are used to store very large values and binary data. From PostgreSQL documentation “String Functions and Operators”[6] we catch the function ENCODE(data bytea, type text). This function encodes binary data to an ASCII-only representation. The supported types are: base64, hex, escape.[http://lab.lonerunners.net/blog/binary-data-fetching-through-sqli]
:: [http://www.enterprisedb.com/docs/en/8.3R2/jdbc/Postgres_Plus_Advanced_Server_JDBC_Connector_Guide-24.htm Using BYTEA Data with Java]
:: Binary operations and functions: ENCODE & DECODE [http://www.postgresql.org/docs/current/static/functions-string.html]
:: Example [http://stackoverflow.com/questions/3103242/inserting-text-string-with-hex-into-postgresql-as-a-bytea]
INSERT INTO mytable (testcol)
VALUES (decodeDECODE('013d7d16d7ad4fefb61bd95b765c8ceb', 'hex'))
SELECT ENCODE(testcol, 'hex') FROM mytable; // prints: 013d7d16d7ad4fefb61bd95b765c8ceb
: Why using 'BINARY strings' instead of INTEGER unique identifiers [http://www.xcitestudios.com/blog/2010/01/31/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql/]
: Compare JDBC data types with PostgreSQL types [http://db.apache.org/ddlutils/databases/postgresql.html]
: bit & bit varying - for storing binaries [http://www.postgresql.org/docs/8.1/static/datatype-bit.html]
: BLOBs [http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html]
: Serial - Auto_Increment - Sequence
:: A sequence is a special kind of database object designed for generating unique numeric identifiers. It is typically used to generate artificial primary keys. Sequences are similar, but not identical, to the AUTO_INCREMENT concept in MySQL. Sequences are most commonly used via the serial pseudotype.[http://neilconway.org/docs/sequences/] - [http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL]
:: Adding sequence [http://pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-an-auto-increment-column-to-a-table.html]
===Functions===
: Compare Data types [http://www.linuxnetworks.de/doc/index.php/OpenDBX/DBMS_Datatypes]
: Compare MySQL, Oracle, PostgreSQL [http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html
: Converting MySQL to PostgreSQL -> data types & Functions & Command-line [http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL]
