mysql

t57042 · Post by **t57042** » Fri Jan 11, 2013 4:27 pm

Has anyone a 'working' example how to use mysql in PB?

Richard

infratec · Post by **infratec** » Fri Jan 11, 2013 5:54 pm

Hi,

ODBC or native via dll or .so

Which operating system

t57042 · Post by **t57042** » Fri Jan 11, 2013 7:47 pm

preferably via dll - OS is windows 7.

infratec · Post by **infratec** » Fri Jan 11, 2013 7:56 pm

Hi,

Ok, let's go:

First you need a wrapper for the dll: (save it as mysql.pbi)

Code: Select all

;
; MySQL.pbi V1.00
;
; a wrapper for MySQL 6.0.2
;
; by BKK



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
EndEnumeration

Enumeration
  #MYSQL_STATUS_READY
  #MYSQL_STATUS_GET_RESULT
  #MYSQL_STATUS_USE_RESULT 
EndEnumeration

Enumeration
  #MYSQL_PROTOCOL_DEFAULT
  #MYSQL_PROTOCOL_TCP
  #MYSQL_PROTOCOL_SOCKET
  #MYSQL_PROTOCOL_PIPE
  #MYSQL_PROTOCOL_MEMORY
EndEnumeration

Enumeration
  #MYSQL_STMT_INIT_DONE = 1
  #MYSQL_STMT_PREPARE_DONE
  #MYSQL_STMT_EXECUTE_DONE
  #MYSQL_STMT_FETCH_DONE
EndEnumeration

Enumeration
  #STMT_ATTR_UPDATE_MAX_LENGTH
  #STMT_ATTR_CURSOR_TYPE
  #STMT_ATTR_PREFETCH_ROWS
EndEnumeration



#SCRAMBLE_LENGTH = 20
#SCRAMBLE_LENGTH_323 = 8

#SCRAMBLED_PASSWORD_CHAR_LENGTH = (#SCRAMBLE_LENGTH*2+1)
#SCRAMBLED_PASSWORD_CHAR_LENGTH_323 = (#SCRAMBLE_LENGTH_323*2)


#NOT_NULL_FLAG = 1		  ; Field can't be NULL
#PRI_KEY_FLAG	= 2       ; Field is part of a primary key
#UNIQUE_KEY_FLAG = 4    ; Field is part of a unique key
#MULTIPLE_KEY_FLAG = 8  ; Field is part of a key
#BLOB_FLAG = 16         ; Field is a blob
#UNSIGNED_FLAG = 32     ; Field is unsigned
#ZEROFILL_FLAG = 64     ; Field is zerofill
#BINARY_FLAG = 128      ; Field is binary

#ENUM_FLAG = 256        ; field is an enum
#AUTO_INCREMENT_FLAG = 512  ; field is a autoincrement field
#TIMESTAMP_FLAG = 1024      ; Field is a timestamp
#SET_FLAG = 2048            ; field is a set
#NO_DEFAULT_VALUE_FLAG = 4096 ; Field doesn't have default value
#N_UPDATE_NOW_FLAG = 8192     ; Field is set To NOW on UPDATE
#NUM_FLAG = 32768             ; Field is num (For clients)
#PART_KEY_FLAG = 16384        ; Intern; Part of some key
#GROUP_FLAG = 32768           ; Intern: Group field
#UNIQUE_FLAG = 65536          ; Intern: Used by sql_yacc
#BINCMP_FLAG = 131072         ; Intern: Used by sql_yacc
#GET_FIXED_FIELDS_FLAG = (1 << 18)  ; Used To get fields in item tree
#FIELD_IN_PART_FUNC_FLAG = (1 << 19)  ; Field part of partition func
#FIELD_IN_ADD_INDEX = (1<< 20)        ; Intern: Field used in ADD INDEX
#FIELD_IS_RENAMED = (1<< 21)          ; Intern: Field is being renamed
#FIELD_STORAGE_FLAGS = 22             ; Storage type: bit 22, 23 And 24
#COLUMN_FORMAT_FLAGS = 25             ; Column format: bit 25, 26 And 27

#REFRESH_GRANT = 1    ; Refresh grant tables
#REFRESH_LOG = 2      ; Start on new log file
#REFRESH_TABLES = 4   ; close all tables
#REFRESH_HOSTS = 8    ; Flush host cache
#REFRESH_STATUS = 16  ; Flush status variables
#REFRESH_THREADS = 32 ; Flush thread cache
#REFRESH_SLAVE = 64   ; Reset master info And restart slave thread
#REFRESH_MASTER = 128 ; Remove all bin logs in the index And truncate the index

#REFRESH_READ_LOCK = 16384  ; Lock tables For Read
#REFRESH_FAST = 32768       ; Intern flag

#REFRESH_QUERY_CACHE = 65536
#REFRESH_QUERY_CACHE_FREE = $20000  ; pack query cache
#REFRESH_DES_KEY_FILE = $40000
#REFRESH_USER_RESOURCES = $80000
#REFRESH_BACKUP_LOG = $200000




#CLIENT_LONG_PASSWORD	= 1	            ; new more secure passwords
#CLIENT_FOUND_ROWS = 2	              ; Found instead of affected rows
#CLIENT_LONG_FLAG	= 4	                ; Get all column flags
#CLIENT_CONNECT_WITH_DB = 8	          ; One can specify db on connect
#CLIENT_NO_SCHEMA	= 16	              ; Don't allow database.table.column
#CLIENT_COMPRESS = 32	                ; Can use compression protocol
#CLIENT_ODBC = 64	                    ; Odbc client
#CLIENT_LOCAL_FILES =	128	            ; Can use LOAD Data LOCAL
#CLIENT_IGNORE_SPACE = 256	          ; Ignore spaces before '('
#CLIENT_PROTOCOL_41	= 512	            ; New 4.1 protocol
#CLIENT_INTERACTIVE	= 1024	          ; This is an interactive client
#CLIENT_SSL = 2048	                  ; Switch To SSL after handshake
#CLIENT_IGNORE_SIGPIPE = 4096         ; IGNORE sigpipes
#CLIENT_TRANSACTIONS = 8192	          ; Client knows about transactions
#CLIENT_RESERVED = 16384              ; Old flag For 4.1 protocol
#CLIENT_SECURE_CONNECTION = 32768     ; New 4.1 authentication
#CLIENT_MULTI_STATEMENTS = (1 << 16)  ; Enable/disable multi-stmt support
#CLIENT_MULTI_RESULTS = (1 << 17)     ; Enable/disable multi-results
#CLIENT_PS_MULTI_RESULTS = (1 << 18)  ; Multi-results in PS-protocol

#CLIENT_SSL_VERIFY_SERVER_CERT = (1 << 30)
#CLIENT_REMEMBER_OPTIONS = (1 << 31) 


#SERVER_STATUS_IN_TRANS = 1               ; Transaction has started
#SERVER_STATUS_AUTOCOMMIT = 2             ; Server in auto_commit mode
#SERVER_MORE_RESULTS_EXISTS = 8           ; Multi query - Next query exists
#SERVER_QUERY_NO_GOOD_INDEX_USED = 16
#SERVER_QUERY_NO_INDEX_USED = 32
#SERVER_STATUS_CURSOR_EXISTS = 64
#SERVER_STATUS_LAST_ROW_SENT = 128
#SERVER_STATUS_DB_DROPPED = 256           ; A database was dropped
#SERVER_STATUS_NO_BACKSLASH_ESCAPES = 512
#SERVER_STATUS_METADATA_CHANGED = 1024
#SERVER_QUERY_WAS_SLOW = 2048
#SERVER_PS_OUT_PARAMS = 4096 


Enumeration
  #MYSQL_TYPE_DECIMAL
  #MYSQL_TYPE_TINY
  #MYSQL_TYPE_SHORT
  #MYSQL_TYPE_LONG
  #MYSQL_TYPE_FLOAT
  #MYSQL_TYPE_DOUBLE
  #MYSQL_TYPE_NULL
  #MYSQL_TYPE_TIMESTAMP
  #MYSQL_TYPE_LONGLONG
  #MYSQL_TYPE_INT24
  #MYSQL_TYPE_DATE
  #MYSQL_TYPE_TIME
  #MYSQL_TYPE_DATETIME
  #MYSQL_TYPE_YEAR
  #MYSQL_TYPE_NEWDATE
  #MYSQL_TYPE_VARCHAR
  #MYSQL_TYPE_BIT
  #MYSQL_TYPE_NEWDECIMAL = 246
  #MYSQL_TYPE_ENUM = 247
  #MYSQL_TYPE_SET = 248
  #MYSQL_TYPE_TINY_BLOB = 249
  #MYSQL_TYPE_MEDIUM_BLOB = 250
  #MYSQL_TYPE_LONG_BLOB = 251
  #MYSQL_TYPE_BLOB = 252
  #MYSQL_TYPE_VAR_STRING = 253
  #MYSQL_TYPE_STRING = 254
  #MYSQL_TYPE_GEOMETRY = 255
  #MAX_NO_FIELD_TYPES        ; Should always be last
EndEnumeration

#MYSQL_SHUTDOWN_KILLABLE_CONNECT = (1 << 0)
#MYSQL_SHUTDOWN_KILLABLE_TRANS = (1 << 1)
#MYSQL_SHUTDOWN_KILLABLE_LOCK_TABLE = (1 << 2)
#MYSQL_SHUTDOWN_KILLABLE_UPDATE = (1 << 3)


Enumeration
  #SHUTDOWN_DEFAULT = 0
  #SHUTDOWN_WAIT_CONNECTIONS = #MYSQL_SHUTDOWN_KILLABLE_CONNECT
  #SHUTDOWN_WAIT_TRANSACTIONS = #MYSQL_SHUTDOWN_KILLABLE_TRANS
  #SHUTDOWN_WAIT_UPDATES = #MYSQL_SHUTDOWN_KILLABLE_UPDATE
  #SHUTDOWN_WAIT_ALL_BUFFERS = (#MYSQL_SHUTDOWN_KILLABLE_UPDATE << 1)
  #SHUTDOWN_WAIT_CRITICAL_BUFFERS = (#MYSQL_SHUTDOWN_KILLABLE_UPDATE << 1) + 1
  #KILL_QUERY = 254
  #KILL_CONNECTION = 255
EndEnumeration

Enumeration
  #CURSOR_TYPE_NO_CURSOR = 0
  #CURSOR_TYPE_READ_ONLY = 1
  #CURSOR_TYPE_FOR_UPDATE = 2
  #CURSOR_TYPE_SCROLLABLE = 4
EndEnumeration

Enumeration
  #MYSQL_OPTION_MULTI_STATEMENTS_ON
  #MYSQL_OPTION_MULTI_STATEMENTS_OFF
EndEnumeration

#MYSYS_ERRMSG_SIZE = 512

#HOSTNAME_LENGTH = 60
#SYSTEM_CHARSET_MBMAXLEN = 4
#NAME_CHAR_LEN = 64
#USERNAME_CHAR_LENGTH = 16
#NAME_LEN = (#NAME_CHAR_LEN * #SYSTEM_CHARSET_MBMAXLEN)
#USERNAME_LENGTH = (#USERNAME_CHAR_LENGTH * #SYSTEM_CHARSET_MBMAXLEN)

#SERVER_VERSION_LENGTH = 60
#SQLSTATE_LENGTH = 5

#MYSQL_ERRMSG_SIZE = 512

Structure mysql_field
  name.s                      ; Name of column
  org_name.s                  ; Original column name, if an alias
  table.s                     ; Table of column if column was a field
  org_table.s                 ; Org table name, if table was an alias
  db.s                        ; Database for table
  catalog.s                   ; Catalog for table
  def.s                       ; Default value (set by mysql_list_fields)
  length.l                    ; Width of column (create length)
  max_length.l                ; Max width for selected set
  name_length.i               ;
  org_name_length.i           ;
  table_length.i              ;
  org_table_length.i          ;
  db_length.i                 ;
  catalog_length.i            ;
  def_length.i                ;
  flags.i                     ; Div flags
  decimals.i                  ; Number of decimals in field
  charsetnr.i                 ; Character set
  type.i                      ; Type of field. See mysql_com.h for types
  *extension                  ;
EndStructure

Structure mysql_rows
  *NextRow                    ; list of rows
  *Data                       ;
  length.l                    ;
EndStructure

Structure mysql_data
  *Data                       ;
  *embedded_info              ;
  *alloc                      ;
  rows.q                      ;
  fields.i                    ;
  *extension                  ;
EndStructure

Structure mysql_options
  connect_timeout.i
  read_timeout.i
  write_timeout.i
  port.i
  protocol.i
  client_flag.l
  host.s
  user.s
  password.s
  unix_socket.s
  db.s
  *init_commands
  my_cnf_file.s
  my_cnf_group.s
  charset_dir.s
  charset_name.s
  ssl_key.s
  ssl_cert.s
  ssl_ca.s
  ssl_capath.s
  ssl_cipher.s
  shared_memory_base_name.s
  max_allowed_packet.l
  use_ssl.b
  compress.b
  named_pipe.b
  unused1.b
  unused2.b
  unused3.b
  unused4.b
  methods_to_use.i
  client_ip.s
  secure_auth.b
  report_data_truncation.b
  *local_infile_init
  *local_infile_read
  *local_infile_end
  *local_infile_error
  *local_infile_userdata
  *extension
EndStructure

Structure mysql
  *net                              ; Communication parameters
  *connector_fd.a                   ; ConnectorFd for SSL
  host.s
  user.s
  passwd.s
  unix_socket.s
  server_version.s
  host_info.s
  info.s
  db.s
  *charset
  *fields                           ;
  *field_alloc                      ;
  affected_rows.q                   ;
  insert_id.q                       ; id if insert on table with NEXTNR
  extra_info.q                      ; Not used
  thread_id.l                       ; Id for connection in server
  packet_length.l                   ;
  port.i                            ;
  client_flag.l
  server_capabilities.l             ;
  protocol_version.i                ;
  field_count.i                     ;
  server_status.i                   ;
  server_language.i                 ;
  warning_count.i                   ;
  options.mysql_options
  status.i
  free_me.b                         ; If free in mysql_close
  reconnect.b                       ; set to 1 if automatic reconnect
  scramble.a[#SCRAMBLE_LENGTH + 1]  ;
  unused1.b
  *unused2
  *unused3
  *unused4
  *unused5
  *stmts                            ; list of all statements
  *methods.st_mysql_methods
  *thd;
  *unbuffered_fetch_owner;
  *info_buffer;
  *extension; 
EndStructure

Structure character_set
  number.i      ; character set number
  state.i       ; character set state
  csname.s      ; collation name
  name.s        ; character set name
  comment.s     ; comment
  dir.s         ; character set directory
  mbminlen.i    ; min. length for multibyte strings
  mbmaxlen.i    ; max. length for multibyte strings
EndStructure

Structure mysql_res
  row_count.q
  *fields.mysql_field
  *Datas.mysql_data
  *data_cursor.mysql_rows
  *lengths.l                    ; column lengths of current row
  *handle.mysql                 ; for unbuffered reads
  *methods.mysql_methods        ;
  *row                          ; If unbuffered read
  *current_row                  ; buffer to current row
  *field_alloc                  ;
  field_count.i
  current_field.i               ;
  eof.b                         ; Used by mysql_fetch_row
  unbuffered_fetch_cancelled.b  ;
  *extension
EndStructure

Structure mysql_parameter
  *p_max_allowed_packet.l
  *p_net_buffer_length.l
  *extension
EndStructure

Structure mysql_bind
  *length.l         ; output length pointer
  *is_null.b        ; Pointer to null indicator
  *buffer           ; buffer to get/put data
  *error.b          ;
  *row_ptr.a        ; for the current data position
  *store_param_func
  *fetch_result
  *skip_result
  buffer_length.l   ;
  offset.l          ; offset position for char/binary fetch
  length_value.l    ; Used if length is 0
  param_number.i    ; For null count and error messages
  pack_length.i     ; Internal length for packed data
  buffer_type.i     ; buffer type
  error_value.b     ; used if error is 0
  is_unsigned.b     ; set if integer type is unsigned
  long_data_used.b  ; If used with mysql_send_long_data
  is_null_value.b   ; Used if is_null is 0
  *extension        ;
EndStructure

Structure mysql_stmt
  *mem_root                         ; root allocations
  *List                             ; list to keep track of all stmts
  *mysql.mysql                      ; connection handle
  *params.mysql_bind                ; input parameters
  *bind.mysql_bind                  ; output parameters
  *fields.mysql_field               ; result set metadata
  result.mysql_data                 ; cached result set
  *data_cursor.mysql_rows           ; current row in cached result
  *read_row_func
  affected_rows.q                   ;
  insert_id.q                       ; copy of mysql->insert_id
  stmt_id.l                         ; Id for prepared statement
  flags.l                           ; i.e. type of cursor To open
  prefetch_rows.l                   ; number of rows per one COM_FETCH
  server_status.i                   ;
  last_errno.i                      ; error code
  param_count.i                     ; input parameter count
  field_count.i                     ; number of columns in result set
  state.i                           ; statement state
  last_error.s[#MYSQL_ERRMSG_SIZE]  ; error message
  sqlstate.s[#SQLSTATE_LENGTH + 1]  ;
  send_types_to_server.b            ;
  bind_param_done.b                 ; input buffers were supplied
  bind_result_done.a                ; output buffers were supplied
  unbuffered_fetch_cancelled.b      ;
  update_max_length.b               ;
  *extension                        ;
EndStructure

Structure mysql_methods
  *read_query_result
  *advanced_command
  *read_rows
  *use_result
  *fetch_lengths
  *flush_use_result
  *list_fields
  *read_prepare_result
  *stmt_execute
  *read_binary_rows
  *unbuffered_fetch
  *free_embedded_thd
  *read_statistics
  *next_result
  *read_change_user_result
  *read_rows_from_cursor
EndStructure


Global mysql_lib

CompilerSelect #PB_Compiler_OS
  CompilerCase #PB_OS_Windows
    Global mysql_filename$ = "libmysql.dll"
    Macro OSPrototype
     Prototype
    EndMacro
  CompilerCase #PB_OS_Linux
    Global mysql_filename$ = "libmysql.so.1"
    Macro OSPrototype
     PrototypeC
    EndMacro
  CompilerCase #PB_OS_MacOS
    Global mysql_filename$ = "libmysql.so.1"
    Macro OSPrototype
     PrototypeC
    EndMacro
CompilerEndSelect




OSPrototype.q Proto_mysql_affected_rows(*mysql)
OSPrototype.b Proto_mysql_autocommit(*mysql, auto_mode.b)
OSPrototype.b Proto_mysql_change_user(*mysql, user.s, passwd.s, db.s)
OSPrototype.i Proto_mysql_character_set_name(*mysql)
OSPrototype.i Proto_mysql_close(*mysql)
OSPrototype.b Proto_mysql_commit(*mysql)
OSPrototype Proto_mysql_data_seek(*result, offset.q)
OSPrototype Proto_mysql_debug(debugstr.s)
OSPrototype.i Proto_mysql_dump_debug_info(*mysql)
OSPrototype.b Proto_mysql_embedded()
OSPrototype.b Proto_mysql_eof(*result)
OSPrototype.i Proto_mysql_errno(*mysql)
OSPrototype.s Proto_mysql_error(*mysql)
OSPrototype.l Proto_mysql_escape_string(tostr.s, fromstr.s, from_length.l)
OSPrototype.i Proto_mysql_fetch_field(*result)
OSPrototype.i Proto_mysql_fetch_field_direct(*result, fieldnr.i)
OSPrototype.i Proto_mysql_fetch_fields(*result)
OSPrototype.i Proto_mysql_fetch_lengths(*result)
OSPrototype.i Proto_mysql_fetch_row(*result)
OSPrototype.i Proto_mysql_field_count(*mysql)
OSPrototype.i Proto_mysql_field_seek(*result, offset.i)
OSPrototype.i Proto_mysql_field_tell(*result)
OSPrototype Proto_mysql_free_result(*result)
OSPrototype Proto_mysql_get_character_set_info(*mysql, *charset)
OSPrototype.s Proto_mysql_get_client_info()
OSPrototype.l Proto_mysql_get_client_version()
OSPrototype.s Proto_mysql_get_host_info(*mysql)
OSPrototype.i Proto_mysql_get_parameters()
OSPrototype.i Proto_mysql_get_proto_info(*mysql)
OSPrototype.s Proto_mysql_get_server_info(*mysql)
OSPrototype.l Proto_mysql_get_server_version(*mysql)
OSPrototype.s Proto_mysql_get_ssl_cipher(*mysql)
OSPrototype.l Proto_mysql_hex_string(tostr.s, fromstr.s, fromlen.l)
OSPrototype.s Proto_mysql_info(*mysql)
OSPrototype.i Proto_mysql_init(*mysql)
OSPrototype.q Proto_mysql_insert_id(*mysql)
OSPrototype.i Proto_mysql_kill(*mysql, pid.l)
OSPrototype.i Proto_mysql_list_dbs(*mysql, wild.s)
OSPrototype.i Proto_mysql_list_fields(*mysql, table.s, wild.s)
OSPrototype.i Proto_mysql_list_processes(*mysql)
OSPrototype.i Proto_mysql_list_tables(*mysql, wild.s)
OSPrototype.b Proto_mysql_more_results(*mysql)
OSPrototype.i Proto_mysql_next_result(*mysql)
OSPrototype.i Proto_mysql_num_fields(*result)
OSPrototype.q Proto_mysql_num_rows(*result)
OSPrototype.i Proto_mysql_options(*mysql, option.i, arg.s)
OSPrototype.i Proto_mysql_ping(*mysql)
OSPrototype.i Proto_mysql_query(*mysql, q.s)
OSPrototype.b Proto_mysql_read_query_result(*mysql)
OSPrototype.i Proto_mysql_real_connect(*mysql, host.s, user.s, passwd.s, db.s, port.i, unix_socket.s, clientflag.l)
OSPrototype.l Proto_mysql_real_escape_string(*mysql, tostr.s, fromstr.s, length.l)
OSPrototype.i Proto_mysql_real_query(*mysql, q.s, length.l)
OSPrototype.i Proto_mysql_refresh(*mysql, refresh_options.i)
OSPrototype.b Proto_mysql_rollback(*mysql)
OSPrototype.i Proto_mysql_row_seek(*result, *offset)
OSPrototype.i Proto_mysql_row_tell(*result)
OSPrototype.i Proto_mysql_select_db(*mysql, db.s)
OSPrototype.i Proto_mysql_send_query(*mysql, q.s, length.l)
OSPrototype Proto_mysql_server_end()
OSPrototype.i Proto_mysql_server_init(argc.i, *argv, *groups)
OSPrototype.i Proto_mysql_set_character_set(*mysql, csname.s)
OSPrototype Proto_mysql_set_local_infile_default(*mysql)
OSPrototype.i Proto_mysql_set_local_infile_handler(*mysql, *local_infile_init, *local_infile_read, *local_infile_end, *local_infile_error)
OSPrototype.i Proto_mysql_set_server_option(*mysql, option.i)
OSPrototype.i Proto_mysql_shutdown(*mysql, shutdown_level.i)
OSPrototype.s Proto_mysql_sqlstate(*mysql)
OSPrototype.b Proto_mysql_ssl_set(*mysql, key.s, cert.s, ca.s, capath.s, cipher.s)
OSPrototype.s Proto_mysql_stat(*mysql)
OSPrototype.q Proto_mysql_stmt_affected_rows(*stmt)
OSPrototype.b Proto_mysql_stmt_attr_get(*stmt, attr_type.i, *attr)
OSPrototype.b Proto_mysql_stmt_attr_set(*stmt, attr_type.i, *attr)
OSPrototype.b Proto_mysql_stmt_bind_param(*stmt, *bnd)
OSPrototype.b Proto_mysql_stmt_bind_result(*stmt, *bnd)
OSPrototype.b Proto_mysql_stmt_close(*stmt)
OSPrototype Proto_mysql_stmt_data_seek(*stmt, offset.q)
OSPrototype.i Proto_mysql_stmt_errno(*stmt)
OSPrototype.s Proto_mysql_stmt_error(*stmt)
OSPrototype.i Proto_mysql_stmt_execute(*stmt)
OSPrototype.i Proto_mysql_stmt_fetch(*stmt)
OSPrototype.i Proto_mysql_stmt_fetch_column(*stmt, *bind_arg, column.i, offset.l)
OSPrototype.i Proto_mysql_stmt_field_count(*stmt)
OSPrototype.b Proto_mysql_stmt_free_result(*stmt)
OSPrototype.i Proto_mysql_stmt_init(*stmt)
OSPrototype.q Proto_mysql_stmt_insert_id(*stmt)
OSPrototype.i Proto_mysql_stmt_next_result(*stmt)
OSPrototype.q Proto_mysql_stmt_num_rows(*stmt)
OSPrototype.l Proto_mysql_stmt_param_count(*stmt)
OSPrototype.i Proto_mysql_stmt_param_metadata(*stmt)
OSPrototype.i Proto_mysql_stmt_prepare(*stmt, query.s, length.l)
OSPrototype.b Proto_mysql_stmt_reset(*stmt)
OSPrototype.i Proto_mysql_stmt_result_metadata(*stmt)
OSPrototype.i Proto_mysql_stmt_row_seek(*stmt, *offset)
OSPrototype.i Proto_mysql_stmt_row_tell(*stmt)
OSPrototype.b Proto_mysql_stmt_send_long_data(*stmt, param_number.i, DataStr.s, length.l)
OSPrototype.i Proto_mysql_stmt_sqlstate(*stmt)
OSPrototype.i Proto_mysql_stmt_store_result(*stmt)
OSPrototype.i Proto_mysql_store_result(*mysql)
OSPrototype Proto_mysql_thread_end()
OSPrototype.l Proto_mysql_thread_id(*mysql)
OSPrototype.b Proto_mysql_thread_init()
OSPrototype.i Proto_mysql_thread_safe()
OSPrototype.i Proto_mysql_use_result(*mysql)
OSPrototype.i Proto_mysql_warning_count(*mysql)




Global mysql_affected_rows.Proto_mysql_affected_rows
Global mysql_autocommit.Proto_mysql_autocommit
Global mysql_change_user.Proto_mysql_change_user
Global mysql_character_set_name.Proto_mysql_character_set_name
Global mysql_close.Proto_mysql_close
Global mysql_commit.Proto_mysql_commit
Global mysql_data_seek.Proto_mysql_data_seek
Global mysql_debug.Proto_mysql_debug
Global mysql_dump_debug_info.Proto_mysql_dump_debug_info
Global mysql_embedded.Proto_mysql_embedded
Global mysql_eof.Proto_mysql_eof
Global mysql_errno.Proto_mysql_errno
Global mysql_error.Proto_mysql_error
Global mysql_escape_string.Proto_mysql_escape_string
Global mysql_fetch_field.Proto_mysql_fetch_field
Global mysql_fetch_field_direct.Proto_mysql_fetch_field_direct
Global mysql_fetch_fields.Proto_mysql_fetch_fields
Global mysql_fetch_lengths.Proto_mysql_fetch_lengths
Global mysql_fetch_row.Proto_mysql_fetch_row
Global mysql_field_count.Proto_mysql_field_count
Global mysql_field_seek.Proto_mysql_field_seek
Global mysql_field_tell.Proto_mysql_field_tell
Global mysql_free_result.Proto_mysql_free_result
Global mysql_get_character_set_info.Proto_mysql_get_character_set_info
Global mysql_get_client_info.Proto_mysql_get_client_info
Global mysql_get_client_version.Proto_mysql_get_client_version
Global mysql_get_host_info.Proto_mysql_get_host_info
Global mysql_get_parameters.Proto_mysql_get_parameters
Global mysql_get_proto_info.Proto_mysql_get_proto_info
Global mysql_get_server_info.Proto_mysql_get_server_info
Global mysql_get_server_version.Proto_mysql_get_server_version
Global mysql_get_ssl_cipher.Proto_mysql_get_ssl_cipher
Global mysql_hex_string.Proto_mysql_hex_string
Global mysql_info.Proto_mysql_info
Global mysql_init.Proto_mysql_init
Global mysql_insert_id.Proto_mysql_insert_id
Global mysql_kill.Proto_mysql_kill
Global mysql_list_dbs.Proto_mysql_list_dbs
Global mysql_list_fields.Proto_mysql_list_fields
Global mysql_list_processes.Proto_mysql_list_processes
Global mysql_list_tables.Proto_mysql_list_tables
Global mysql_more_results.Proto_mysql_more_results
Global mysql_next_result.Proto_mysql_next_result
Global mysql_num_fields.Proto_mysql_num_fields
Global mysql_num_rows.Proto_mysql_num_rows
Global mysql_options.Proto_mysql_options
Global mysql_ping.Proto_mysql_ping
Global mysql_query.Proto_mysql_query
Global mysql_read_query_result.Proto_mysql_read_query_result
Global mysql_real_connect.Proto_mysql_real_connect
Global mysql_real_escape_string.Proto_mysql_real_escape_string
Global mysql_real_query.Proto_mysql_real_query
Global mysql_refresh.Proto_mysql_refresh
Global mysql_rollback.Proto_mysql_rollback
Global mysql_row_seek.Proto_mysql_row_seek
Global mysql_row_tell.Proto_mysql_row_tell
Global mysql_select_db.Proto_mysql_select_db
Global mysql_send_query.Proto_mysql_send_query
Global mysql_server_end.Proto_mysql_server_end
Global mysql_server_init.Proto_mysql_server_init
Global mysql_set_character_set.Proto_mysql_set_character_set
Global mysql_set_local_infile_default.Proto_mysql_set_local_infile_default
Global mysql_set_local_infile_handler.Proto_mysql_set_local_infile_handler
Global mysql_set_server_option.Proto_mysql_set_server_option
Global mysql_shutdown.Proto_mysql_shutdown
Global mysql_sqlstate.Proto_mysql_sqlstate
Global mysql_ssl_set.Proto_mysql_ssl_set
Global mysql_stat.Proto_mysql_stat
Global mysql_stmt_affected_rows.Proto_mysql_stmt_affected_rows
Global mysql_stmt_attr_get.Proto_mysql_stmt_attr_get
Global mysql_stmt_attr_set.Proto_mysql_stmt_attr_set
Global mysql_stmt_bind_param.Proto_mysql_stmt_bind_param
Global mysql_stmt_bind_result.Proto_mysql_stmt_bind_result
Global mysql_stmt_close.Proto_mysql_stmt_close
Global mysql_stmt_data_seek.Proto_mysql_stmt_data_seek
Global mysql_stmt_errno.Proto_mysql_stmt_errno
Global mysql_stmt_error.Proto_mysql_stmt_error
Global mysql_stmt_execute.Proto_mysql_stmt_execute
Global mysql_stmt_fetch.Proto_mysql_stmt_fetch
Global mysql_stmt_fetch_column.Proto_mysql_stmt_fetch_column
Global mysql_stmt_field_count.Proto_mysql_stmt_field_count
Global mysql_stmt_free_result.Proto_mysql_stmt_free_result
Global mysql_stmt_init.Proto_mysql_stmt_init
Global mysql_stmt_insert_id.Proto_mysql_stmt_insert_id
Global mysql_stmt_next_result.Proto_mysql_stmt_next_result
Global mysql_stmt_num_rows.Proto_mysql_stmt_num_rows
Global mysql_stmt_param_count.Proto_mysql_stmt_param_count
Global mysql_stmt_param_metadata.Proto_mysql_stmt_param_metadata
Global mysql_stmt_prepare.Proto_mysql_stmt_prepare
Global mysql_stmt_reset.Proto_mysql_stmt_reset
Global mysql_stmt_result_metadata.Proto_mysql_stmt_result_metadata
Global mysql_stmt_row_seek.Proto_mysql_stmt_row_seek
Global mysql_stmt_row_tell.Proto_mysql_stmt_row_tell
Global mysql_stmt_send_long_data.Proto_mysql_stmt_send_long_data
Global mysql_stmt_sqlstate.Proto_mysql_stmt_sqlstate
Global mysql_stmt_store_result.Proto_mysql_stmt_store_result
Global mysql_store_result.Proto_mysql_store_result
Global mysql_thread_end.Proto_mysql_thread_end
Global mysql_thread_id.Proto_mysql_thread_id
Global mysql_thread_init.Proto_mysql_thread_init
Global mysql_thread_safe.Proto_mysql_thread_safe
Global mysql_use_result.Proto_mysql_use_result
Global mysql_warning_count.Proto_mysql_warning_count




Procedure.i mysql_lib_init()
  
  Protected Result
  
  Result = #False
 
  mysql_lib = OpenLibrary(#PB_Any, mysql_filename$)
  If mysql_lib
    mysql_affected_rows = GetFunction(mysql_lib, "mysql_affected_rows")
    mysql_autocommit = GetFunction(mysql_lib, "mysql_autocommit")
    mysql_change_user = GetFunction(mysql_lib, "mysql_change_user")
    mysql_character_set_name = GetFunction(mysql_lib, "mysql_character_set_name")
    mysql_close = GetFunction(mysql_lib, "mysql_close")
    mysql_commit = GetFunction(mysql_lib, "mysql_commit")
    mysql_data_seek = GetFunction(mysql_lib, "mysql_data_seek")
    mysql_debug = GetFunction(mysql_lib, "mysql_debug")
    mysql_dump_debug_info = GetFunction(mysql_lib, "mysql_dump_debug_info")
    mysql_embedded = GetFunction(mysql_lib, "mysql_embedded")
    mysql_eof = GetFunction(mysql_lib, "mysql_eof")
    mysql_errno = GetFunction(mysql_lib, "mysql_errno")
    mysql_error = GetFunction(mysql_lib, "mysql_error")
    mysql_escape_string = GetFunction(mysql_lib, "mysql_escape_string")
    mysql_fetch_field = GetFunction(mysql_lib, "mysql_fetch_field")
    mysql_fetch_field_direct = GetFunction(mysql_lib, "mysql_fetch_field_direct")
    mysql_fetch_fields = GetFunction(mysql_lib, "mysql_fetch_fields")
    mysql_fetch_lengths = GetFunction(mysql_lib, "mysql_fetch_lengths")
    mysql_fetch_row = GetFunction(mysql_lib, "mysql_fetch_row")
    mysql_field_count = GetFunction(mysql_lib, "mysql_field_count")
    mysql_field_seek = GetFunction(mysql_lib, "mysql_field_seek")
    mysql_field_tell = GetFunction(mysql_lib, "mysql_field_tell")
    mysql_free_result = GetFunction(mysql_lib, "mysql_free_result")
    mysql_get_character_set_info = GetFunction(mysql_lib, "mysql_get_character_set_info")
    mysql_get_client_info = GetFunction(mysql_lib, "mysql_get_client_info")
    mysql_get_client_version = GetFunction(mysql_lib, "mysql_get_client_version")
    mysql_get_host_info = GetFunction(mysql_lib, "mysql_get_host_info")
    mysql_get_parameters = GetFunction(mysql_lib, "mysql_get_parameters")
    mysql_get_proto_info = GetFunction(mysql_lib, "mysql_get_proto_info")
    mysql_get_server_info = GetFunction(mysql_lib, "mysql_get_server_info")
    mysql_get_server_version = GetFunction(mysql_lib, "mysql_get_server_version")
    mysql_get_ssl_cipher = GetFunction(mysql_lib, "mysql_get_ssl_cipher")
    mysql_hex_string = GetFunction(mysql_lib, "mysql_hex_string")
    mysql_info = GetFunction(mysql_lib, "mysql_info")
    mysql_init = GetFunction(mysql_lib, "mysql_init")
    mysql_insert_id = GetFunction(mysql_lib, "mysql_insert_id")
    mysql_kill = GetFunction(mysql_lib, "mysql_kill")
    mysql_list_dbs = GetFunction(mysql_lib, "mysql_list_dbs")
    mysql_list_fields = GetFunction(mysql_lib, "mysql_list_fields")
    mysql_list_processes = GetFunction(mysql_lib, "mysql_list_processes")
    mysql_list_tables = GetFunction(mysql_lib, "mysql_list_tables")
    mysql_more_results = GetFunction(mysql_lib, "mysql_more_results")
    mysql_next_result = GetFunction(mysql_lib, "mysql_next_result")
    mysql_num_fields = GetFunction(mysql_lib, "mysql_num_fields")
    mysql_num_rows = GetFunction(mysql_lib, "mysql_num_rows")
    mysql_options = GetFunction(mysql_lib, "mysql_options")
    mysql_ping = GetFunction(mysql_lib, "mysql_ping")
    mysql_query = GetFunction(mysql_lib, "mysql_query")
    mysql_read_query_result = GetFunction(mysql_lib, "mysql_read_query_result")
    mysql_real_connect = GetFunction(mysql_lib, "mysql_real_connect")
    mysql_real_escape_string = GetFunction(mysql_lib, "mysql_real_escape_string")
    mysql_real_query = GetFunction(mysql_lib, "mysql_real_query")
    mysql_refresh = GetFunction(mysql_lib, "mysql_refresh")
    mysql_rollback = GetFunction(mysql_lib, "mysql_rollback")
    mysql_row_seek = GetFunction(mysql_lib, "mysql_row_seek")
    mysql_row_tell = GetFunction(mysql_lib, "mysql_row_tell")
    mysql_select_db = GetFunction(mysql_lib, "mysql_select_db")
    mysql_send_query = GetFunction(mysql_lib, "mysql_send_query")
    mysql_server_end = GetFunction(mysql_lib, "mysql_server_end")
    mysql_server_init = GetFunction(mysql_lib, "mysql_server_init")
    mysql_set_character_set = GetFunction(mysql_lib, "mysql_set_character_set")
    mysql_set_local_infile_default = GetFunction(mysql_lib, "mysql_set_local_infile_default")
    mysql_set_local_infile_handler = GetFunction(mysql_lib, "mysql_set_local_infile_handler")
    mysql_set_server_option = GetFunction(mysql_lib, "mysql_set_server_option")
    mysql_shutdown = GetFunction(mysql_lib, "mysql_shutdown")
    mysql_sqlstate = GetFunction(mysql_lib, "mysql_sqlstate")
    mysql_ssl_set = GetFunction(mysql_lib, "mysql_ssl_set")
    mysql_stat = GetFunction(mysql_lib, "mysql_stat")
    mysql_stmt_affected_rows = GetFunction(mysql_lib, "mysql_stmt_affected_rows")
    mysql_stmt_attr_get = GetFunction(mysql_lib, "mysql_stmt_attr_get")
    mysql_stmt_attr_set = GetFunction(mysql_lib, "mysql_stmt_attr_set")
    mysql_stmt_bind_param = GetFunction(mysql_lib, "mysql_stmt_bind_param")
    mysql_stmt_bind_result = GetFunction(mysql_lib, "mysql_stmt_bind_result")
    mysql_stmt_close = GetFunction(mysql_lib, "mysql_stmt_close")
    mysql_stmt_data_seek = GetFunction(mysql_lib, "mysql_stmt_data_seek")
    mysql_stmt_errno = GetFunction(mysql_lib, "mysql_stmt_errno")
    mysql_stmt_error = GetFunction(mysql_lib, "mysql_stmt_error")
    mysql_stmt_execute = GetFunction(mysql_lib, "mysql_stmt_execute")
    mysql_stmt_fetch = GetFunction(mysql_lib, "mysql_stmt_fetch")
    mysql_stmt_fetch_column = GetFunction(mysql_lib, "mysql_stmt_fetch_column")
    mysql_stmt_field_count = GetFunction(mysql_lib, "mysql_stmt_field_count")
    mysql_stmt_free_result = GetFunction(mysql_lib, "mysql_stmt_free_result")
    mysql_stmt_init = GetFunction(mysql_lib, "mysql_stmt_init")
    mysql_stmt_insert_id = GetFunction(mysql_lib, "mysql_stmt_insert_id")
    mysql_stmt_next_result = GetFunction(mysql_lib, "mysql_stmt_next_result")
    mysql_stmt_num_rows = GetFunction(mysql_lib, "mysql_stmt_num_rows")
    mysql_stmt_param_count = GetFunction(mysql_lib, "mysql_stmt_param_count")
    mysql_stmt_param_metadata = GetFunction(mysql_lib, "mysql_stmt_param_metadata")
    mysql_stmt_prepare = GetFunction(mysql_lib, "mysql_stmt_prepare")
    mysql_stmt_reset = GetFunction(mysql_lib, "mysql_stmt_reset")
    mysql_stmt_result_metadata = GetFunction(mysql_lib, "mysql_stmt_result_metadata")
    mysql_stmt_row_seek = GetFunction(mysql_lib, "mysql_stmt_row_seek")
    mysql_stmt_row_tell = GetFunction(mysql_lib, "mysql_stmt_row_tell")
    mysql_stmt_send_long_data = GetFunction(mysql_lib, "mysql_stmt_send_long_data")
    mysql_stmt_sqlstate = GetFunction(mysql_lib, "mysql_stmt_sqlstate")
    mysql_stmt_store_result = GetFunction(mysql_lib, "mysql_stmt_store_result")
    mysql_store_result = GetFunction(mysql_lib, "mysql_store_result")
    mysql_thread_end = GetFunction(mysql_lib, "mysql_thread_end")
    mysql_thread_id = GetFunction(mysql_lib, "mysql_thread_id")
    mysql_thread_init = GetFunction(mysql_lib, "mysql_thread_init")
    mysql_thread_safe = GetFunction(mysql_lib, "mysql_thread_safe")
    mysql_use_result = GetFunction(mysql_lib, "mysql_use_result")
    mysql_warning_count = GetFunction(mysql_lib, "mysql_warning_count")
    
    Result = #True
  EndIf
 
  ProcedureReturn Result
 
EndProcedure


Procedure mysql_lib_free()
  If IsLibrary(mysql_lib) : CloseLibrary(mysql_lib) : EndIf
  mysql_lib = 0
EndProcedure

Than you can do things like:

Code: Select all

#Host$ = "192.168.18.247"
#User$ = "bkk"
#Passwd$ = "bkk"
#DB$ = "Test"
#Table$ = "TestTable"

IncludeFile "mysql.pbi"

If mysql_lib_init()
  *mysql = mysql_init(#Null)
  If *mysql
    If mysql_real_connect(*mysql, #Host$, #User$, #Passwd$, #DB$, 0, #NULL$, #CLIENT_COMPRESS) <> #Null
      For i = 1 To 100
        Insert$ = "INSERT INTO "+ #DB$ + "." + #Table$ + " VALUES('" + Str(i) + "','" + FormatDate("%yyyy-%mm-%dd %hh:%ii:%ss", Date()) + "')"
        mysql_real_query(*mysql, Insert$, Len(Insert$))
      Next i
    EndIf
    
    mysql_close(*mysql)
  EndIf
  mysql_lib_free()
EndIf

Bernd

Post by **Fred** » Fri Jan 11, 2013 7:56 pm

May be this ? http://www.purebasic.fr/english/viewtop ... ilit=mysql

infratec · Post by **infratec** » Fri Jan 11, 2013 8:03 pm

An example for a SELECT:

Code: Select all

Procedure.i GetDeviceInfo(*MySQL_Handle, MAC$, *Ceza.CezaStructure)
  
  Protected Result.i, SQL$, *MySQLResult, *MySQL_Row, *MySQL_Len, Offset.i, length.i, fieldptr.i
  
  Result = #False
  
  SQL$ = "SELECT `id`, `delay`, `parameter` FROM `device` WHERE `mac` LIKE '" + MAC$ + "'"
  Logging(SQL$)
  If MySQL_Real_Query(*MySQL_Handle, SQL$) = 0
    *MySQL_Result = MySQL_Use_Result(*MySQL_Handle)
    If *MySQL_Result
      *MySQL_Row = MySQL_Fetch_Row(*MySQL_Result)
      If *MySQL_Row <> 0
        *MySQL_Len = MySQL_Fetch_Lengths(*MySQL_Result)
        
        length = PeekL(*MySQL_Len)           
        fieldptr = PeekL(*MySQL_Row)
        If fieldptr > 0
          *Ceza\ID = Val(PeekS(fieldptr, length))
        EndIf
        
        ; + 8 wegen 64bit int. Bei 32bit + 4
        CompilerIf #PB_Compiler_Processor = #PB_Processor_x64
          Offset = 8
        CompilerElse
          Offset = 4
        CompilerEndIf
        
        length = PeekL(*MySQL_Len + Offset)
        fieldptr = PeekL(*MySQL_Row + Offset)
        If fieldptr > 0
          *Ceza\Delay = Val(PeekS(fieldptr, length))
        EndIf
        
        length = PeekL(*MySQL_Len + Offset * 2)
        If length > 0
          fieldptr = PeekL(*MySQL_Row + Offset * 2)
          If fieldptr > 0
            *Ceza\Parameter = PeekS(fieldptr, length)
          EndIf
        Else
          *Ceza\Parameter = ""
        EndIf
        
      EndIf
      MySQL_Free_Result(*MySQL_Result)
    EndIf
  EndIf
  
  ProcedureReturn Result
  
EndProcedure

t57042 · Post by **t57042** » Sat Jan 12, 2013 2:01 pm

Hi Infratec,

For the INSERT code I created the database test and the table testtable: CREATE TABLE `testtable` (
`Column 1` INT(10) NULL DEFAULT NULL,
`Column 2` DATETIME NULL DEFAULT NULL,
`Column 3` DATE NULL DEFAULT NULL
)

The program compiles and runs without error, but nothing is inserted in the table.
I adapted the 4 first lines:
#Host$ = "127.0.0.1"
#User$ = "root"
#Passwd$ = "root"
#DB$ = "test"
#Table$ = "testtable"
-------------------------------------
To keep thing simple ,first of all I would like to see the result of a SELECT on a table which exists on my system (localhost).
The database = namebook
table = names
3 columns: code smallint length 5
name varchar length 40
email varchar length 40

PS: I am using MARIADB 5.5.28a

Thanks
Richard

infratec · Post by **infratec** » Sat Jan 12, 2013 2:55 pm

Hi Richard,

this code works with mysql.
I don't know if MariaDB is accessible with the mysql.dll

Code: Select all

#Host$ = "127.0.0.1"
#User$ = "root"
#Passwd$ = "root"
#DB$ = "test"

Define SQL$, *MySQL_Handle, *MySQL_Result, *MySQL_Row, *MySQL_Len, Offset.i, length.i, fieldptr.i, Rows.i
Define Code.i, Name$, email$

IncludeFile "mysql.pbi"

If mysql_lib_init()
  *MySQL_Handle = mysql_init(#Null)
  If *MySQL_Handle
    If mysql_real_connect(*MySQL_Handle, #Host$, #User$, #Passwd$, #DB$, 0, #NULL$, #CLIENT_COMPRESS) <> #Null
    
    
      SQL$ = "SELECT `code`, `name`, `email` FROM `testtable`"
      
      If MySQL_Real_Query(*MySQL_Handle, SQL$, Len(SQL$)) = 0
        *MySQL_Result = MySQL_store_Result(*MySQL_Handle)
        If *MySQL_Result
          
          Rows = mysql_num_rows(*MySQL_Result)
          
          Offset = SizeOf(Integer)          
          
          While Rows
          
            *MySQL_Row = MySQL_Fetch_Row(*MySQL_Result)
            If *MySQL_Row <> 0
              *MySQL_Len = MySQL_Fetch_Lengths(*MySQL_Result)
              
              length = PeekL(*MySQL_Len + Offset * 0)
              fieldptr = PeekL(*MySQL_Row + Offset * 0)
              If fieldptr > 0
                Code = Val(PeekS(fieldptr, length))
              EndIf
              
              length = PeekL(*MySQL_Len + Offset * 1)
              fieldptr = PeekL(*MySQL_Row + Offset)
              If fieldptr > 0
                Name$ = PeekS(fieldptr, length)
              EndIf
              
              length = PeekL(*MySQL_Len + Offset * 2)
              fieldptr = PeekL(*MySQL_Row + Offset * 2)
              If fieldptr > 0
                email$ = PeekS(fieldptr, length)
              EndIf
            
            EndIf
            
            Debug Str(Code) + " - " + Name$ + " - " + email$
            
            Rows - 1  
          Wend
          
          MySQL_Free_Result(*MySQL_Result)
        EndIf
      EndIf
    EndIf
  EndIf
EndIf

The internal PB commands are much easier to use.
I prefer ODBC or postgres.

Or you can write higher level functions arround.

Bernd

t57042 · Post by **t57042** » Sat Jan 12, 2013 3:21 pm

After adaptation of DBname and tablename works fine with MariaDB.

Can you describe the procedure (files needed...) and give an example wirh the same table and ODBC?

Thanks
Richard

infratec · Post by **infratec** » Sat Jan 12, 2013 3:36 pm

Hi Richard,

t57042 wrote:After adaptation of DBname and tablename works fine with MariaDB.

Good.

t57042 wrote:Can you describe the procedure (files needed...) and give an example wirh the same table and ODBC?

If it is running, you have all files which are needed.

If you mean as executable:
Your exe and the libmysql.dll file. That's all.

This should work (not tested)
You need an ODBC connection named Test which is working on th database test including user and password.
(use the connection test in the ODBC dialog)

Code: Select all

UseODBCDatabase()

Define DB.i, SQL$
Define Code.i, Name$, email$


DB = OpenDatabase(#PB_Any, "Test", "", "", #PB_Database_ODBC)
If DB
  
  SQL$ + "SELECT `code`, `name`, `email` FROM `testtable`"
  If DatabaseQuery(DB, SQL$) 
    
    While NextDatabaseRow(DB)
      
      Code = GetDatabaseLong(DB, 0)
      Name$ = GetDatabaseString(DB, 1)
      email$ = GetDatabaseString(DB, 2)
      
      Debug Str(Code) + " - " + Name$ + " - " + email$
    Wend
    FinishDatabaseQuery(DB)
  EndIf
  
  CloseDatabase(DB)
Else
  MessageRequester("Error", "Was not able to open the ODBC 'Test'")
EndIf

Bernd

Btw. If you use Win 7 64bit, you need the 32 bit ODBC dialog for the mysql ODBC driver

Call 'odbcad32.exe' by hand

t57042 · Post by **t57042** » Sat Jan 12, 2013 4:21 pm

I get 'not able to open ODBC test'
What do you mean by:

You need an ODBC connection named Test which is working on the database test including user and password.
(use the connection test in the ODBC dialog)

I adapted the code as follows:

Code: Select all

UseODBCDatabase()

    Define DB.i, SQL$
    Define Code.i, Name$, email$


    DB = OpenDatabase(#PB_Any, "namebook", "root", "root", #PB_Database_ODBC)
    If DB
     
      SQL$ + "SELECT `code`, `name`, `email` FROM `names`"
      If DatabaseQuery(DB, SQL$)
       
        While NextDatabaseRow(DB)
         
          Code = GetDatabaseLong(DB, 0)
          Name$ = GetDatabaseString(DB, 1)
          email$ = GetDatabaseString(DB, 2)
         
          Debug Str(Code) + " - " + Name$ + " - " + email$
        Wend
        FinishDatabaseQuery(DB)
      EndIf
     
      CloseDatabase(DB)
    Else
      MessageRequester("Error", "Was not able to open the ODBC 'Test'")
    EndIf

Richard

infratec · Post by **infratec** » Sat Jan 12, 2013 5:06 pm

Hi Richard,

you have to setup and configure your ODBC connection.
Database User and password are stored in there.
So you don't set them in OpenDatabase()
Ther is also a test button to test the connection to your database

See here:
http://www.thewebhostinghero.com/tutori ... howto.html

The name you have to use in the OpenDatabase() call is the 'Data Source Name'
and not the name of your database.

Bernd

t57042 · Post by **t57042** » Sun Jan 13, 2013 6:30 pm

Hi Bernd,

I played a bit with your code and decided to stick to the DLL.

Or you can write higher level functions arround.

Do you know if someone has already done this?
Where can I find info on the function in ther DLL?

Richard

infratec · Post by **infratec** » Sun Jan 13, 2013 7:36 pm

Hi Richard,

I don't know if someone has already written some higher level procedures.
Here you can find the documentation about the mysql API:

http://dev.mysql.com/doc/refman/5.6/en/c.html

Bernd

VoSs2o0o · Post by **VoSs2o0o** » Tue Feb 05, 2013 6:15 pm

Have a look on my Wrapper with Purebasic - like MYSQL-Handling:

http://www.purebasic.fr/english/viewtop ... t=libmysql

PureBasic Forums - English

mysql

mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql

Re: mysql