Difference between revisions of "ODBC: Features and Limitations"

From Recital Documentation Wiki
Jump to: navigation, search
 
 
(10 intermediate revisions by one user not shown)
Line 1: Line 1:
====Supported ODBC SQL data types====
+
==See Also==
 +
[[ODBC: Function Summary|Function Summary]], [[ODBC: Supported SQL Grammar|SQL Grammar]]
  
  
{|
+
==Supported ODBC SQL data types==
|fSqlType||Recital/SQL Data Type
+
 
 +
 
 +
{| class="wikitable"
 +
!fSqlType||Recital/SQL Data Type
 
|-
 
|-
 
|SQL_CHAR||CHAR(n)
 
|SQL_CHAR||CHAR(n)
Line 51: Line 55:
 
|}
 
|}
  
====Query optimization====
+
 
* Full query optimization is supported on the server using "index restriction" query optimization. Make sure you have the required indexes set up.  
+
==Query optimization==
* You can execute any Recital non user-interface command from the ODBC driver by prefixing the command in SQLExecute() or SQLExecDirect() with the keyword 'recital'. For example:  
+
Full query optimization is supported on the server using "index restriction" query optimization. Make sure you have the required indexes set up.  
 +
 
 +
 
 +
==recital==
 +
You can execute any Recital non user-interface command from the ODBC driver by prefixing the command in SQLExecute() or SQLExecDirect() with the keyword 'recital'. For example:
 +
 
 +
<code lang="c">
 
SQLExecDirect(hstmt, "recital do myproc", cbSqlStr);
 
SQLExecDirect(hstmt, "recital do myproc", cbSqlStr);
* Any output can then be accessed using SQLFetch() calls. You can control shared access with this command. For example:  
+
</code>
 +
 
 +
Any output can then be accessed using SQLFetch() calls. You can control shared access with this command. For example:
 +
 
 +
<code lang="c"> 
 
SQLExecDirect(hstmt, "recital set exclusive off", cbSqlStr);
 
SQLExecDirect(hstmt, "recital set exclusive off", cbSqlStr);
* All Recital data types are supported (including memos). These can be specified in the CREATE TABLE command.  
+
</code>
* Character and binary values supplied for parameterized queries are limited to 255 bytes.  
+
 
* Qualifiers or owners are not allowed on databases or tables.
+
 
* Column and table names are not case sensitive.  String data comparisons are case sensitive.
+
==Data types==
* By default all tables are opened shareable on the server. If you do not want this then:  
+
All Recital [[SQL Data Types|data types]] are supported (including memos). These can be specified in the [[CREATE TABLE]] command.
* Call SQLExecute(hstmt, "recital set exclusive on") before opening a table.  
+
 
* Set the 'useoptions' string in the ''sysodbc.ini'' file on the server to 'exclusive' for each table that you require non-shareable access to.  See the UAS Installation and Configuration Guide for more information about ''sysodbc.ini''.  
+
* Add the command 'set exclusive on' to the ''config.db'' file.  This will cause all tables to be opened exclusively.  See the UAS Installation and Configuration Guide for more information about ''config.db''.  
+
==Parameters==
* Memo fields are seen as SQL 'LONG VARCHAR' columns, and default to a maximum of 64k characters.  This can be altered by setting the 'maxvarcharsize' entry in ''recital.ini'' on the client to the required value.  Be careful when making this too large as performance will be unduly affected.  The ''recital.ini'' file should reside in the <WINDOWS> directory.
+
Character and binary values supplied for parameterized queries are limited to 255 bytes.
* ODBC driver and Recital Universal Data and Application Server interaction can be debugged on the client by selecting the 'Trace RSI calls' checkbox in the SQLDriverConnect() dialog.  
+
 
* ODBC server activity can be debugged by setting the 'logging' string in 'recital.ini' or see the UAS Installation and Configuration Guide for information about setting up logging on the server.  The resultant file on the server can be viewed using a standard text editor.
+
 
 +
==Qualifiers and owners==
 +
Qualifiers or owners are not allowed on databases or tables.
 +
 
 +
 
 +
==Case sensitivity==
 +
Column and table names are not case sensitive.  String data comparisons are case sensitive.
 +
 
 +
 
 +
==Exclusive access==
 +
By default all tables are opened shareable on the server. If you do not want this then:  
 +
* Call SQLExecute(hstmt, "recital [[SET EXCLUSIVE|set exclusive on]]") before opening a table.  
 +
* Set the 'useoptions' string in the [[Configuration Files|sysodbc.ini]] file on the server to 'exclusive' for each table that you require non-shareable access to.
 +
* Add the command [[SET EXCLUSIVE|set exclusive on]] to the [[Configuration Files|config.db]] file.  This will cause all tables to be opened exclusively.
 +
 
 +
 
 +
==Memo / long varchar==
 +
Memo fields are seen as SQL [[SQL Data Types|LONG VARCHAR]] columns, and default to a maximum of 64k characters.  This can be altered by setting the 'maxvarcharsize' entry in [[Configuration Files|recital.ini]] on the client to the required value.  Be careful when making this too large as performance will be unduly affected.
 +
 
 +
 
 +
==Tracing==
 +
ODBC driver and Recital Server interaction can be debugged on the Windows client by selecting the ''Trace RSI calls'' checkbox in the SQLDriverConnect() dialog or by setting the ''Clientlogging'' and ''Clientlogfile'' parameters in the DSN on Linux.
 +
 
 +
 
 +
==Logging==
 +
Recital Server activity can be debugged by setting the 'logging' string in [[Configuration Files|recital.ini]] or see [[recitalserver]] for information about starting the server with logging.  The resultant files on the server can be viewed using a standard text editor.
 +
 
  
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:Reference]]
 
[[Category:Reference]]
 
[[Category:ODBC|Features and Limitations]]
 
[[Category:ODBC|Features and Limitations]]

Latest revision as of 17:04, 2 March 2010

See Also

Function Summary, SQL Grammar


Supported ODBC SQL data types

fSqlType Recital/SQL Data Type
SQL_CHAR CHAR(n)
SQL_VARCHAR VARCHAR(n)
SQL_LONGVARCHAR LONG VARCHAR
SQL_NUMERIC DECIMAL(precision, scale)
SQL_NUMERIC NUMERIC(precision, scale)
SQL_NUMERIC SMALLINT
SQL_INTEGER INTEGER
SQL_REAL REAL
SQL_FLOAT FLOAT
SQL_NUMERIC DOUBLE PRECISION
SQL_BIT BIT
SQL_TINYINT TINYINT
SQL_NUMERIC BIGINT
SQL_SHORT SHORT(precision, scale)
SQL_DATE DATE
SQL_BIT LOGICAL
SQL_LONGVARCHAR MEMO
SQL_NUMERIC BYTE
SQL_LONGVARBINARY OBJECT
SQL_DATETIME DATETIME
SQL_CURRENCY CURRENCY
SQL_ZONED ZONED


Query optimization

Full query optimization is supported on the server using "index restriction" query optimization. Make sure you have the required indexes set up.


recital

You can execute any Recital non user-interface command from the ODBC driver by prefixing the command in SQLExecute() or SQLExecDirect() with the keyword 'recital'. For example:

SQLExecDirect(hstmt, "recital do myproc", cbSqlStr);

Any output can then be accessed using SQLFetch() calls. You can control shared access with this command. For example:

SQLExecDirect(hstmt, "recital set exclusive off", cbSqlStr);


Data types

All Recital data types are supported (including memos). These can be specified in the CREATE TABLE command.


Parameters

Character and binary values supplied for parameterized queries are limited to 255 bytes.


Qualifiers and owners

Qualifiers or owners are not allowed on databases or tables.


Case sensitivity

Column and table names are not case sensitive. String data comparisons are case sensitive.


Exclusive access

By default all tables are opened shareable on the server. If you do not want this then:

  • Call SQLExecute(hstmt, "recital set exclusive on") before opening a table.
  • Set the 'useoptions' string in the sysodbc.ini file on the server to 'exclusive' for each table that you require non-shareable access to.
  • Add the command set exclusive on to the config.db file. This will cause all tables to be opened exclusively.


Memo / long varchar

Memo fields are seen as SQL LONG VARCHAR columns, and default to a maximum of 64k characters. This can be altered by setting the 'maxvarcharsize' entry in recital.ini on the client to the required value. Be careful when making this too large as performance will be unduly affected.


Tracing

ODBC driver and Recital Server interaction can be debugged on the Windows client by selecting the Trace RSI calls checkbox in the SQLDriverConnect() dialog or by setting the Clientlogging and Clientlogfile parameters in the DSN on Linux.


Logging

Recital Server activity can be debugged by setting the 'logging' string in recital.ini or see recitalserver for information about starting the server with logging. The resultant files on the server can be viewed using a standard text editor.