Connect ADO User's Guide and Reference Chapter 5 Connect ADO for SybaseConnect ADO for Sybase (the 'Sybase data provider') supports SQLServer System 11, Sybase Adaptive Server 11.5 and higher, and SybaseAdaptive Server Enterprise 12.0 and 12.5.The Sybase data provider can be installed in the Windows environments(see ).This chapter describes how to set up Connect ADO forSybase and provides data provider-specific information. For moreinformation on developing applications for the Sybase data provider, seethe following sections:.Creating DataSourcesYou create or define a data sourceusing the DataDirect Technologies Configuration Manager.
To create a datasource for Connect ADO for Sybase using the Configuration Manager, see.After you create a data source using theConfiguration Manager, you specify connection values in the dataprovider's Setup dialog box. For more information, see. Configuring Data SourcesYou use the provider Setup dialog box to specify connection valuesfor the provider and a data source. The data source can be the defaultdata source that comes with the provider or a data source that you havecreated with the Configuration Manager.Thevalues that you specify in the Setup dialog box become the defaults whenyou connect to the data source.
You can change these defaults byreconfiguring your data source.To configure a provider data source:. Start the DataDirect TechnologiesConfiguration Manager by double-clicking its icon in the Connect ADOprogram group.
Double-click theData Sources folder to display a list of datasources. Select the data sourcename and review the settings in the right pane of the ConfigurationManager. Perform one of thefollowing actions to display the DataDirect Sybase ADO Provider Setupdialog box:. Double-click thedata source name.
SelectEdit / Modify. NOTE: The General tab displays only fields that are required forcreating a data source.
The fields on all other tabs are optional, unlessnoted otherwise. On the Generaltab, provide the following information; then, clickApply.Data Source Name: Thedata source name is supplied automatically in this noneditable field.Description: Type anoptional long description of a data source name. For example, 'MyAccounting Database' or 'System 12 on Server number 1.' Server Name: Type thehost name of the server that contains the Sybase Server you want toaccess.HA Server Name:(Optional) Type the name of the server that provides the Sybaseconnection failover capability. For information on the connectionfailover feature, refer to your Sybase documentation.Database Name: Type the name ofthe database to which you want to connect by default.
If you do notspecify a value, the default is the database defined by the systemadministrator for each user.Network Protocol: Select the name of the Sybasenetlib to use for application communication. The options are Winsockand Named Pipes. The default is Winsock.Server Port or Name Pipes Address: Type the addressof the Sybase server. If the Sybase server uses TCP/IP, the defaultvalue for the Server Port is 5000. If you selected Named Pipes in theNetwork Protocol field, type the string address of the server afterthe server name, for example, machinenamepipesybasequery.HA Server Port or Name PipesAddress: (Optional) Type the address of the failoverserver. If the Sybase server uses TCP/IP, the default value for theServer Port is 5000.
If you selected Named Pipes in the NetworkProtocol field, type the string address of the server after theserver name, for example, machinenamepipesybasequery. Optionally, click theAdvanced tab to specify data source settings. On this tab, provide any of the following optional information;then, click Apply.Default Length for Long Data (in KB): Type aninteger value that specifies, in 1024-byte multiples, the maximumlength of data fetched from a TEXT or IMAGE column.
The default is1024 kilobytes. You will need to increase this value if the totalsize of any long data exceeds 1 megabyte.Enable Quoted Identifiers: Select this checkbox to allow support of quoted identifiers.
By default, the check boxis not selected; quoted identifiers are not enabled.Initialization String: Type astring that supports the running of Sybase commands at connect time.Separate multiple commands by commas.Raise Errors: Select when the error is returned andwhere the cursor is positioned when raise error is encountered. Thedefault is MS Compatible.
When set to MS Compatible, raise error is treated as aseparate result set. When set to Warnings Only, allraise errors are sent back to the user as a warning and are nottreated as a separate result set.Print Statements: Select whether printstatements are sent back to the user as separate result sets.
Thedefault is MS Compatible. When set to MS Compatible, each print statement istreated as a separate result set. When set to Warnings Only, allprint statement errors are sent back to the user as a warning andare not treated as a separate result set.Extended Error Info: Selectwhether to return extended Sybase Server error information. Thedefault is FALSE; only the Native error code and error message textis returned. If you select TRUE, additional Sybase Server errorinformation is returned.StoredProc Row Count: Select the row count for SQL statements inthe stored procedure.
Sybase Sql Anywhere 16 Download
When set to Last Statement Only, the default, the rowcount from the last SQL statement in the stored procedure isreturned. When set to Each Statement, ifthe IMultipleResults interface is requested, the count of rowsaffected for each SQL statement in the stored procedure isreturned. When used with the IRowset interface, this returns onlythe row count, and does not return resultsets.Tightly Coupled DistributedTransactions: Select this check box to use tightly coupleddistributed transactions to ensure that multiple connections withinthe same distributed transaction do not obey each other's locks whenconnected to a Sybase ASE version 12 database.When this check box is not selected, the default, the overallperformance of the data provider is better. However, multipleconnections within the same distributed transaction may hang eachother because the connections do not obey each other's locks.This option is valid only when the dataprovider is enlisted in a distributed transaction and when it isconnected to a Sybase ASE version 12 database. Otherwise, this optionis ignored. Optionally,click the Connection tab to specify data sourcesettings.
Sybase Sql Advantage
On this tab, provide any of the following optional information;then, click Apply.Default Logon ID: Type the default logon ID used toconnect to your Sybase database. This ID is case-sensitive.IMPORTANT: A value entered here overrides values passed in aconnection string. Leave this field blank if you intend to pass theLogon ID in a connection string.Workstation ID: Type the workstation ID used by theclient.Character Set:Type the name of a character set corresponding to a subdirectory in$SYBASE/charsets. The default is the setting on the Sybase server.Application Name: Typethe name used by Sybase to identify your application.Language: Type the nationallanguage corresponding to a subdirectory in $SYBASE/locales.
Sybase Sql Top
Thedefault is English.ConnectTimeout(Sec): Type an integer value for the time, inseconds, to wait for initialization to complete. The default is 15seconds.InterfacesFile: Type the path name of the Interfaces file. If you donot specify value in the Interfaces File Server Name field, then theprovider looks for the path name of the Interfaces file in theRegistry underHKEYLOCALMACHINESOFTWAREDataDirectInterfacesFile.
If thisRegistry value is empty, then the provider attempts to open theSQL.INI file found in the same directory as the provider and use itas the Interfaces file.ServerName: Type the name of the section in the Interfaces filethat contains the network connection information for the Sybaseserver you want to access. The section name typically is the hostname of the server that contains the Sybase server you want toaccess. Optionally, clickthe Performance tab to specify data source performancesettings. On this tab, provide any of the following optional information;then, click Apply.Select Method: Select a value that determineswhether database cursors are used for Select statements. The initialdefault is Direct; Select statements are executed directly withoutusing database cursors. With this setting, the data source is limitedto one active statement. When set to Cursor, database cursors areused.Prepare Method:Select a value that determines whether stored procedures are createdon the server for every call to ICommand::Prepare.
When set to Partial (theinitial default), the data provider creates stored procedures onlyif the statement contains parameters. Otherwise, the statement iscached and executed directly at ICommand::Execute time. When set to None, storedprocedures are created for every call to ICommand::Prepare. Thiscan decrease performance when processing non-parameterizedstatements. When set to Full, storedprocedures are never created.
The data provider caches thestatement and executes it directly at ICommand::Execute time. Withthis setting, no syntax errors, or similar errors, and columninformation will be made available at ICommand::Prepare time. When set to Full at Prepare,stored procedures are never created. The data provider caches thestatement and executes it directly at ICommand::Execute time.
Withthis setting, some syntax errors, or similar errors, and columninformation will be made available at ICommand::Prepare time,instead of at ICommand::Execute time. Use Full at Prepare only ifyou must have syntax errors or column information atICommand::Prepare time, and before calling ICommand::Execute.Syntax errors will not be reported on the WHERE clause.Row Cache Size:Type the number of rows the data provider retrieves from the serverfor each fetch. The initial default is 50 rows. To optimizeperformance, set this value according to the size of the rowsets thatyou expect to be returned. This allows multiple statements to beexecuted on a connection without using database cursors.Packet Size: Type a value thatdetermines the number of bytes per network packet transferred fromthe database server to the client. The correct setting of thisattribute can improve performance.Whenset to x, an integer from 1 to 10, which indicates amultiple of 512 bytes (for example, if you enter 6, you set thepacket size to 6.
512 = 3072 bytes). The initial default is 1.When set to 0, the Sybase data provideruses the default packet size as specified in the Sybase serverconfiguration.To take advantage of thisconnection attribute, you must configure the Sybase server for amaximum network packet size greater than or equal to the value youspecified for PacketSize. For example,spconfigure 'maximum network packet size', 5120reconfigureRestart Sybase Server.
Optionally, clickthe Options tab to add connection values specific to theSybase data provider. See the online help for the latest authorizedvalues. On this tab, provide any of the following optional information;then, click Apply.Enable: Select this check box to enable tracingsupport; this enables the other options on the Trace tab. By default,the check box is not selected.Trace IUknown Methods: Select this check box toenable tracing support for IUknown methods. By default, the check boxis not selected.Append toExisting Output File: Select this check box to appendtracing results to an output file. By default, the check box isselected. However, the setting takes effect only if the Enable checkbox is selected.
When this check box is not selected, the new logfile overrides the existing log file.Output File: Type the name of the file to whichtracing results will be appended. This file contains the tracingresults for all data sources for the data provider. Click Test Connectto verify that the data source can connect to the data store throughthis data provider, using the connection properties specified in theSetup dialog box. The Sybase ADO Provider Connection dialog box isdisplayed. Type a user name, andpassword, if required. ClickConnect.
The Sybase data provider attempts tomake the connection. If the Sybase dataprovider can connect, it promptly releases the connection anddisplays a Successful Completion message. If the Sybase dataprovider cannot connect, a message describing the problem isdisplayed.
For example, the password might be invalid, or therequired client software is not installed. Click OK orCancel.
If you click OK, the valuesyou have specified become the defaults when you connect to the datasource.Sybase ADO ProviderConnection Dialog BoxThe Sybase ADOConnection dialog box is displayed when you click the Test Connectbutton. Applications can be configured to display this dialog box if youneed to enter a user name and password.Toconfigure advanced, optional properties, click Options.The dialog box expands to include additional configuration properties, asshown in the following figure. NOTE: The data source name is supplied automatically and cannot bechanged.Follow these steps to complete theSybase ADO Provider Connection dialog box:.
In the Server field, type the name of the server. If notsupplied, the server name in the DSQUERY environment variable is used. If required, type the username and password.
Use the names specified when you defined the datasource on the Connect ADO data source setup dialog box. From the Network Protocoldrop-down list, select the name of the Sybase netlib to use forapplication communication. The options are Winsock and Named Pipes. Thedefault is Winsock. Type the address of theSybase server.
If the Sybase server uses TCP/IP, the default value forthe Server Port is 5000. If you selected Named Pipes in the NetworkProtocol field, type the string address of the server after the servername, for example, machinenamepipesybasequery. To configure advanced,optional properties, click Options if it is enabled.Otherwise, continue at.
Type the name of thedatabase to which you want to connect. If you do not specify a value,the provider connects to the default database as defined by the SystemAdministrator. In the HA Server field,type the name of the server that provides the Sybase connectionfailover capability. In the HA Server Port orName Pipes Address field, type the address of the failover server. ClickConnect to test the connection.NOTE: See for information on connecting to a data sourceusing a provider string.
Connecting to aData Source Using a Connection StringOncea data source is set up through the Configuration Manager, yourapplication can connect directly to that data source by specifying thename of the data source for the DBPROPINITDATASOURCE property.However, if you do not have a DataDirect datasource, you may still be able to connect using a connection string.Perform the following actions:. Specify any connection properties using the appropriateOLE DB initialization property. For example, specify the 'User ID'using DBPROPINITUSERID.
Specify any backendconnection properties that do not have obvious OLE DB analogs inthe 'provider string' property. The provider string, which is similarto the ODBC connection string, is represented by theDBPROPINITPROVIDERSTRING property, described in the followingparagraphs.A provider string containsattribute=value pairs that control various aspects of the dataprovider's connection and interaction with the database. When anapplication names a specific data source to connect to, the applicationcan also pass the data provider a provider string of attribute=valuepairs.
The data provider uses the values in the provider string insteadof any default values that already exist for the data source in thesystem information.This feature allowsapplication developers to configure connections for usersprogrammatically and ensures that users have the optimum settings forworking with the provider and database. Any values a user has set for adata source through the Configuration Manager override correspondingvalues in the provider string. The provider string values are not writtento the system information. The provider string values apply only to thecurrent session.The provider string setsthe DBPROPINITPROVIDERSTRING initialization property and has the form:'attribute=value;attribute=value;'gives the name and a description for each provider string attributespecific to Sybase data provider.Thedefaults listed in the table are initial defaults that apply when novalue is specified in either the provider string or in the data sourcedefinition in the system information. If you specified a value for theattribute when configuring the data source in the Setup dialog box, thatvalue is your default.
CATALOGS. COLUMNPRIVILEGES.
COLUMNS. FOREIGNKEYS. INDEXES. PRIMARYKEYS. PROCEDURES.
PROCEDURECOLUMNS. PROCEDUREPARAMETERS. PROVIDERTYPES. REFERENTIALCONSTRAINTS. SCHEMATA. STATISTICS.
TABLECONSTRAINTS. TABLEPRIVILEGES. TABLES.
VIEWSFor more information about rowsets, refer to theMicrosoft OLE DB programming documentation.MTS SupportWhen you install the Sybase data provider from the Server Pack, youcan take advantage of Microsoft Transaction Server (MTS) capabilities,specifically, the Distributed Transaction Coordinator (DTC). The servermust be a Sybase 12. For a general discussion of MTSand DTC, refer to the help file of the 'Microsoft Transaction ServerSDK.'
To enable Distributed Transactionson the Sybase server:. Assignthe dtmtmrole to each user who will participate in distributedtransactions (who will log in to Adaptive Server). You can do thisusing the sprole command.
For example:sprole 'grant', dtmtmrole, usernameIn the open string for resource managers, thespecified username must have the dtmtmrole. Specify a default database otherthan the master for each user. Sybase cannot start distributedtransactions in a master database.Custom Error Object SupportBasicerror information is returned as part of each error record. In addition,the Sybase data provider defines a custom error object that maps theserver-generated errors to the equivalent SQLSTATE value. Connect ADO User's Guide andReference© 2002, DataDirect Technologies. All rightsreserved.