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.

1 comment:

Steve said...

If I were to use an insert statement and select the lastautoinc from a stored procedure how would I setup my input and output variables?