Calling ODBC routine SQLBindCol does not work anymor

Hello!

I have a problem with my library for ODBC access.

A sample program which worked in the past (I guess prior 5.0) does not with FTN95 v5. It calls ODBC routine SQLBindCol(...) whose import is defined as

  STDCALL SQLBINDCOL 'SQLBindCol' VAL,VAL,VAL,REF,VAL,REF): INTEGER*2
  !  extension to allow for different column types
  STDCALL SQLBINDCOLCHAR 'SQLBindCol' (VAL,VAL,VAL,REF,VAL,REF): INTEGER*2
  

Other interfaces declared similarly.

Variables are declared as follows USE qt_ODBC ! to define SQL KINDs, INTERFACEs etc. IMPLICIT NONE ...
INTEGER (SQLINTEGER), PARAMETER :: STR_LEN = 128+1, REM_LEN = 254+1 CHARACTER (LEN=STR_LEN) :: szCatalog, szSchema, szTableName, & szColumnName, szTypeName, lastCatalogName INTEGER (SQLSMALLINT) :: DataType, cb0 = 0 INTEGER (SQLINTEGER) :: cbDataType, cbCatalog, cbSchema, cbTableName, & cbColumnName, cbTypeName, cbTableType ...

The routines are called as follows:

  icol =	1
  rtc = SQLBindColChar(stmt, icol, SQL_C_CHAR, szCatalog, STR_LEN, cbCatalog)
  icol =	2
  rtc = SQLBindColChar(stmt, icol, SQL_C_CHAR, szSchema, STR_LEN, cbSchema)
  icol =	3
  rtc = SQLBindColChar(stmt, icol, SQL_C_CHAR, szTableName, STR_LEN, cbTableName)
  icol =	4
  rtc = SQLBindColChar(stmt, icol, SQL_C_CHAR, szColumnName, STR_LEN, cbColumnName)
  icol =	5
  rtc = SQLBindColI2(stmt, icol, SQL_C_SSHORT, DataType, 0, cbDataType)
  icol =	6
  rtc = SQLBindColChar(stmt, icol, SQL_C_CHAR, szTypeName, STR_LEN, cbTypeName)

SQLBindCol passes the addresses of the 4th and the 6th argument to ODBC.

Then a few lines below, in a DO loop, the ODBC routine SQLFetch is called rtc = SQLFetch(stmt) which causes those variables bound before by SQLBINDCOL to be filled with values.

This worked with FTN95 in earlier versions (I think up to v4.90) so far and it does with any other Fortran compiler I use. But it does not with v5. The following happens: The variables szCatalog, szSchema, szTableName, szColumnName, DataType and their length arguments cbCatalog, cbSchema etc. are left unchanged. However, the variables szTypeName and cbTypeName of the last call to SQLBindColChar are changed and they receive the values which actually should be written to szCatalog and cbCatalog! Any idea why this isn't working anymore? The only thing I can imagine is that the address of those bound variables have changed. But this is something I can hardly believe since SQLBindCol and SQLFetch are called in the same SUBROUTINE. The calls of ODBC routines SQLSetEnvAttr, SQLAllocConnect, SQLDriverConnect, SQLAllocStmt and SQLExecDirect prior SQLBindCol seem to work. Please help. Thank you.

Jörg Kuthe www.qtsoftware.de

I do not think that we have enough information here (or in another sense too much) to indentify the problem.

Can you post or send a small working program that illustrates a problem?

If the library is your own, do you get the same problem if you avoid using a library but add the code directly into the executable?

If necessary please provide code to reconstruct a small library.

We may also need to know the command line switches that you are using.

For the moment let's assume that there is only one problem/bug.

Dear Paul,

thank you for your reply.

I have read the documentation regarding STDCALL again and found that the way it works has changed. Now, CHARACTER objects are copied to a compiler defined temporary variable. This mechanism does not allow to use essential ODBC functions anymore.

ODBC requires that variables are bound to columns of a table in a database by calling SQLBindCol or SQLBindParameter. Then, calling SQLFetch or SQLExecute the data are passed to those bound variables. Example: A database may have a table named 'person'. This contains two columns: 'name' and 'age'. To obtain all rows of that table we program in Fortran: CHARACTER(50) cName INTEGER iAge

! ... connect to database

! execute the SQL command which generates a result set (internally) rtc = SQLExecDirect (stmt, 'SELECT name, age FROM person', 28 )

! bind variables to columns of the result set iCol = 1 rtc = SQLBindColChar( stmt, icol, SQL_C_CHAR, cName, LEN(cName), icbName ) icol = 2 rtc = SQLBindColI4( stmt, icol, SQL_C_LONG, iAge, 0, icbAge )

To obtain a single row of the result set we have to call SQLFetch: rtc = SQLFetch( stmt ) This causes variables cName and iAge to be filled with values - but not with FTN95 v5 😢 because SQLBindColChar obtains the address of a temporary variable and not that one of the variable cName. And so the value from the result set never finds its way back from the temporary variable when SQLFetch is called.

I hope you understand the problem and have an idea how I can make my library work again with FTN95. Thank you for your efforts.

Jörg Kuthe

Jörg

I don't think that I can help without a working program and code that illustrates the problem. I need to be able to trace through the fine details of what is happening in your code and in FTN95.

If you can send a very small 'working' program then I may be able to make some changes or recommend a way out. I will also need code for a sample DLL if this has to be separately compiled.

Regards

Paul

Hi,

unfortunately I cannot contribute much to this subject other than that I would like to use this library. We have recently installed FTN 5.1 and cannot continue without this function :x

One option off coarse to install FTN 4.91. But then again what is the use of going back to the future :!:

Nevertheless I wish you good luck and hope you guys can sort out the problem 😉

Jacques

Hi Paul,

Jörg explained me what the exact problem is. It seems that the changes to version 5.10 makes it impossible to make use of ODBC.

If this is true this off coarse means that we cannot use FTN95 anymore. Or do you think that there is a possible way to solve this.

What is your comment on this?

Jacques

I am waiting for a sample program from Jörg.