Page 1 of 1
How to set mysql_optionsv parameter for working with mariadb
Posted: Wed Jul 15, 2020 4:06 pm
by 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?
Re: How to set mysql_optionsv parameter for working with mar
Posted: Wed Jul 15, 2020 7:05 pm
by 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
Re: How to set mysql_optionsv parameter for working with mar
Posted: Wed Jul 15, 2020 7:39 pm
by mk-soft
Update
- Update constants
- Added mysql_get_optionv
Re: How to set mysql_optionsv parameter for working with mar
Posted: Wed Jul 15, 2020 8:06 pm
by 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
Re: How to set mysql_optionsv parameter for working with mar
Posted: Wed Jul 15, 2020 8:33 pm
by 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/
Re: How to set mysql_optionsv parameter for working with mar
Posted: Wed Jul 15, 2020 8:56 pm
by 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;
Re: How to set mysql_optionsv parameter for working with mar
Posted: Wed Jul 15, 2020 11:02 pm
by mk-soft
Re: How to set mysql_optionsv parameter for working with mar
Posted: Thu Jul 16, 2020 5:04 am
by olmak
mk-soft wrote: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...