Monday, May 31, 2010

ADS 10 Tip #1 – Finding Servers

A new API for locating all Advantage Servers on a network has been added. AdsFindServers uses multicast packets to discover all instances of Advantage running on the network. This new API can discover any version of Advantage.

The API has two options ADS_FS_MULTICAST_ONLY (0x01) and ADS_FS_CONNECT_ALL (0x02). The multicast only option retrieves the minimal information about the servers on the network which includes the IP address and Port number. Version 10 servers will also return the server name. When using connect all the API will attempt to connect to each server that is discovered returning more detailed information.

ADSHANDLE hTable;
UNSIGNED32 ulRet;

ulRet = AdsFindServers( ADS_FS_MULTICAST_ONLY, &hTable );
if ( ulRet == AE_SUCCESS )
  // Use the table handle to save or view the data

Since this is a new API it is only currently available via direct API calls. When the operation completes you receive a table handle which can be used to view the results. For example you could use the AdsCopyTable API to create a physical table containing the results, an example of this method is in the help file. You could also read the results from the table using the Advantage API calls.

Friday, May 28, 2010

FAQs – May 2010

Adding Tables to a Dictionary Using a Server-Side Alias

When you are connected to Advantage using a server-side alias you cannot add tables to a dictionary by simply browsing to the tables. Tables are generally added to a database using the AdsDDAddTable90 API call. This uses Windows API calls to locate the file and add it to the dictionary. When using a server-side alias the path supplied to the AdsDDAddTable90 call is not a valid Windows file path.

You can add tables using a server-side alias path using the sp_AddTableToDatabase system procedure. This system procedure can use a physical path or UNC path to the free table to be added. This path will be properly resolved by the server prior to adding the table to the database.

// Add a free table to a data dictionary
EXECUTE PROCEDURE sp_AddTableToDatabase(
  'Customers', // Object name for dictionary 
  '\\server\alias\customer.dbf', // UNC Path
  1, // DBF/NTX table type
  2, // OEM Collation
  'Cust_Id.ntx;LastName.ntx', // Index files
  NULL ); // No comments
  
EXECUTE PROCEDURE sp_AddTableToDatabase(
  'Employees', // Object name for dictionary 
  '\\server\alias\employee.adt', // UNC Path
  3, // ADT table type
  1, // ANSI Collation
  NULL, // No need to include additional index files
  NULL ); // No comments

Rejected Connections

Advantage 9 and newer automatically increase the connections limit if it is exceeded which should avoid the client seeing any errors if the initially configured value is exceeded. However, when using TCP/IP communications instead of the default UDP/IP, the number of connections is limited to 3000. If this limit is reached then clients may receive a 7033 "Maximum Number of Connections Exceeded" even if the connections parameter is set much higher than 3000.

To avoid this error use the default UDP/IP communication type or use a mix of communication types to avoid exceeding the 3000 connection limit. The TCP/IP communication option was added in version 8.1 of Advantage. Version 8.1 has a limit of 1000 TCP/IP connections.

Where Do I Find Advantage Constants

Many of the Advantage API calls and system procedures use constants for various settings. These constants are also found in some of the system tables, for example system.indexes reports the index type as an integer value ( see this tip ). These constants are all defined in the Advantage Client Engine header file which is distributed with most clients.

In Delphi many of the constants are already defined, for example the AdsTableOptions class contains various options which map to the corresponding constants. Beginning with version 9 of the Advantage .NET Data Provider the Advantage Client Engine namespace was added. You can use the object explorer in Visual Studio to view the defined constants.

Here are the constants used in the system.tables table.

Table Types
DBF/NTX 1
DBF/CDX 2
ADT 3
VFP 4

Table Permission Level
Allow Hidden Field Filters 1
Prevent Hidden Field Filters 2
SQL Access Only 3

Table Caching
No Caching 0
Cache Reads 1
Cache Reads/Writes 2

Wednesday, May 19, 2010

Parsing a Custom Error String

You can create your own exception messages within an SQL script using the RAISE keyword. These custom messages are appended to the end of the Advantage Exception that is returned to the client. For example, this script gets an order reference number from the table based on the Order ID supplied to the Stored Procedure. If no reference number is found then the specified Order ID is invalid and a custom error is raised.

IF @OrdRef IS NULL THEN
  RAISE PROCESS_ORDER_ERROR( 1001, 'Order Number ''' + cInput.OrdID
                             + ''' does not exist');
END IF;

If an invalid order number was passed in the following error would be returned.

poQuery: Error 7200: AQE Error: State = HY000; NativeError = 5154;[iAnywhere Solutions][Advantage SQL][ASA] Error 5154: Execution of the stored procedure failed. Error 7200: AQE/>Error: State = 42000; NativeError = 2224; [iAnywhere Solutions][Advantage SQL Engine]An exception is raised in the SQL script. {[PROCESS_ORDER_ERROR] 1001 : Order Number 'C645D242-6912-11DF-8CBD-7659DFD72085' does not exist} ** Script error information: -- Location of error in the SQL statement is: 1203 (line: 49 column: 9)

This very detailed error message is very useful when creating and debugging SQL scripts. However, there are a couple of potential issues. First whenever a stored procedure fails it will return the 5154 native error code. So you need a way to determine if it was an error you raised or if it was a problem with the stored procedure itself. Second if it is a custom error then you only want to return the custom error message.

If you take a quick look you will notice that the custom error message is delimited by curly braces ( { } ). Making it very easy to parse the error message and extract the custom error string. Here is a simple C# function that does just that.

// Gets the custom error message from a stored procedure error 
// if no error then an empty string is returned
private string ParseSPError(string ErrorMessage)
{
  int iStart = 0;
  int iEnd = 0;

  iStart = ErrorMessage.IndexOf("{");
  iEnd = ErrorMessage.IndexOf("}");

  if (iStart > 0 && iEnd > 0)
     return ErrorMessage.Substring(iStart + 1, iEnd - iStart - 1);
  else
     return "";
}

You could also use a Regular Expression to parse out the message. Be sure to use the SingleLine option to ignore any newline characters in the error message

// Gets the custom error message from a stored procedure error 
// if no error then an empty string is returned
private string ParseSPError(string ErrorMessage)
{
  Match match = Regex.Match(ErrorMessage, (?<={).*?(?=}), 
                            RegexOptions.Singleline);
  if (match.Success)
    return match.Value;
  else
    return "";
}

Monday, May 17, 2010

Syntax Highlighting

I have been using a code snippet plug-in for Windows Live Writer from Leo Vildosola for quite some time. However, it generates a lot of markup so I thought I would do some experimenting with some other syntax highlighting options. 

Having been impressed with StackOverflow and how they display code I decided to try Google Prettify. This is a java based code snippet syntax highlighter which works with most code. It does a very good job highlighting the code and it has some options for specific language support.

Since I am using Blogger as my hosting service I had to add the references to my layout template. Fortunately several people had already done this and after a quick Google search I found an article on StackOverflow – How to use Prettify with Blogger/BlogSpot. The instructions were very straight forward making it fairly simple to test. I posted an example page using Google Prettify here.

Here are my impressions of Google Prettify:

  • Does a good job highlighting the syntax
  • Easy to designate code blocks with the <pre> or <code> html tags
  • Simple to specify highlight colors
  • No support for SQL or Delphi code
  • Line numbers must be added manually
  • Shading is a bit difficult

Another popular highlighter is SyntaxHighlighter by Alex Gorbatchev. This is also a java based code snippet syntax highlighter but it does a lot more formatting than Google Prettify. There are several themes available as well as instructions on creating your own. It also has support for many languages, including SQL and Delphi, as well as a good tutorial for adding new languages. In addition to the syntax highlighting features the plug-in also includes buttons for showing the code in a new window, copying the code to the clipboard and printing options.

I was able to find a great blog post from Carter Cole on how to configure SyntaxHighlighter for blogger. I setup an example page using SyntaxHighlighter here.

Here are my impressions of SyntaxHighlighter:

  • Does a good job highlighting the syntax
  • Easy to designate code blocks with <pre> or <script> tags
  • Support for all the languages I use
  • Good default formatting of code
  • Options for viewing, printing and copying the code
  • CSS themes are a bit complex
  • Load time can be a bit slow ( I am using the hosted model )

In the end I chose to use SyntaxHiglighter because it had more features and I liked the formatting better. It is easier to tweak the formatting for Google Prettify but it takes a lot more markup to get the look that I want for my code snippets.

The only real downfall to using either of these methods is that the formatting is only available when viewing the actual blog site. The formatting will not be shown in the RSS feed, so if you are looking at a big block of code you might want to open the post directly.

Wednesday, May 12, 2010

Sybase DevDays 2010

You may have noticed my brief hiatus from blogging. I have been on the road presenting at Sybase DevDays 2010. Mike Hagman and I presented in Boston, New York, Houston, Los Angeles and San Francisco over the past two weeks. We had a great time talking about version 10 and listening to the feedback from all of the attendees.

Thank you to all of the developers who attended one of the Sybase DevDays Events over the past few weeks. If you weren't able to attend DevDays you can get more information about Advantage 10 by watching the Advantage Pure and Simple Webcast series. You can view the sessions using the links below.

Session 1: Progression
Session 2: Developer Productivity
Session 3: Application Performance

Advantage 10 is due to be released in June and I have lots of new features to discuss. I am currently compiling a list of new features and enhancements to discuss. June will be another tip of the day series focused on Advantage 10.

Monday, May 3, 2010

FAQs – April 2010

Error When Backing Up Advantage Extended Procedure

You may get a file not found error when backing up an Advantage Extended Procedure (AEP) file that was added to a data dictionary prior to version 9. Even if the aep file is located in the proper directory and the path seems correct in the dictionary. Previous versions of Advantage did not include the .aep extension in the path for the file. You can fix this problem by deleting and adding the reference to the AEP file which will include the file extension.

64bit Servers and External Libraries

If you have built external libraries which contain either Triggers or Stored Procedures these libraries may cause errors with a 64-bit Advantage server. Since these external libraries are loaded into the Advantage Server process space they must be built as 64-bit libraries. A 64-bit process cannot load a 32-bit DLL.

If you built the libraries in .NET you can compile them as 64-bit, beginning with version 9 of the Advantage .NET Data Provider. Unfortunately Delphi does not currently support 64-bit development.

You can run the 32-bit version of Advantage on a 64-bit operating system which will continue to work with your 32-bit external libraries.

Validating SQL Statements

You can verify that an SQL statement is valid without running the statement on the server using the AdsVerifySQL API. This function sends an SQL statement to the server where it is parsed but not executed. If the statement is valid then AE_SUCCESS ( 0 ) is returned otherwise an error is returned.

If you are using Delphi you can validate an SQL statement using the VerifySQL method of the TAdsQuery component. This method will throw an exception if the SQL statement is not valid.

try 
  TAdsQuery1.CommandText := 'SELECT * FROM tablename'; 
  TAdsQuery2.Open;
except 
on E: EDatabaseError do 
begin 
  if ( E is EADSDatabaseError ) then 
  begin 
    ErrorString := (E as EADSDatabaseError).SQLErrorCode + E.Message; 
    application.messagebox ( pchar(ErrorString), 'Advantage Error', 0 ); 
  end;
end; 

Getting the Last AutoInc Value

There are several ways to get the last AutoInc value after inserting a record. The AdsCommnad object and TAdsQuery object have a LastAutoInc property. You can also get the last AutoInc value within an SQL Script using the LastAutoInc() scalar function. For example:

INSERT INTO EmployeeTable ( Name, HireDate ) 
  VALUES ('Chris Franz', '1999-11-01');
SELECT LastAutoInc( STATEMENT ) FROM system.iota;

There are two options for this function; STATEMENT and CONNECTION. STATEMENT returns the last AutoInc value for the current statement as shown above. CONNECTION returns the last AutoInc value for the entire connection.