CanaimaSoft
f90SQL
Search
Contents
f90ADO
Links
f90VB
ProductsServices ArticlesNewsProduct SupportOnline StoreAbout UsContact Us
Products
Services
Articles
News
Product Support
Online Store
 
About Us
Contact Us

Frequently Technical Asked Questions About f90SQL

Table of Contents

At the end of installation of f90SQL the following error message appears: "ComponentMoveData failed. Error 145". What is causing this?

This error occurs when you are installing a version of f90SQL for a compiler which is not installed on your system. During installation the setup program prompts for a location to store the Modules, Include and Library components. If a version of the Fortran compiler matching the chosen version of f90SQL is detected, setup will present the proper component installation directories as defaults in the installation dialogs. Otherwise, the default directory is blank and you must supply a valid installation directory for the components. If you proceed with the installation without providing a component installation directory, the ComponentMoveData error occurs. Note: at the beginning of the installation, setup checks for an installed version of Fortran matching the selected version of f90SQL. If none is found, a warning dialog is presented letting the user know that a matching version was not found, and that the user will have to manually supply the installation directories for the f90SQL components.

Back to Top

Is it possible to open a connection for a database without using a DSN?

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.

Back to Top

How can I access an Access database directly using the UNC convention rather than a DSN?

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:

  • Make sure you have write/execute access to the directory and mdb file pointed to by the UNC.
  • Make sure the path exists (try, for example, mapping a drive letter to the UNC).
  • 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).
  • 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.
  • 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.

Back to Top

I am using f90SQL-Lite but find that I can only access about 127 records at a time. Is there any way to access more than 127 records?

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 127 records which may be read from a database. On the other hand the professional version, f90SQL-Pro, uses 32 bits cursors so it does not have a practical limit on the number of records which may be retrieved.

Using f90SQL-Lite you may, however, read more than 127 records by issuing sequential queries. To do this you need to prepare your queries so that the first query returns the first 127 records, the second query returns the second 127, 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

Back to Top

I'm getting errors when I try to print large chapter from the HTML-Help version of the f90SQL Manual.

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 distribution CD.

Back to Top

What documentation and in what format is included with f90SQL?

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 installtion 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).

Back to Top

The HTML-Help version of the manual does not seem to be working.

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.

Back to Top

I am trying to INSERT a date into my database table using a parameterized query, but don't know how to set Date_Len, ColumnSize and DecimalDigits.

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.

Back to Top

What does a -1 or -2 value in iRet mean?

All f90SQL subroutines return one of the following constants in iRet:

SQL_ERROR = -1
SQL_INVALID_HANDLE = -2
SQL_NO_DATA_FOUND = 100
SQL_NO_DATA = 100
SQL_NEED_DATA =
SQL_SUCCESS = 0
SQL_SUCCESS_WITH_INFO = 1
SQL_NO_IMPLEMENTED = -100 (only will be returned in f90SQL-Lite)
SQL_STILL_EXECUTING =

SQL_ERROR means that an error condition occurred during the execution of the subroutine. A call to ShowDiags (included below) will print the error condition, together with the SQLSTATE value. For example, let's say ShowDiags prints a SQLSTATE of 'HY004' when you call f90SQLBindParameter. If you go to the manual and check the reference for this subroutine, you will find that SQLSTATE HY004 is the code for "Invalid SQL data type" (The value specified for the argument ParameterType was neither a valid ODBC SQL data type identifier nor a driver-specific SQL data type identifier supported by the driver).

SQL_INVALID_HANDLE is returned when you pass an invalid handle to a f90SQL subroutine. Usually this happens when you pass a handle that has not been allocated, or you pass, for example, a connection handle to a procedure that expects a statement handle.

SQL_NO_IMPLEMENTED is returned only if you are using f90SQL-Lite. This return value indicates that the feature you are trying to use IS implemented in f90SQL-Pro, but it is not available in f90SQL-Lite. This value is also returned by f90SQL-Lite when you try to fetch more than 128 records from a record set. You will never get this return value if you are using f90SQL-Pro.

SQL_NO_DATA_FOUND and SQL_NO_DATA are aliases to the same return value. They indicate that you tried to fetch a record set past the end of the record set.

SQL_NEED_DATA is used to indicate that more data is needed, such as when parameter data is sent at execution time or additional connection information is required. The application can call f90SQLGetDiagRec or f90SQLGetDiagField to retrieve additional information, if any.

SQL_SUCCESS is returned when a function completed successfully.

SQL_SUCCESS_WITH_INFO means that the function completed successfully, but possibly there was a nonfatal error or warning. You can check the warning message using f90SQLGetDiagRec.

SQL_STILL_EXECUTING is returned when you call a f90SQL subroutine asynchronously and the subroutine has not finished yet.

The following is the listing of ShowDiags. You can use this subroutine to print the SQLSTATE and Error description when you get an error. You call this subroutine passing the handle type and the handle used by the f90SQL subroutine that returned the error condition.

subroutine ShowDiags(HndlType,Hndl)
!This subroutine prints error diagnostics

!load f90SQL modules
use f90SQLConstants
use f90SQL
implicit none

integer(SQLHANDLE_KIND)::Hndl
integer(SQLSMALLINT_KIND)::HndlType
character(len=6):: SqlState
character(len= SQL_MAX_MESSAGE_LENGTH)::Msg integer(SQLINTEGER_KIND)::NativeError
integer(SQLSMALLINT_KIND):: iDiag, MsgLen
integer(SQLRETURN_KIND):: DiagRet

iDiag = 1

do while (.true.)

call f90SQLGetDiagRec(HndlType, Hndl, iDiag, SqlState, NativeError, Msg, MsgLen, DiagRet)

if (DiagRet.ne.SQL_SUCCESS .and. DiagRet.ne.SQL_SUCCESS_WITH_INFO) exit

print *,trim(SqlState),',', NativeError,',', Msg(1:MsgLen) iDiag=iDiag+1

enddo

end subroutine ShowDiags

Back to Top

What methods are available in f90SQL to connect to a database?

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, f90SQLDriverConnect returns with an error condition. The on-line manual for f90SQLDriverConnect has more information on the options.

There are several examples that use f90SQLDriverCOnnect (both in the f90SQL-lite distribution and the www.canaimasoft.com web pages). All of the examples use the SQL_DRIVER_COMPLETE option for f90SQLDriverConect, because they are intended to be executed in a very wide range of conditions and computers.

The only caveat to using f90SQLDriverConnect to create on-the-fly ODBC connections is that these connections are not permanent i.e. you are just creating a temporary DSN that is available only to your application and while it is executing. Other than this, it is probably the best method to open database connections.

You can also use f90SQLDriverConnect to open a DSN that has been defined using the ODBC administrator.

Back to Top

How can I handle a situation in which I don't have all the information necessary to connect to a database at design time?

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.

Back to Top

When I try to install f90SQL-Lite, setup prompts me for a Serial ID.

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

Back to Top

I have read that some ODBC drivers do not support 'positional updates', but I am not sure what this refers to.

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 update, however, some older drivers only support standard update/delete/insert queries.

Back to Top

I am trying to read from a text field, but I get iRet=SQL_ERROR when fetching the records that have this field empty

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 behavior is by design and explained in the reference manual, at the end of the description of the StrLen_or_IndPtr argument for f90SQLBindCol.

The following is an example of how binding to character field should be done:

1) Add the following variables to your declaration section:

integer(SQLINTEGER_KIND)::MyXStrLen
character(len=255)::MyXStr

2) Use the following binding call example to bind a text field as the third column of a record set:

ColNumber = 3
call f90SQLBindCol (StmtHndl, ColNumber, SQL_F_CHAR, MyXStr, loc(MyXStrLen), iRet)

3) To check if the fetched field is null, you can do:

if (MyXStrLen.eq.SQL_NULL_DATA) then
print *, 'field is null'
else
print *, MyXStr (1: MyXStrLen)
endif

Back to Top

While trying to compile and link an example of f90SQL-Pro with LF90, I get the following error message: ERROR LINK 4270: Import symbol "F90SQLALLOCHANDLE" is allowed only in a PE .EXE file.

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

Back to Top

I'm porting a Fortran application from a mainframe system that makes extensive use of Indexed-Sequential (ISAM) files into a Client/Server system in Windows. Is f90SQL suitable for this type of work?

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.

Back to Top

Are there any restrictions in using the f90SQL library in developing commercially-available software?

f90SQL-Lite cannot be used to develop commercial products. Its use is restricted to personal and academic projects. Read the f90SQL-Pro License agreement for details on restrictions to incorporate f90SQL-Pro on commercial software.

Back to Top

I've recently switched to MSSQLServer 7.0, and I'm finding that large database builds are running much slower. Do you have any suggestions to improve perfomance?

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?

Back to Top

I keep getting the message during compile time "Unable to interface with any known generic interface". What does this mean?

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).

Back to Top

When reading the results of a query, the only way I can determine the end of the records is to continue reading rows until SQL_NO_DATA occurs. Is there another way to do this?

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.

Back to Top

Is it possible to use f90SQL to connect to a database application using DDE?

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.

Back to Top

Is there a way to get f90SQL to work with via OLEDB without going through ODBC? Possibly using ADO?

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.

Back to Top

Is there some way that f90SQL can be instructed to retrieve the data as either date or integer (or in general to retrieve either as "formatted" vs. "true" data type) from an Excel spreasheet?

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.

Back to Top

How are INDICATOR variables implemented with f90SQL?

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.

Back to Top

I cannot retrieve records from an Access database having empty fields, although records with all fields populated are accessed. How do I retrieve records with empty fields?

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.

Back to Top

I am using f90SQL to write some numbers to an Excel spreadsheet but the numbers are written as characters.

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.

Back to Top

Is it possible to read an Excel file that contains several rows of text entry before the actual data begins?

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

Back to Top

What is the correct syntax for retrieving data from Excel named ranges. For example, if the X,Y data in your examples was on "Sheet1$" in named range "Data", how would you write the SQL query?

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.

Back to Top

When retrieving an Excel table (either a sheet or a named range) what are the implications for "column binding", particularly when retrieving to a Fortran array such as A(1:nRow,1:nCol)?

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)

 

Back to Top

Why do Fortran character variables that are bound to query columns or out parameters need to 1 character larger than the real column or parameter?

The reason for this is that the ODBC Driver Manager always puts a null terminator at the end returned strings. In most cases, f90SQL can remove this null terminator and replace it spaces, the padding character used by Fortran strings. However, the interaction between bound variables and query columns is not mediated by f90SQL. In other words, during a fetch operation bound Fortran variables are updated directly by the ODBC Driver Manager, not by f90SQL. Because of this direct interaction, f90SQL is unable to remove the null terminator. You can, however, call f90SQLStrFormat after fetching character columns to remove the null terminator. There are several examples in the f90SQL distribution that use this function.

Back to Top

Click below to

Downlad our Free Software!
Copyright © 1998-2000 Canaima Software
For questions regarding this site, send an e-mail to
webmaster@canaimasoft.com