| $Id: odbc.txt 2850 2009-02-23 22:27:10Z unsaved $ | |
| This file documents various aspects of HyerSQL's ODBC support. | |
| It does not document driver-side issues (at least once we can discriminate), | |
| since those issues will be documented with the dedicated ODBC driver product. | |
| Critical server-side todo items. | |
| + Support passing of size/precision/scale values for columns to the ODBC | |
| client. I guess the client uses this for display formatting purposes (I | |
| don't know any other reason why the client would want the value, since | |
| the database, not the client, enforces conformance). | |
| It's quite possible that no real purpose is served. In that case we can | |
| greatly improve efficiency of the PgType class by sharing a static list | |
| of elements instead of making tons of PgType instances. | |
| + Support binary database data types (BINARY, OBJECT, etc.) | |
| + Support TIME and DATETIME database data types (DATEs already supported) | |
| (May want to postpone *INTERVAL* types for another iteration). | |
| + Verify tactic used to generate numeric "object identifiers" for tables | |
| is adequate: Java hash of String "schema.tablename". | |
| + Fix column oid (numeric object identifier) generation tactic. I am | |
| just returning the sequence in the generated result set. The problem | |
| with this tactic is that the number for a column is dependent upon | |
| the query instead of on the table definition. | |
| KNOWN LIMITATIONS | |
| Limitations corresponding to the TODO items above. | |
| Can't fetch the same column (or virtual column) twice from JDBC, even with | |
| different getters like rs.getInt() and rs.getObject(). I don't know what | |
| product is responsible for this limitation. | |
| No metadata querying ability, other than the ones implicit with setting | |
| up prepared statements (including the 'D'). | |
| Don't know if it is a bug with Sun's jdbc:odbc or with psqlodbc, but | |
| all fetchsize settings are rejected with a message saying that the | |
| value is unacceptable. | |
| psqlodbc can handle compound commands (*;*) only in SIMPLE (Q) mode; and | |
| even that needs to be tested to see if server will generate the expected | |
| number of reply packets. | |
| POSTGRESQL DEPENDENCIES TO BE ELIMINATED (uncertain whether will handle these | |
| on client or server side). | |
| Search ServerConnection.java (from odbcproto1 branch) for comments about | |
| "stub" and "swallow", ignoring stuff about client side swallowing. | |
| Over-the-wire Postgresql-specific SQL commands that must be handled. | |
| SELECT | |
| PREPARE/EXECUTE/DEALLOCATE | |
| SET/SHOW | |
| DECLARE/FETCH/MOVE/CLOSE | |
| psqlodbc source code locations | |
| The info30.c file seems to be good (ODBC v.3 metadata) | |
| connect.c | |
| select oid,... upon startup. | |
| current_schema() | |
| convert.c | |
| Uses ctid pseudo-column | |
| convert_escape() generates queries. Func. should probably be | |
| eliminated. | |
| copy_statement_with_parameters looks dependent upon PostgresQL | |
| PREPARE SQL statements. | |
| (this calls Prepare_and_convert(), which also depends on them). | |
| multibyte.c | |
| CC_lookup_cs_new() Not run from Linux driver. Test whether | |
| called from CC_lookup_characterset() from Windows ANSI client. | |
| parse.c | |
| CheckHasOids() | |
| getCOLIfromTable() | |
| results.c | |
| tupleExists() uses ctid pseudo-column | |
| Literal commands known to be sent over the wire: | |
| select n.nspname, c.relname, a.attname, a.atttypid,t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 3411470544) inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum | |
| select current_schema() | |
| select oid, typbasetype from pg_type where typname = 'lo' | |
| deallocate <cursorname> (all cases I've encountered so far seem to be | |
| an over-zealous attempt by the driver to free cursors managed completely | |
| EXTENDED protocol, and therefore my server code can handle these). | |
| set client_encoding to... | |
| Literal commands I see in code, but have not yet taken the time to confirm or | |
| reject as will ever be sent to our server. Repeat: THESE STATEMENTS MAY | |
| NEVER BE SENT. I will have a definitive answer about several of these shortly.: | |
| "select oid, 0 from pg_type where typname='" PG_TYPE_LO_NAME "'" | |
| Dynamic queries with: " where ctid = '(0,0)';select \"ctid"... from... | |
| All sorts of metadata/data-dictionary stuff in "info.c" file. | |
| select relhasoids, c.oid from pg_class c, pg_namespace n where relname = '%s' and nspname = '%s' and c.relnamespace = n.oid" | |
| "select a.attname, a.atttypid from pg_index i, pg_attribute a where indrelid=%u and indnatts=1 and indisunique | |
| and indexprs is null and indpred is null and i.indrelid = a.attrelid and a.attnum=i.indkey[0] and attnotnull and atttypid in (%d, %d)" | |
| "select nspname from pg_namespace n, pg_class c" | |
| "select 1 from \"%s\" where ctid = '(%d,%d)'" | |
| Complex dynamic parsing or genereration code in convert.c:convert_escape(). | |
| SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL * | |
| show max_identifier_length | |
| Show Client_Encoding | |
| DATA TYPE SUPPORT MATRIX (type synonyms not listed) @=Yet-to-be-Implemented | |
| HyperSQL type wid prec scale | Driver type oid adtsz atttypm | |
| --------------------------- --- ---- ----- - ----------- --- --- --- | |
| TINYINT 8 3 0 | HSQL_TINYINT 9999 1 -1 | |
| SMALLINT 16 5 0 | int2 21 2 -1 | |
| INTEGER 32 10 0 | int4 23 4 -1 | |
| BIGINT 64 19 0 | int8 20 8 -1 | |
| NUMERIC(p?,s?) [100] [0] | numeric 1700 -1 [-1]** | |
| FLOAT(p?) 128 [0] 0 | float8 701 8 -1** | |
| DOUBLE 128 0 0 | float8 701 8 -1 | |
| BOOLEAN 0 0 | bool 16 1 -1 | |
| CHAR(1l)* = [1]++ 0 | bpchar 1042 -1 [1] | |
| VARCHAR(1l) [0] 0 | varchar 1043 -1 [-1] | |
| If precision unlimited/0: | text 25 -1 -1 | |
| LITERAL, simple atomic str+:CHARACTER len 0 | unknown 705 -2 -1 | |
| DERIVED, compound str+: VARCHAR prtlen 0 | text 25 -1 -1 | |
| CLOB(1l) [0] 0 | @ | |
| BINARY(1l)* [0] 0 | bytea 17 -1 -1 | |
| VARBINARY(1l) [0] 0 | bytea 17 -1 -1 | |
| BLOB(1l) [0] 0 | @ | |
| BIT(1l)* [1] 0 | bit 1560 -1 -1 | |
| BIT VARYING(1l) [0] 0 | varbit 1562 -1 -1 | |
| OTHER 0 0 | @ | |
| DATE 0 0 | date 1082 4 -1 | |
| TIME(p0) 0 [0] | time 1083 8 [-1] | |
| TIME(p0) WITH TIME ZONE 0 [0] | time_with_tmzone 1266 12 [-1] | |
| TIMESTAMP(p0) 0 [6] | timestamp_no_tmzone 1114 8[-1] | |
| TIMESTAMP (p0) WITH TIME ZONE 0 [6] | datetime ?1184 8 [-1]***** | |
| INTERVAL...(p2,p0) [2] [6] | tinterval 1186 16 [-1]**** | |
| * these types present at least a facade of data values always padded to the | |
| specified length. | |
| ** atttypmod for numerics determines column size and scale. | |
| atttypmod = (precision << 16) + scale + 4 | |
| (but there seems to be a bug in psqlodbc where this still does not work). | |
| *** Postgresql seems to use the FLOAT(x) precisions specifier just to decide | |
| whether to create a float4 or float8 column. | |
| **** I get atttypmod value of 2147418111 for INTERVAL(3) | |
| = short of 32767 + short of -1 and 217418110 for INTERVAL(2) | |
| So far unsuccessful to reverse engineer atttypmods for INTERVALs. | |
| As of today, support for following HyperSQL INTERVAL variants: | |
| DAY TO SECOND, HOUR TO SECOND, MINUTE TO SECOND, SECOND. | |
| ***** Seems to be a driver bug here. Should return 1296 for TIMESTAMP, not | |
| 1184 for DATETIME. | |
| +: If HyperSQL determines the output is a known constant size, it sets the | |
| precision to that. That seems to be the intention of Postgresql, but PG is | |
| not as smart about figuring out when expressions will resolve to a constant. | |
| When combining a constant and a col., HyperSQL confusingly some really | |
| crazy lengths. I haven't figured out the method or intention. | |
| ++: This defaults to 1 instead of 0 if sql.enforce_strict_size is set. | |
| Interval types seem to be wildly different between PG and HyperSQL. | |
| I believe that for all interval literals, precisions will automatically be set | |
| to preserve the specified value (unless you specify the precisions). | |
| Therefore, in practice, precision specs are usually only useful in col. defs. | |
| PG: INTERVAL(p); Where [0] <= p <= 6 (sub-sec. resolution, trunk/round vary!) | |
| Resolution years to microsecond. Literals: | |
| '1 12:59:10 ago' == '1 day 12 hours 59 min 10 sec ago' | |
| '1.234' = '1.234 sec' | |
| '-8 days - 12:59:10.472' | |
| HS: Resolution is either in months or (sub)seconds. Can't mix. | |
| p1 [2], p2[0]. | |
| INTERVAL NON_SECOND(p1) TO NON_SECOND | |
| Or | |
| INTERVAL NON_SECOND(p1) TO SECOND(p2) | |
| Or | |
| INTERVAL NON_SECOND(p1) | |
| Or | |
| INTERVAL SECOND(p1,p2) | |
| Literals | |
| INTERVAL '145 23:12:19.345' DAY(3) TO SECOND(3) | |
| = INTERVAL '3503:12:19.345' HOUR TO SECOND(3) | |
| INTERVAL '19.345' SECOND(4,3) | |
| REFERENCES | |
| JDBC type mapping tables. Section "Tables for Type Mapping" of Sun's | |
| "Getting Started with the JDBC API", which is section 9.9 of the current | |
| version, but this section number changes with document revisions. | |
| http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html#1038075 | |
| psqlodbc code repository. Module "psqlodbc" at | |
| :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/psqlodbc | |
| This is the code base that our odbc driver forked from. | |
| Protocol specification: | |
| http://www.postgresql.org/docs/8.3/interactive/protocol.html | |
| Article on ODBC escape sequences. May support these some day. | |
| http://www.ibprovider.com/eng/documentation/odbc_escape_sequences_eng.html |