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]
: @ [http://www.postgresql.org/docs/9.0/interactive/creating-cluster.html PostgreSQL documentation] - [http://www.postgresql.org/docs/9.0/interactive/manage-ag-createdb.html]
===Mapping Data Types, Metadata===* Issuing a Query Mapping Overview: Because data types in SQL and Processing data types in the Result @ Java programming language are not identical, there needs to be some mechanism for transferring data between an application using Java types and a database using SQL types. [http://download.oracle.com/javase/1.3/docs/guide/jdbc/getstart/mapping.html]: Mapping - Postgre [http://postgresql.org1045698.n5.nabble.com/PostgreSQL-data-types-mapped-Java-classes-for-JDBC-td3205204.html]* Data Types: Bytea - Binary - hex::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/documentation8.3R2/84jdbc/queryPostgres_Plus_Advanced_Server_JDBC_Connector_Guide-24.html 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 Documentationdocumentation “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: fetch sizebase64, cursorhex,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]<source lang = java>CREATE TABLE mytable (testcol BYTEA); INSERT INTO mytable (testcol) VALUES (DECODE('013d7d16d7ad4fefb61bd95b765c8ceb', 'hex'))
: 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===
* Functions & Operators
: String literals and string functions in postgreSQL @ [http://www.postgresql.org/docs/current/static/functions-string.html PostgreSQl Documentation]
: Pattern Matching [http://pgsqld.active-venture.com/functions-matching.html
: postgre [http://sql-info.de/postgresql/postgres-gotchas.html gotchas]
* Issuing a Query and Processing the Result @ [http://jdbc.postgresql.org/documentation/84/query.html PostgreSQL Documentation]
: fetch size, cursor,...
* Localization
: Character set support -
::The character set support in PostgreSQL allows you to store text in a variety of character sets (also called encodings), including single-byte character sets such as the ISO 8859 series and multiple-byte character sets such as EUC (Extended Unix Code), UTF-8, and Mule internal code. All supported character sets can be used transparently by clients, but a few are not supported for use within the server (that is, as a server-side encoding). The default character set is selected while initializing your PostgreSQL database cluster using initdb. It can be overridden when you create a database, so you can have multiple databases each with a different character set. An important restriction, however, is that each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.) Note! Not all client APIs support all the listed character sets. For example, the PostgreSQL JDBC driver does not support MULE_INTERNAL, LATIN6, LATIN8, and LATIN10. [http://www.postgresql.org/docs/current/static/multibyte.html]
==MySQL Specifications==
: The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is,they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.(From MySQL Manual)
: In MySQL SQL syntax the function HEX() can be used to get the hexadecimal value of one field of any data-type. [http://lab.lonerunners.net/blog/binary-data-fetching-through-sqli]
: Example
<source lang = java>
CREATE TABLE mytable (testcol BINARY(16));
INSERT INTO mytable (testcol)
VALUES (0x013d7d16d7ad4fefb61bd95b765c8ceb);
SELECT hex(testcol) FROM mytable; // prints: 013d7d16d7ad4fefb61bd95b765c8ceb
</source>
* SQL mode
: If strict SQL mode is not enabled and you assign a value to a BINARY or VARBINARY column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated.
: Compare Postgre and MySQL [http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html]
: Comparison based on Postgre website [http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007]
: 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]
