Sybase ODBC - ODBC DSN less connection issues using ASE ODBC Driver

Author

ODBC DSN less connection issues using ASE ODBC Driver

DRM

2005-07-01, 8:23 pm

Hi,

I am facing a problem while making dsn less connection to
sybase
Here is the thing, I shall try to make it simple:
this piece of connection string works fine in production
with sybase 11 open client installed
Driver={SYBASE SYSTEM
11};Srvr=SYBPRD_DEV;
Uid=SYBPRDCOM;Pwd=we
lcomedb1;

However, I am trying to setup a Dev environment with Sybase
open client 12.5 ..this piece fails with the following error
msgs,
Connection string:

err msg: [Microsoft][ODBC Driver Manager] Data source name
not found and no default driver specified

(i tried changing it to {SYBASE ASE ODBC Driver} instead of
{SYBASE SYSTEM 11} but still in it returns below error
msg...any ideas/help will be greatly appreciated.

Error: [DataDirect][ODBC Sybase Wire Protocol
driver]Insufficient information to connect to the data
source.

Thanks!
DRM

Paul

2005-07-05, 1:23 pm

Hi -

It sounds like you updated from the very old system 11 ODBC Driver to the
TDS based driver (12.5). Try this in the connection string:

Instead of Srvr, replace that with NetworkAddress=AseHo
st, AsePort where
AseHost is the name of the host machine running the ASE, and AsePort is the
port number the ASE uses to listen on the network.

This was a major change in the ODBC driver. It does not require Open Client
to access the ASE.

-PV

<DRM> wrote in message news:42c5c50f.300f.1681692777@sybase.com...
> Hi,
>
> I am facing a problem while making dsn less connection to
> sybase
> Here is the thing, I shall try to make it simple:
> this piece of connection string works fine in production
> with sybase 11 open client installed
> Driver={SYBASE SYSTEM
> 11};Srvr=SYBPRD_DEV;
Uid=SYBPRDCOM;Pwd=we
lcomedb1;
>
> However, I am trying to setup a Dev environment with Sybase
> open client 12.5 ..this piece fails with the following error
> msgs,
> Connection string:
>
> err msg: [Microsoft][ODBC Driver Manager] Data source name
> not found and no default driver specified
>
> (i tried changing it to {SYBASE ASE ODBC Driver} instead of
> {SYBASE SYSTEM 11} but still in it returns below error
> msg...any ideas/help will be greatly appreciated.
>
> Error: [DataDirect][ODBC Sybase Wire Protocol
> driver]Insufficient information to connect to the data
> source.
>
> Thanks!
> DRM

DRM

2005-07-05, 8:23 pm

Hi PV
Thanks for your note...i am able to connect now...however,
since i have to implement this change in a dyanamic connect
process,
i may have to tweak a few things to support this new setup,
so can you pls. send me the complete connection string
(including optional arguements/parameteres) this would help
me to understand SYBASE ASE ODBC Driver connection string
parameters completely and implement the change accordingly.
thanks a ton! in advance!
Cheers,
DRM
> Hi -
>
> It sounds like you updated from the very old system 11
> ODBC Driver to the TDS based driver (12.5). Try this in
> the connection string:
>
> Instead of Srvr, replace that with NetworkAddress=AseHo
st,
> AsePort where AseHost is the name of the host machine
> running the ASE, and AsePort is the port number the ASE
> uses to listen on the network.
>
> This was a major change in the ODBC driver. It does not
> require Open Client to access the ASE.
>
> -PV
>
> <DRM> wrote in message
>
>

Paul

2005-07-05, 8:23 pm

Hi DRM,

The main parameters like UID, PWD are the same. Its just that SRVR is not
used, with NetworkAddress (NA) replacing it.

This list first appeared in the ISUG Technical Journal Q3 2003:

Data Source Name - DataSourceName (DSN)

This attribute is handled directly by the ODBC Driver Manager. The value of
this attribute is the name of the folder under the following registry keys:

For System DSN:

HKEY_LOCAL_MACHINE\S
OFTWARE\ODBC\ODBC.INI

For User DSN:

HKEY_CURRENT_USER\SO
FTWARE\ODBC\ODBC.INI

This attribute represents the collection of attributes contained in the ODBC
driver.

Description

This is just used to provide a description of the DSN, mainly for
documentation and clarification purposes.

Network Library Name- NetworkLibraryName (NLN)

Indicates which network protocol to use, either Name Pipes or TCPIP.
Winsock is used for TCPIP, and NamedPipes is used when using Named Pipes.
The default value is Winsock. Winsock is used by nearly everyone using this
TDS based ODBC driver.

Network Address - NetworkAddress (NA)

Network address provides the connectivity information used to make the
connection to the ASE. For TCPIP you specify the hostname or ip address of
the machine hosting the ASE. The format is "hostname[or IP Address], port".
The ODBC driver uses this information and passes it to the internal network
library code that will establish the socket to socket connection between the
ODBC client machine and the ASE host machine. This attribute is required
for connecting to ASE, unless the DSN is using the Interfaces file method
for connecting.

Database Name - Database (DB)

This option will issue a "use DatabaseName" command upon connecting to ASE
if the target database for the application is to be a database that is not
the user default database. This option is good to use if applications are
based on ASE databases.

For the HA Failover Server Connection Information, which is an optional
feature:

Network Address - FailoverNetworkAddre
ss (FNA)

If the HA (High Availability) feature of ASE is being deployed, this field
takes care of the Hostname, Port_Number to be used to make the HA connection
to the failover ASE. When the connection is dropped, the ODBC driver
automatically makes the connection to the information specified by FNA.

Another optional feature is the use of the Interfaces file, "Use Interfaces
File for Connection Information". This contains two fields:

Interfaces File - InterfacesFile (IF)

EBF 10719 introduced this feature, which utilizes the SQL.INI file to make a
connection to a specified ASE. IF is used to represent the full path to the
SQL.INI file. It is in the form of PATH\SQL.INI. You need a SQL.INI file
generated by Open Client, or formatted similarly, but you do not need Open
Client installed to use this feature.

Server Name - InterfacesFileServer
Name (IFSN)

This is the name of the ASE as found in the SQL.INI file.

EBF 10515 introduced LDAP support. Configuring the ODBC Driver for LDAP is
found in the "Sybase LDAP Schema" section.

Sybase LDAP URL- SybaseLDAPURL (SLU)

This is the URL to use that represents the search operation to be used to
retrieve the LDAP information to make the ASE connection. This URL includes
the host and port that the LDAP server listens on. An example of such a URL
is this:

ldap://ldapHost:2289/dc=sybase,dc=com

The information represents the search method to be used, where the Sybase
connection information is contained. The next option contains the ASE name.

Sybase Server Name- SybaseServerName (SSN)

This is the unique identifier for the LDAP server that contains the
connection information for the ASE.

Use LDAP HA Failover Server - UseLDAPHAServer (ULHS)

You can specify the HA server name in the LDAP repository. Specify that
name hear if using the HA feature on ASE.

Cursor Positioning for raiserror- RaiserrorPositionBeh
avior (REPB)

This attribute is useful for processing T-SQL raiserror messages. The
attribute determines where the driver should be positioned when returning
the messages from the raiserror statements. When the default value of "0 -
Cursor" is used the raiserror message is returned with the API calls
SQLExecuteDirect(), SQLExecute() and SQLMoreResults(). When set to "1 - MS
Comaptible" the raiserror message is returned on the SQLFetch call after
fetching all of the rows in the result set that precedes the raiserror.
This can be demonstrated with the following stored procedure:

Create procedure sp_reTest

as

select @@version

raiserror 81111 "raiserror 1"

select @@version

raiserror 82222 "raiserror 2"

If using the default value, the raiserror message is returned when the ODBC
application makes the SQLMoreResults() call. This API call returns
SQL_SUCCESS when there is a result set available. In the above example, the
ODBC application will process the "select @@version" as a result set.
SQLMoreResults() returns SQL_ERROR, with the raiserror message as part of
its text. In a shortened demonstration of API calls this behavior would be
like this:

SQLExecDirect("exec sp_retest")

<Process first result - typically SQLNumResultCols, SQLFetch()>

SQLFetch returns SQL_NO_MORE_DATA, now check for next result set

SQLMoreResults() returns as SQL_ERROR, with MessageText = "[DataDirect][ODBC
Sybase Wire Protocol driver][SQL Server]raiserror 1"

Continue processing and the same pattern occurs.

When setting the value to "1- MS Compatible" the message will return on the
SQLFetch() call that is made when there is no more data in the result set.
Then the call to SQLMoreResults returns SQL_SUCCESS, indicating there is
another result set to process.

Default Buffer Size for Long Columns (in Kb) - DefaultLongDataBuffL
en
(DLDBL)

This is used to process TEXT or IMAGE data columns. The value is specified
in Kb, default value being 1024 Kb, or 1 MB. The best way to use this value
is to specify it in a connection string, or a specific data source used to
handle this type of data. You will want to adjust it to the maximum size of
the data. You need to be careful, since the ODBC Driver will pre-allocate
the buffer when the driver is processing this type of data; the long buffer
is created whenever the driver is called upon to handle the TEXT or IMAGE
column in a read or write situation. You will want this value to match the
ASE TEXTSIZE parameter, and you can force the driver to set TEXTSIZE upon
connection (see Initialization String).

The next two items pertain to the use of Microsoft MTS/DTC (MS Transaction
Server/Distributed Transaction Coordinator). This article will not go into
detail on this feature and ODBC users may email me in regards to finding out
more about this feature.

Distributed Transaction Model - DistributedTransacti
onModel (DTM)

The default value is set to "0 - XA Protocol". This means the ODBC driver
will send XA compliant calls to the ASE/DTM. The other value is "1 - Native
OLE", using the Microsoft protocol. Since ASE/DTM is XA compliant "0" is
the best parameter to use.

XA Open String Parameters - XAOpenStringParamete
rs (XAOSP)

For the 12.5 ODBC Driver, this parameter passes the location of the path and
filename for the XA trace files generated by the ODBC driver. Setting the
filename to XAtrace generates two files : XAtrace and XAtrace.driver. The
first file traces all the XA calls made to ASE/DTM, and the driver file
traces the DTM enlistments and unenlistments as they occur.

Initialization String - InitializationString
(IS)

This is useful. This will send T-SQL commands to the ASE, upon the
connection of the ODBC driver to ASE. This works great for workarounds, or
setting system values that are contrary to the ASE default values. One
major use here is for ANSINULL behavior. When the ASE ODBC Driver became
ANSI compliant (3.X ODBC drivers and above), it was required to send "SET
ANSINULL ON" upon connection. When customers upgraded to the new drivers,
they discovered that some of their SQL statements sent to ASE returned no
data or returned less data then they anticipated. By placing "SET ANSINULL
OFF" in the Initialization String the problem was solved.

Enable Quoted Identifiers - EnableQuotedIdentifi
ers (EQI)

This enables Quoted Identifiers when the check box is checked, meaning the
value is set to "1". On the connection, on 12.5 ODBC, a TDS_OPTIONCMD token
is sent, with the command name TDS_OPT_QUOTED_IDENT
, set to "1". ODBC 12.0
sends "set quoted_identifier on" during the connection phase.

Enable Describe Parameter - EnableDescribeParame
ter (EDP)

Default is unchecked. When checked, SQLDescribeParameter
is enabled. This
enables the application to use this API call, but OptimizePrepare must be
set to 0 or 1 in order for this to work. Older applications, such as those
using the RDO API use this API call. What happens is that with
OptimizePrepare set to 0 or 1 (see below), the SQL Statement is sent in the
form of a stored procedure. For example, if sending "select c1 from
odbc_table where c1 = ?", the SQLPrepare() API call will send "create Proc
S03000000 as select c1 from ado_table where c1 = ?". This in turn returns
the metadata information for the column in the where clause, "c1". This is
useful in code where you do not always know what the parameter datatypes are
and you do not want to call SQLGetTypeInfo for performance reasons. The
column information is collected from the TDS returned by the ASE.

Tightly Coupled Distributed Transactions -
TightlyCoupledDistri
butedTransactions (TCDT)

This option is used with ASE/DTM. When selected, this means that when
multiple connections are running from the same component object, they will
obey each others locks. When the option is off, then it is possible for the
transaction activity on the connections to "self lock", exhibiting hanging
behavior on the DTC/COM+ application.

The next panel is the Connection panel. There are few options here and it d
oes not need to be displayed. This is optional information that can be
useful for application development and to use specific options that are not
the default options when connecting to ASE.

Database List - Databases (no short name)

The DSN creator enters the names of databases that this DSN might need to
access, separating the names with commas. Databases will appear in a list
box when using the Logon Dialog box. The application user selects the
desired database, the ODBC driver will issue the "use DatabaseName" command
to the ASE, so that the user will connect to that specific database.

Default Logon Id - LogonID (UID)

UID is not a typo, this is the short name for the Logon ID used to connect
to ASE. When it is specified in this field, this ID will be used to connect
to the ASE, unless it is specified in a connection string. Updating this
value updates the value for the System DSN. User DSN is only updated when
an application uses the Logon Dialog box, so you must be careful when using
this option.

Workstation ID - WorkStationID (WSID)

Enter the name of the client machine name. This is useful to identify
workstations with sp_who. For example if the machine name is "tbone":

1> sp_who

2> go

fid spid status loginame origname hostname blk_spid

dbname cmd block_xloid

------ ------ ------------ ------------ ------------ ---------- --------

---------- ---------------- -----------

0 17 recv sleep sa sa tbone 0

odbc AWAITING COMMAND 0

The WSID is found under the "hostname" column.

Charset - Charset (CS)

This can specify the charset found on the ASE. The ASE must
support the charset or you will receive an error that the specified charset
is not supported. If you want to use Unicode, then you must specify "utf8",
and the ASE must be setup for utf8. The charset is provided by Open Client
when using ODBC 12.0.

The ODBC 12.5 driver now supports SSL (Secure Sockets Layer) and
this information can be configured, provided the ASE has the SSL feature
licensed and enabled. You must create the trusted roots file first, per the
instructions provided in the ASE documentation. Under the "SSL Information"
section there are two options.

Use SSL - UseSSL (USSL)

This is off by default. You check the box when using SSL. This
will then disable the "Network Address" option. It will also expect a value
for the next option described below:

Trusted Roots File Name - TrustedRootsFileName
(TRFN)

This is set to the full path and filename of the trusted roots
file. The file must exist on the machine that the ODBC driver resides on.
A Client Library based ODBC driver (12.0) will be able to connect to an ASE
setup for SSL if the driver uses OCS 12.5, and the interfaces file is setup
correctly for SSL.

The last panel is the Performance panel. This is where the ODBC
driver can be tweaked to provide improved performance, depending on the
nature of the SQL being sent to the ASE.

Select Method - SelectMethod (SM)

The default value is indicated as "1 - Direct". This option
determines if the driver will use cursors or language when sending a SELECT
statement to the ASE. When set to "1", the driver will allow only ONE
active statement per connection. In some instances, when temp tables are
used, for example, the ODBC driver may open internal connections to the ASE
to simulate multiple active statements. In these cases you need to be very
careful since these are separate individual connections, and the use of temp
tables with the "#" naming convention can lead to missing object errors.
Setting this option to "0 - Cursor", or "0" in a connection string will send
SELECT statements to the ASE as cursors. The 12.5 driver handles this with
the TDS cursor tokens. Using this method allows multiple active statements
on the ASE per connection. If you are writing code that uses a paging
algorithm, this is a good way to go, since you can actually improve
performance. You will not need to do full table scans, if the code is
written properly. In this case, the cursor would allow you to seek up to
where you indicate the position to be in the code.

Prepare Method - OptimizePrepare (OP)

This option is documented well in the ODBC help file. Default
setting is "2". This means that no stored procedures are created when
SQLPrepare() is called. Instead the driver caches the SQL and executes it
when SQLExecute() is called. Settings "0" and "1" will execute the call at
SQLPrepare(). The driver "wraps" the SQL in a create procedure statement
and will send this to the ASE during SQLPrepare(). This provides a means to
get metadata on the parameters (if Enable Describe Parameter is ON), and can
improve performance if the programmer knows that the SQL will remain the
same, but they need to make several calls on the SQL with different
parameters.

Fetch Array Size - ArraySize (AS)

This option is used when SelectMethod is set to "0". The
default value is 50. This option sets up the cursor rows option. This
means that for every cursor fetch made to ASE, 50 rows are sent back to the
ODBC driver, which then caches the data internally. This is transparent to
the application. Depending on resources and network configuration this is a
good option to tweak when using cursors.

Packet Size - PacketSize (PS)

This is well documented in the Help file. This value is
adjusted based on the network configuration of the ASE and the actual
network. This is something that must be adjusted to find the right
optimization.

<DRM> wrote in message news:42caf654.6557.1681692777@sybase.com...[color=darkred]
> Hi PV
> Thanks for your note...i am able to connect now...however,
> since i have to implement this change in a dyanamic connect
> process,
> i may have to tweak a few things to support this new setup,
> so can you pls. send me the complete connection string
> (including optional arguements/parameteres) this would help
> me to understand SYBASE ASE ODBC Driver connection string
> parameters completely and implement the change accordingly.
> thanks a ton! in advance!
> Cheers,
> DRM

Sponsored Links