SYBASE TIPS AND TECHNIQUES

Q: Can I make use of/execute Sybase Stored Procedures? (PDOXWIN, other)

A: Yes.  In fact it is quite simple.  Let's say you need information 
on who is currently connected to the server.  Open a new SQL file, select 
the alias for you Sybase connection and when the editor comes up,
type in sp_who and press F8.  You will be presented with an ANSWER.DB 
table with pertinent information about who is currently logged in/connected.

Q: What about stored procedures in ObjectPAL? (PDOXWIN, other)

A: Yes.  Again, it is quite simple.  There are two basic components and 
they are the database and SQL types which are key ingredients in making 
this happen.  A very rudimentary example would a pushButton
to add a new user and login to the server.  To do so in Sybase, the stored 
procedure sp_adduser and sp_addlogin will be used.

method pushButton(var eventInfo Event)
var
 db 		database
 tc		TCursor
 sqlVar		SQL
 name,
 password	string
endVar

name.view("Enter New User Name")
password.view("Enter Password For New User")
db.open("SYBASE1")
db.begintransaction()
sqlVar=sql
               use master
	          exec sp_addlogin ~name, ~password, databaseName
	   use sqltech
	          exec sp_adduser ~name, ~password, groupName
               endsql

if not executeSQL(db,sqlVar) then
   errorshow("Add User Failed")
   else msgInfo("Add User Successful",name+" "+"has been added successfully")
endif
db.close()
endmethod

Q: Can Paradox make use of Sybase Triggers? (PDOXWIN, other)

A: Yes.  INSERT, UPDATE and DELETE are fully supported in Paradox for 
Windows. Here is an example of how to prevent the update of a column 
from happening and raise a user defined SQL error in Sybase in
the process.  The first part is the trigger definition which can be 
executed in the SQL File editor.
Remember to set your sybase alias BEFORE you attempt to run it.


create trigger denyUpdate
on tableName
   for update as
   if update(columnName)
      begin
        	raiserror 30000 'Cannot change the value of columName. Changes 				
	have been discarded.'
      rollback transaction
      end

Once you have created the trigger object on Sybase proceed by creating 
a form and putting your SQL table in the data model.  Since the trigger you 
have created raises an error, you'll "raise" the error from the
status bar into a dialog box.  For that, the error method will be used 
at the form or page level.  Whichever is best suited for your application, 
type in the following:

error method(var eventInfo ErrorEvent)
if eventinfo.reason()=ErrorWarning
    then eventinfo.setreason(ErrorCritical)
endif
endmethod

Q: Will Paradox use Sybase indexes?  (PDOXWIN, other)

A: Absolutely.  It is recommended that unique clustered indexes be used 
since performance is greatly enhanced.  Particularly with large tables, 
TCursors, setGenFilter(), qLocate, etc.  All are greatly improved
performance wise.

Q: When Paradox is SQL enabled, I really cannot tell that I am truly 
connected.  How do I get to my data interactively?

A:
1) Tables:  File | Open | Table.  When the Open Table dialog box 
is displayed, select the "Drive (or Alias)" drop down list or click the
"Aliases" button.  DoubleClick on  your Sybase alias (logon if needed) 
and your Sybase tables will be displayed where Paradox tables are normally 
displayed.
	
2) QBE:  File | New | Query.  When the "Open File"dialog box is displayed,
select the "Drive or Alias" drop down list or click the "Aliases" button.  
DoubleClick on  your Sybase alias (logon if needed) and your Sybase tables 
will be displayed where Paradox tables are normally displayed.

3) Reports and Forms - data-modeling:  To put a Sybase table in your data
model, you will repeat the same steps for opening a table or QBE, however,
you will be in the data model of the form or report.  When the "Data Model"
dialog box is displayed, select the "Drive or Alias" drop down list.  
DoubleClick on  your Sybase alias (logon if needed) and your Sybase tables will be 	
displayed where Paradox tables are normally displayed.

4) Field Objects in Forms and Reports:  When placing an undefined field in
a form or a report, you'll select the elipse (...) to get to the "Define Field" 
dialog box.  If the field is not in the current table(s) that are in the data 
model, repeat the steps outlined in topic 3.

Problem:  Closing/Opening Sybase Tables is SLOW
This can be caused by many different things, but, fortunately it is usually
because the tables do not have UNIQUE CLUSTERED INDEXes or the SQLSERVER 
section does not include the URGENT parameter.

Problem:  URGENT Parameter w/TCPIP
Document from SYBASE compuserve form:

			Urgent Data Setup requirements
			==============================

In order for the "urgent" parameter to have an effect, both the PC and the
host must be capable of Out of Band Data (OOBD) using the RFC 793 spec.

The following are the vendors who support OOBD:

	FTP PC/TCP should load ethdrv.exe with a -B option to enable
	the use of the RFC 793 style urgent data (default is RFC 1122).

	Almost all of the TCP/IP protocol stacks support the urgent
	data (OOBD - RFC 793) in their current implementations. Some
	of the older versions of the software may not have the urgent
	data implemented (e.g. - LWP 4.0 did not have this, and needed
	a patch to implement).

	IPX/SPX has OOBD support inherent in the protocol, as does
	the named pipes protocol.

	DECNet is not capable of OOBD.

========================================================================

Most hosts that have TCP/IP support RFC 793, and need no modifications
to use this style of urgent data. The exceptions are:

	Solaris 2.2 needs to have patch 101018-06 & -04
	Solaris 2.3 needs to have patch 101346-03

Both of these may need to run:

 /usr/sbin/ndd -set /dev/tcp tcp_rexmit_interval_max XXXX

Where XXX is in milliseconds (500 for 1/2 second) to increase the TCP
retransmit interval.
=======================================================================

	NCR Unix System V.4  2.0.2 needs to have the
	/etc/conf/pack.d/tcp/space.c recompiled with the following
	parameter in the file modified:

	int tcp_bsd42_urgent = 0

change to

	int tcp_bsd42_urgent = 1

	After re-compiling, re-build the kernal, and restart the OS.

=====================================================================

		Explanation of urgent data (OOBD)
		=================================

System 10 used in band urgent data. This means that it will send the
urgent request in the normal stream of communications. The System 10
server can also respond to the OOBD by using RFC 793 style urgent bit.
This allows a pre-system 10 client to communicate the dbcancel() to a
System 10 server. A System 10 client will not use RFC 793 at all, so
it won't do urgent data to a pre-System 10 server (no OOBD).

RFC stands for Request For Change, which is what changes to IEEE
standards are tracked by.

Pre-System 10 servers use only RFC 793 OOBD, and don't to inband
attention signal. System 10 servers use inband attention signals,
and can respond to OOBD. Pre-System 10 NetLib will use OOBD, and
can't do inband attention. System 10 NetLib will do only inband
attention, and doesn't send OOBD (as far as I know).

Now the reason for all of the flags, etc. :

There are two "standards" for OOBD, RFC 793 (single bit), and RFC 1122
(seven bit). Our software only understands the single bit version. Some
other software will handle the other RFC (notably ODBC). Solaris, and
possibly other O/S environments also implement the RFC 1122 as their
default for OOBD. This means that our software (NetLib) won't be able
to pass the correct data, since it is using an urgent bit instead of an
urgent byte. ODBC has implemented this same strategy, but the 34cancel
parameter tells it to use the "pre 4.x" style of OOBD, which is how we
have implemented the urgent data.

The "-B" flag on the ethdrv tells the FTP software to use the BSD style
of urgent pointers (RFC 793). Otherwise it defaults to RFC 1122 style
urgent pointers.

OOBD = urgent data = (RFC 793 or RFC 1122)

RFC 793 = BSD (Berkley Sockets) style urgent data = single bit = 34cancel

RFC 1122 = seven bit = MS 4.x = ethdrv default


Problem:  Moving large amounts of data up to a Sybase server can fail if 
you do not have enough space in the database "tempdb".

When copying a large PDOX table to a remote SQL table or inserting large
amounts of rows with SQL...ENDSQL, make sure that there is enough
space allocated to the transaction log in "tempdb".  When space is limited, the
following error may occur:

"SQLERROR: Can't allocate space for object '-nn' in database 'tempdb'
because the system segment is full.  If you ran out of space in Syslogs,
dump the transaction log.  Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment."

Executing the following will take care of this error.  But, use it with caution:

DUMP TRANSACTION DATABASE_NAME WITH NO_LOG

In the future, you can alter the "tempdb" database and increase the amount of 
disk space for temporary tables/transactions.

Q:  Where do I find Sybase error messages?

A:  You can query the system table sysmessages for virually all of the Sybase 
generated errors.

Q:  Where is the text of the triggers and stored procs that I have written?

A:  Query the table syscomments.  Make sure to join it with the sysobjects table by
the id field.  Your where clause will be for sysobjects.  Be sure you know the
name of your object(s).

Problem:  Data Disappears After It Is Entered Into a Newly Created Table
The table needs to be uniquely indexed.  Make sure to use a unique clustered index.

What is the syntax for ....

CREATE TABLE
create table tableName
(columName colType,
 columName colType,
 .....)

CREATE INDEX
create [unique clustered] index indexName on tableName(colName, colName, ....)

CREATE TRIGGER
create trigger ownerName.triggerName
before update of tableName on ownerName.tableName
for each row
begin
	raise_application_error(-20501,'Cannot be null');
end;

GRANT
grant select,update,insert on tableName/ objectName to userName

Creating Stored Procedures
create proc returnCode @num float, @str varchar(20)
as select * from mastertab
where first=@num and Name=@str
select @@rowcount

Problem:  TCursor.Copy() Fails on Sybase
Remember that Sybase is the only server that appends an object with its 
ownername.  For example:

dbo.syslogins

When providing a name for the new table, be sure to include the ownername:

tc.copy(":SYBASE:"+"dbo."+"newTableName")

Problem:  Error when Opening Tables With Over 64 Columns
When you create a table with > 64 columns and do not specify that they are 
null or not null, Sybase defaults to not null.  This is a required field as
far as IDAPI is concerned and it allocates 1k per required
check.  Remember that Windows has a 64k resource limit.

Problem:  Column Types Must Be Lower Case When Creating Tables
Not much to this problem.  If you specify (columName CHAR(10)), this will fail.
You must type in char all lower case.

Problem:  DateTime Column Problems
These problems manifest themselves in different ways.  One of them is an error 
message "Conversion of char to DateTime resulted in a datetime value out of 
range."  To correct this make sure that the DBLibrary
- W3DBLIB.DLL - is dated 9-17-93.  Or, simply try one until it works.  
There are many different sources for this file.

Additionally, connecting to a System 10 server will REQUIRE W3DBLIB.DLL until
IDAPI supports Sybase's Client Library.

Problem:  Network Layer not loaded
The error is accurate.  If connecting to an NLM server, make sure you have your
network software loaded (LSL, IPXODI, NETX, etc.).

Problem:  Table does not appear in the table list dialog after it was created
In 4.5 Paradox w/SQL Link 1.0, you had to disconnect and then reconnect to see 
the newly created table. In With IDAPI, all you have to do is set SQLPASSTHRU
MODE to SHARED AUTOCOMMIT and the changes implemented will be immediate.

Problem:  ODBC, New Driver in IDAPI
Trying to configure an ODBC driver with IDAPI causes an error along the lines 
of "The version of ODBC Adminstrator must be 2.0 or greater."  Usually this
occurrs because the files ODBC.NEW and ODBCINST.NEW in the IDAPI directory 
need to be copied to the WINDOWS\SYSTEM directory.  That is
to say:

COPY ODBC[INST].NEW C:\WINDOWS\SYSTEM\ODBC[INST].DLL

The rest is academic.


Problem:  Client Library vs. DB Library (System 10)
A lot of things can be covered on this.  What it boils down to is that IDAPI in 
its current incarnation does not support System 10 Client Library API (CTLIB.DLL).
The only way to connect to a System 10 server is to use the DBLibrary API 
(W3DBLIB.DLL).  Having said that, consider the alternative.

IDAPI can connect to many different file formats and database back-end systems/SQL 
servers via ODBC drivers.  That means systems that are not normally tested or 
supported.  Here then is a way to get to
System 10 and CTLIB API.  To make it happen, you'll need the latest ODBC drivers
from a vendor that has developed a driver (MS, Intersolv, etc.) for System 10 and
the Client Library API.  Some things you may run into setting up these drivers 
through the ODBC Admin utility are listed  below:

Problem:  Setting up the DSN (DataSource Name) for System 10 gives an error message
claiming that there is not enough memory to run the setup routines for this driver.

Solution:  In almost all instances of this error, it is because the DLLs that 
support CTLIB do not exist or cannot be found.  Make sure the installation of the
client software has been done correctly.  Often times, the user just needs to 
run the batch file WSYBSET.BAT.  Here is a sample batch 
file:

set PATH=C:\DOS;C:\WINDOWS;C:\QEODBC;C:\SQL10\BIN;C:\SQL10\DLL
set SYBASE=C:\SQL10
set DSQUERY=mercury
set INCLUDE=%INCLUDE%;C:\SQL10\INCLUDE
set LIB=%LIB%;C:\SQL10\LIB
set USER=pdox1

Here is a sample of the DLLs needed to connect to the NLM version of System 10:

WNLNOVSP.DLL	21,310	01-17-94   2:37p
SH20W16.DLL	37,376	08-24-93   1:00a
WCTLIB.DLL	303,271	02-18-94   3:19p
WCOMNLIB.DLL 	229,555	03-01-94   3:48p
WBLKLIB.DLL	55,953	01-18-94   3:31a
WINTLLIB.DLL	35,725	01-17-94   9:47p
WTCLLIB.DLL	113,599	03-08-94  10:32a
WCSLIB.DLL	43,047	02-18-94  10:20a
SH20MON.EXE	7,168	02-03-95   4:14p

Here is a sample of the network DLLs needed in addition to the latter:

NWIPXSPX.DLL	35,308	05-24-94   4:00p
NWNETAPI.DLL	106,047	05-24-94   4:00p