UseMySQLDatabase() Failure

[swissbob]

This should have been so simple... copy the example code from the help file and run it...

Code: Select all

UseMySQLDatabase()

; You should have a server running on localhost
;
If OpenDatabase(0, "host=localhost port=3306 dbname='test'", "user", "password")
  Debug "Connected to MySQL"
Else
  Debug "Connection failed: " + DatabaseError()
EndIf

Except that it dies on the very first line! The error message tells me I need to call UseODBCDatabase(), UseSQLiteDatabase() or UsePostgreSQLDatabase() first!

No idea of what to do next.....

[Marco2007]

Which version?

No problems here with PB 6.10 or 6.11 (both x64).

[Bisonte]

Which version?

No problems here with PB 6.10 or 6.11 (both x64).

this will be a version that was before PB 5.70 LTS

[Fred]

MySQL needs the mariadb DLL, be sure it's in the path. You can also debug the result of UseMySQLDatabase() to see if it prints non zero

[swissbob]

Machine: Apple M1
MacOS: Sonoma 14.4.1

PureBasic 6.11 LTS

[jacdelad]

MacOS does not need a dll, the library is linked static.
However, look at the parameters for Open database(). The very last one. The one after the password.

[mk-soft]

MacOS does not need a dll, the library is linked static.
However, look at the parameters for Open database(). The very last one. The one after the password.

That's not quite right. The libmariadb.dylib must be supplied. It is best to copy it into the APP in the path ‘Contents/Library/libmariadb.dylib’.
For this I have an IDE tool ‘MyAppData’ to copy the resources and library path into Contents.
To do this, a folder ‘MyAppData’ must be created in the project path and subfolders Resources and Library must be created. Then copy the required libraries into it.

Link: PB IDE Tool MyAppData
Compile the tool and configure it as a tool in the IDE according to Description.

Code: Select all

CompilerIf #PB_Compiler_OS = #PB_OS_MacOS

  Procedure.s GetAppPath()
    Protected bundlePathPtr = CocoaMessage(0,CocoaMessage(0,CocoaMessage(0,0,"NSBundle mainBundle"),"bundlePath"),"UTF8String")
    If bundlePathPtr
      Protected bundlePath.s = PeekS(bundlePathPtr,-1,#PB_UTF8) + "/"
    EndIf
    ProcedureReturn bundlePath
  EndProcedure
  
  ; ---------------------------------------------------------------------------------------
  
  Procedure.s GetResourcePath()
    Protected bundlePathPtr = CocoaMessage(0,CocoaMessage(0,CocoaMessage(0,0,"NSBundle mainBundle"),"resourcePath"),"UTF8String")
    If bundlePathPtr
      Protected bundlePath.s = PeekS(bundlePathPtr,-1,#PB_UTF8) + "/"
    EndIf
    ProcedureReturn bundlePath
  EndProcedure
  
  ; ---------------------------------------------------------------------------------------
  
  
  libdb.s = GetAppPath() + "Contents/Library/libmariadb.dylib"
    
  If UseMySQLDatabase(libdb) = 0
    Debug "Error MySQL"
    MessageRequester("Error", "libmariadb.dylib not found!")
    End
  EndIf
  
CompilerElse
  
  libdb.s = GetPathPart(ProgramFilename()) + "libmariadb.dll"
  
  If UseMySQLDatabase(libdb) = 0
    Debug "Error MySQL"
    MessageRequester("Error", "libmariadb.dll not found!")
    End
  EndIf
  
CompilerEndIf

[jacdelad]

Yeah, you're right, I confused it with Scintilla.

[swissbob]

So I modified the program as follows, based on comments here:

Code: Select all

UseMySQLDatabase("libmariadb.dylib")

; You should have a server running on localhost
;
OpenConsole()

If OpenDatabase(0, "host=localhost port=3306 dbname='test'", "user", "password", #PB_Database_MySQL)
  PrintN("Connected to MySQL")
Else
  PrintN("Connection failed: " + DatabaseError())
EndIf

CloseConsole()

I created a console app and copied the "libmariadb.dylib" to the same folder and ran it, getting the following errors:

Code: Select all

Connection failed: Plugin caching_sha2_password could not be loaded: dlopen(lib/mariadb/plugin/caching_sha2_password.so, 0x0002): tried: 'lib/mariadb/plugin/caching_sha2_password.so' (no such file), '/System/Volumes/Preboot/Cryptexes/OSlib/mariadb/plugin/caching_sha2_password.so' (no such file), '/usr/lib/lib/mariadb/plugin/caching_sha2_password.so' (no such file, not in dyld cache), 'lib/mariadb/plugin/caching_sha2_password.so' (no such file)

Which is not surprising given that there is no MariaDb installed on this machine! So from my perspective this is at best flaky and more like fundamentally flawed. You can't claim to support MySQL if you require a MariaDb installation in order to get the base libs....

[Fred]

Seems like MySQL is using a new password mode, you're not alone here: https://stackoverflow.com/questions/491 ... d#49966020

May be you can try one of the solution posted here.

[swissbob]

Seems like MySQL is using a new password mode, you're not alone here: https://stackoverflow.com/questions/491 ... d#49966020

May be you can try one of the solution posted here.

Thanks and yes I'm aware of this and have already "fixed" and tested this.

The problem seems to be that PureBasic is hardwired to look for the MiraDb libs (lib/mariadb/plugin/caching_sha2_password.so), which of course are not installed. And as far as I can see there is not way to just install a MiraDb client of any kind. So as I said at point I think I'll have to pass on using PureBasic to drive my data processes, which is a pity as I have been writing BASIC code for over 30 years.

[Fred]

You can also use ODBC but that's less straightforward. I will take a closer look to this as MySQL 8 will be more and more common

[Geert]

I had the same problem accessing an online MySQL (version 8.x) database.
To solve the problem, I use the following technique on my Mac mini M1:

First install homebrew. (https://brew.sh)

Code: Select all

g***@mac ~ % /bin/bash -c ‘$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)’

With brew install the formula ‘mariadb-connector-c’:

Code: Select all

g***@mac ~ % brew install mariadb-connector-c

In your purebasic program, point to the installed library ‘libmariadb.dylib’ by brew. Set the environment variable ‘MARIA_PLUGIN_DIR’ to the mariadb-plugin directory.

Code: Select all

;For macOS only!

CompilerIf #PB_Compiler_Processor=#PB_Processor_Arm64
 #HomebrewPath$="/opt/homebrew/"
CompilerElse 
 #HomebrewPath$="/usr/local/"
CompilerEndIf

SetEnvironmentVariable("MARIADB_PLUGIN_DIR",#HomebrewPath$+"lib/mariadb/plugin/")
UseMySQLDatabase(#HomebrewPath$+"lib/mariadb/libmariadb.dylib")

#Database=0

If OpenDatabase(#Database, "host=localhost port=3306 dbname='test'", "root", "")
 Debug "Connected to MySQL"
 DatabaseUpdate(#Database,"DROP TABLE IF EXISTS Persons")
 DatabaseUpdate(#Database,"CREATE TABLE Persons (LastName varchar(255), FirstName varchar(255))")
 DatabaseUpdate(#Database,~"INSERT INTO Persons (FirstName,LastName) VALUES (\"John\",\"Doe\")")
 DatabaseUpdate(#Database,~"INSERT INTO Persons (FirstName,LastName) VALUES (\"Jane\",\"Smith\")")
 If DatabaseQuery(#Database, "SELECT * FROM Persons")
  While NextDatabaseRow(#Database)
   Debug GetDatabaseString(#Database,1)+" "+GetDatabaseString(#Database,0)
  Wend  
  FinishDatabaseQuery(#Database)
 EndIf
 CloseDatabase(#Database)
Else
 Debug "Connection failed: "+DatabaseError()
EndIf

To test, you can install a MySQL server on your computer with ‘brew install mysql’ and create a database "test" as follows:

Code: Select all

g***@mac ~ % brew install mysql

Code: Select all

g***@mac ~ % mysql.server start                    
Starting MySQL
 SUCCESS! 
g***@mac ~ % mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.0.1 Homebrew

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0,01 sec)

mysql>

If all goes well, you will get the following result when you run the purebasic program:

Connected to MySQL
John Doe
Jane Smith

To simulate the intel version (x64) on a Mac with arm64 processor, you can install the intel version of homebrew and the formula ‘mariadb-connector-c’ in the following way (Rosetta 2 must be installed):

Code: Select all

g***@mac ~ % arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
g***@mac ~ % /usr/local/bin/brew install mariadb-connector-c

Then run the program with the intel version of purebasic.