IVF, MySQL and .NET

[OP1]

Hi,

I am wondering if among the experts in this forum some have experience with connecting a MySQL database with a Fortran application. There were a couple threads on the subject back in 2005, but I am afraid the information there has become a bit dated.
I am new to this topic - so pointers to samples or tutorials would be appreciated very much.

Thanks in advance,

Olivier

[Steven_L_Intel1]

MySQL provides a C API, which should be usable from Fortran with a bit of effort in declaring interfaces.

[lrobison_arl]

I've been looking into connecting to a MySQL database from Fortran as well. Like Olivier, I haven't found many currently supported libraries that do this, especially as my code will be running on Linux instead of Windows. I've looked into the MySQL Connect library for C, and writing a wrapper is probably more than a bit of work, as the mysql.h header file alone weighs in at over 700 lines of code.

Olivier, did you attempt this as well? Do you have any pointers you'd like to share?

Luke

[Arjen_Markus]

Have you looked at my Flibs project (http://flibs.sf.net)? It contains an interface for MySQL provided by
Daniel Kraft. It may be too limited for your purposes, but it is a start.

Regards,

Arjen

[OP1]

Luke,

I suppose we're in the same boat, so to speak. I also thought about writting wrappers for the interface - a substantial task by itself. Since thisdoes not havethe highest priority, this project is on hold for the moment. I'll keep looking for ways of achieving this though. Connecting a MySQL database to a Fortran code can be an elegant solution to address two sides of a problem (data storage and organization, and computational oumpf).

Arg. Why can't all languages (C, C++ in particular) be just like Fortran :) ...

Olivier

[lrobison_arl]

Arjen,

I have actually, but I was unable to find the MySQL module in the FLIBS 0.9 source tree. Is it a newer feature that may not be in the 0.9 release, or is it right in front of me somewhere?

I actually just sent you and relaxmike an email to that effect, before I realized that you were the same person I was talking to here. :-)

Luke

[Arjen_Markus]

Luke,

for the moment you will have to do with the CVS repositorydirectly. I may be able to put together
a new tar-ball next week (but I will have to check the state of the various modules - development
has been a haphazard process of late :)).

Regards,

Arjen

[lrobison_arl]

Thanks Arjen.

I'm using the mysql.f90 file that I got from the current CVS. Everything is working wonderfully except for actually reading fields out of the results. Am I doing it wrong or do I need to be looking into the library for an error? I'm afraid I'm not terribly familiar with the ISO_C_BINDING module, and not even that familiar with Fortran in general.

I've declared my field to be a CHARACTER(LEN=40). I don't think anything else is relevant, since the connection portion of the program seems functional, but I'd be happy to provide the whole thing. Also let me know if posting code in the forums is fowned upon and I can take this discussion offline.

[bash]! Perform a SQL Query.
  query_result = myfortran_query_get( sql_conn, 'SELECT  test_int, test_float, test_double,
 test_char, test_blob, test_pk FROM test_table' )

  print *, 'Query successful.'

  do while ( myfortran_fetch_row( query_result, row ) )
     do field_index = 1, row%num_fields
        field = myfortran_get_field( row, field_index )
        write (*,'(A,I2,A,A,A,I2)') 'Field ', field_index, ' value is''', field, ''' Length is ', row%lengths(field_index)
     end do
  end do[/bash]

The relevant function: myfortran_get_field() is shown here:

[fortran]! Get a field from a row.                                                                                                      
  FUNCTION myfortran_get_field (row, ind)
    IMPLICIT NONE
    TYPE(myfortran_row), INTENT(IN) :: row
    INTEGER, INTENT(IN) :: ind
    CHARACTER(LEN=row%lengths(ind)) :: myfortran_get_field

    TYPE(C_PTR), POINTER :: cstrs(:)
    CHARACTER(KIND=C_CHAR), POINTER :: cstr(:)
    INTEGER :: i

    CALL C_F_POINTER (row%c_row, cstrs, SHAPE (row%lengths))
    CALL C_F_POINTER (cstrs(ind), cstr, (/ row%lengths(ind) /))

    DO i = 1, row%lengths(ind)
      myfortran_get_field(i:i) = cstr(i)
    END DO
  END FUNCTION myfortran_get_field
[/fortran]
 

The output of this program is as follows:

Query successful.
Field  1 value is'42                                      ' Length is  2
Field  2 value is'                                        ' Length is  0
Field  3 value is'3.14159                                 ' Length is  7
Field  4 value is'                                        ' Length is  0
Field  5 value is'   a blank padde                        ' Length is 16
Field  6 value is'                                        ' Length is  0

Somehow only alternating rows are being received. The output should be:

Field 1 value is '42                                     ' Length is 2
Field 2 value is '3.14159                                ' Length is 7
Field 3 value is '3.14159265358979                       ' Length is 16
Field 4 value is '   a blank padded str                  ' Length is 21
Field 5 value is '   a blank padded blob with null       ' Length is 37
Field 6 value is '1                                      ' Length is 1

Any thoughts or suggestions?

[Arjen_Markus]

Hi Luke,

could you send me your program? I think this is getting us too far off the track of Intel Fortran,
so best continue this off-line, I'd say.

Regards,

Arjen

[Arjen_Markus]

The problem turned out to be one of types: the original interface assumed the values are ints, but they are longs. On 32-bits machines this makes no difference, but it does on 64 bits. I have changed the code in the SF project (flibs.sf.net).

Regards,

Arjen

[lrobison_arl]

Thank you Arjen for all your help on this.

In the end, because of a few other factors, and because of my familiarity with C, I went ahead and made a C library that does application specific queries and created an Fortran interface to that library rather than to MySQL itself. Reading the mysql.f90 code was a great introduction to interfaces for me though. Thanks again!

-Luke

[Arjen_Markus]

YW, it was an eye opener for me too - thanks to Daniel Kraft. I would have used a thin C layer myself (and have done so for other databases), but it seems really worth the time to learn.

Regards,

Arjen

[afcalderncsu_edu]

Hi All,

I have been trying to link the mysql.f90 object output from the flibs' CVS with either libmysql.lib or mysqlclient.lib.

I am using Visual Studio 2010 Ultimate and Intel Fortran Composer XE 2011 Update 6 on a Windows 7 64-Bit machine. So far I have not been very successful at it.

I keep getting linking errors such as:

Error 1 error LNK2019: unresolved external symbol _mysql_init referenced in function _MYFORTRAN_BINDING_mp_MYFORTRAN_CONNECT mysql.obj

Does one need to create a C project with header files? Or should one just be trying to link against the lib? I have tried to do so with the default libraries that come with MySQL 5.5.18 32-Bit MSI as well as well as the ones I generated by re-compiling the MySQL Source. Any advice on the subject would be appreciated.

Thank you,

Adan Calderon

[Arjen_Markus]

Hi Adan,

I suspect the problem has to do with the naming/calling conventions. Can you use a tool like
Dependency Walker to see the actual names in the MySQL library? (I do not have access
to it right now and I have no access to a 64-bits machine either, so I can not check myself).

You should be linking against the import libraries (.lib).

Regards,

Arjen

[afcalderncsu_edu]

Hi Thanks for the reply, Even though I am on a 64-Bit machine I believe I am doing all my work in 32-bit mode. I used dumpbin /EXPORTS libmysql.lib and got the following:

Dump of file libmysql.lib

File Type: LIBRARY

Exports

ordinal name

_load_defaults
_myodbc_remove_escape@8
_mysql_affected_rows@4
_mysql_autocommit@8
_mysql_change_user@16
_mysql_character_set_name@4
_mysql_close@4
_mysql_commit@4
_mysql_data_seek@12
_mysql_debug@4
_mysql_dump_debug_info@4
_mysql_embedded@0
_mysql_eof@4
_mysql_errno@4
_mysql_error@4
_mysql_escape_string@12
_mysql_fetch_field@4
_mysql_fetch_field_direct@8
_mysql_fetch_fields@4
_mysql_fetch_lengths@4
_mysql_fetch_row@4
_mysql_field_count@4
_mysql_field_seek@8
_mysql_field_tell@4
_mysql_free_result@4
_mysql_get_character_set_info@8
_mysql_get_client_info@0
_mysql_get_client_version@0
_mysql_get_host_info@4
_mysql_get_proto_info@4
_mysql_get_server_info@4
_mysql_get_server_version@4
_mysql_get_ssl_cipher@4
_mysql_hex_string@12
_mysql_info@4
_mysql_init@4
_mysql_insert_id@4
_mysql_kill@8
_mysql_list_dbs@8
_mysql_list_fields@12
_mysql_list_processes@4
_mysql_list_tables@8
_mysql_more_results@4
_mysql_next_result@4
_mysql_num_fields@4
_mysql_num_rows@4
_mysql_options@12
_mysql_ping@4
_mysql_query@8
_mysql_read_query_result@4
_mysql_real_connect@32
_mysql_real_escape_string@16
_mysql_real_query@12
_mysql_refresh@8
_mysql_rollback@4
_mysql_row_seek@8
_mysql_row_tell@4
_mysql_select_db@8
_mysql_send_query@12
_mysql_server_end@0
_mysql_server_init@12
_mysql_set_character_set@8
_mysql_set_local_infile_default
_mysql_set_local_infile_handler
_mysql_set_server_option@8
_mysql_shutdown@8
_mysql_sqlstate@4
_mysql_ssl_set@24
_mysql_stat@4
_mysql_stmt_affected_rows@4
_mysql_stmt_attr_get@12
_mysql_stmt_attr_set@12
_mysql_stmt_bind_param@8
_mysql_stmt_bind_result@8
_mysql_stmt_close@4
_mysql_stmt_data_seek@12
_mysql_stmt_errno@4
_mysql_stmt_error@4
_mysql_stmt_execute@4
_mysql_stmt_fetch@4
_mysql_stmt_fetch_column@16
_mysql_stmt_field_count@4
_mysql_stmt_free_result@4
_mysql_stmt_init@4
_mysql_stmt_insert_id@4
_mysql_stmt_next_result@4
_mysql_stmt_num_rows@4
_mysql_stmt_param_count@4
_mysql_stmt_param_metadata@4
_mysql_stmt_prepare@12
_mysql_stmt_reset@4
_mysql_stmt_result_metadata@4
_mysql_stmt_row_seek@8
_mysql_stmt_row_tell@4
_mysql_stmt_send_long_data@16
_mysql_stmt_sqlstate@4
_mysql_stmt_store_result@4
_mysql_store_result@4
_mysql_thread_end@0
_mysql_thread_id@4
_mysql_thread_init@0
_mysql_thread_safe@0
_mysql_use_result@4
_mysql_warning_count@4

Summary

C6 .debug$S
14 .idata$2
14 .idata$3
4 .idata$4
4 .idata$5
E .idata$6

I am as of right now just trying to use the default libraries that come from mysql-5.5.18-win32.msi.

if I go in and add the @numbers as in BIND(C, NAME='mysql_init@4') , I begin to have less and less linking errors. I remember reading about the default calling conventions having changed from CVF, but in this case it's the C compiled libarys that have the @N suffix. I am still kind of lost on this one. I am by no means a visual studio expert either. Is there any advice on this? Or even book recommendations on this subject. I'd hate to go and edit my 12 errors as I would feel like I am forcing someting that should not be.

Thanks,

Adan Calderon