MySQL DLL

[SpriggsySpriggs]

Does anyone know where a 64 bit version of the MySQL DLL is so that it can be used in a 64 bit application? When I use the DLL in a 64 bit program it finds the library just fine in the IDE but at runtime always says that it can't find the library. Works fine in 32 bit but not 64. Anyone have a solution?

[bplus]

It seems to me this was asked before, did you try a search here at forum?

I know searches here are frustrating, I've searched for things I know are here I just manage to use the wrong word or word set or something.

Oh yeah, and Steve has this dictionary thing posted somewhere where every dang word is found in that idiot thing! :P

[bplus]

Really odd how search engine works, I search MySQL Dll and get a bunch of stuff, forgot to copy link and search again, nothing but this thread comes up.

So I go back to my Browser and go back a few screens and there it is again:
https://www.qb64.org/forum/index.php?topic=2499.msg117388#msg117388
the recent discussion of MySQL Dll 64 bit.

[Juanjogomez]

I have found a 32 and 64 bit version. I still use a different 32-bit but I tried these two and they worked.
Tell me if they work well for you in case I use them in the future.

[SpriggsySpriggs]

Thank you! I will try these soon and let you know how it goes!

[SpriggsySpriggs]

I have found a 32 and 64 bit version. I still use a different 32-bit but I tried these two and they worked.
Tell me if they work well for you in case I use them in the future.

Absolutely amazing. It worked immediately. Thank you so much.

[Juanjogomez]

I'm happy!! Finally find someone from the forum who is programming with mysql

[SpriggsySpriggs]

I'm happy!! Finally find someone from the forum who is programming with mysql

MySQL is the best! It works well with QB64. I only wish there was a way to do Datasets so you can grab the column names at the same time as the rest of the data in the query. The current MySQL example on the Wiki does not allow for it.

[Juanjogomez]

All the names of the fields of a table can be obtained with this command. In a consultation I do not know how they can be obtained.

"SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name='" + table$ + "' ORDER BY ordinal_position" + CHR$(0)

And now a question,,,,Do you know how to get the affected rows in an INSERT or a DELETE?

[SpriggsySpriggs]

All the names of the fields of a table can be obtained with this command. In a consultation I do not know how they can be obtained.
"SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name='" + table$ + "' ORDER BY ordinal_position" + CHR$(0)
And now a question,,,,Do you know how to get the affected rows in an INSERT or a DELETE?

I was aware of being able to get column names as a separate command and I do use that. I just know that in C# the column headings are grabbed automatically in each query and make it a lot easier when you are wanting specific data by column name instead of having to remember the column number in the query. As for affected rows, I assume you mean the number of rows that MySQL tells you matched the query and were changed. If so then no, I don't know how to do this. I only see it in workbench. I usually just run a select statement after doing anything that updates the database to see if the new data is in the table.

[Juanjogomez]

ok. Thanks

[SpriggsySpriggs]

Maybe you and I need to get together and figure out a way to find a reliable way to get the affected rows. I'm sure someone online has found a way to do it.

[SpriggsySpriggs]

And now a question,,,,Do you know how to get the affected rows in an INSERT or a DELETE?

I do now! I checked online and found the reference for MySQL 8.0 and it had the function mysql_affected_rows() which is to be called after the mysql_query() function. I added a shared variable to my declarations called affectedRows& and a function called mysql_affected_rows~&& (BYVAL mysql AS _OFFSET)
I called affectedRows& = msyql_affected_rows(Database(DB_Selected).Object) and it worked! I can now see how many rows are updated! All I had to do was type PRINT affectedRows& in my main module! Thank you, @Juanjogomez for inspiring me to do this!
Here is a screenshot showing the rows affected return value as well as the ID of the field updated/added without having to run another select!
  [ You are not allowed to view this attachment ]  
And another screenshot after I fixed some issues:
  [ You are not allowed to view this attachment ]  
Here is the updated "BI" and "BM" file that uses the new function for affected rows:
"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.         END DECLARE
35.     $ELSE
36.         DECLARE DYNAMIC LIBRARY "libmysql64bit"
37.         FUNCTION mysql_init%& (BYVAL always_0 AS _OFFSET)
38.         FUNCTION mysql_error$ (BYVAL mysql AS _OFFSET)
39.         FUNCTION mysql_get_client_info$
40.         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)
41.         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)
42.         SUB mysql_query (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
43.         FUNCTION mysql_query& (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
44.         FUNCTION mysql_store_result%& (BYVAL mysql AS _OFFSET)
45.         FUNCTION mysql_num_fields~& (BYVAL result AS _OFFSET)
46.         FUNCTION mysql_num_rows&& (BYVAL result AS _OFFSET)
47.         FUNCTION mysql_fetch_row%& (BYVAL result AS _OFFSET)
48.         FUNCTION mysql_fetch_lengths%& (BYVAL result AS _OFFSET)
49.         SUB mysql_close (BYVAL mysql AS _OFFSET)
50.         SUB mysql_free_result (BYVAL result AS _OFFSET)
51.         FUNCTION mysql_affected_rows~&& (BYVAL mysql AS _OFFSET)
52.         FUNCTION mysql_insert_id~&&(BYVAL mysql AS _OFFSET)
53.         END DECLARE
54.     $END IF
55.     '--------------------------------------------------------------------------------------------
56.     TYPE DB_TYPE
57.         Object AS _OFFSET
58.     END TYPE

"BM"

Code: QB64: [Select]

1. SUB DB_Critical_Error (message AS STRING)
2. PRINT message
3. 'SYSTEM
4. END
5. END SUB
6.  
7. FUNCTION DB_Open (host_ip$, user_name$, user_password$, DB_name$) 'if DB_name="" then no database is selected
8. DB_Last_Error = ""
9. 'create new handle
10. FOR DB = 1 TO DB_Last
11. IF Database(DB).Object = 0 THEN EXIT FOR
12. NEXT
13. IF DB > UBOUND(Database) THEN REDIM _PRESERVE Database(UBOUND(Database) + 10) AS DB_TYPE: DB_Last = DB
14. 'create new object
15. Database(DB).Object = mysql_init(0): IF Database(DB).Object = 0 THEN DB_Critical_Error "mysql_init failed"
16. 'attempt to connect
17. IF DB_name$ = "" THEN
18. 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)
19. ELSE
20. 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)
21. END IF
22. IF object%& = 0 THEN
23. DB_Last_Error = mysql_error(Database(DB).Object)
24. Database(DB).Object = 0 'free index
25. EXIT FUNCTION
26. END IF
27. DB_Selected = DB
28. DB_Open = DB
29. END FUNCTION
30.  
31. SUB DB_QUERY (mysql_command$)
32. IF DB_Selected < 0 OR DB_Selected > DB_Last THEN
33. DB_Critical_Error "DB_QUERY: Invalid handle"
34. SYSTEM
35. END IF
36. IF Database(DB_Selected).Object = 0 THEN
37. DB_Critical_Error "DB_QUERY: Invalid handle"
38. SYSTEM
39. END IF
40. DB_Last_Error = ""
41. result = mysql_query(Database(DB_Selected).Object, mysql_command$ + CHR$(0))
42. affectedRows& = mysql_affected_rows(Database(DB_Selected).Object)
43. returnedID& = mysql_insert_id(Database(DB_Selected).Object)
44. IF result THEN
45. DB_Last_Error = mysql_error(Database(DB_Selected).Object)
46. EXIT SUB
47. END IF
48. DIM mysql_result AS _OFFSET
49. mysql_result = mysql_store_result(Database(DB_Selected).Object)
50. IF mysql_result = 0 THEN
51. '...todo...
52. ELSE
53. columns = mysql_num_fields(mysql_result)
54. rows = mysql_num_rows(mysql_result)
55. REDIM DB_RESULT(columns, rows) AS STRING
56. FOR y = 1 TO rows
57. DIM mysql_row AS _OFFSET
58. mysql_row = mysql_fetch_row(mysql_result)
59. DIM mem_mysql_row AS _MEM
60. mem_mysql_row = _MEM(mysql_row, columns * LEN(an_offset%&))
61. DIM mysql_lengths AS _OFFSET
62. mysql_lengths = mysql_fetch_lengths(mysql_result)
63. DIM mem_mysql_lengths AS _MEM
64. mem_mysql_lengths = _MEM(mysql_lengths, columns * 4)
65. DIM mem_field AS _MEM
66. FOR x = 1 TO columns
67. length = _MEMGET(mem_mysql_lengths, mem_mysql_lengths.OFFSET + (x - 1) * 4, _UNSIGNED LONG)
68. mem_field = _MEM(_MEMGET(mem_mysql_row, mem_mysql_row.OFFSET + (x - 1) * LEN(an_offset%&), _OFFSET), length)
69. DB_RESULT(x, y) = SPACE$(length)
70. _MEMGET mem_field, mem_field.OFFSET, DB_RESULT(x, y)
71. _MEMFREE mem_field
72. NEXT
73. _MEMFREE mem_mysql_lengths
74. _MEMFREE mem_mysql_row
75. NEXT
76. selectedRows = UBOUND(db_result,2)
77. mysql_free_result mysql_result
78. END IF
79. END SUB

Try it out and let me know if it works for you!

[Juanjogomez]

I've tried it and it works perfectly. I knew that MySQL had that function but I didn't know how to declare it in the library and use it.

Thank you very much SpriggsySpringgs!! It was a feature that I missed and that is very useful.

[SpriggsySpriggs]

Thank you very much SpriggsySpringgs!! It was a feature that I missed and that is very useful.

You're very welcome! Glad to have helped you! Now I'll update the SQL Client Wiki this week with the new functions and variables so that other people can get a new demo. I'll make sure to post a new download link for the DLLs on the page as well. Thank you for your help in testing the code! Looking forward to doing MySQL projects with you in the future (if you want)!