Working with Visual DataFlex 14.1

April 22nd, 2009

A new addition to Visual DataFlex 14.1  studio is the ability to do data modeling without leaving the studio. The erstwhile DBBuilder still works but is being designated as a tool to maintain the embedded DataFlex database. 

The VDF 14.1 studio was designed to work with minimal changes in the various drivers available in the market from Mertech and DAW. In order to get the information the studio needs  from the driver, a driver specific XML file was introduced which contains attributes supported by the underlying driver ( look in your VDF 14.1\bin directory for these files).

Mertech’s V9 and v10 drivers both work with VDF 14.1 runtime.  However, since most of the changes in VDF 14.1 are in the studio, meaning the new features are geared towards the developer, we recommend that you use v10 drivers for working with VDF 14.1 studio.  

If you do use v9 drivers with VDF 14.1 studio, you will encounter a couple of issues:  1)  you will encounter a crash in the studio when the Mertech login dialog  pops up when you open the table. This is due to some resource contention at the runtime level. This issue was fixed in v10 working in conjunction with Data Access  2) Some new attributes were added to v10 which are present in the driver XML file shipped with VDF 14.1. These attributes are absent in v9 drivers and you will get errors about missing attributes. 

So our recommendation is that if you are using VDF 14.1 studio for designing your apps, use v10 drivers. However, you can continue to deploy with both v9 and v10 drivers with VDF 14.1 runtimes.  The new changes in v10 though will give you better performance and new features.

Refreshing Table Structure
A new feature of VDF 14.1 studio is the ability to refresh the  table definition to get the latest table structure from the backend. A side effect of caching table structures locally in .TD or CCH files is that those structures can out of synch if the tables were modified with non-DF tools on the backend .

To solve this issue, Mertech decided to completely get rid of .TD files so that on every open, we get a fresh structure information from the server and improved the code to open the files just as fast the first time and much faster if the files are closed and opened again. We do this by caching the structures in memory. 

VDF 14.1 studio refreshes the table structure by first  deleting the driver structure cache files (.TD or .CCH in the case of DAW CKs) and then opening and closing the files and generating the .FD file on the fly. This forces drivers using the cache  files to re-read the table structure information from the server.  We feel a better approach is to do this through the API and let the driver handle it (but that is a different story). 

In the case of v10 drivers, if the change is made to the backend and you refresh the table structure, the change will not be read back into the studio because we do not have the .TD files anymore and opening and closing will not release the in cache memory. If the studio had done this through an API call, let the driver handle it, then this would not have been a problem because the driver knows how to refresh its structure information (we have added a new command for that).  Because v10 always reads the data from the backend the first time a file is opened, you will always get the most up to date table definition. However, if someone makes the change to the table while you had the file open, then you will have to get out of the studio and then reopen it.

This is a temporary issue, has limited scope so we will be fixing this  in the next inline revision of v10 drivers.

 

Using Native Client with v10 Flex2SQL MS SQL Server

April 21st, 2009

The v10 SQL Server drivers exclusively use the SQL Server native client libraries.  This is the approach MS is taking and is evident by the fact that almost all new features in SQL 2008 can only be accessed through the native client.

However, as with all things Microsoft, things are not always that simple! In our testing, we have seen anamolies that shouldn’t be there but are. For example, in previous version, we could be sure that older versions of the OLEDB providers would work with newer versions of SQL Server. Simple right?  Not so with the native clients.

When  it is necessary to match up the various versions of  native client versions with the versions of SQL Server. Here is what we recommend:

If you are connecting to  SQL Server 2005,  you can use Native Client 2005 or Native Client 2008 installed.
If you are connecting to  SQL Server 2008,  you MUST use Native Client 2008.  You cannot use the Native Client 2005.

So what does this mean? If your customer has upgraded to SQL Server 2008 without informing you and now are experiencing “weird” behavior  with your application, tell them to make sure they install the Native Client for SQL Server 2008.  And yes, Microsoft wants you to intsall the native client because it is too much for them to just provide a single client connection library like MySQL or PostgreSQL!

The Flex2SQL v10 installation comes with a native client installation, for your convenience.

Resources on Oracle’s Best Practices

March 31st, 2009

Oracle has created a site showing best practices for high availability.

Read the rest of this entry »

How to Set DataBase Name at Login in Flex2SQL for PostgreSQL and MySQL

March 12th, 2009

Sometimes it can be a problem accessing remote servers from the Internet which does not allows access to control databases but just for your database.

Read the rest of this entry »

Accessing existing non-DF tables, synonyms or views in DataFlex

February 26th, 2009

There are two ways to do this: (1) by using INTERMEDIATE FILES and (2) by changing the root name of the Filelist entry to Universal Naming Convention (UNC) syntax.

 Using Intermediate Files (recommended)

Flex2SQL provides an easy way to generate .INT, .FD and .TAG files from an existing table or view:

 Using Flex2SQL, login to the database backend (if you are not already logged on).

Select Generate | .INT File from Table/View/Synonym from the menu. This will bring up a list of all the tables/view/synonyms that exist on the server (Figure: 10.1).

Select the tables you want to generate the intermediate file for and press OK. The .INT and .TD files will be generated and added to your Filelist with the driver prefix (SQL_DRV: or ORA_DRV:).

In order to generate the .FD and .TAG file, check the appropriate options in the “.INT File From Table Grouping” dialog to enable the file generations in Filelist | Options.

Without Using Intermediate Files
To access an existing table without generating an intermediate file, a new entry must be added in the Filelist with the root name obeying the UNC convention.

 ora_drv:\\servername\schemaname*tablename

 Where SCHEMA Name is same as the creator of the table.

Now an open TABLE_NAME command will use the table on the Oracle server.

Following our login example, to open a table called EMP created by user SCOTT (also called schema name) on an Oracle server, we will have the following entry:

 ora_drv:\\servername\scott*emp

 Where “*” (asterisk or star) is used to distinguish between schema name and table name.

Since the calls to retrieve index information from the database system are resource intensive, you might notice a slower opening of files compared to using .INT file method.

In the case of MS SQL Server, you also need to specify the database name sql_drv:\\server name\databasename*schemaname*tablename.

 Steps to Access Existing Tables Using VDF

  • Open Flex2SQL and select Generate | .INT File from Table/View/Synonym.
  • Select a table or view, and press OK. In the “Convert DAT to Table” dialog, change the available options as needed.
  • When prompted, select a Filelist slot for the file.
  • Open DBBuilder and select the file. Make sure it has the driver prefix before it.
  • The Mertech “Login” dialog pops-up. Enter the login information. After login, DBBuilder shows the file structure information.
  • Generate the Data Dictionary files (.DD files) for the table.
  • Open VDF IDE and generate a view with the view wizard using the .DD generated in the previous step.

 You are ready to compile and run your DataFlex program.