How to set mysql_optionsv parameter for working with mariadb

[olmak]

To load a text file into the mariadb table, I need to run the command
LOAD Data LOCAL INFILE. But the LOCAL parameter must be explicitly enabled as with the server one,
so from the client side. Well, from the server side, I installed it in the server settings file
my.ini with the string local-infile = 1. But with the client side I need
set mysql_optionsv with MYSQL_OPT_LOCAL_INFILE enabled
https://mariadb.com/kb/en/mysql_optionsv/
The question is how to do this in Purebasic?

[mk-soft]

I made it together once.
Couldn't test it all, though.

Remember that the libmariadb,[dll,so,dylib] must be shipped with it.

Update
- Update constants
- Added mysql_get_optionv

Code: Select all

;-TOP

Enumeration
	#MYSQL_OPT_CONNECT_TIMEOUT
	#MYSQL_OPT_COMPRESS
	#MYSQL_OPT_NAMED_PIPE
	#MYSQL_INIT_COMMAND
	#MYSQL_READ_DEFAULT_FILE
	#MYSQL_READ_DEFAULT_GROUP
	#MYSQL_SET_CHARSET_DIR
	#MYSQL_SET_CHARSET_NAME
	#MYSQL_OPT_LOCAL_INFILE
	#MYSQL_OPT_PROTOCOL
	#MYSQL_SHARED_MEMORY_BASE_NAME
	#MYSQL_OPT_READ_TIMEOUT
	#MYSQL_OPT_WRITE_TIMEOUT
	#MYSQL_OPT_USE_RESULT
	#MYSQL_OPT_USE_REMOTE_CONNECTION
	#MYSQL_OPT_USE_EMBEDDED_CONNECTION
	#MYSQL_OPT_GUESS_CONNECTION
	#MYSQL_SET_CLIENT_IP
	#MYSQL_SECURE_AUTH
	#MYSQL_REPORT_DATA_TRUNCATION
	#MYSQL_OPT_RECONNECT
	#MYSQL_OPT_SSL_VERIFY_SERVER_CERT
	#MYSQL_PLUGIN_DIR
	#MYSQL_DEFAULT_AUTH
	#MYSQL_OPT_BIND
	#MYSQL_OPT_SSL_KEY
	#MYSQL_OPT_SSL_CERT
	#MYSQL_OPT_SSL_CA
	#MYSQL_OPT_SSL_CAPATH
	#MYSQL_OPT_SSL_CIPHER
	#MYSQL_OPT_SSL_CRL
	#MYSQL_OPT_SSL_CRLPATH
	#MYSQL_OPT_CONNECT_ATTR_RESET
	#MYSQL_OPT_CONNECT_ATTR_ADD
	#MYSQL_OPT_CONNECT_ATTR_DELETE
	#MYSQL_SERVER_PUBLIC_KEY
	#MYSQL_ENABLE_CLEARTEXT_PLUGIN
	#MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
	#MYSQL_OPT_SSL_ENFORCE
	#MYSQL_OPT_MAX_ALLOWED_PACKET
	#MYSQL_OPT_NET_BUFFER_LENGTH
	#MYSQL_OPT_TLS_VERSION
	#MYSQL_OPT_SSL_MODE
EndEnumeration

PrototypeC proto_mysql_optionsv(*mysql, mysql_option, arg)
PrototypeC proto_mysql_optionsv2(*mysql, mysql_option, arg, arg2)
PrototypeC proto_mysql_optionsv3(*mysql, mysql_option, arg, arg2, arg3)
PrototypeC proto_mysql_get_optionv(*mysql, mysql_option, arg)
PrototypeC proto_mysql_get_optionv2(*mysql, mysql_option, arg, arg2)
PrototypeC proto_mysql_get_optionv3(*mysql, mysql_option, arg, arg2, arg3)

CompilerSelect #PB_Compiler_OS
  CompilerCase #PB_OS_Windows
    path_lib.s = "libmariadb.dll"
  CompilerCase #PB_OS_Linux
    path_lib.s = "libmariadb.so"
  CompilerCase #PB_OS_MacOS
    path_lib.s = "libmariadb.dylib"
CompilerEndSelect

If OpenLibrary(0, path_lib)
  func_addr = GetFunction(0, "mysql_optionsv")
  mysql_optionsv.proto_mysql_optionsv = func_addr
  mysql_optionsv2.proto_mysql_optionsv2 = func_addr
  mysql_optionsv3.proto_mysql_optionsv3 = func_addr
  func_addr = GetFunction(0, "mysql_get_optionv")
  mysql_get_optionv.proto_mysql_get_optionv = func_addr
  mysql_get_optionv2.proto_mysql_get_optionv2 = func_addr
  mysql_get_optionv3.proto_mysql_get_optionv3 = func_addr
Else
  Debug "Error open lib " + path_lib
  End
EndIf

If UseMySQLDatabase() = 0
  Debug "Error MySQL"
  End
EndIf

database.s = "host=server port=3306 dbname=developer"
user.s = "developer"
pass.s = "purebasic"

CompilerIf Defined(PB_Database_MySQL, #PB_Constant) = 0
  #PB_Database_MySQL = 4
CompilerEndIf

If OpenDatabase(0, database, user, pass, #PB_Database_MySQL)
  mysql_handle = DatabaseID(0)
  If mysql_handle
    
    timeout = 5
    r1 = mysql_optionsv(mysql_handle, #MYSQL_OPT_CONNECT_TIMEOUT, @timeout)
    If r1 = 0
      Debug "Ok"
    Else
      Debug "Error: mysql_optionsv -> Errorcode = " + Hex(r1)
    EndIf
    
    r1 = mysql_get_optionv(mysql_handle, #MYSQL_OPT_CONNECT_TIMEOUT, @timeout)
    If r1 = 0
      Debug "Ok: Timeout = " + timeout
    Else
      Debug "Error: mysql_optionsv -> Errorcode = " + Hex(r1)
    EndIf
    
  EndIf
  
  CloseDatabase(0)
Else
  Debug "Can't open database !"
  CallDebugger
EndIf

[mk-soft]

Update
- Update constants
- Added mysql_get_optionv

[olmak]

Thank you very much for your answer. This helped me a lot in understanding the subject. But I need to set the OPT_LOCAL_INFILE parameter and as a result get the ability to upload large files to the database using the command LOAD Data LOCAL INFILE. I changed your code a bit in order to set MYSQL_OPT_LOCAL_INFILE to the parameter I need to turn it on. The function works successfully, but the LOAD Data LOCAL INFILE 'c: \\ Proxy_Logs \\ test4.txt' INTO TABLE TempWork command does not work, although when I execute this command through the HeidiSQL utility, the data is successfully imported into the table. I can’t understand whether the MYSQL_OPT_LOCAL_INFILE parameter still did not set to the resolved state or the problem is something else

Code: Select all

#Db=0 ; Db Number
Procedure DbUpd(ParDbUpdStr$)
  result = DatabaseUpdate(#Db, ParDbUpdStr$) ; Выполняем запрос модификации БД
 If result
    Debug "Modification Request "+ ParDbUpdStr$ + " completed successfully"
  Else
    Debug "Error" + DatabaseError() + "  modification request : " + ParDbUpdStr$
  EndIf   
EndProcedure

#MYSQL_OPT_CONNECT_TIMEOUT = 0
#MYSQL_OPT_COMPRESS = 1       
#MYSQL_OPT_NAMED_PIPE = 2     
#MYSQL_INIT_COMMAND = 3       
#MYSQL_READ_DEFAULT_FILE = 4 
#MYSQL_READ_DEFAULT_GROUP = 5
#MYSQL_SET_CHARSET_DIR = 6   
#MYSQL_SET_CHARSET_NAME = 7   
#MYSQL_OPT_LOCAL_INFILE = 8   
#MYSQL_OPT_PROTOCOL = 9       
#MYSQL_SHARED_MEMORY_BASE_NAME = 10
#MYSQL_OPT_READ_TIMEOUT = 11       
#MYSQL_OPT_WRITE_TIMEOUT = 12     
#MYSQL_OPT_USE_RESULT = 13         
#MYSQL_OPT_USE_REMOTE_CONNECTION = 14
#MYSQL_OPT_USE_EMBEDDED_CONNECTION = 15
#MYSQL_OPT_GUESS_CONNECTION = 16       
#MYSQL_SET_CLIENT_IP = 17             
#MYSQL_SECURE_AUTH = 18               
#MYSQL_REPORT_DATA_TRUNCATION = 19     
#MYSQL_OPT_RECONNECT = 20             
#MYSQL_OPT_SSL_VERIFY_SERVER_CERT = 21

PrototypeC mysql_optionsv(*mysql, mysql_option, arg)
PrototypeC mysql_optionsv2(*mysql, mysql_option, arg, arg2)
PrototypeC mysql_optionsv3(*mysql, mysql_option, arg, arg2, arg3)

CompilerSelect #PB_Compiler_OS
  CompilerCase #PB_OS_Windows
    path_lib.s = "libmariadb.dll"
  CompilerCase #PB_OS_Linux
    path_lib.s = "libmariadb.so"
  CompilerCase #PB_OS_MacOS
    path_lib.s = "libmariadb.dylib"
CompilerEndSelect

If OpenLibrary(0, path_lib)
  func_addr = GetFunction(0, "mysql_optionsv")
  mysql_optionsv.mysql_optionsv = func_addr
  mysql_optionsv2.mysql_optionsv2 = func_addr
  mysql_optionsv3.mysql_optionsv3 = func_addr
Else
  Debug "Error open lib " + path_lib
  End
EndIf

If UseMySQLDatabase() = 0
  Debug "Error MySQL"
  End
EndIf

database.s = "host=localhost port=3306 dbname=ProxyLogs"
user.s = "root"
pass.s = "pasw"

CompilerIf Defined(PB_Database_MySQL, #PB_Constant) = 0
  #PB_Database_MySQL = 4
CompilerEndIf

If OpenDatabase(#Db, database, user, pass, #PB_Database_MySQL)
  on=1
  mysql_handle = DatabaseID(#Db)
  If mysql_handle
    r1 = mysql_optionsv(mysql_handle, #MYSQL_OPT_LOCAL_INFILE, @on)
    If r1 = 0
      Debug "Ok"
    Else
      Debug "Error: mysql_optionsv -> Errorcode = " + Hex(r1)
    EndIf
  EndIf
  
  Dbupd("CREATE TABLE IF NOT EXISTS `TempWork` (`Field1` VARCHAR(10) NULL DEFAULT NULL ) ; ")
  Dbupd("LOAD Data LOCAL INFILE 'c:\\Proxy_Logs\\test4.txt' INTO TABLE TempWork ;")
 
  CloseDatabase(#Db)
Else
  Debug "Can't open database !"
  CallDebugger
EndIf

[mk-soft]

I think is a String, but I don't know UNICODE or UTF8

Code: Select all

r1 = mysql_optionsv(mysql_handle, #MYSQL_OPT_LOCAL_INFILE, @"1")

mysql_optionsv(mysql, MYSQL_OPT_LOCAL_INFILE, NULL); /* disable */
mysql_optionsv(mysql, MYSQL_OPT_LOCAL_INFILE, (void *)"1"); /* enable */

More: https://mariadb.com/kb/en/load-data-infile/

[olmak]

[quote="mk-soft"]I think is a String, but I don't know UNICODE or UTF8

Code: Select all

r1 = mysql_optionsv(mysql_handle, #MYSQL_OPT_LOCAL_INFILE, @"1")

So the mysql_optionsv function also succeeds. But DatabaseUpdate still doesn't execute the command (LOAD Data LOCAL INFILE 'c: \\ Proxy_Logs \\ test4.txt' INTO TABLE TempWork;

[mk-soft]

perhaps security setting on the mysql server ...

https://stackoverflow.com/questions/141 ... ata-infile

[olmak]

perhaps security setting on the mysql server ...

Well, from the server side, I installed it in the server settings file
my.ini with the string local-infile = 1

when I execute this command through the HeidiSQL utility, the data is successfully imported into the table

The script with the LOAD Data LOCAL INFILE command is also successfully executed using the mysql.exe utility...