MYSQL with unicode = problem

Just starting out? Need help? Post your questions and find answers here.
Post Reply
User avatar
flaith
Enthusiast
Enthusiast
Posts: 704
Joined: Mon Apr 25, 2005 9:28 pm
Location: $300:20 58 FC 60 - Rennes
Contact:
Contact flaith

MYSQL with unicode = problem

Post by flaith »

Hi people,

i got a prog to do that must be in unicode. I'm using Mysql for the database and the wrapper made by Flype (thanks to him) and his sample (here is the complete wrapper) :

Code: Select all

;- 
;- File:     libmysql.pbi
;- Version:  2.0
;- Author:   flype - flype44(at)gmail.com
;- Modified: flaith (24.07.2009)
;-
;- About:    MySQL Interface for Purebasic 4.3.
;- 
;- updated:  p-utf8 format (for unicode or ascii)

EnableExplicit

;-
;- Private - MySQL API
;-

#MYSQL_NOERROR = 0

Enumeration 0 ; client
  #CLIENT_COMPRESS
  #CLIENT_FOUND_ROWS
  #CLIENT_IGNORE_SPACE
  #CLIENT_INTERACTIVE
  #CLIENT_LOCAL_FILES
  #CLIENT_MULTI_STATEMENTS
  #CLIENT_MULTI_RESULTS
  #CLIENT_NO_SCHEMA
  #CLIENT_ODBC
  #CLIENT_SSL
EndEnumeration
Enumeration 0 ; type
  #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
  #MYSQL_TYPE_SET
  #MYSQL_TYPE_TINY_BLOB
  #MYSQL_TYPE_MEDIUM_BLOB
  #MYSQL_TYPE_LONG_BLOB
  #MYSQL_TYPE_BLOB
  #MYSQL_TYPE_VAR_STRING
  #MYSQL_TYPE_STRING
  #MYSQL_TYPE_GEOMETRY
EndEnumeration

Structure MYSQL_FIELD
  name.s
  org_name.s
  table.s
  org_table.s
  db.s
  catalog.s
  def.s
  length.l
  max_length.l
  name_length.l
  org_name_length.l
  table_length.l
  org_table_length.l
  db_length.l
  catalog_length.l
  def_length.l
  flags.l
  decimals.l
  charset_nr.l
  Type.l
EndStructure
Structure MYSQL_FIELD_ARRAY
  field.MYSQL_FIELD[0]
EndStructure

Structure MYSQL_LENGTH
  *l.l[0]
EndStructure
Structure MYSQL_ROW
  *field.s[0]
EndStructure

Import "libmysql.lib" 
  mysql_affected_rows(*mysql) 
  mysql_autocommit(*mysql, *mode.Byte)
  mysql_change_user(*mysql, user.p-utf8, passwd.p-utf8, db.p-utf8) 
  mysql_character_set_name(*mysql)
  mysql_close(*mysql)
  mysql_commit(*mysql)
  mysql_data_seek(*result, offset.d)
  mysql_eof(*result)
  mysql_errno(*mysql)
  mysql_error(*mysql)
  mysql_escape_string(*strTo, strFrom.p-utf8, length.l)
  mysql_fetch_field(*result)
  mysql_fetch_field_direct(*result, fieldnr.l)
  mysql_fetch_fields(*result)
  mysql_fetch_lengths(*result)
  mysql_fetch_row(*result)
  mysql_field_count(*mysql)
  mysql_field_seek(*result, offset.l)
  mysql_field_tell(*result)
  mysql_free_result(*result)
  mysql_get_character_set_info(*mysql, *charsetinfo)
  mysql_get_client_info()
  mysql_get_client_version()
  mysql_get_host_info(*mysql)
  mysql_get_parameters()
  mysql_get_proto_info(*mysql)
  mysql_get_server_info(*mysql)
  mysql_get_server_version(*mysql)
  mysql_hex_string(strTo.p-utf8, strFrom.p-utf8, length.l)
  mysql_info(*mysql)
  mysql_init(*mysql)
  mysql_insert_id(*mysql)
  mysql_kill(*mysql, pid.l)
  mysql_list_dbs(*mysql, wild.p-utf8)
  mysql_list_fields(*mysql, table.p-utf8, wild.p-utf8)
  mysql_list_processes(*mysql)
  mysql_list_tables(*mysql, wild.p-utf8)
  mysql_master_query(*mysql, query.p-utf8, length.l)
  mysql_more_results(*mysql)
  mysql_next_result(*mysql)
  mysql_num_fields(*result)
  mysql_num_rows(*result)
  mysql_options(*mysql, option.l, arg.p-utf8)
  mysql_ping(*mysql)
  mysql_query(*mysql, query.p-utf8)
  mysql_read_query_result(*mysql)
  mysql_real_connect(*mysql, host.p-utf8, user.p-utf8, passwd.p-utf8, db.p-utf8, port.l, unix_socket.p-utf8, flags.l)
  mysql_real_escape_string(*mysql, *strTo, strFrom.p-utf8, length.l)
  mysql_real_query(*mysql, query.p-utf8, length.l)
  mysql_refresh(*mysql, Options.l)
  mysql_reload(*mysql)
  mysql_rollback(*mysql)
  mysql_row_seek(*mysql, offset.l)
  mysql_row_tell(*result)
  mysql_select_db(*mysql, db.p-utf8)
  mysql_store_result(*mysql)
EndImport

;-
;- Private - MySQL Interface
;-

Structure IMYSQL_VTABLE
  
  *host
  *proto
  *client_long
  *client_string
  *server_long
  *server_string
  
  *affected_rows
  *error
  *escape_string
  
  *field
  *field_length
  *field_length_max
  *field_name
  *field_reset
  *field_type
  *field_type_string
  
  *free
  
  *GetDate 
  *GetDateTime 
  *GetDouble 
  *GetFloat 
  *GetLong 
  *GetQuad 
  *GetString 
  *GetTime 
  
  *get_date
  *get_date_string
  *get_datetime
  *get_datetime_string
  *get_double
  *get_float
  *get_long
  *get_quad
  *get_string
  *get_time
  *get_time_string
  
  *info
  *insert_id
  
  *num_fields
  *num_rows
  
  *ping
  
  *query
  *query_from_file
  *query_xml
  *query_xml_from_file
  
  *row
  *selectdb
  
  *update
  *update_from_file

  ;added 24/07/09
  *autocommit
  *commit
EndStructure
Structure SMYSQL
  *vt.IMYSQL_VTABLE
  *link
  *result
  *row.MYSQL_ROW
  *field.MYSQL_FIELD
EndStructure
Interface IMYSQL
  
  host.s()
  proto.l()
  client_long.l()
  client_string.s()
  server_long.l()
  server_string.s()
  
  affected_rows.l()
  error.s()
  escape_string.s(string.s)
  field.l()
  field_length.l()
  field_length_max.l()
  field_name.s()
  field_reset.l()
  field_type.l()
  field_type_string.s()
  free.l()
  GetDate.l(name.s)
  GetDateTime.l(name.s)
  GetDouble.d(name.s)
  GetFloat.f(name.s)
  GetLong.l(name.s)
  GetQuad.q(name.s)
  GetString.s(name.s)
  GetTime.l(name.s)
  get_date.l(column.l = 0)
  get_date_string.s(column.l = 0)
  get_datetime.l(column.l = 0)
  get_datetime_string.s(column.l = 0)
  get_double.d(column.l = 0)
  get_float.f(column.l = 0)
  get_long.l(column.l = 0)
  get_quad.q(column.l = 0)
  get_string.s(column.l = 0)
  get_time.l(column.l = 0)
  get_time_string.s(column.l = 0)
  info.s()
  insert_id.l()
  num_fields.l()
  num_rows.l()
  ping.l()
  query.l(query.s)
  query_from_file.l(fileIn.s)
  query_xml(query.s, fileOut.s, table.l = #False, db.l = #False)
  query_xml_from_file(fileIn.s, fileOut.s, table.l = #False, db.l = #False)
  row.l()
  selectdb.l(database.s)
  update.l(query.s)
  update_from_file.l(fileIn.s)
  ;added 24/07/09
  autocommit(mode.l = #True)
  commit.l()
EndInterface

Procedure.s PeekS_Utf8(*buffer)       ;also checks if buffer is valid, returns an empty string if *buffer is 0
  If *buffer
    ProcedureReturn PeekS(*buffer, -1, #PB_UTF8)
  Else
    ProcedureReturn ""
  EndIf
EndProcedure 

Procedure.s mysql_name_string(*field.MYSQL_FIELD, db.l, table.l)
  
  Protected result.s
  
  If db
    result + *field\db + "."
  EndIf
  
  If table
    result + *field\table + "."
  EndIf
  
  ProcedureReturn result + *field\name
  
EndProcedure
Procedure.s mysql_type_string(Type.l)
  
  Select Type
    Case #MYSQL_TYPE_DECIMAL:     ProcedureReturn "decimal"
    Case #MYSQL_TYPE_TINY:        ProcedureReturn "tiny"
    Case #MYSQL_TYPE_SHORT:       ProcedureReturn "short"
    Case #MYSQL_TYPE_LONG:        ProcedureReturn "long"
    Case #MYSQL_TYPE_FLOAT:       ProcedureReturn "float"
    Case #MYSQL_TYPE_DOUBLE:      ProcedureReturn "double"
    Case #MYSQL_TYPE_NULL:        ProcedureReturn "null"
    Case #MYSQL_TYPE_TIMESTAMP:   ProcedureReturn "timestamp"
    Case #MYSQL_TYPE_LONGLONG:    ProcedureReturn "longlong"
    Case #MYSQL_TYPE_INT24:       ProcedureReturn "int24"
    Case #MYSQL_TYPE_DATE:        ProcedureReturn "date"
    Case #MYSQL_TYPE_TIME:        ProcedureReturn "time"
    Case #MYSQL_TYPE_DATETIME:    ProcedureReturn "datetime"
    Case #MYSQL_TYPE_YEAR:        ProcedureReturn "year"
    Case #MYSQL_TYPE_NEWDATE:     ProcedureReturn "newdate"
    Case #MYSQL_TYPE_VARCHAR:     ProcedureReturn "varchar"
    Case #MYSQL_TYPE_BIT:         ProcedureReturn "bit"
    Case #MYSQL_TYPE_NEWDECIMAL:  ProcedureReturn "newdecimal"
    Case #MYSQL_TYPE_ENUM:        ProcedureReturn "enum"
    Case #MYSQL_TYPE_SET:         ProcedureReturn "set"
    Case #MYSQL_TYPE_TINY_BLOB:   ProcedureReturn "tinyblob"
    Case #MYSQL_TYPE_MEDIUM_BLOB: ProcedureReturn "mediumblob"
    Case #MYSQL_TYPE_LONG_BLOB:   ProcedureReturn "longblob"
    Case #MYSQL_TYPE_BLOB:        ProcedureReturn "blob"
    Case #MYSQL_TYPE_VAR_STRING:  ProcedureReturn "varstring"
    Case #MYSQL_TYPE_STRING:      ProcedureReturn "string"
    Case #MYSQL_TYPE_GEOMETRY:    ProcedureReturn "geometry"
  EndSelect
  
  ProcedureReturn "unknown"
  
EndProcedure

Procedure.l db_affected_rows(*this.SMYSQL)
  
  If *this And *this\link 
    mysql_affected_rows(*this\link) 
  EndIf
  
EndProcedure
Procedure.s db_error(*this.SMYSQL)
  
  If *this And *this\link
    
    If mysql_error(*this\link)
      ;ProcedureReturn PeekS(mysql_error(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_error(*this\link))
    EndIf
    
  EndIf
  
EndProcedure
Procedure.s db_escape_string(*this.SMYSQL, string.s)
  
  Protected result.s
  
  If *this And *this\link 
    
    result = Space((Len(string) + 2) * 2)
    result = ReplaceString(Left(result, mysql_real_escape_string(*this\link, @result, string, Len(string))), #TAB$, "\t")
    
    ProcedureReturn result
    
  EndIf
  
EndProcedure

Procedure.l db_field(*this.SMYSQL)
  
  If *this And *this\result
    
    *this\field = mysql_fetch_field(*this\result)
    
    ProcedureReturn *this\field
    
  EndIf
  
EndProcedure
Procedure.l db_field_length(*this.SMYSQL)
  
  If *this And *this\field
    ProcedureReturn *this\field\length
  EndIf
  
EndProcedure
Procedure.l db_field_length_max(*this.SMYSQL)
  
  If *this And *this\field
    ProcedureReturn *this\field\max_length
  EndIf
  
EndProcedure
Procedure.s db_field_name(*this.SMYSQL)
  
  If *this And *this\field
    ProcedureReturn *this\field\name
  EndIf
  
EndProcedure
Procedure.l db_field_reset(*this.SMYSQL)
  
  If *this And *this\result
    ProcedureReturn mysql_field_seek(*this\result, 0)
  EndIf
  
EndProcedure
Procedure.l db_field_type(*this.SMYSQL)
  
  If *this And *this\field
    ProcedureReturn *this\field\type
  EndIf
  
EndProcedure
Procedure.s db_field_type_string(*this.SMYSQL)
  
  If *this And *this\field
    ProcedureReturn mysql_type_string(*this\field\type)
  EndIf
  
EndProcedure

Procedure.l db_free(*mysql.SMYSQL)
  
  If *mysql
    
    If *mysql\result
      mysql_free_result(*mysql\result)
    EndIf
    
    If *mysql\link
      mysql_close(*mysql\link) 
    EndIf
    
  EndIf
  
EndProcedure

Procedure.l db_GetDate(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0) 
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ProcedureReturn ParseDate("%yyyy-%mm-%dd", *this\row\field[i])
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure
Procedure.l db_GetDateTime(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0) 
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ProcedureReturn ParseDate("%yyyy-%mm-%dd %hh:%ii:%ss", *this\row\field[i])
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure
Procedure.d db_GetDouble(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0) 
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ProcedureReturn ValD(*this\row\field[i])
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure
Procedure.f db_GetFloat(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0) 
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ProcedureReturn ValF(*this\row\field[i])
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure
Procedure.l db_GetLong(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0) 
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ProcedureReturn Val(*this\row\field[i])
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure
Procedure.q db_GetQuad(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0) 
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ;ProcedureReturn ValQ(*this\row\field[i])
          ProcedureReturn Val(*this\row\field[i])
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure
Procedure.s db_GetString(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0)
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ProcedureReturn *this\row\field[i]
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure
Procedure.l db_GetTime(*this.SMYSQL, name.s)
  
  Protected *field.MYSQL_FIELD, nFields.l, i.l
  
  If *this\result And *this\row
    
    nFields = mysql_num_fields(*this\result)
    
    If nFields
      
      mysql_field_seek(*this\result, 0) 
      
      For i = 0 To nFields - 1
        
        *field = mysql_fetch_field(*this\result)
        
        If *field\name = name
          ProcedureReturn ParseDate("%hh:%ii:%ss", *this\row\field[i])
        EndIf
        
      Next
      
    EndIf
    
  EndIf
  
EndProcedure

Procedure.l db_get_date(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn ParseDate("%yyyy-%mm-%dd", *this\row\field[column])
  EndIf
  
EndProcedure
Procedure.s db_get_date_string(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    Protected date.l = ParseDate("%yyyy-%mm-%dd", *this\row\field[column])
    If date <> -1
      ProcedureReturn FormatDate("%dd-%mm-%yyyy", date)
    EndIf
  EndIf
  
EndProcedure
Procedure.l db_get_datetime(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn ParseDate("%yyyy-%mm-%dd %hh:%ii:%ss", *this\row\field[column])
  EndIf
  
EndProcedure
Procedure.s db_get_datetime_string(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    Protected datetime.l = ParseDate("%yyyy-%mm-%dd %hh:%ii:%ss", *this\row\field[column])
    If datetime <> -1
      ProcedureReturn FormatDate("%dd-%mm-%yyyy %hh:%ii:%ss", datetime)
    EndIf
  EndIf
  
EndProcedure
Procedure.d db_get_double(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn ValD(*this\row\field[column])
  EndIf
  
EndProcedure
Procedure.f db_get_float(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn ValF(*this\row\field[column])
  EndIf
  
EndProcedure
Procedure.l db_get_long(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn Val(*this\row\field[column])
  EndIf
  
EndProcedure
Procedure.q db_get_quad(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ;ProcedureReturn ValQ(*this\row\field[column])
    ProcedureReturn Val(*this\row\field[column])
  EndIf
  
EndProcedure
Procedure.s db_get_string(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn *this\row\field[column]
  EndIf
  
EndProcedure
Procedure.l db_get_time(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn ParseDate("%hh:%ii:%ss", *this\row\field[column])
  EndIf
  
EndProcedure
Procedure.s db_get_time_string(*this.SMYSQL, column.l = 0)
  
  If *this And *this\row 
    ProcedureReturn *this\row\field[column]
  EndIf
  
EndProcedure

Procedure.s db_info(*this.SMYSQL)
  
  If *this And *this\link 
    
    If mysql_info(*this\link) 
      ;ProcedureReturn PeekS(mysql_info(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_info(*this\link))
    EndIf
    
  EndIf
  
EndProcedure
Procedure.l db_insert_id(*this.SMYSQL)
  
  If *this And *this\link 
    mysql_insert_id(*this\link) 
  EndIf
  
EndProcedure
Procedure.l db_num_fields(*this.SMYSQL)
  
  If *this And *this\result
    ProcedureReturn mysql_num_fields(*this\result)
  EndIf
  
EndProcedure
Procedure.l db_num_rows(*this.SMYSQL)
  
  If *this And *this\result
    ProcedureReturn mysql_num_rows(*this\result)
  EndIf
  
EndProcedure
Procedure.l db_ping(*this.SMYSQL)
  
  If *this And *this\link
    
    If mysql_ping(*this\link) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
    
  EndIf
  
EndProcedure
Procedure.l db_query(*this.SMYSQL, query.s)
  
  If *this And *this\link
    
    If *this\result
      mysql_free_result(*this\result)
    EndIf
    
    If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
    ;If mysql_query(*this\link, query) = #MYSQL_NOERROR
      
      *this\result = mysql_store_result(*this\link)

      If *this\result
        ProcedureReturn #True
      EndIf
      
    EndIf
    
  EndIf
  
EndProcedure

Procedure.l db_query_from_file(*this.SMYSQL, fileIn.s)
  
  Protected result.l, file.l, query.s
  
  If *this And *this\link
    
    If *this\result
      mysql_free_result(*this\result)
    EndIf
    
    file = ReadFile(#PB_Any, fileIn)
    
    If file
      
      query = Space(Lof(file))
      
      If ReadData(file, @query, Lof(file)) = Lof(file)
        
        If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
          
          *this\result = mysql_store_result(*this\link)
          
          If *this\result
            result = #True
          EndIf
          
        EndIf
        
      EndIf
      
      CloseFile(file)
      
    EndIf
    
  EndIf
  
  ProcedureReturn result
  
EndProcedure
Procedure.l db_query_xml(*this.SMYSQL, query.s, fileOut.s, table.l = #False, db.l = #False)
  
  Protected file.l, result.l, *result
  Protected i.l, num_rows.l, *row.MYSQL_ROW
  Protected j.l, num_fields.l, *field.MYSQL_FIELD
  
  If *this And *this\link
    
    If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
      
      *result = mysql_store_result(*this\link)
      
      If *result
        
        file = CreateFile(#PB_Any, fileOut)
        
        If file
          
          num_rows   = mysql_num_rows(*result)
          num_fields = mysql_num_fields(*result)
          
          WriteStringN(file, "<?xml version='1.0' ?>")
          WriteStringN(file, "<mysql>")
          WriteStringN(file, "  <date>" + FormatDate("%yyyy-%mm-%dd %hh:%ii:%ss", Date()) + "</date>")
          WriteStringN(file, "  <query>" + db_escape_string(*this, query) + "</query>")
          WriteStringN(file, "  <fields count='" + Str(num_fields) + "'>")
          
          For j = 0 To num_fields - 1
            *field = mysql_fetch_field(*result)
            WriteStringN(file, "    <field id='" + Str(j+1) + "' type='"   + mysql_type_string(*field\Type) + "' length='" + Str(*field\length) + "' name='" + mysql_name_string(*field, db, table) + " />")
          Next j
          
          WriteStringN(file, "  </fields>")
          WriteStringN(file, "  <rows count='" + Str(num_rows) + "'>")
          
          For i = 0 To num_rows - 1
            
            mysql_field_seek(*result, 0)
            *row = mysql_fetch_row(*result)
            WriteStringN(file, "    <row id='" + Str(i+1) + "'>")
            
            For j = 0 To num_fields - 1
              *field = mysql_fetch_field(*result)
              WriteString (file, "      <" + mysql_name_string(*field, db, table) + ">")
              WriteString (file, db_escape_string(*this, *row\field[j]))
              WriteStringN(file, "</" + mysql_name_string(*field, db, table) + ">")
            Next j
            
            WriteStringN(file, "    </row>")
            
          Next i
          
          WriteStringN(file, "  </rows>")
          WriteStringN(file, "</mysql>")
          CloseFile(file)
          result = #True
          
        EndIf
        
        mysql_free_result(*result)
        
      EndIf
      
    EndIf
    
  EndIf
  
  ProcedureReturn result
  
EndProcedure
Procedure.l db_query_xml_from_file(*this.SMYSQL, fileIn.s, fileOut.s, table.l = #False, db.l = #False)
  
  ;**** to do
  
EndProcedure
Procedure.l db_row(*this.SMYSQL)
  
  If *this And *this\result
    
    *this\row = mysql_fetch_row(*this\result)
    
    ProcedureReturn *this\row
    
  EndIf
  
EndProcedure
Procedure.l db_selectdb(*this.SMYSQL, database.s)
  
  If *this And *this\link And (mysql_select_db(*this\link, database) = #MYSQL_NOERROR)
    ProcedureReturn #True
  EndIf
  
EndProcedure
Procedure.l db_update(*this.SMYSQL, query.s)
  
  If *this And *this\link
    
    If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
    
  EndIf
  
EndProcedure
Procedure.l db_update_from_file(*this.SMYSQL, fileIn.s)
  
  Protected result.l, file.l, query.s
  
  If *this And *this\link
    
    file = ReadFile(#PB_Any, fileIn)
    
    If file
      
      query = Space(Lof(file))
      
      If ReadData(file, @query, Lof(file)) = Lof(file)
        If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
          result = #True
        EndIf
      EndIf
      
      CloseFile(file)
      
    EndIf
    
  EndIf
  
  ProcedureReturn result
  
EndProcedure

Procedure.s db_host(*this.SMYSQL)
  
  If *this And *this\link
    If mysql_get_host_info(*this\link)
      ;ProcedureReturn PeekS(mysql_get_host_info(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_get_host_info(*this\link))
    EndIf
  EndIf
  
EndProcedure
Procedure.l db_proto(*this.SMYSQL)
  
  If *this And *this\link
    ProcedureReturn mysql_get_proto_info(*this\link)
  EndIf
  
EndProcedure
Procedure.l db_client_long(*this.SMYSQL)
  
  If *this
    ProcedureReturn mysql_get_client_version()
  EndIf
  
EndProcedure
Procedure.s db_client_string(*this.SMYSQL)
  
  If *this And mysql_get_client_info()
    ;ProcedureReturn PeekS(mysql_get_client_info())
    ProcedureReturn PeekS_Utf8(mysql_get_client_info())
  EndIf
  
EndProcedure
Procedure.l db_server_long(*this.SMYSQL)
  
  If *this And *this\link
    ProcedureReturn mysql_get_server_version(*this\link)
  EndIf
  
EndProcedure
Procedure.s db_server_string(*this.SMYSQL)
  
  If *this And *this\link
    If mysql_get_server_info(*this\link)
      ;ProcedureReturn PeekS(mysql_get_server_info(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_get_server_info(*this\link))
    EndIf
  EndIf
  
EndProcedure

; added 24/07/09
Procedure.l db_autocommit(*this.SMYSQL, mode.l)
  If *this And *this\link
    
    If mysql_autocommit(*this\link, mode) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
    
  EndIf
EndProcedure

Procedure.l db_commit(*this.SMYSQL)
  If *this And *this\link
    
    If mysql_commit(*this\link) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
    
  EndIf
EndProcedure

;-
;- Public - MySQL Interface
;-

ProcedureDLL.l mysql(host.s = "localhost", user.s = "root", pwd.s = "", db.s = "", port.l = 3306, flags.l = #Null)
  
  Protected *this.SMYSQL = AllocateMemory(SizeOf(SMYSQL))
  
  If *this
    
    *this\vt = AllocateMemory(SizeOf(IMYSQL_VTABLE))
    
    If *this\vt
      
      *this\link = mysql_init(#Null)
      
      If *this\link
        
        If mysql_real_connect(*this\link, host, user, pwd, db, port, "NULL", flags) = *this\link
          
          *this\vt\host                = @db_host() 
          *this\vt\proto               = @db_proto() 
          *this\vt\client_long         = @db_client_long() 
          *this\vt\client_string       = @db_client_string() 
          *this\vt\server_long         = @db_server_long() 
          *this\vt\server_string       = @db_server_string() 
          
          *this\vt\affected_rows       = @db_affected_rows() 
          *this\vt\error               = @db_error() 
          *this\vt\escape_string       = @db_escape_string() 
          *this\vt\field               = @db_field() 
          *this\vt\field_length        = @db_field_length() 
          *this\vt\field_length_max    = @db_field_length_max() 
          *this\vt\field_name          = @db_field_name() 
          *this\vt\field_reset         = @db_field_reset() 
          *this\vt\field_type          = @db_field_type() 
          *this\vt\field_type_string   = @db_field_type_string() 
          *this\vt\free                = @db_free() 
          *this\vt\GetDate             = @db_GetDate()
          *this\vt\GetDateTime         = @db_GetDateTime()
          *this\vt\GetDouble           = @db_GetDouble()
          *this\vt\GetFloat            = @db_GetFloat()
          *this\vt\GetLong             = @db_GetLong()
          *this\vt\GetQuad             = @db_GetQuad()
          *this\vt\GetString           = @db_GetString()
          *this\vt\GetTime             = @db_GetTime()
          *this\vt\get_date            = @db_get_date() 
          *this\vt\get_date_string     = @db_get_date_string() 
          *this\vt\get_datetime        = @db_get_datetime() 
          *this\vt\get_datetime_string = @db_get_datetime_string() 
          *this\vt\get_double          = @db_get_double() 
          *this\vt\get_float           = @db_get_float() 
          *this\vt\get_long            = @db_get_long() 
          *this\vt\get_quad            = @db_get_quad() 
          *this\vt\get_string          = @db_get_string() 
          *this\vt\get_time            = @db_get_time()
          *this\vt\get_time_string     = @db_get_time_string()
          *this\vt\info                = @db_info() 
          *this\vt\insert_id           = @db_insert_id() 
          *this\vt\num_fields          = @db_num_fields() 
          *this\vt\num_rows            = @db_num_rows() 
          *this\vt\ping                = @db_ping() 
          *this\vt\query               = @db_query() 
          *this\vt\query_from_file     = @db_query_from_file() 
          *this\vt\query_xml           = @db_query_xml() 
          *this\vt\query_xml_from_file = @db_query_xml_from_file() 
          *this\vt\row                 = @db_row() 
          *this\vt\selectdb            = @db_selectdb() 
          *this\vt\update              = @db_update() 
          *this\vt\update_from_file    = @db_update_from_file() 
          *this\vt\autocommit          = @db_autocommit()
          *this\vt\commit              = @db_commit()

          ProcedureReturn *this
          
        EndIf
        
        mysql_close(*this\link) : *this\link = #Null
        
      EndIf
      
      FreeMemory(*this\vt) : *this\vt = #Null
      
    EndIf
    
    FreeMemory(*this) : *this = #Null
    
  EndIf
  
EndProcedure
ProcedureDLL.l mysql_from_ini()
  
  OpenPreferences("libmysql.ini")
  
  PreferenceGroup("CONNECTION")
  
  Protected db_host.s = ReadPreferenceString("host", "localhost")
  Protected db_user.s = ReadPreferenceString("user", "root")
  Protected db_pass.s = ReadPreferenceString("pass", #NULL$)
  Protected db_base.s = ReadPreferenceString("base", #NULL$)
  Protected db_port.l = ReadPreferenceLong  ("port", 3306)
  Protected db_opts.l = ReadPreferenceLong  ("opts", #Null)
  
  ClosePreferences()
  
  ProcedureReturn mysql(db_host, db_user, db_pass, db_base, db_port, db_opts)
  
EndProcedure

DisableExplicit

;- 
;- End Of File
;-
And the sample in unicode :

Code: Select all

IncludePath #PB_Compiler_Home + "Includes\"
XIncludeFile "libmysql_utf8.pbi"      ;Unicode ou ascii

db.IMYSQL = mysql("localhost","hotline", "", "mysql")

If db
 
  If db\query("SELECT 'Hello' AS 'f1', 'World !' AS 'f2', NOW() as 'f3', 12345.6789")
   
    While db\row()
     
      Debug db\GetString("f1")
      Debug db\GetString("f2")
     
      Debug FormatDate("%dd.%mm.%yyyy", db\GetDate("f3"))
     
      Debug db\get_double(3)
     
    Wend
   
  Else
   
    Debug db\error()
   
  EndIf
 
  db\free()
 
EndIf
result = nada :(
“Fear is a reaction. Courage is a decision.” - WC
Top
Num3
PureBasic Expert
PureBasic Expert
Posts: 2812
Joined: Fri Apr 25, 2003 4:51 pm
Location: Portugal, Lisbon
Contact:
Contact Num3

Post by Num3 »

Use the Prototype include for mysql !
Just remember to change PrototypeC to Prototype under windows.
Top
lexvictory
Addict
Addict
Posts: 1027
Joined: Sun May 15, 2005 5:15 am
Location: Australia
Contact:
Contact lexvictory

Post by lexvictory »

Num3 wrote:Use the Prototype include for mysql
What would be the point of that? Import does the same thing more or less. (just the OS loads the dll)

@flaith: your problem is that you are defining structure fields with '.s'. In ascii mode, this is ok, because for the most part ASCII=UTF8.
However unicode strings are much more different, hence why nothing works. (though you do have the necessary ground work there, the PeekS_Utf8 proc)
I overlooked those structures when I converted it for unicode because I don't use them in the program I use the wrapper in!

Basically it just needs the structures to be changed, and all references to the fields changed too:

Code: Select all

;-
;- File:     libmysql.pbi
;- Version:  2.0
;- Author:   flype - flype44(at)gmail.com
;- Modified: flaith (24.07.2009)
;-
;- About:    MySQL Interface for Purebasic 4.3.
;-
;- updated:  p-utf8 format (for unicode or ascii)

EnableExplicit

;-
;- Private - MySQL API
;-

#MYSQL_NOERROR = 0

Enumeration 0 ; client
  #CLIENT_COMPRESS
  #CLIENT_FOUND_ROWS
  #CLIENT_IGNORE_SPACE
  #CLIENT_INTERACTIVE
  #CLIENT_LOCAL_FILES
  #CLIENT_MULTI_STATEMENTS
  #CLIENT_MULTI_RESULTS
  #CLIENT_NO_SCHEMA
  #CLIENT_ODBC
  #CLIENT_SSL
EndEnumeration
Enumeration 0 ; type
  #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
  #MYSQL_TYPE_SET
  #MYSQL_TYPE_TINY_BLOB
  #MYSQL_TYPE_MEDIUM_BLOB
  #MYSQL_TYPE_LONG_BLOB
  #MYSQL_TYPE_BLOB
  #MYSQL_TYPE_VAR_STRING
  #MYSQL_TYPE_STRING
  #MYSQL_TYPE_GEOMETRY
EndEnumeration

Structure MYSQL_FIELD
  name.i
  org_name.i
  table.i
  org_table.i
  db.i
  catalog.i
  def.i
  length.l
  max_length.l
  name_length.l
  org_name_length.l
  table_length.l
  org_table_length.l
  db_length.l
  catalog_length.l
  def_length.l
  flags.l
  decimals.l
  charset_nr.l
  Type.l
EndStructure
Structure MYSQL_FIELD_ARRAY
  field.MYSQL_FIELD[0]
EndStructure

Structure MYSQL_LENGTH
  *l.l[0]
EndStructure
Structure MYSQL_ROW
  ;*field.s[0]
  *field[0]
EndStructure

Import "libmysql.lib"
  mysql_affected_rows(*mysql)
  mysql_autocommit(*mysql, *mode.Byte)
  mysql_change_user(*mysql, user.p-utf8, passwd.p-utf8, db.p-utf8)
  mysql_character_set_name(*mysql)
  mysql_close(*mysql)
  mysql_commit(*mysql)
  mysql_data_seek(*result, offset.d)
  mysql_eof(*result)
  mysql_errno(*mysql)
  mysql_error(*mysql)
  mysql_escape_string(*strTo, strFrom.p-utf8, length.l)
  mysql_fetch_field(*result)
  mysql_fetch_field_direct(*result, fieldnr.l)
  mysql_fetch_fields(*result)
  mysql_fetch_lengths(*result)
  mysql_fetch_row(*result)
  mysql_field_count(*mysql)
  mysql_field_seek(*result, offset.l)
  mysql_field_tell(*result)
  mysql_free_result(*result)
  mysql_get_character_set_info(*mysql, *charsetinfo)
  mysql_get_client_info()
  mysql_get_client_version()
  mysql_get_host_info(*mysql)
  mysql_get_parameters()
  mysql_get_proto_info(*mysql)
  mysql_get_server_info(*mysql)
  mysql_get_server_version(*mysql)
  mysql_hex_string(strTo.p-utf8, strFrom.p-utf8, length.l)
  mysql_info(*mysql)
  mysql_init(*mysql)
  mysql_insert_id(*mysql)
  mysql_kill(*mysql, pid.l)
  mysql_list_dbs(*mysql, wild.p-utf8)
  mysql_list_fields(*mysql, table.p-utf8, wild.p-utf8)
  mysql_list_processes(*mysql)
  mysql_list_tables(*mysql, wild.p-utf8)
  mysql_master_query(*mysql, query.p-utf8, length.l)
  mysql_more_results(*mysql)
  mysql_next_result(*mysql)
  mysql_num_fields(*result)
  mysql_num_rows(*result)
  mysql_options(*mysql, option.l, arg.p-utf8)
  mysql_ping(*mysql)
  mysql_query(*mysql, query.p-utf8)
  mysql_read_query_result(*mysql)
  mysql_real_connect(*mysql, host.p-utf8, user.p-utf8, passwd.p-utf8, db.p-utf8, port.l, unix_socket.p-utf8, flags.l)
  mysql_real_escape_string(*mysql, *strTo, strFrom.p-utf8, length.l)
  mysql_real_query(*mysql, query.p-utf8, length.l)
  mysql_refresh(*mysql, Options.l)
  mysql_reload(*mysql)
  mysql_rollback(*mysql)
  mysql_row_seek(*mysql, offset.l)
  mysql_row_tell(*result)
  mysql_select_db(*mysql, db.p-utf8)
  mysql_store_result(*mysql)
EndImport

;-
;- Private - MySQL Interface
;-

Structure IMYSQL_VTABLE
 
  *host
  *proto
  *client_long
  *client_string
  *server_long
  *server_string
 
  *affected_rows
  *error
  *escape_string
 
  *field
  *field_length
  *field_length_max
  *field_name
  *field_reset
  *field_type
  *field_type_string
 
  *free
 
  *GetDate
  *GetDateTime
  *GetDouble
  *GetFloat
  *GetLong
  *GetQuad
  *GetString
  *GetTime
 
  *get_date
  *get_date_string
  *get_datetime
  *get_datetime_string
  *get_double
  *get_float
  *get_long
  *get_quad
  *get_string
  *get_time
  *get_time_string
 
  *info
  *insert_id
 
  *num_fields
  *num_rows
 
  *ping
 
  *query
  *query_from_file
  *query_xml
  *query_xml_from_file
 
  *row
  *selectdb
 
  *update
  *update_from_file

  ;added 24/07/09
  *autocommit
  *commit
EndStructure
Structure SMYSQL
  *vt.IMYSQL_VTABLE
  *link
  *result
  *row.MYSQL_ROW
  *field.MYSQL_FIELD
EndStructure
Interface IMYSQL
 
  host.s()
  proto.l()
  client_long.l()
  client_string.s()
  server_long.l()
  server_string.s()
 
  affected_rows.l()
  error.s()
  escape_string.s(string.s)
  field.l()
  field_length.l()
  field_length_max.l()
  field_name.s()
  field_reset.l()
  field_type.l()
  field_type_string.s()
  free.l()
  GetDate.l(name.s)
  GetDateTime.l(name.s)
  GetDouble.d(name.s)
  GetFloat.f(name.s)
  GetLong.l(name.s)
  GetQuad.q(name.s)
  GetString.s(name.s)
  GetTime.l(name.s)
  get_date.l(column.l = 0)
  get_date_string.s(column.l = 0)
  get_datetime.l(column.l = 0)
  get_datetime_string.s(column.l = 0)
  get_double.d(column.l = 0)
  get_float.f(column.l = 0)
  get_long.l(column.l = 0)
  get_quad.q(column.l = 0)
  get_string.s(column.l = 0)
  get_time.l(column.l = 0)
  get_time_string.s(column.l = 0)
  info.s()
  insert_id.l()
  num_fields.l()
  num_rows.l()
  ping.l()
  query.l(query.s)
  query_from_file.l(fileIn.s)
  query_xml(query.s, fileOut.s, table.l = #False, db.l = #False)
  query_xml_from_file(fileIn.s, fileOut.s, table.l = #False, db.l = #False)
  row.l()
  selectdb.l(database.s)
  update.l(query.s)
  update_from_file.l(fileIn.s)
  ;added 24/07/09
  autocommit(mode.l = #True)
  commit.l()
EndInterface

Procedure.s PeekS_Utf8(*buffer)       ;also checks if buffer is valid, returns an empty string if *buffer is 0
  If *buffer
    ProcedureReturn PeekS(*buffer, -1, #PB_UTF8)
  Else
    ProcedureReturn ""
  EndIf
EndProcedure

Procedure.s mysql_name_string(*field.MYSQL_FIELD, db.l, table.l)
 
  Protected result.s
 
  If db
    result + PeekS_Utf8(*field\db) + "."
  EndIf
 
  If table
    result + PeekS_Utf8(*field\table) + "."
  EndIf
 
  ProcedureReturn result + PeekS_Utf8(*field\name)
 
EndProcedure
Procedure.s mysql_type_string(Type.l)
 
  Select Type
    Case #MYSQL_TYPE_DECIMAL:     ProcedureReturn "decimal"
    Case #MYSQL_TYPE_TINY:        ProcedureReturn "tiny"
    Case #MYSQL_TYPE_SHORT:       ProcedureReturn "short"
    Case #MYSQL_TYPE_LONG:        ProcedureReturn "long"
    Case #MYSQL_TYPE_FLOAT:       ProcedureReturn "float"
    Case #MYSQL_TYPE_DOUBLE:      ProcedureReturn "double"
    Case #MYSQL_TYPE_NULL:        ProcedureReturn "null"
    Case #MYSQL_TYPE_TIMESTAMP:   ProcedureReturn "timestamp"
    Case #MYSQL_TYPE_LONGLONG:    ProcedureReturn "longlong"
    Case #MYSQL_TYPE_INT24:       ProcedureReturn "int24"
    Case #MYSQL_TYPE_DATE:        ProcedureReturn "date"
    Case #MYSQL_TYPE_TIME:        ProcedureReturn "time"
    Case #MYSQL_TYPE_DATETIME:    ProcedureReturn "datetime"
    Case #MYSQL_TYPE_YEAR:        ProcedureReturn "year"
    Case #MYSQL_TYPE_NEWDATE:     ProcedureReturn "newdate"
    Case #MYSQL_TYPE_VARCHAR:     ProcedureReturn "varchar"
    Case #MYSQL_TYPE_BIT:         ProcedureReturn "bit"
    Case #MYSQL_TYPE_NEWDECIMAL:  ProcedureReturn "newdecimal"
    Case #MYSQL_TYPE_ENUM:        ProcedureReturn "enum"
    Case #MYSQL_TYPE_SET:         ProcedureReturn "set"
    Case #MYSQL_TYPE_TINY_BLOB:   ProcedureReturn "tinyblob"
    Case #MYSQL_TYPE_MEDIUM_BLOB: ProcedureReturn "mediumblob"
    Case #MYSQL_TYPE_LONG_BLOB:   ProcedureReturn "longblob"
    Case #MYSQL_TYPE_BLOB:        ProcedureReturn "blob"
    Case #MYSQL_TYPE_VAR_STRING:  ProcedureReturn "varstring"
    Case #MYSQL_TYPE_STRING:      ProcedureReturn "string"
    Case #MYSQL_TYPE_GEOMETRY:    ProcedureReturn "geometry"
  EndSelect
 
  ProcedureReturn "unknown"
 
EndProcedure

Procedure.l db_affected_rows(*this.SMYSQL)
 
  If *this And *this\link
    mysql_affected_rows(*this\link)
  EndIf
 
EndProcedure
Procedure.s db_error(*this.SMYSQL)
 
  If *this And *this\link
   
    If mysql_error(*this\link)
      ;ProcedureReturn PeekS(mysql_error(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_error(*this\link))
    EndIf
   
  EndIf
 
EndProcedure
Procedure.s db_escape_string(*this.SMYSQL, string.s)
 
  Protected result.s
 
  If *this And *this\link
   
    result = Space((Len(string) + 2) * 2)
    result = ReplaceString(Left(result, mysql_real_escape_string(*this\link, @result, string, Len(string))), #TAB$, "\t")
   
    ProcedureReturn result
   
  EndIf
 
EndProcedure

Procedure.l db_field(*this.SMYSQL)
 
  If *this And *this\result
   
    *this\field = mysql_fetch_field(*this\result)
   
    ProcedureReturn *this\field
   
  EndIf
 
EndProcedure
Procedure.l db_field_length(*this.SMYSQL)
 
  If *this And *this\field
    ProcedureReturn *this\field\length
  EndIf
 
EndProcedure
Procedure.l db_field_length_max(*this.SMYSQL)
 
  If *this And *this\field
    ProcedureReturn *this\field\max_length
  EndIf
 
EndProcedure
Procedure.s db_field_name(*this.SMYSQL)
 
  If *this And *this\field
    ProcedureReturn PeekS_Utf8(*this\field\name)
  EndIf
 
EndProcedure
Procedure.l db_field_reset(*this.SMYSQL)
 
  If *this And *this\result
    ProcedureReturn mysql_field_seek(*this\result, 0)
  EndIf
 
EndProcedure
Procedure.l db_field_type(*this.SMYSQL)
 
  If *this And *this\field
    ProcedureReturn *this\field\type
  EndIf
 
EndProcedure
Procedure.s db_field_type_string(*this.SMYSQL)
 
  If *this And *this\field
    ProcedureReturn mysql_type_string(*this\field\type)
  EndIf
 
EndProcedure

Procedure.l db_free(*mysql.SMYSQL)
 
  If *mysql
   
    If *mysql\result
      mysql_free_result(*mysql\result)
    EndIf
   
    If *mysql\link
      mysql_close(*mysql\link)
    EndIf
   
  EndIf
 
EndProcedure

Procedure.l db_GetDate(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ProcedureReturn ParseDate("%yyyy-%mm-%dd", PeekS_Utf8(*this\row\field[i]))
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure
Procedure.l db_GetDateTime(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ProcedureReturn ParseDate("%yyyy-%mm-%dd %hh:%ii:%ss", PeekS_Utf8(*this\row\field[i]))
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure
Procedure.d db_GetDouble(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ProcedureReturn ValD(PeekS_Utf8(*this\row\field[i]))
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure
Procedure.f db_GetFloat(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ProcedureReturn ValF(PeekS_Utf8(*this\row\field[i]))
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure
Procedure.l db_GetLong(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ProcedureReturn Val(PeekS_Utf8(*this\row\field[i]))
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure
Procedure.q db_GetQuad(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ;ProcedureReturn ValQ(*this\row\field[i])
          ProcedureReturn Val(PeekS_Utf8(*this\row\field[i]))
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure
Procedure.s db_GetString(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ProcedureReturn PeekS_Utf8(*this\row\field[i])
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure
Procedure.l db_GetTime(*this.SMYSQL, name.s)
 
  Protected *field.MYSQL_FIELD, nFields.l, i.l
 
  If *this\result And *this\row
   
    nFields = mysql_num_fields(*this\result)
   
    If nFields
     
      mysql_field_seek(*this\result, 0)
     
      For i = 0 To nFields - 1
       
        *field = mysql_fetch_field(*this\result)
       
        If PeekS_Utf8(*field\name) = name
          ProcedureReturn ParseDate("%hh:%ii:%ss", PeekS_Utf8(*this\row\field[i]))
        EndIf
       
      Next
     
    EndIf
   
  EndIf
 
EndProcedure

Procedure.l db_get_date(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn ParseDate("%yyyy-%mm-%dd", PeekS_Utf8(*this\row\field[column]))
  EndIf
 
EndProcedure
Procedure.s db_get_date_string(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    Protected date.l = ParseDate("%yyyy-%mm-%dd", PeekS_Utf8(*this\row\field[column]))
    If date <> -1
      ProcedureReturn FormatDate("%dd-%mm-%yyyy", date)
    EndIf
  EndIf
 
EndProcedure
Procedure.l db_get_datetime(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn ParseDate("%yyyy-%mm-%dd %hh:%ii:%ss", PeekS_Utf8(*this\row\field[column]))
  EndIf
 
EndProcedure
Procedure.s db_get_datetime_string(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    Protected datetime.l = ParseDate("%yyyy-%mm-%dd %hh:%ii:%ss", PeekS_Utf8(*this\row\field[column]))
    If datetime <> -1
      ProcedureReturn FormatDate("%dd-%mm-%yyyy %hh:%ii:%ss", datetime)
    EndIf
  EndIf
 
EndProcedure
Procedure.d db_get_double(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn ValD(PeekS_Utf8(*this\row\field[column]))
  EndIf
 
EndProcedure
Procedure.f db_get_float(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn ValF(PeekS_Utf8(*this\row\field[column]))
  EndIf
 
EndProcedure
Procedure.l db_get_long(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn Val(PeekS_Utf8(*this\row\field[column]))
  EndIf
 
EndProcedure
Procedure.q db_get_quad(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ;ProcedureReturn ValQ(*this\row\field[column])
    ProcedureReturn Val(PeekS_Utf8(*this\row\field[column]))
  EndIf
 
EndProcedure
Procedure.s db_get_string(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn PeekS_Utf8(*this\row\field[column])
  EndIf
 
EndProcedure
Procedure.l db_get_time(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn ParseDate("%hh:%ii:%ss", PeekS_Utf8(*this\row\field[column]))
  EndIf
 
EndProcedure
Procedure.s db_get_time_string(*this.SMYSQL, column.l = 0)
 
  If *this And *this\row
    ProcedureReturn PeekS_Utf8(*this\row\field[column])
  EndIf
 
EndProcedure

Procedure.s db_info(*this.SMYSQL)
 
  If *this And *this\link
   
    If mysql_info(*this\link)
      ;ProcedureReturn PeekS(mysql_info(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_info(*this\link))
    EndIf
   
  EndIf
 
EndProcedure
Procedure.l db_insert_id(*this.SMYSQL)
 
  If *this And *this\link
    mysql_insert_id(*this\link)
  EndIf
 
EndProcedure
Procedure.l db_num_fields(*this.SMYSQL)
 
  If *this And *this\result
    ProcedureReturn mysql_num_fields(*this\result)
  EndIf
 
EndProcedure
Procedure.l db_num_rows(*this.SMYSQL)
 
  If *this And *this\result
    ProcedureReturn mysql_num_rows(*this\result)
  EndIf
 
EndProcedure
Procedure.l db_ping(*this.SMYSQL)
 
  If *this And *this\link
   
    If mysql_ping(*this\link) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
   
  EndIf
 
EndProcedure
Procedure.l db_query(*this.SMYSQL, query.s)
 
  If *this And *this\link
   
    If *this\result
      mysql_free_result(*this\result)
    EndIf
   
    If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
    ;If mysql_query(*this\link, query) = #MYSQL_NOERROR
     
      *this\result = mysql_store_result(*this\link)

      If *this\result
        ProcedureReturn #True
      EndIf
     
    EndIf
   
  EndIf
 
EndProcedure

Procedure.l db_query_from_file(*this.SMYSQL, fileIn.s)
 
  Protected result.l, file.l, query.s
 
  If *this And *this\link
   
    If *this\result
      mysql_free_result(*this\result)
    EndIf
   
    file = ReadFile(#PB_Any, fileIn)
   
    If file
     
      query = Space(Lof(file))
     
      If ReadData(file, @query, Lof(file)) = Lof(file)
       
        If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
         
          *this\result = mysql_store_result(*this\link)
         
          If *this\result
            result = #True
          EndIf
         
        EndIf
       
      EndIf
     
      CloseFile(file)
     
    EndIf
   
  EndIf
 
  ProcedureReturn result
 
EndProcedure
Procedure.l db_query_xml(*this.SMYSQL, query.s, fileOut.s, table.l = #False, db.l = #False)
 
  Protected file.l, result.l, *result
  Protected i.l, num_rows.l, *row.MYSQL_ROW
  Protected j.l, num_fields.l, *field.MYSQL_FIELD
 
  If *this And *this\link
   
    If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
     
      *result = mysql_store_result(*this\link)
     
      If *result
       
        file = CreateFile(#PB_Any, fileOut)
       
        If file
         
          num_rows   = mysql_num_rows(*result)
          num_fields = mysql_num_fields(*result)
         
          WriteStringN(file, "<?xml version='1.0' ?>")
          WriteStringN(file, "<mysql>")
          WriteStringN(file, "  <date>" + FormatDate("%yyyy-%mm-%dd %hh:%ii:%ss", Date()) + "</date>")
          WriteStringN(file, "  <query>" + db_escape_string(*this, query) + "</query>")
          WriteStringN(file, "  <fields count='" + Str(num_fields) + "'>")
         
          For j = 0 To num_fields - 1
            *field = mysql_fetch_field(*result)
            WriteStringN(file, "    <field id='" + Str(j+1) + "' type='"   + mysql_type_string(*field\Type) + "' length='" + Str(*field\length) + "' name='" + mysql_name_string(*field, db, table) + " />")
          Next j
         
          WriteStringN(file, "  </fields>")
          WriteStringN(file, "  <rows count='" + Str(num_rows) + "'>")
         
          For i = 0 To num_rows - 1
           
            mysql_field_seek(*result, 0)
            *row = mysql_fetch_row(*result)
            WriteStringN(file, "    <row id='" + Str(i+1) + "'>")
           
            For j = 0 To num_fields - 1
              *field = mysql_fetch_field(*result)
              WriteString (file, "      <" + mysql_name_string(*field, db, table) + ">")
              WriteString (file, db_escape_string(*this, PeekS_Utf8(*row\field[j])))
              WriteStringN(file, "</" + mysql_name_string(*field, db, table) + ">")
            Next j
           
            WriteStringN(file, "    </row>")
           
          Next i
         
          WriteStringN(file, "  </rows>")
          WriteStringN(file, "</mysql>")
          CloseFile(file)
          result = #True
         
        EndIf
       
        mysql_free_result(*result)
       
      EndIf
     
    EndIf
   
  EndIf
 
  ProcedureReturn result
 
EndProcedure
Procedure.l db_query_xml_from_file(*this.SMYSQL, fileIn.s, fileOut.s, table.l = #False, db.l = #False)
 
  ;**** to do
 
EndProcedure
Procedure.l db_row(*this.SMYSQL)
 
  If *this And *this\result
   
    *this\row = mysql_fetch_row(*this\result)
   
    ProcedureReturn *this\row
   
  EndIf
 
EndProcedure
Procedure.l db_selectdb(*this.SMYSQL, database.s)
 
  If *this And *this\link And (mysql_select_db(*this\link, database) = #MYSQL_NOERROR)
    ProcedureReturn #True
  EndIf
 
EndProcedure
Procedure.l db_update(*this.SMYSQL, query.s)
 
  If *this And *this\link
   
    If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
   
  EndIf
 
EndProcedure
Procedure.l db_update_from_file(*this.SMYSQL, fileIn.s)
 
  Protected result.l, file.l, query.s
 
  If *this And *this\link
   
    file = ReadFile(#PB_Any, fileIn)
   
    If file
     
      query = Space(Lof(file))
     
      If ReadData(file, @query, Lof(file)) = Lof(file)
        If mysql_real_query(*this\link, query, Len(query)) = #MYSQL_NOERROR
          result = #True
        EndIf
      EndIf
     
      CloseFile(file)
     
    EndIf
   
  EndIf
 
  ProcedureReturn result
 
EndProcedure

Procedure.s db_host(*this.SMYSQL)
 
  If *this And *this\link
    If mysql_get_host_info(*this\link)
      ;ProcedureReturn PeekS(mysql_get_host_info(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_get_host_info(*this\link))
    EndIf
  EndIf
 
EndProcedure
Procedure.l db_proto(*this.SMYSQL)
 
  If *this And *this\link
    ProcedureReturn mysql_get_proto_info(*this\link)
  EndIf
 
EndProcedure
Procedure.l db_client_long(*this.SMYSQL)
 
  If *this
    ProcedureReturn mysql_get_client_version()
  EndIf
 
EndProcedure
Procedure.s db_client_string(*this.SMYSQL)
 
  If *this And mysql_get_client_info()
    ;ProcedureReturn PeekS(mysql_get_client_info())
    ProcedureReturn PeekS_Utf8(mysql_get_client_info())
  EndIf
 
EndProcedure
Procedure.l db_server_long(*this.SMYSQL)
 
  If *this And *this\link
    ProcedureReturn mysql_get_server_version(*this\link)
  EndIf
 
EndProcedure
Procedure.s db_server_string(*this.SMYSQL)
 
  If *this And *this\link
    If mysql_get_server_info(*this\link)
      ;ProcedureReturn PeekS(mysql_get_server_info(*this\link))
      ProcedureReturn PeekS_Utf8(mysql_get_server_info(*this\link))
    EndIf
  EndIf
 
EndProcedure

; added 24/07/09
Procedure.l db_autocommit(*this.SMYSQL, mode.l)
  If *this And *this\link
   
    If mysql_autocommit(*this\link, mode) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
   
  EndIf
EndProcedure

Procedure.l db_commit(*this.SMYSQL)
  If *this And *this\link
   
    If mysql_commit(*this\link) = #MYSQL_NOERROR
      ProcedureReturn #True
    EndIf
   
  EndIf
EndProcedure

;-
;- Public - MySQL Interface
;-

ProcedureDLL.l mysql(host.s = "localhost", user.s = "root", pwd.s = "", db.s = "", port.l = 3306, flags.l = #Null)
 
  Protected *this.SMYSQL = AllocateMemory(SizeOf(SMYSQL))
 
  If *this
   
    *this\vt = AllocateMemory(SizeOf(IMYSQL_VTABLE))
   
    If *this\vt
     
      *this\link = mysql_init(#Null)
     
      If *this\link
       
        If mysql_real_connect(*this\link, host, user, pwd, db, port, "NULL", flags) = *this\link
         
          *this\vt\host                = @db_host()
          *this\vt\proto               = @db_proto()
          *this\vt\client_long         = @db_client_long()
          *this\vt\client_string       = @db_client_string()
          *this\vt\server_long         = @db_server_long()
          *this\vt\server_string       = @db_server_string()
         
          *this\vt\affected_rows       = @db_affected_rows()
          *this\vt\error               = @db_error()
          *this\vt\escape_string       = @db_escape_string()
          *this\vt\field               = @db_field()
          *this\vt\field_length        = @db_field_length()
          *this\vt\field_length_max    = @db_field_length_max()
          *this\vt\field_name          = @db_field_name()
          *this\vt\field_reset         = @db_field_reset()
          *this\vt\field_type          = @db_field_type()
          *this\vt\field_type_string   = @db_field_type_string()
          *this\vt\free                = @db_free()
          *this\vt\GetDate             = @db_GetDate()
          *this\vt\GetDateTime         = @db_GetDateTime()
          *this\vt\GetDouble           = @db_GetDouble()
          *this\vt\GetFloat            = @db_GetFloat()
          *this\vt\GetLong             = @db_GetLong()
          *this\vt\GetQuad             = @db_GetQuad()
          *this\vt\GetString           = @db_GetString()
          *this\vt\GetTime             = @db_GetTime()
          *this\vt\get_date            = @db_get_date()
          *this\vt\get_date_string     = @db_get_date_string()
          *this\vt\get_datetime        = @db_get_datetime()
          *this\vt\get_datetime_string = @db_get_datetime_string()
          *this\vt\get_double          = @db_get_double()
          *this\vt\get_float           = @db_get_float()
          *this\vt\get_long            = @db_get_long()
          *this\vt\get_quad            = @db_get_quad()
          *this\vt\get_string          = @db_get_string()
          *this\vt\get_time            = @db_get_time()
          *this\vt\get_time_string     = @db_get_time_string()
          *this\vt\info                = @db_info()
          *this\vt\insert_id           = @db_insert_id()
          *this\vt\num_fields          = @db_num_fields()
          *this\vt\num_rows            = @db_num_rows()
          *this\vt\ping                = @db_ping()
          *this\vt\query               = @db_query()
          *this\vt\query_from_file     = @db_query_from_file()
          *this\vt\query_xml           = @db_query_xml()
          *this\vt\query_xml_from_file = @db_query_xml_from_file()
          *this\vt\row                 = @db_row()
          *this\vt\selectdb            = @db_selectdb()
          *this\vt\update              = @db_update()
          *this\vt\update_from_file    = @db_update_from_file()
          *this\vt\autocommit          = @db_autocommit()
          *this\vt\commit              = @db_commit()

          ProcedureReturn *this
         
        EndIf
       
        mysql_close(*this\link) : *this\link = #Null
       
      EndIf
     
      FreeMemory(*this\vt) : *this\vt = #Null
     
    EndIf
   
    FreeMemory(*this) : *this = #Null
   
  EndIf
 
EndProcedure
ProcedureDLL.l mysql_from_ini()
 
  OpenPreferences("libmysql.ini")
 
  PreferenceGroup("CONNECTION")
 
  Protected db_host.s = ReadPreferenceString("host", "localhost")
  Protected db_user.s = ReadPreferenceString("user", "root")
  Protected db_pass.s = ReadPreferenceString("pass", #NULL$)
  Protected db_base.s = ReadPreferenceString("base", #NULL$)
  Protected db_port.l = ReadPreferenceLong  ("port", 3306)
  Protected db_opts.l = ReadPreferenceLong  ("opts", #Null)
 
  ClosePreferences()
 
  ProcedureReturn mysql(db_host, db_user, db_pass, db_base, db_port, db_opts)
 
EndProcedure

DisableExplicit

;-
;- End Of File
;-
(works in both unicode and ascii modes here)
Demonio Ardente

Currently managing Linux & OS X Tailbite
OS X TailBite now up to date with Windows!
Top
User avatar
flaith
Enthusiast
Enthusiast
Posts: 704
Joined: Mon Apr 25, 2005 9:28 pm
Location: $300:20 58 FC 60 - Rennes
Contact:
Contact flaith

Post by flaith »

Num3 wrote:Use the Prototype include for mysql !
Just remember to change PrototypeC to Prototype under windows.
Thanks Num3, but i need to keep the way the wrapper is :wink:
lexvictory wrote:@flaith: your problem is that you are defining structure fields with '.s'. In ascii mode, this is ok, because for the most part ASCII=UTF8.
However unicode strings are much more different, hence why nothing works. (though you do have the necessary ground work there, the PeekS_Utf8 proc)
I overlooked those structures when I converted it for unicode because I don't use them in the program I use the wrapper in!

Basically it just needs the structures to be changed, and all references to the fields changed too
Thanks a lot lexvictory, now i can read inside my db :)

But after resolving a problem, there is another one :
now i'm writing datas inside a db, one of my record contain a special french character : "é"
First code : worked

Code: Select all

; UNICODE ON
IncludePath #PB_Compiler_Home + "Includes"
XIncludeFile "libmysql.pbi"

db.IMYSQL = mysql("localhost","pdt_creator", "", "test")

If db
  db\query("DROP TABLE test_utf8")
  db\query("CREATE TABLE test_utf8 (nom varchar(40), prenom varchar(25))")
  db\query("INSERT into test_utf8 VALUES('AAAAAA','Marc'),('BBBBBB','Aurélie'),('CCCCCC','Mathieu'))")

  db\query("SELECT * from test_utf8")
 
  While db\row()
    Debug db\GetString("nom") +" - " + db\GetString("prenom")
  Wend

  db\free()

Else
  Debug db\error()
EndIf
With phpmyadmin i can see that the name Aurélie is writting : "Aurélie"

Second Code, with structure & List :

Code: Select all

; UNICODE ON
IncludePath #PB_Compiler_Home + "Includes"
XIncludeFile "libmysql.pbi"      ;Unicode ou ascii

Structure sTEC
  NOM.s
  PRENOM.s
EndStructure

Global NewList TEC.sTEC()

AddElement(TEC())
  TEC()\NOM = "AAAAAA"
  TEC()\PRENOM = "Marc"

AddElement(TEC())
  TEC()\NOM = "BBBBBB"
  TEC()\PRENOM = "Aurélie"

AddElement(TEC())
  TEC()\NOM = "CCCCCC"
  TEC()\PRENOM = "Mathieu"

db.IMYSQL = mysql("localhost","pdt_creator", "", "test")

If db
  db\query("DROP TABLE test_utf8")
  db\query("CREATE TABLE test_utf8 (nom varchar(40), prenom varchar(25))")

  ForEach TEC()
    SQL$="INSERT into test_utf8 VALUES("
    SQL$+"'"+TEC()\NOM+"',"
    SQL$+"'"+TEC()\PRENOM+"')"
    Debug SQL$
    db\query(SQL$)
  Next

  db\query("SELECT * from test_utf8")
 
  While db\row()
    Debug db\GetString("nom") +" - " + db\GetString("prenom")
  Wend

  db\free()

Else
  Debug db\error()
EndIf
The line with "Aurélie" is not written

i try to convert with several functions found in the forum :

Code: Select all

; UTF8 to Unicode/AscII (depend if the app is compiled as unicode or not).
Procedure.s Unicode(s.s)
	Protected *Buffer 
	
	*Buffer = AllocateMemory(StringByteLength(S,#PB_UTF8) + SizeOf(CHARACTER)) ;<== add a byte for the Null (1 or 2?) 
	PokeS(*Buffer,S, -1, #PB_Ascii)
	Result$=PeekS(*Buffer, -1, #PB_UTF8)
	FreeMemory(*Buffer)
	ProcedureReturn Result$
EndProcedure
	
; Unicode/AscII (depend if the app is compiled as unicode or not) to UTf8.
Procedure.s UTF8(s.s)
	Protected *Buffer 
	
	*Buffer = AllocateMemory(StringByteLength(S,#PB_UTF8) + SizeOf(CHARACTER)) ;<== add a byte for the Null (1 or 2)? 
	PokeS(*Buffer,S, -1, #PB_UTF8)
	Result$=PeekS(*Buffer, -1, #PB_Ascii)
	FreeMemory(*Buffer)
	ProcedureReturn Result$
EndProcedure
;
; or
;
Procedure.s UTF8(a$)
  *buffer = AllocateMemory(Len(a$) * SizeOf(CHARACTER) + 1)
  If *buffer
    PokeS(*buffer, a$, -1, #PB_UTF8)
  EndIf
  ProcedureReturn PeekS(*buffer, -1, #PB_Ascii)
EndProcedure
;
; or
;   
Procedure.s UTF8(a$)
  *TextUnicode=AllocateMemory(StringByteLength(a$, #PB_Unicode) + 2) ;adjust the length by 2 bytes for the Null in Unicode String
  PokeS(*TextUnicode, a$, MemorySize(*TextUnicode), #PB_Unicode)
  ProcedureReturn a$
EndProcedure
Function unicode() is the most closer to a good result except the char "é" is not inserted :?
“Fear is a reaction. Courage is a decision.” - WC
Top
lexvictory
Addict
Addict
Posts: 1027
Joined: Sun May 15, 2005 5:15 am
Location: Australia
Contact:
Contact lexvictory

Post by lexvictory »

no need for any extra procs; p-utf8 will take care of the Unicode->UTF8 conversion properly. (and all of those procs there are going to return a unicode string anyway because of the PeekS calls)
the problem iirc is that the mysql charset isn't set to utf-8, because mysql's default charset is some ascii based one. (latin1_swedish_ci or something)

the easiest way to fix this is to create the database in phpMyAdmin, and setting the collation/charset (there are two fields needing to be set i think) to utf8
or if you need to do the creation in your program, create a database in phpMyAdmin, and copy/modify the sql that it shows it used.
Demonio Ardente

Currently managing Linux & OS X Tailbite
OS X TailBite now up to date with Windows!
Top
User avatar
flaith
Enthusiast
Enthusiast
Posts: 704
Joined: Mon Apr 25, 2005 9:28 pm
Location: $300:20 58 FC 60 - Rennes
Contact:
Contact flaith

Post by flaith »

lexvictory wrote:no need for any extra procs; p-utf8 will take care of the Unicode->UTF8 conversion properly. (and all of those procs there are going to return a unicode string anyway because of the PeekS calls)
the problem iirc is that the mysql charset isn't set to utf-8, because mysql's default charset is some ascii based one. (latin1_swedish_ci or something)

the easiest way to fix this is to create the database in phpMyAdmin, and setting the collation/charset (there are two fields needing to be set i think) to utf8
or if you need to do the creation in your program, create a database in phpMyAdmin, and copy/modify the sql that it shows it used.
Thanks lexvictory,
my database is already in "utf8_general_ci" (i tried several format, ie 'latin1_general_ci') : same :wink:

NB: If i use the wrapper with #pb_ascii, it's ok (but i'm not using unicode)
“Fear is a reaction. Courage is a decision.” - WC
Top
lexvictory
Addict
Addict
Posts: 1027
Joined: Sun May 15, 2005 5:15 am
Location: Australia
Contact:
Contact lexvictory

Post by lexvictory »

then i'm not sure.
you may have to do a bit of research; i remember in the days of phpBB 2, to get it to use utf8 there was some sql command(s) needed on every opening of the database.

otherwise I can't really help you sorry.
Demonio Ardente

Currently managing Linux & OS X Tailbite
OS X TailBite now up to date with Windows!
Top
Post Reply

Return to “Coding Questions”