Monday, November 2, 2009

FAQs October 2009

DSN less connection

There are two ways to connect to an ODBC driver, using a Data Source Name (DSN) or by providing a connection string. The latter is generally referred to as a DSN Less connection. A DSN Less connection can be useful because there is no need to create a DSN on every system your application is running on.

A DSN connection string needs at least a Driver and a DataDirectory. When connecting to a data dictionary you will also have to specify a UID (Username) and PWD (Password). When working with free tables we recommend specifying the table type using the DefaultType keyword. See the example DSN connection strings below.

// Connection to an Advantage Data Dictionary
Driver=Advantage StreamlineSQL ODBC; DataDirectory=C:\Data\Demo\DemoDictionary.add; Uid=adsuser;Pwd=password;

// Connecting to a directory of Visual FoxPro tables
Driver=Advantage StreamlineSQL ODBC; DataDirectory=C:\Data\VFP ; DefaultType=FoxPro;

All of the properties for an ODBC connection are listed in the  help file 

Permissions Required for Modifying RI Rules

You cannot assign rights to Referential Integrity objects within the database. RI rules can only be created in the database by an administrator. Users with ALTER permissions on the tables involved in the RI rule can modify the rule. RI Rules are not visible in the dictionary without the proper permissions. RI rules are enforced for every database user there is no way to exclude users from an RI rule.

Using the AdsCommandBuilder

The Advantage .NET Data Provider includes a AdsCommandBuilder class which is responsible for creating SQL commands to update, insert and delete data for an AdsDataAdapter. Any statements that are not set directly on the AdsDataAdapter will be generated by the AdsCommandBuilder.

By default the command builder requires the table to have a primary key in order to generate the additional commands. Ideally you will have primary keys on all of the tables that you use within your application. In practice this is not always the case. If the table does not have a primary key assigned you must set the RequirePrimaryKey property to false.

The following code demonstrates using the command builder to generate INSERT, UPDATE and DELETE commands for a given AdsDataAdapter.

oCommand.CommandText = "SELECT * FROM Customer"
daTemp = New AdsDataAdapter(oCommand)
dtTemp = New DataTable()
daTemp.Fill(dtTemp)
 
' build the INSERT, UPDATE and DELETE commands
cbAds = New AdsCommandBuilder(daTemp)
cbADS.RequirePrimaryKey = False    ' Primary key not available on the table
daTemp.InsertCommand = cbAds.GetInsertCommand()
daTemp.UpdateCommand = cbAds.GetUpdateCommand()
daTemp.DeleteCommand = cbAds.GetDeleteCommand()

Error 7156

The 7156 "Auto Index Rebuild Active" error appears in the error log it most likely occurred because the server was stopped abnormally. For performance reasons indexes are stored in cache and written to disk as necessary. If the Advantage service is stopped abnormally it is possible that all of the index updates have not been written to the index file.

To correct this situation the Advantage server will automatically rebuild any indexes that it had in cache prior to the cache. This will ensure that the indexes are proper and up to date. It will lock the tables while the index is being rebuilt so you may need to wait a few minutes before connecting clients after restarting a server that has crashed.

No comments: