It is currently Sun Dec 06, 2020 2:45 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: How to set mysql_optionsv parameter for working with mariadb
PostPosted: Wed Jul 15, 2020 4:06 pm 
Offline
User
User

Joined: Thu Aug 11, 2016 4:00 am
Posts: 14
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?


Top
 Profile  
Reply with quote  
 Post subject: Re: How to set mysql_optionsv parameter for working with mar
PostPosted: Wed Jul 15, 2020 7:05 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 2751
Location: Germany
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:
;-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

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: How to set mysql_optionsv parameter for working with mar
PostPosted: Wed Jul 15, 2020 7:39 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 2751
Location: Germany
Update
- Update constants
- Added mysql_get_optionv

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: How to set mysql_optionsv parameter for working with mar
PostPosted: Wed Jul 15, 2020 8:06 pm 
Offline
User
User

Joined: Thu Aug 11, 2016 4:00 am
Posts: 14
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:
#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


Top
 Profile  
Reply with quote  
 Post subject: Re: How to set mysql_optionsv parameter for working with mar
PostPosted: Wed Jul 15, 2020 8:33 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 2751
Location: Germany
I think is a String, but I don't know UNICODE or UTF8

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


Quote:
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/

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: How to set mysql_optionsv parameter for working with mar
PostPosted: Wed Jul 15, 2020 8:56 pm 
Offline
User
User

Joined: Thu Aug 11, 2016 4:00 am
Posts: 14
[quote="mk-soft"]I think is a String, but I don't know UNICODE or UTF8

Code:
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;


Top
 Profile  
Reply with quote  
 Post subject: Re: How to set mysql_optionsv parameter for working with mar
PostPosted: Wed Jul 15, 2020 11:02 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 2751
Location: Germany
perhaps security setting on the mysql server ...

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

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: How to set mysql_optionsv parameter for working with mar
PostPosted: Thu Jul 16, 2020 5:04 am 
Offline
User
User

Joined: Thu Aug 11, 2016 4:00 am
Posts: 14
Quote:
mk-soft wrote:
perhaps security setting on the mysql server ...

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

Quote:
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...


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 22 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye