Cannot find dynamic library file

[Paolo_Ricardo]

I've been experimenting with mySQL and I tried to use the SQL Client code in the QB64 Wiki. The code compiles without errors and a console screen appears with the above message. There is no indication of which .dll id missing.

I have mysql_helper.h and mysql.dll in the QB64 program folder. I gave tried compiling with both the 32-bit and the 64-bit versions of mysql.dll

Thanks

[SpriggsySpriggs]

@Paolo_Ricardo

Can you paste a snippet of your code? I have both the 32 and 64 bit DLLs and they work fine. Of course, you will want to use the 64 bit DLL with 64 bit QB64 and the 32 bit DLL with 32 bit QB64. I have code that is designed for both so you don't have to change the code as well as more functions like determining the number of affected rows after an update to a table.

  [ You are not allowed to view this attachment ]  64 bit
  [ You are not allowed to view this attachment ]  32 bit

MYSQL.BI

Code: QB64: [Select]

1. DIM SHARED sqlID%
2. DIM SHARED sqlstring AS STRING
3. DIM SHARED sql_database_ip AS STRING
4. DIM SHARED my_password AS STRING
5. DIM SHARED my_username AS STRING
6. DIM SHARED affectedRows AS LONG
7. DIM SHARED selectedRows AS LONG
8. DIM SHARED returnedID AS LONG
9. DIM SHARED DB_Version AS STRING: DB_Version = mysql_get_client_info$ 'currently "6.0.0" in Windows
10. DIM SHARED DB_Last AS LONG
11. DIM SHARED Database(DB_Last) AS DB_TYPE
12. DIM SHARED DB_Last_Error AS STRING
13. DIM SHARED DB_Selected AS LONG
14. DIM SHARED DB_Debug_Mode AS LONG: DB_Debug_Mode = 0
15. DIM SHARED DB_RESULT(1 + 1, 1 + 1) AS STRING '***DO NOT EDIT THIS LINE***
16. $IF 32BIT THEN
17.     DECLARE DYNAMIC LIBRARY ".\mysql"
18.     FUNCTION mysql_init%& (BYVAL always_0 AS _OFFSET)
19.     FUNCTION mysql_error$ (BYVAL mysql AS _OFFSET)
20.     FUNCTION mysql_get_client_info$
21.     FUNCTION mysql_real_connect%& (BYVAL mysql AS _OFFSET, host AS STRING, user AS STRING, password AS STRING, db AS STRING, BYVAL port AS _UNSIGNED LONG, BYVAL unix_socket AS _OFFSET, BYVAL client_flag AS _UNSIGNED _OFFSET)
22.     FUNCTION mysql_real_connect_dont_open%& ALIAS mysql_real_connect (BYVAL mysql AS _OFFSET, host AS STRING, user AS STRING, password AS STRING, BYVAL db AS _OFFSET, BYVAL port AS _UNSIGNED LONG, BYVAL unix_socket AS _OFFSET, BYVAL client_flag AS _UNSIGNED LONG)
23.     SUB mysql_query (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
24.     FUNCTION mysql_query& (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
25.     FUNCTION mysql_store_result%& (BYVAL mysql AS _OFFSET)
26.     FUNCTION mysql_num_fields~& (BYVAL result AS _OFFSET)
27.     FUNCTION mysql_num_rows&& (BYVAL result AS _OFFSET)
28.     FUNCTION mysql_fetch_row%& (BYVAL result AS _OFFSET)
29.     FUNCTION mysql_fetch_lengths%& (BYVAL result AS _OFFSET)
30.     SUB mysql_close (BYVAL mysql AS _OFFSET)
31.     SUB mysql_free_result (BYVAL result AS _OFFSET)
32.     FUNCTION mysql_affected_rows~&& (BYVAL mysql AS _OFFSET)
33.     FUNCTION mysql_insert_id~&& (BYVAL mysql AS _OFFSET)
34.     FUNCTION mysql_sqlstate$ (BYVAL mysql AS _OFFSET)
35.     FUNCTION mysql_errno& (BYVAL mysql AS _OFFSET)
36.     FUNCTION mysql_get_server_version~& (BYVAL mysql AS _OFFSET)
37.     FUNCTION mysql_ping (BYVAL mysql AS _OFFSET)
38.     END DECLARE
39. $ELSE
40.     DECLARE DYNAMIC LIBRARY ".\libmysql64bit"
41.         FUNCTION mysql_init%& (BYVAL always_0 AS _OFFSET)
42.         FUNCTION mysql_error$ (BYVAL mysql AS _OFFSET)
43.         FUNCTION mysql_get_client_info$
44.         FUNCTION mysql_real_connect%& (BYVAL mysql AS _OFFSET, host AS STRING, user AS STRING, password AS STRING, db AS STRING, BYVAL port AS _UNSIGNED LONG, BYVAL unix_socket AS _OFFSET, BYVAL client_flag AS _UNSIGNED _OFFSET)
45.         FUNCTION mysql_real_connect_dont_open%& ALIAS mysql_real_connect (BYVAL mysql AS _OFFSET, host AS STRING, user AS STRING, password AS STRING, BYVAL db AS _OFFSET, BYVAL port AS _UNSIGNED LONG, BYVAL unix_socket AS _OFFSET, BYVAL client_flag AS _UNSIGNED LONG)
46.         SUB mysql_query (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
47.         FUNCTION mysql_query& (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
48.         FUNCTION mysql_store_result%& (BYVAL mysql AS _OFFSET)
49.         FUNCTION mysql_num_fields~& (BYVAL result AS _OFFSET)
50.         FUNCTION mysql_num_rows&& (BYVAL result AS _OFFSET)
51.         FUNCTION mysql_fetch_row%& (BYVAL result AS _OFFSET)
52.         FUNCTION mysql_fetch_lengths%& (BYVAL result AS _OFFSET)
53.         SUB mysql_close (BYVAL mysql AS _OFFSET)
54.         SUB mysql_free_result (BYVAL result AS _OFFSET)
55.         FUNCTION mysql_affected_rows~&& (BYVAL mysql AS _OFFSET)
56.         FUNCTION mysql_insert_id~&& (BYVAL mysql AS _OFFSET)
57.         FUNCTION mysql_sqlstate$ (BYVAL mysql AS _OFFSET)
58.         FUNCTION mysql_errno& (BYVAL mysql AS _OFFSET)
59.         FUNCTION mysql_get_server_version~& (BYVAL mysql AS _OFFSET)
60.         FUNCTION mysql_ping (BYVAL mysql AS _OFFSET)
61.     END DECLARE
62. $END IF
63. '--------------------------------------------------------------------------------------------
64. TYPE DB_TYPE
65.     Object AS _OFFSET
66. END TYPE

MYSQL.BM

Code: QB64: [Select]

1. DECLARE LIBRARY
2.     FUNCTION MessageBox& (BYVAL Zer0 AS LONG, message AS STRING, title AS STRING, BYVAL MBType AS _UNSIGNED LONG)
3. END DECLARE
4.  
5. CONST MB_OK = 0 'OK button only
6. CONST MB_OKCANCEL = 1 'OK & Cancel
7. CONST MB_ABORTRETRYIGNORE = 2 'Abort, Retry & Ignore
8. CONST MB_YESNOCANCEL = 3 'Yes, No & Cancel
9. CONST MB_YESNO = 4 'Yes & No
10. CONST MB_RETRYCANCEL = 5 'Retry & Cancel
11. CONST MB_CANCELTRYCONTINUE = 6 'Cancel, Try Again & Continue
12. CONST MB_ICONSTOP = 16 'Error stop sign icon
13. CONST MB_ICONQUESTION = 32 'Question-mark icon
14. CONST MB_ICONEXCLAMATION = 48 'Exclamation-point icon
15. CONST MB_ICONINFORMATION = 64 'Letter i in a circle icon
16. CONST MB_DEFBUTTON1 = 0 '1st button default(left)
17. CONST MB_DEFBUTTON2 = 256 '2nd button default
18. CONST MB_DEFBUTTON3 = 512 '3rd button default(right)
19. CONST MB_APPLMODAL = 0 'Message box applies to application only
20. CONST MB_SYSTEMMODAL = 4096 'Message box on top of all other windows
21. CONST MB_SETFOCUS = 65536 'Set message box as focus
22. CONST IDOK = 1 'OK button pressed
23. CONST IDCANCEL = 2 'Cancel button pressed
24. CONST IDABORT = 3 'Abort button pressed
25. CONST IDRETRY = 4 'Retry button pressed
26. CONST IDIGNORE = 5 'Ignore button pressed
27. CONST IDYES = 6 'Yes button pressed
28. CONST IDNO = 7 'No button pressed
29. CONST IDTRYAGAIN = 10 'Try again button pressed
30. CONST IDCONTINUE = 1 'Continue button pressed
31.  
32. SUB DB_Critical_Error (message AS STRING)
33.     DIM Answer AS LONG
34.     Answer = MessageBox(0, "The program ran into a problem it couldn't handle and needs to exit." + message, "Error", MB_OK + MB_SETFOCUS + MB_ICONEXCLAMATION)
35.     SYSTEM
36. END SUB
37.  
38. SUB MySQLError (errorcode AS LONG, sqlstate AS STRING, errormessage AS STRING)
39.     DIM Answer AS LONG
40.     Answer = MessageBox(0, LTRIM$(STR$(errorcode)) + " (" + sqlstate + ")", "Error", MB_OK + MB_SETFOCUS + MB_ICONEXCLAMATION)
41. END SUB
42.  
43. FUNCTION DB_Open (host_ip$, user_name$, user_password$, DB_name$) 'if DB_name="" then no database is selected
44.     DIM DB
45.     DIM object%&
46.     DB_Last_Error = ""
47.     'create new handle
48.     FOR DB = 1 TO DB_Last
49.         IF Database(DB).Object = 0 THEN EXIT FOR
50.     NEXT
51.     IF DB > UBOUND(Database) THEN REDIM _PRESERVE Database(UBOUND(Database) + 10) AS DB_TYPE: DB_Last = DB
52.     'create new object
53.     Database(DB).Object = mysql_init(0): IF Database(DB).Object = 0 THEN DB_Critical_Error "mysql_init failed"
54.     'attempt to connect
55.     IF DB_name$ = "" THEN
56.         object%& = mysql_real_connect_dont_open(Database(DB).Object, host_ip$ + CHR$(0), user_name$ + CHR$(0), user_password$ + CHR$(0), 0, 0, 0, 0)
57.     ELSE
58.         object%& = mysql_real_connect(Database(DB).Object, host_ip$ + CHR$(0), user_name$ + CHR$(0), user_password$ + CHR$(0), DB_name$ + CHR$(0), 0, 0, 0)
59.     END IF
60.     IF object%& = 0 THEN
61.         DB_Last_Error = mysql_error(Database(DB).Object)
62.         Database(DB).Object = 0 'free index
63.         EXIT FUNCTION
64.     END IF
65.     DB_Selected = DB
66.     DB_Open = DB
67. END FUNCTION
68.  
69. SUB DB_QUERY (mysql_command$)
70.     DIM result
71.     IF DB_Selected < 0 OR DB_Selected > DB_Last THEN
72.         DB_Critical_Error "DB_QUERY: Invalid handle"
73.         SYSTEM
74.     END IF
75.     IF Database(DB_Selected).Object = 0 THEN
76.         DB_Critical_Error "DB_QUERY: Invalid handle"
77.         SYSTEM
78.     END IF
79.     DB_Last_Error = ""
80.     result = mysql_query(Database(DB_Selected).Object, mysql_command$ + CHR$(0))
81.     IF mysql_error(Database(DB_Selected).Object) <> "" THEN
82.         MySQLError mysql_errno(Database(DB_Selected).Object), mysql_sqlstate(Database(DB_Selected).Object), mysql_error(Database(DB_Selected).Object)
83.     END IF
84.     affectedRows = mysql_affected_rows(Database(DB_Selected).Object)
85.     returnedID = mysql_insert_id(Database(DB_Selected).Object)
86.     IF result THEN
87.         DB_Last_Error = mysql_error(Database(DB_Selected).Object)
88.         EXIT SUB
89.     END IF
90.     DIM mysql_result AS _OFFSET
91.     mysql_result = mysql_store_result(Database(DB_Selected).Object)
92.     IF mysql_result = 0 THEN
93.         '...todo...
94.     ELSE
95.         DIM columns AS _UNSIGNED LONG
96.         DIM rows AS _UNSIGNED LONG
97.         DIM y AS _UNSIGNED LONG
98.         DIM an_offset%&
99.         DIM x AS _UNSIGNED LONG
100.         DIM length AS _UNSIGNED LONG
101.         columns = mysql_num_fields(mysql_result)
102.         rows = mysql_num_rows(mysql_result)
103.         REDIM DB_RESULT(columns, rows) AS STRING
104.         FOR y = 1 TO rows
105.             DIM mysql_row AS _OFFSET
106.             mysql_row = mysql_fetch_row(mysql_result)
107.             DIM mem_mysql_row AS _MEM
108.             mem_mysql_row = _MEM(mysql_row, columns * LEN(an_offset%&))
109.             DIM mysql_lengths AS _OFFSET
110.             mysql_lengths = mysql_fetch_lengths(mysql_result)
111.             DIM mem_mysql_lengths AS _MEM
112.             mem_mysql_lengths = _MEM(mysql_lengths, columns * 4)
113.             DIM mem_field AS _MEM
114.             FOR x = 1 TO columns
115.                 length = _MEMGET(mem_mysql_lengths, mem_mysql_lengths.OFFSET + (x - 1) * 4, _UNSIGNED LONG)
116.                 mem_field = _MEM(_MEMGET(mem_mysql_row, mem_mysql_row.OFFSET + (x - 1) * LEN(an_offset%&), _OFFSET), length)
117.                 DB_RESULT(x, y) = SPACE$(length)
118.                 _MEMGET mem_field, mem_field.OFFSET, DB_RESULT(x, y)
119.                 _MEMFREE mem_field
120.             NEXT
121.             _MEMFREE mem_mysql_lengths
122.             _MEMFREE mem_mysql_row
123.         NEXT
124.         selectedRows = UBOUND(db_result, 2)
125.         mysql_free_result mysql_result
126.     END IF
127. END SUB

Example code for checking for number of affected rows after updating a table:

Code: QB64: [Select]

1. '$INCLUDE:'MYSQL.BI'
2. mydb = DB_Open("Spriggsy", "videouser", "pass@word", "")
3. DB_QUERY "INSERT INTO `videos`.`video` (video_name, coloration, clip_length, clip_type) VALUES ('Superman Returns','color','2h30m','movie'),('Star Wars','color','1h20m','movie'),('The Dirty Dozen','color','2h30m','movie');"
4. PRINT affectedRows&
5. DB_QUERY "SELECT LAST_INSERT_ID();"
6. PRINT DB_RESULT(1, 1)
7. '$INCLUDE:'MYSQL.BM'

And a screenshot showing the proof the change:
  [ You are not allowed to view this attachment ]  

I'm glad someone else is doing MySQL code in QB64. If you ever need help with MySQL then don't hesitate to ask. I'm available on Discord quite often and I check the forums for things like this as well.

[Paolo_Ricardo]

@SpriggsySpriggs

I compiled the code shown here https://www.qb64.org/wiki/SQL_Client. I also used your libmysql32bit.dll and libmysql64bit.dll (of course renaming them to mysql.dll for each use.)  Was there something else I should have compiled e.g. "some code which uses 'mysql_fetch_fields':"

Also ref 32- or 64-bit, where does one decide that? I can't see anything in the IDE giving a choice. Of course with a name like QB64 I assume that 64-bit is the default.

Thanks

[SpriggsySpriggs]

I compiled the code shown here https://www.qb64.org/wiki/SQL_Client. I also used your libmysql32bit.dll and libmysql64bit.dll (of course renaming them to mysql.dll for each use.)

You don't have to rename any of them. It doesn't matter the name. I have it named libmysql64bit.dll so it would just be easier to know which one goes with which IDE. You can just change the name in the code to match the DLL.

Was there something else I should have compiled e.g. "some code which uses 'mysql_fetch_fields':"

I'm not sure about that. I don't think it's necessary.

Also ref 32- or 64-bit, where does one decide that? I can't see anything in the IDE giving a choice. Of course with a name like QB64 I assume that 64-bit is the default.

There are two different downloads. QB64 is just the name of the language/compiler. Look at the title bar of the IDE. It should say QB64 x64 if it is the 64 bit IDE.

[SpriggsySpriggs]

I've updated the MYSQL code to be compatible with OPTION _EXPLICIT

[Paolo_Ricardo]

@SpriggsySpriggs

OK, I missed the separate 32- and 64-bit downloads.

Code: QB64: [Select]

1. I have it named libmysql64bit.dll so it would just be easier to know which one goes with which IDE. You can just change the name in the code to match the DLL.

You mean in the SQL Client code?
When you say you've changed to code to OPTION EXPLICIT which code do you mean?

Sorry, I'm a noob at all this stuff.

[SpriggsySpriggs]

The code that I posted here for your question was updated. OPTION _EXPLICIT is an option that forces you to define variables before you can use them. You don't have to use it but the MYSQL.BI and MYSQL.BM I posted won't give IDE errors if you want to use that option. As for the naming of the DLL, what I meant was that I named the 64 bit DLL the way I did so you would know to use it with 64 bit and it would be named to match the code I gave you. You can also just change that SQL Client code to match the DLL name as well. I haven't looked at the Wiki for that page in quite some time. I think I might have edited it before. Can't be certain. What do you plan on doing with MySQL? Do you have your own test server and MySQL Workbench?

[Paolo_Ricardo]

@SpriggsySpriggs

Thanks for the clarification. Understood now.

As far as MySQL is concerned, first I wanted to try my hand at Basic, something I haven't done for upwards of 40 years. Second I seem to recall that Basic (apart from Microsoft PDS 7.1) did not have any form of indexed file methods i.e. ISAM, and it appears that there are no ISAM libraries around now. I saw references to MySQL in the QB64 forums and, being familiar with SQL and MS SQL Server, I thought I would have a look.

So I've installed all the MySQL stuff including the Workbench and am slowly trying to familiarize myself with it - and QB64 :-)

[Paolo_Ricardo]

I've modified SQL Client thus:

DECLARE DYNAMIC LIBRARY "libmysql64bit" (using the 64-bit QB)

and I still get the "Cannot file dynamic library" message. The .dll and the mysql_helper.h are both in the 64-bit folder for QB64 so I'm stuck.

One thing I noticed: you have DIM conn AS _OFFSET and conn is used throughout the code except right at the end:

'*** Close the db ***
mysql_close con

Shouldn't this be conn?

[SpriggsySpriggs]

I've modified SQL Client thus:

DECLARE DYNAMIC LIBRARY "libmysql64bit" (using the 64-bit QB)

and I still get the "Cannot file dynamic library" message. The .dll and the mysql_helper.h are both in the 64-bit folder for QB64 so I'm stuck.

One thing I noticed: you have DIM conn AS _OFFSET and conn is used throughout the code except right at the end:

'*** Close the db ***
mysql_close con

Shouldn't this be conn?

Ah, I didn't edit the wiki, then. That's not something I did but yes, it should be conn. That's why I like OPTION _EXPLICIT because it keeps issues like that from appearing. Also, try doing this:
DECLARE DYNAMIC LIBRARY ".\libmysql64bit"
The .\ tells it to look in the folder where your source is rather than the folder where the EXE is located. If that still doesn't work then try using the absolute path to the DLL

[Paolo_Ricardo]

I'm getting inconsistent results. Changed the mysql.dll paths to absolute paths: "c:\qb64-32\mysql" and "c:\qb64-64\libmysql64bit" - the .dlls are in the program folder (not the source folders)

Using the 32-bit compiler I get a window which closes very quickly - I cannot see if anything is written to it. With the 64-bit compiler I get a window opening and then the "cannot find .dynamic library'' message.

[SpriggsySpriggs]

Please paste your code. Also, are you sure you are using the 64 bit DLL with the 64 bit IDE? Even if the path is correct it will not be able to find it if the DLL is wrong.

[Paolo_Ricardo]

I have CLIENT32.BAS:

Code: QB64: [Select]

1. DECLARE CUSTOMTYPE LIBRARY "mysql_helper"
2.     FUNCTION offset_to_string$ ALIAS offset_to_offset (BYVAL offset AS _OFFSET)
3.     FUNCTION offset_at_offset%& (BYVAL offset AS _OFFSET)
4. END DECLARE
5.  
6. '#### mysql.dll not provided ####
7. DECLARE DYNAMIC LIBRARY "c:\qb64-32\mysql"
8.  

and CLIENT64.BAS:

Code: QB64: [Select]

1. DECLARE CUSTOMTYPE LIBRARY "mysql_helper"
2.     FUNCTION offset_to_string$ ALIAS offset_to_offset (BYVAL offset AS _OFFSET)
3.     FUNCTION offset_at_offset%& (BYVAL offset AS _OFFSET)
4. END DECLARE
5.  
6. '#### mysql.dll not provided ####
7. DECLARE DYNAMIC LIBRARY "c:\qb64-64\libmysql64bit"
8.  

The rest of the code is common to both (SQL Client).

I am using the correct .DLLs for each version - I have copies of  your original  libmysql32bit.dll and libmysql64bit.dll in the appropriate folder (the 32-bit renamed to mysql.dll) and I have checked that the files sizes are correct.

So, unless I am doing something wrong, I am stumped. I am assuming that I just need to compile the SQL Client code.

Thanks for your patience :-)

[SpriggsySpriggs]

Can you post a screenshot of your IDE with the code? It still boils down to the wrong DLL for the IDE if it still cannot find it IF the path is correct.

[Paolo_Ricardo]

Attached is Screenshots.docx   [ You are not allowed to view this attachment ]