Text Only | Text with Attachments

QB64.org Forum

Active Forums => QB64 Discussion => Topic started by: SpriggsySpriggs on June 16, 2020, 08:58:01 am

Title: MySQL DLL
Post by: SpriggsySpriggs on June 16, 2020, 08:58:01 am
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?
Title: Re: MySQL DLL
Post by: bplus on June 16, 2020, 11:49:07 am
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
Title: Re: MySQL DLL
Post by: bplus on June 16, 2020, 02:11:53 pm
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.
Title: Re: MySQL DLL
Post by: Juanjogomez on June 18, 2020, 02:35:58 pm
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.
Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 18, 2020, 03:32:52 pm
Thank you! I will try these soon and let you know how it goes!
Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 19, 2020, 02:42:53 pm
Quote from: Juanjogomez on June 18, 2020, 02:35:58 pm
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.
Title: Re: MySQL DLL
Post by: Juanjogomez on June 20, 2020, 04:00:48 pm
I'm happy!! Finally find someone from the forum who is programming with mysql
Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 22, 2020, 07:01:26 am
Quote from: Juanjogomez on June 20, 2020, 04:00:48 pm
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.
Title: Re: MySQL DLL
Post by: Juanjogomez on June 22, 2020, 03:02:40 pm
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?



Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 22, 2020, 03:22:05 pm
Quote from: Juanjogomez on June 22, 2020, 03:02:40 pm
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.
Title: Re: MySQL DLL
Post by: Juanjogomez on June 23, 2020, 11:44:58 am
ok. Thanks
Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 23, 2020, 12:35:05 pm
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.
Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 23, 2020, 01:58:38 pm
Quote from: Juanjogomez on June 22, 2020, 03:02:40 pm
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!
  [ This attachment cannot be displayed inline in 'Print Page' view ]  
And another screenshot after I fixed some issues:
  [ This attachment cannot be displayed inline in 'Print Page' view ]  
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
  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
  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)
  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)
  22. IF object%& = 0 THEN
  23. DB_Last_Error = mysql_error(Database(DB).Object)
  24. Database(DB).Object = 0 'free index
  27. DB_Selected = DB
  28. DB_Open = DB
  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"
  36. IF Database(DB_Selected).Object = 0 THEN
  37. DB_Critical_Error "DB_QUERY: Invalid handle"
  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)
  48. DIM mysql_result AS _OFFSET
  49. mysql_result = mysql_store_result(Database(DB_Selected).Object)
  50. IF mysql_result = 0 THEN
  51. '...todo...
  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
  73. _MEMFREE mem_mysql_lengths
  74. _MEMFREE mem_mysql_row
  76. selectedRows = UBOUND(db_result,2)
  77. mysql_free_result mysql_result
Try it out and let me know if it works for you!
Title: Re: MySQL DLL
Post by: Juanjogomez on June 24, 2020, 11:17:34 am
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.
Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 24, 2020, 11:35:09 am
Quote from: Juanjogomez on June 24, 2020, 11:17:34 am
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)!
Title: Re: MySQL DLL
Post by: SpriggsySpriggs on June 24, 2020, 02:49:38 pm
Using my WPFMessageBox function, I made some pretty message boxes for MySQL errors:
  [ This attachment cannot be displayed inline in 'Print Page' view ]  
I probably won't leave it in when I make my post about using MySQL unless I make two versions; one with WPF message boxes and one using the Message Box library from the Wiki. Looks nice, though.
Text Only | Text with Attachments