Movatterモバイル変換

psqlODBC Configuration Options

Advanced Options 1/3 Dialog Box

• DEFAULTS: Press to this button restore the normal defaults for thesettings described below.
   
• Recognize Unique Indexes:Check this option.
   
• Use Declare/Fetch: Iftrue, the driver automatically uses declare cursor/fetch to handleSELECT statements and keeps 100 rows in a cache. This is mostly agreat advantage, especially if you are only interested in reading and notupdating. It results in the driver not sucking down lots of memoryto buffer the entire result set. If set to false, cursors will notbe used and the driver will retrieve the entire result set. For verylarge tables, this is very inefficient and may use up all the Windows memory/resources.However, it may handle updates better since the tables are not kept open,as they are when using cursors. This was the style of the old podbc32driver. However, the behavior of the memory allocation is much improvedso even when not using cursors, performance should at least be better thanthe old podbc32.
   
• CommLog (C:\psqlodbc_xxxx.log):Log communications to/from the backend to that file. This is goodfor debugging problems.
   
• Parse Statements: Tell the driver how to gather the information about result columns of queries, if the application requests that information before executing the query. See alsoServerSide Prepare options.
  The driver checks this option first. If disabled then it checks the Server Side Prepare option.
  
  If this option is enabled,the driver will parse an SQL query statement to identify the columns andtables and gather statistics about them such as precision, nullability,aliases, etc. It then reports this information in SQLDescribeCol,SQLColAttributes, and SQLNumResultCols.
  
  When this option is disabled (the default), the query is sent to the serverto be parsed and described.If the parser can not deal with a column (because it is a functionor expression, etc.), it will fall back to describing the statement inthe server. The parser is fairlysophisticated and can handle many things such as column and table aliases,quoted identifiers, literals, joins, cross-products, etc. It cancorrectly identify a function or expression column, regardless of the complexity,but it does not attempt to determine the data type or precision of thesecolumns.
   
• Ignore Timeout:Ignore SQL_ATTR_QUERY_TIMEOUT set using SQLSetStmtAttr(). Some tools issue SQLSetStmtAttr(.., SQL_ATTR_QUERY_TIMEOUT, ...) internally and sometimes it's difficult for users to change the value.
   
• MyLog (C:\mylog_xxxx.log):Log debug messages to that file. This is goodfor debugging problems with the driver.
   
• Unknown Sizes:This controlswhat SQLDescribeCol and SQLColAttributes will return as to precision forcharacter data types (varchar, text, and unknown) in a result set whenthe precision is unknown.This was more of a workaround for pre-6.4versions of PostgreSQL not being able to return the defined column widthof the varchar data type.
  
  
  • Maximum: Alwaysreturn the maximum precision of the data type.
  • Dont Know: Return "Don't Know"value and let application decide.
  • Longest: Returnthe longest string length of the column of any row. Beware of thissetting when using cursors because the cache size may not be a good representationof the longest column in the cache.
  
  MS Access: Seems to handleMaximum setting ok, as well as all the others.
  Borland: If sizes are large and lots of columns, Borland may crash badly (it doesn't seem to handle memory allocation well)if usingMaximum size.
  
  
• Data Type Options: affects how some data types are mapped:
   
• Text as LongVarChar:PostgreSQL TEXT type is mapped to SQLLongVarchar, otherwise SQLVarchar.
• Unknowns as LongVarChar: Unknown types (arrays, etc) are mapped to SQLLongVarChar, otherwise SQLVarchar
• Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.


• Max Varchar The maximumprecision of the Varchar and BPChar(char[x]) types. The default is 254which actually means 255 because of the null terminator. Note, ifyou set this value higher than 254, Access will not let you index on varcharcolumns!
   
• Cache Size:When usingcursors, this is the row size of the tuple cache and the default is 100 rows.If not using cursors, this has no meaning.
   
• Max LongVarChar: The maximumprecision of the LongVarChar type. The default is 4094 which actuallymeans 4095 with the null terminator. You can even specify (-4) forthis size, which is the odbc SQL_NO_TOTAL value.
   
• SysTable Prefixes: Additional prefixes of table names to regardas System Tables. Tables that begin with "pg_" are always treated as systemtables, even without this option. Separate each prefix with a semicolon(;)
   
• Batch Size:Chunk size when executing batches with arrays ofparameters. Setting 1 to this option forces one by one execution (thebehavior before).
   

Advanced Options 2/3 Dialog Box

• ReadOnly: Whether thedatasource will allow updates.
   
• Show System Tables: Thedriver will treat system tables as regular tables in SQLTables. Thisis good for Access so you can see system tables.
   
• LF<-> CR/LF conversion: Convert Unix style line endings toDOS style.
   
• Updateable Cursors: Enable updateable cursor emulation in thedriver.
   
• Bytea as LO: Allow the use of bytea columns for Large Objects.
   
• Row Versioning: Allowsapplications to detect whether data has been modified by other users whileyou are attempting to update a row. It also speeds the update processsince every single column does not need to be specified in the where clauseto update a row. The driver uses the "xmin" system field of PostgreSQLto allow for row versioning. Microsoft products seem to use thisoption well. See thefaqfor details on what you need to do to your database to allow for the rowversioning feature to be used.
   
• Display Optional Error Message: Display optional(detail, hint, statement position etc) error messages.
   
• True is -1: Represent TRUE as -1 for compatibility with some applications.
   
• Server side prepare:If set, the driver uses server-side prepared statements. See alsoParse Statement option. Note that if a query needs to be described before execution, e.g. because the application calls SQLDescribeCol() or SQLNumResultCols() before SQLExecute(), the driver will send a Parse request to the server even if this option is disabled. In that case, the query that is sent to the server for parsing will have the parameter markers replaced with the actual parameter values, or NULL literals if the values are not known yet.
   
• Int8 As: Define what datatype to report int8 columns as.
   
• Numeric As: Specify the map from numeric items without precision to SQL data types. numeric(default), varchar, double or memo(SQL_LONGVARCHAR) can be specified.
   
• Extra Opts: combination of the following bits.
  
   0x1: Force the output of short-length formatted connection string. Check this bit when you use MFC CDatabase class.
   0x2: Fake MS SQL Server so that MS Access recognizes PostgreSQL's serial type as AutoNumber type.
   0x4: Reply ANSI (not Unicode) char types for the inquiries from applications. Try to check this bit when your applications don't seem to be good at handling Unicode data.
   
• Level of rollback on errors: Specifies what to rollback should anerror occur.
   
  • Nop(0): Don't rollback anything and let the application handle theerror.
     
  • Transaction(1): Rollback the entire transaction.
     
  • Statement(2): Rollback the statement.
     
  
  Setup note: This specification is set up with the PROTOCOL option parameter.
  
  PROTOCOL=7.4-(0|1|2)
  default value is Statement (it is Transaction for servers before 8.0).
  
  
• OID Options:
   
  • Show Column: Includes the OIDin SQLColumns. This is good for using as a unique identifier to updaterecords if no good key exists OR if the key has many parts, whichblows up the backend.
     
  • Fake Index: This optionfakes a unique index on OID. This is useful when there is not a realunique index on OID and for apps which can't ask what the unique identifiershould be (i.e, Access 2.0).
     
• Connect Settings: Thedriver sends these commands to the backend upon a successful connection. It sends these settings AFTER it sends the driver "Connect Settings".Use a semi-colon (;) to separate commands. This can now handle anyquery, even if it returns results. The results will be thrown awayhowever!
   
• TCP KEEPALIVE setting(by sec): Specifies TCP keepalives setting.
   
  • disable: Check when client-side TCP keepalives are not used.
     
  • idle time: The number of seconds of inactivity after which TCP should send a keepalive message to the server.
     
  • interval: The number of seconds after which a TCP keepalive message that is not acknowledged by the server should be retransmitted.
     
  
  

Advanced Options 3/3 Dialog Box

• Allow connections urecoverable by MSDTC?: How to test distributed transactions.
   
• yes: MSDTC is needless unless applications crash. So don't check the connectivity from MSDTC.
   
• rejects sslmode verify-[ca|full]: reject ssl connections with verify-ca or verify-full mode because in those cases msdtc could hardly establish the connection.
   
• no: First confirm the connectivity from MSDTC.
   


• Libpq parameters: Specify libpq connection parameters with conninfostyle strings e.g.sslrootcert=c:\\myfolder\\myroot sslcert=C:\\myfolder\\mycert sslkey=C:\\myfolder\\mykey.
  Though host, port, dbname, user, password, sslmode, keepalives_idle orkeepalive_interval parameters can be set using this(pqopt) option, the useis not recommended because they are ordinarily set by other options.When some settings for those parameters conflict with other ordinary options,connections are rejected.
   

Global settings Dialog Box

This dialog allows you to specify pre-connection/default loggingoptions

• CommLog (C:\psqlodbc_xxxx.log - Communications log):Log communications to/from the backend to that file. This is goodfor debugging problems.
   
• MyLog (C:\mylog_xxxx.log - Detailed debug output):Log debug messages to that file. This is goodfor debugging problems with the driver.
   
• MSDTCLog (C:\pgdtclog\mylog_xxxx.log - MSDTC debug output):Log debug messages to that file. This is goodfor debugging problems with the MSDTC.
   
• Specification of the holder for log outputs:Adjustment of write permission.
   

Manage DSN Dialog Box

This dialog allows you to select which PostgreSQL ODBC driverto use for this connection. Note that this may not work with thirdparty drivers.

How to specify as a connection option

There is a method of specifying a connection option in a keyword strings.

Example:VBA

• myConn = "ODBC;DRIVER={PostgreSQL Unicode};" & serverConn & _
  "A0=0;A1=7.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" & _
  "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;BI=-5;" & _
  "C0=0;C2=dd_;C4=1;C5=1;C6=1;C7=1;C8=1;C9=0;CA=verify-full;D1=30;D4=40;" & _
  "D5={sslrootcert=C:\\myfolder\\myroot sslcert=C:\\myfolder\\mycert sslkey=C:\\myfolder\\mykey}"

Please refer to akeyword list for details.