Yes. You can create a "temporal" DSN using f90SQLDriverConnect. For example, the following opens a DSN-less connection to the Microsoft Access BookSales database (included in the f90SQL installation):

ConnStr = 'DBQ=C:\Program Files\CanaimaSoft\f90SQL\Examples\' &

'Database\BookSales.mdb;DRIVER={Microsoft Access Driver (*.mdb)}'

call f90SQLDriverConnect(ConnHndl, f90SQL_NULL_PTR, ConnStr, &

ConnStr,ConnStrLength,SQL_DRIVER_COMPLETE,iRet)

You must indicate the full path to the database file in the DBQ key. For more examples, you can check the programs under directories ExcelRead, ExcelWrite and ExcelUpdate. All of these use f90SQLDriverConnect to open DSN-less connections to Excel spreadsheets.

Microsoft ODBC driver manager's support for UNC seems to be an old problem that has not been fixed. If you check their knowledge base you will find that repeatedly they give contradictory answers regarding this topic (sometimes they say that UNC is supported, most times they say it isn't). In most cases they recommend that you map the network share to a drive letter in your computer. This can be done using Windows explorer: click on Tools/Map Network Drive.

We were able to establish working connections using UNC. Here is an example of the connection string we used:

ConnStr='DBQ=\\server4\canaimasoft\data\test1.mdb;

Driver={Microsoft Access Driver (*.mdb)};

SystemDB=\\server4\canaimasoft\system\useraccess.mdw;

UID=yourusernamehere;PWD=yourpasswordhere;'

You should check the following points:

1) Make sure you have write/execute access to the directory and mdb file pointed to by the UNC.

2) Make sure the path exists (try, for example, mapping a drive letter to the UNC).

3) If the database is password protected, make sure you include the system database with the SystemDB keyword. Otherwise, you will be denied access. If it isn't password protected you should use the default user 'admin', no password, and the default systemDB file system.mdw which is usually found in the Windows system directory (system32 on Windows NT systems).

4) If the database is password protected, make sure the UID (i.e. user name) and PWD (password) are ok, and that the user is allowed access to the database.

5) Check the version of the ODBC driver manager you are using. To do this, go to your system directory, find odbc32.dll, right-click on it and select properties. The version should be 3.5 or higher (odbc 2.x does not support UNC, according to Microsoft). You can download the latest version from our web site.

f90SQL-Lite is optimized to manage small databases with an extremely small memory footprint (the library uses 1 byte cursors). For this reason f90SQL-Lite imposes a limit of approximately 100 records which may be read from a database. On the other hand the professional version, f90SQL-Pro, uses 16 bit cursors so it does not have a limit on the number of records which may be retrieved.

You may, however, read more than 128 records by issuing sequential queries. To do this you need to prepare your queries so that the first query returns the first 100 records, the second query returns the second 100, and so on. Between each query, you will need to close and re-open the connection with the database. For example, if you need to read 300 records from a table, you can prepare 3 queries as in the following pseudocode example:

query(1)='SELECT * FROM MyTable WHERE RID<=100'

query(2)='SELECT * FROM MyTable WHERE RID>100 and RID<=200'

query(3)='SELECT * FROM MyTable WHERE RID>200 and RID<=300'

Open an environment handle

do i=1,3

Open a database connection (Using f90SQLConnect)

Open a statement handle (Using f90SQLAllocHndle)

Execute query(i) (Using f90SQLExecDirect)

Fetch the result set of query(i)

Close the statement handle

Close the Connection

enddo

Close the environment handle

Microsoft HTML-Help viewer has some documented problems when printing large chapters. These problems may appear in some computers and not in others. If your system reports problems when printing from the HTML-Help viewer you can print using the PDF version of the manual. You need to install Adobe Acrobat Reader to do this. You can find the PDF version of the manual and Adobe Acrobat Reader in your f90SQL CD.

The online documentation for f90SQL and the f90SQL Wizard is included with the f90SQL CD. The f90SQL user manual comes in two formats; Microsoft's HTML-Help, and Adobe Acrobat (PDF). The f90SQL Wizard manual is provided in Adobe Acrobat format only. All of the documentation can be found on the f90SQL CD, in subdirectories under the OnlineManuals directory. During the installation of f90SQL, the manual in HTML-Help format is copied to your hard drive and an icon pointing to this manual is also created in the f90SQL program group. During the installation of the f90SQL Wizard, the wizard's PDF manual is copied to your hard drive. The PDF version of the f90SQL Manual is not copied to your hard drive (it is about 30 MB).

To use the HTML-Help version of the manual, your system needs Microsoft's HTML-Help viewer. If you have installed any of the latest versions of Microsoft's products, it is very likely that the viewer is already in your system. In any case, you can also install the viewer from the Drivers subdirectory in the f90SQL CD. Try double-clicking on the HTMLHelp\f90SQLHelp.chm file on the f90SQL CD; if it opens then the viewer is already installed.

You don't need set a length for parameters (or columns) that are fixed-length. These include integers (all accepted types), real, double precision and dates. For the case of date-type parameters, the column size and number of decimal digits are also ignored.

You can connect to a database using two subroutines: f90SQLConnect and f90SQLDriverConnect. In both cases you go through the ODBC API. f90SQLConnect expects to find a DSN that you have previously created using the ODBC Administrator. With f90SQLDriverConnect, however, you can create a temporary DSN on-the-fly, by passing all the information the application needs to connect to the database. As a minimum, this information should include a driver name (DRIVER key), the path and file name of the database file (DBQ key), a user name, and a password. The DBQ key only works on local databases (i.e. you could not use this key to open a connection to a SQL server). For example, if you need to open a connection to an Access database in c:\Databases\db1.mdb you can use:

ConnStr='DRIVER={Microsoft Access Driver(*.mdb)};

DBQ=c:\Databases\db1.mdb;UID=YourUserID;PWD=YourPassword'

call f90SQLDriverConnect(ConnHndl, f90SQL_NULL_HNDL, ConnStr, TmpStr,

TmpStrLen, SQL_DRIVER_NOPROMPT, iRet)

TempStr is a temporary string in which the full connection string (i.e. with all parameters) is returned by the driver. TempStrLen is an integer variable in which the length of this TempStr is returned. SQL_DRIVER_NOPROMPT makes sure f90SQL doesn't request additional connection information if the driver needs more than you passed in the connection string. In this last case, if you don't pass enough information to open the connection, f90SQL returns from f90SQLDriverConnect with an error condition.

The on-line manual for f90SQLDriverConnect has more information on the options. There are also several examples that use f90SQLDriverCOnnect (both in the f90SQL-lite distribution and the

Use f90SQLDriverConnect with the SQL_DRIVER_COMPLETE option. At run time f90SQLDriverConnect attempts to open a database with the information you provide in your code. If more information is needed (for example, a user name and password), f90SQLDriverConnect will request it from the user using a pop up window.

The serial number requested by f90SQL-Lite is given in the same window that asks for it. The number is 00-0000-0.

With f90SQL/ODBC you have two basic ways to change data in a table:

1) You can use insert/delete/update queries. For example, you can create an update query like this:

UPDATE Employees

SET Employees.Salary = 50000

WHERE Employees.ID = 1025

This query finds the employee with ID 1025 and changes his/her salary. This is the standard method to perform an update. The disadvantage of this method is that the database engine has to execute the query. This means it has to parse and compile the query, create an access plan, execute, etc. All of these processes take server resources. The advantage is that the whole process takes place in the server. You are not moving data between the server and the application, so there is little network overhead.

2) A positional update takes advantage of a cursor that you have already set. A cursor is the link between your application and the database, and it contains references to a group of records you have selected at some point. Very frequently, updating values in a record is preceded by a SELECT query. For example, your application may have issued a SELECT query to present the records to the user, so she could choose the record she wants to change and enter the new value for one of the fields. A positional update gives you the ability to perform the change using the cursor the SELECT query created. Since the reference to the record (i.e. the cursor) is already there, why send a query as in (1) when you already have the record pinpointed? This is the normal method used to make changes to a record set in ADO or DAO.

The main advantages of the method are that the server does not have to compile/execute another query to effect the change. Also, positional updates seem to better fit the procedural paradigm we use when writing a FORTRAN program.

The disadvantages are several: Higher network traffic (records have to make roundtrips from the server to the application and back), cursors take a lot of space (either on the client or the server), and if you have a multi-user system, you also have to consider things like record locking, concurrent use, etc.

Most modern database managers support positional updates, however, some older drivers only support standard update/delete/insert queries.

This problem occurs because you are calling f90SQLBindCol with f90SQL_NULL_PTR for the StrLen_or_IndPtr argument, and some of the character fields have null values. The StrLen_or_IndPtr points to a variable that contains the length of the string read for a character field when you fetch a row of data. When the field contains a null value, f90SQL tells you this by setting the variable pointed by StrLen_or_IndPtr to SQL_NULL_DATA, but because you are passing a null pointer (i.e. f90SQL_NULL_PTR), f90SQL has no way to tell you this, and instead returns an error message. This is indicated in the reference manual, at the end of the description of the StrLen_or_IndPtr argument for f90SQLBindCol.

The following is an example of the correct way to bind to a character field:

1) Add the following variables to your declaration section:

integer(SQLINTEGER_KIND)::VarXStrLen

character(len=255)::VarX

2) Use the following binding call example:

ColNumber = 3

call f90SQLBindCol (StmtHndl, ColNumber, SQL_F_CHAR, VarX, &

loc(VarXStrLen), iRet)

3) Check if the fetched field is null with the following code:

if (VarXStrLen.eq.SQL_NULL_DATA) then

print *, 'field is null'

else

print *, VarX(1:VarXStrLen)

endif

One more thing to be aware of: When you fetch the data, VarXStrLen contains the length of the fetched strings or SQL_NULL_DATA if the database had a null value for the field 'VarX'. In this last case, the value stored in your Fortran variable 'VarX' before the fetching operation does not change. So let's say you read a record that has VarX='Some character string'. Your Fortran variable VarX will contain 'Some character string' and VarXStrLen has 21. After this you fetch another record for which VarX is null. Your Fortran variable VarX will still contain 'Some character string', from the previous record, but VarXStrLen has SQL_NULL_DATA. In other words, you must always check VarXStrLen before you use Fortran variable VarX.

f90SQL only works with programs compiled for the Windows environment. It doesn't work with Lahey's DOS-Extender.

f90SQL was originally developed for a similar project. We were porting a Datatrieve database from VMS to a client/server NT system. The code to make the database analyses was all written in Fortran. So f90SQL certainly would work for your project.

There are, however, a few points you must keep in mind when doing such porting:

1) If the data access code of your application is well isolated, it should be easy to replace it with subroutines developed around f90SQL. This would keep the same ISAM data access paradigm you are using right now. This should work fine if the new database will be located in the same computer where your Fortran-f90SQL application runs.

2) If the database will be located remotely (i.e. your Fortran application and the database are located in different machines), then you may need to shift to a client/server data access paradigm. f90SQL works equally well under client/server systems, but very likely you'll be faced with having to do significant changes to the design of your application. This is because the ISAM access paradigm usually does not scale well in Client/Server systems.

If most of what your current Fortran code does is pure data-access, then you may be better off rewriting the application with a different language/data access product combination (e.g. VB/ADO, Delphi/BDE, etc.). The bottlenecks for data access are at the server level (processing the data requests) and the network level (moving the data from the server to your client application and back), so the language/compiler you use has little effect. The object-oriented data access facilities of VB and Delphi would make your work easier.

If most of your current Fortran code performs manipulation/analysis of the data obtained from the database, then you would want to stick to Fortran-f90SQL. Fortran is still the fastest language for data analysis. Data access with f90SQL is just a little more complicated than using ADO or BDE, but it is also faster because f90SQL has much less overhead.

A hybrid solution that some of our clients have used to move ISAM based applications into client/server environments is to develop a middle tier using fortran-f90SQL. The middle tier works as a buffer accessing the database using the client/server conventions, while offering the data to the application using the ISAM convention. The advantage of this approach is that the original application only needs to be changed as in (1), while at the same time you benefit from the advantages of the client/server systems.

Read the f90SQL License agreement for details.

In most cases a reduction in performance when moving from an ISAM system (like MS-Access) to a client/server system is the result of attempting to keep the same paradigm to access data. You may want to review your code and the way you are moving your data between your programs (the clients) and the database (the server). Ideally you want the data to do at most one trip in each direction, and try as much as possible to have the server do the processing. Of course this is not always possible, and some applications simply cannot be adapted to the client/server paradigm. In a client/server you have two main sources of bottlenecks: the server itself (i.e. a busy server without enough resources to handle all the requests), and the network (the data has to move from your computer to the server and back). The main culprit is usually the second. Following are a few aspects to consider when designing/reviewing an application:

1) Is there any processing that can be done in the server rather than the client (for example, is your client selecting records by scanning through them)? If this is the case, you should move these operations to the server.

2) What types of cursors are you using? Dynamic cursors require a lot of network and server resources where a static cursor may be sufficient.

3) Are you using positional updates?

4) What type of locks are you using in your recordset?

5) Is the size of your recordset larger than it needs to be? Are you using all of the records and fields that are retrieved when executing your queries?

This error is usually the result of calling a function with arguments that do not match the function definition. All f90SQL subroutines are strongly typed, so these kinds of errors can be detected during compilation.

Check that the arguments agree with the definition in the manual. A common mistake is passing an integer literal constant (which in most compilers is, by default, of type integer*4 or an f90SQL SQLINTEGER_KIND) where the subroutine is expecting an integer*2 (i.e. a SQLSMALLINT_KIND).

Try using a "count" query. For example:

Select count(F1) as NRows FROM MyTable

This will return a record set with only one column (NRows) and one record, containing the number of rows in MyTable.

f90SQL does not provide facilities for DDE communication, because it performs all its tasks through the ODBC drivers of the databases. If your Fortran program gets data from another source (e.g. a continuous data feed), then you can move this data into a database using a query and f90SQL (or using positional updates). If the database is being updated through some external component (e.g. an interface to a data feed), you can also use f90SQL to get the updated data from the database. However, unless the real-time data feed provides an ODBC interface, we're not aware of a way that you could use f90SQL to directly read the data produced by the real-time data feed.

No, f90SQL is strictly ODBC. However, OLEDB uses ODBC for many databases and ODBC is used for some tasks in databases that have OLEDB drivers. So OLEDB is not completely isolated from ODBC. In fact, many OLEDB drivers will not work if the corresponding ODBC driver is not present. In general, if you can access a database through OLEDB, you will also be able to reach it through the corresponding ODBC driver, and through f90SQL. Keep in mind, however, that the functionality offered by OLEDB may be different to the functionality offered by the ODBC driver.

You can use SQL date functions to do some of this (i.e. extract year, month, day, etc. from a date column. See Appendix 5 in the user manual). You can then bind integer Fortran variables to the results of these functions. Also, if you bind a date column to a Fortran string variable, f90SQL will convert the date into a character string using the local windows setup. Another option that gives a numeric representation of a date is to bind the date column to a Fortran variable of type TIMESTAMP_STRUCT. Example 55 does this. Chapter 4 (Fortran data types in ODBC) describes this structure. However, we're not aware of any way to obtain the internal representation used by Excel to store a date. Attempting to bind the date column to an integer Fortran variable would likely return an error, as this conversion is not supported by f90SQL (a table of supported conversions is shown in Appendix 3 of the user manual).

Dates are stored in Excel as the number of days elapsed since a given base starting date. This base date should be given in the Excel documentation. If you really need the internal date representation used by Excel, you should be able to derive it by issuing a query in which one of the returned columns is the result of ODBC function TIMESTAMPDIFF (i.e. time stamp difference between your date and the base date.), setting the interval to days. The only problem with this approach is that you would be hard-coding the base date into your Fortran program. If Excel ever changes this date, your Fortran program would give you the wrong results.

Indicator variables are used only on the Fortran side. Length-indicator variables are used mainly for character strings returned by a query or to know whether a numeric value returned by the query is null. The idea is this:

Suppose you have the following query:

SELECT NumericField, CharacterField from MyTable

The query returns two columns; one with a numeric value, the other with a string. Suppose you used the following bindings for this query:

f90SQLBindCol(stmthndl, 1, SQL_F_INTEGER, FortranVar1,

loc(FortranVar1_LenInd),iRet)

f90SQLBindCol(stmthndl, 2, SQL_F_CHAR, FortranVar2,

loc(FortranVar2_LenInd),iRet)

After fetching a few records, you get a value of 0 for FortranVar1. Was this really a zero in the database or was it a null value (i.e. an empty field for this record)? The only way to determine this is by testing the value returned in FortranVar1_LenInd. If the value is SQL_NULL_DATA then the database had a null value.

In the case of FortranVar2, which is a character variable bound to a character column, FortranVar2_LenInd always contains the length of the character strings returned by the query for the record you have just fetched, or SQL_NULL_DATA if the fetched record returned a null value for this column.

See Chapter 4 (Buffers) and Chapter 5 for more information about this topic and a few useful examples.

This problem involves checking for null (empty) values in a field: You cannot do this directly with the variable that you bind to the field. Instead, you must check null values using a length indicator variable. For example suppose you have the following binding:

call f90SQLBindCol(StmtHndl,ColNumber, SQL_F_SHORT, &

Year, loc(YearInd), iRet)

When you fetch your data, variable Year contains the value of the field, or if the field is null (empty) then YearInd contains SQL_NULL_DATA, indicating a null value.

In Excel the individual cells of a column can contain any type of data (numeric, characters, or even graphics). This heterogeneity would never happen in a database table because a column can only contain a single type of data. If you have an Excel column that has characters in column 1, row 1 and numbers in column 1, rows 2 and 3, how should this column be presented to your ODBC program - as a character column or as a numeric column? The way Microsoft's ODBC driver for Excel solves this problem is to scan the first few rows of each column and decide the data type of the column based on which data is the most common. There are two ways you can force the ODBC driver for Excel to write data as numeric values:

1) You can add a few hidden rows with zeroes at the beginning of your spreadsheet. The number of rows you need to add depends on the number of text rows for the same column. If you check the spreadsheet used in Sine.xls (examples included in f90SQL), you will notice that the first two rows of the spreadsheet are hidden and contain zeroes.

2) You can make sure the Excel spreadsheet is open while your f90SQL program is running. When the spreadsheet is open, the default type of all columns exposed through the ODBC driver becomes numeric (double real).

Please note that this is not a problem with the f90SQL library, but the result of the behavior of the ODBC driver provided by Microsoft.

An Excel spreadsheet from the ODBC point of view is nothing more than a database table. This means it has column names and column data, which all must be of the same type for a given column. So you cannot have a row with one type of data, such as a string, and another row with numeric data.

Here is a possible workaround. Put the units together with the column name (i.e. "Y (meters)") in a single cell. You can do this and still have Excel display them as two lines by using a soft line break. You can then use the f90SQLTables and f90SQLColumns catalog functions to get the name of the columns and extract the units using the available Fortran character functions.

Named ranges are treated as tables. So let's say you create a named range called MyRange containing the following range: Sheet1!$B$5:$C$17. You access the data in this range using:

Select * FROM MyRange

The columns returned by a named range "table" are always named F1, F2, F3, etc. There are some limitations regarding the editing you can do with tables defined as named ranges. In particular, you cannot insert/append new rows to these tables.

In Excel, numeric columns are always considered as double (i.e. real*8) values. You should bind accordingly. Example 55 (column-wise binding) and ExcelRead (both in the "examples" directory of your f90SQL installation) demonstrate how to read Excel tables directly into an array. Because Fortran uses column major storage for matrices (i.e. the first dimension varies faster) you can use column-wise binding directly.

Here is an extract of code that shows the important points of how to do the binding for a 2-dimensional matrix. The example uses Sine.xls, included in the f90SQL installation:

The query is "SELECT X,Y FROM [Sheet1$]"

Where X and Y are numeric.

integer(SQLUINTEGER_KIND),parameter::MAX_ROWSET_SIZE = 10

!Query Column Bindings

real(SQLDOUBLE_KIND)::fCol(MAX_ROWSET_SIZE,2)

!Number of rows fetched by a f90SQLFetch

integer(SQLUINTEGER_KIND)::RowsFetched

!Fetch results variables

integer(SQLUSMALLINT_KIND)::RowsStat(MAX_ROWSET_SIZE)

!Binding Variables Size Indicators

integer(SQLINTEGER_KIND)::fCol1_LenInd(MAX_ROWSET_SIZE)

integer(SQLINTEGER_KIND)::fCol2_LenInd(MAX_ROWSET_SIZE)

!set attributes for column-wise block cursor

call f90SQLSetStmtAttr(StmtHndl, SQL_ATTR_ROW_BIND_TYPE, &

SQL_BIND_BY_COLUMN, iRet)

call f90SQLSetStmtAttr(StmtHndl, SQL_ATTR_ROW_ARRAY_SIZE, &

MAX_ROWSET_SIZE , iRet)

call f90SQLSetStmtAttr(StmtHndl, SQL_ATTR_ROW_STATUS_PTR, &

loc(RowsStat), iRet)

call f90SQLSetStmtAttr(StmtHndl, SQL_ATTR_ROWS_FETCHED_PTR, &

loc(RowsFetched), iRet)

!bindings

call f90SQLBindCol(StmtHndl, int(1, SQLUSMALLINT_KIND), SQL_F_DOUBLE, &

loc(fCol(1,1)), int(0, SQLUINTEGER_KIND), loc(fCol1_LenInd), iRet)

call f90SQLBindCol(StmtHndl, int(2, SQLUSMALLINT_KIND), SQL_F_DOUBLE, &

loc(fCol(1,2)), int(0, SQLUINTEGER_KIND), loc(fCol2_LenInd), iRet)