Login, query and retreive data from a MySQL server, direct

[Fangbeast]

Code updated For 5.20+
Read the comments at the top of the code. You need the libmsql.dll library in the same dir as this code.

Code: Select all

;==============================================================================================================================
; Please note that all the MySQL direct database code I found in the PureBasic forum, done by Max2 and not by me. He has
; given me permission to use it as I see fit in a tutorial and it is hoped that this mini program will help someone get to
; grips with using MySQL server queries directly, without having to install either an ODBC driver or having to go through
; the ODBC control panel to setup a database each time.
;
; I only put this program together using his code, no big thing. Use it as you see fit.
;
; *NOTE* There is no syntax checking on database statements, I simply don't have that sort of time on my hands, nor the
; experience needed. This should be enough for most people.
;
; If you like it, say thanks to Mx2 and (me too maybe???). If you don't like it, I don't want to know. Fang.
;==============================================================================================================================
; Predefine my global structure types so I don't have to keep adding the type throughout the program
;==============================================================================================================================

dbHnd.l     
SQL.s
row.s
i.l
j.l
affRows.l
fieldNum.l
rowsNum.l

;==============================================================================================================================
; Any global variables my programs need, particularly the aliases MySQL library functions
;==============================================================================================================================

Global CFF_MySQL_Init.l,        CFF_MySQL_ERRNO.l
Global CFF_MySQL_ERROR.l,       CFF_MySQL_Real_Connect.l
Global CFF_MySQL_Real_Query.l,  CFF_MySQL_Store_Result.l
Global CFF_MySQL_Field_Count.l, CFF_MySQL_Use_Result.l
Global CFF_MySQL_Fetch_Row.l,   CFF_MySQL_Fetch_Lengths.l
Global CFF_MySQL_Free_Result.l, CFF_MySQL_Close.l

;==============================================================================================================================
; Get the current directory. May be used for various functions
;==============================================================================================================================

CurrentDir.s = Space(512)
Result = GetCurrentDirectory_(Len(CurrentDir), @CurrentDir)

;==============================================================================================================================
; Specific constans for the MySQL functions
;==============================================================================================================================

#libmysql               = 1

#MySQL_CLIENT_COMPRESS  = 32

;============================================================================================================================
; Main window title which we will overwrite with other information
;============================================================================================================================

#MainTitle = "MySQL - "

;============================================================================================================================
; Constants
;============================================================================================================================

Enumeration 1
  #Window_mysqltest
EndEnumeration

#WindowIndex = #PB_Compiler_EnumerationValue

Enumeration 1
  #Gadget_mysqltest_mainframe
  #Gadget_mysqltest_datalist
  #Gadget_mysqltest_controlframe
  #Gadget_mysqltest_querylabel
  #Gadget_mysqltest_querybox
  #Gadget_mysqltest_loginframe
  #Gadget_mysqltest_hostlabel
  #Gadget_mysqltest_hostbox
  #Gadget_mysqltest_userlabel
  #Gadget_mysqltest_userbox
  #Gadget_mysqltest_passwordlabel
  #Gadget_mysqltest_passwordbox
  #Gadget_mysqltest_databaselabel
  #Gadget_mysqltest_databasebox
  #Gadget_mysqltest_portlabel
  #Gadget_mysqltest_portbox
  #Gadget_mysqltest_savelogin
  #Gadget_mysqltest_dumpbutton
  #Gadget_mysqltest_otherframe
  #Gadget_mysqltest_helpbutton
  #Gadget_mysqltest_exitbutton
  #Gadget_mysqltest_clearlist
  #Gadget_mysqltest_return
EndEnumeration

#GadgetIndex = #PB_Compiler_EnumerationValue

;==============================================================================================================================
; Program window
;==============================================================================================================================

Procedure.l Window_mysqltest()
  If OpenWindow(#Window_mysqltest,175,0,800,555,#MainTitle,#PB_Window_ScreenCentered|#PB_Window_Invisible)
    AddKeyboardShortcut(#Window_mysqltest, #PB_Shortcut_Return, #Gadget_mysqltest_return)
    
    FrameGadget3D(#Gadget_mysqltest_mainframe,0,0,640,510,"")
    ListIconGadget(#Gadget_mysqltest_datalist,5,10,630,495,"itemslist",1000,#PB_ListIcon_GridLines|#PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection)
    GadgetToolTip(#Gadget_mysqltest_datalist,"All items returned from a properly formatted SQL query will end up in this list and be cleared in  the next call")
    FrameGadget3D(#Gadget_mysqltest_controlframe,0,510,640,45,"")
    TextGadget(#Gadget_mysqltest_querylabel,10,530,60,15,"SQL Query")
    StringGadget(#Gadget_mysqltest_querybox,70,525,470,20,"")
    GadgetToolTip(#Gadget_mysqltest_querybox,"Type in a properly formatted SQL query in here and press ENTER/RETURN to execute it")
    FrameGadget3D(#Gadget_mysqltest_loginframe,645,0,155,510,"")
    TextGadget(#Gadget_mysqltest_hostlabel,655,15,135,15,"Host IP or host name")
    StringGadget(#Gadget_mysqltest_hostbox,655,30,135,20,"")
    GadgetToolTip(#Gadget_mysqltest_hostbox,"Type in the name of the system that hosts the database or the IP version")
    TextGadget(#Gadget_mysqltest_userlabel,655,55,135,15,"User name")
    StringGadget(#Gadget_mysqltest_userbox,655,70,135,20,"",#PB_String_LowerCase)
    GadgetToolTip(#Gadget_mysqltest_userbox,"Type in the name of the user who is registered for use with that database")
    TextGadget(#Gadget_mysqltest_passwordlabel,655,95,135,15,"Password")
    StringGadget(#Gadget_mysqltest_passwordbox,655,110,135,20,"",#PB_String_Password|#PB_String_LowerCase)
    GadgetToolTip(#Gadget_mysqltest_passwordbox,"Type in the login password of the user that is registered for use with that database")
    TextGadget(#Gadget_mysqltest_databaselabel,655,135,135,15,"Database name")
    StringGadget(#Gadget_mysqltest_databasebox,655,150,135,20,"",#PB_String_LowerCase)
    GadgetToolTip(#Gadget_mysqltest_databasebox,"Type in the name of the database that you wish to access on the remote system")
    TextGadget(#Gadget_mysqltest_portlabel,655,175,135,15,"Port number")
    StringGadget(#Gadget_mysqltest_portbox,655,190,135,20,"",#PB_String_Numeric)
    GadgetToolTip(#Gadget_mysqltest_portbox,"Type in the port number that the database server allows queries on")
    CheckBoxGadget(#Gadget_mysqltest_savelogin,655,220,135,15,"Save current login")
    GadgetToolTip(#Gadget_mysqltest_savelogin,"Check this box if you want to save the current login to be auto-loaded next time you start this program")
    ButtonGadget(#Gadget_mysqltest_dumpbutton,655,480,135,20,"Dump list to disk")
    GadgetToolTip(#Gadget_mysqltest_dumpbutton,"press this button to dump the results of the query to a disk file")
    FrameGadget3D(#Gadget_mysqltest_otherframe,645,510,155,45,"")
    ButtonGadget(#Gadget_mysqltest_helpbutton,655,525,60,20,"Help")
    GadgetToolTip(#Gadget_mysqltest_helpbutton,"press this button to show any help file linked to this program")
    ButtonGadget(#Gadget_mysqltest_exitbutton,730,525,60,20,"Exit")
    GadgetToolTip(#Gadget_mysqltest_exitbutton,"press this button to exit this program immediately")
    CheckBoxGadget(#Gadget_mysqltest_clearlist,550,530,80,15,"Clear list")
    GadgetToolTip(#Gadget_mysqltest_clearlist,"Select this button to clear the list before the next SQL query")
    HideWindow(#Window_mysqltest,0)
    ProcedureReturn WindowID(#Window_mysqltest)
    
  EndIf
EndProcedure

;==============================================================================================================================
; All of the aliased MySQL library routines and the library loader
;==============================================================================================================================

Procedure MySQL_Init()
  Shared CurrentDir.s
  If OpenLibrary(#libmysql, CurrentDir.s + "\libmySQL.dll")
    CFF_MySQL_Init          = GetFunction(#libmysql, "mysql_init")
    CFF_MySQL_ErrNo         = GetFunction(#libmysql, "mysql_errno")
    CFF_MYSQL_Error         = GetFunction(#libmysql, "mysql_error")
    CFF_MySQL_Real_Connect  = GetFunction(#libmysql, "mysql_real_connect")
    CFF_MySQL_Real_Query    = GetFunction(#libmysql, "mysql_real_query")
    CFF_MySQL_Store_Result  = GetFunction(#libmysql, "mysql_store_result")
    CFF_MySQL_Field_Count   = GetFunction(#libmysql, "mysql_field_count")
    CFF_MySQL_Use_Result    = GetFunction(#libmysql, "mysql_use_result")
    CFF_MySQL_Fetch_Row     = GetFunction(#libmysql, "mysql_fetch_row")
    CFF_MySQL_Fetch_Lengths = GetFunction(#libmysql, "mysql_fetch_lengths")
    CFF_MySQL_Free_Result   = GetFunction(#libmysql, "mysql_free_result")
    CFF_MySQL_Close         = GetFunction(#libmysql, "mysql_close")
    ProcedureReturn CallFunctionFast (CFF_MySQL_Init, dbHnd)
  EndIf
EndProcedure

Procedure.s MySQL_GetError(db_ID, requester)
  Protected Errormsg.s, i.l, Error.l
  If CallFunctionFast(CFF_MySQL_ErrNo, db_ID) > 0
    *Error = CallFunctionFast(CFF_MySQL_Error, db_ID)       
    Errormsg = PeekS(*Error)
    If requester
      SetWindowTitle(#Window_mysqltest, #MainTitle + Errormsg)
    EndIf
  EndIf
  ProcedureReturn Errormsg
EndProcedure

Procedure MySQL_Real_Connect(dbHnd, host.s, user.s, password.s, db.s, port.l, options.l)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Connect, dbHnd, @host, @user, @password.s, @db, port, 0, options)
EndProcedure

Procedure MySQL_Real_Query(dbHnd, Query.s)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Query, dbHnd, @Query, Len(Query))
EndProcedure

Procedure MySQL_Store_Result(dbHnd)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Store_Result, dbHnd)
EndProcedure

Procedure MySQL_Field_Count(dbHnd)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Field_Count, dbHnd)
EndProcedure

Procedure MySQL_Use_Result(dbHnd)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Use_Result, dbHnd)
EndProcedure

Procedure MySQL_Fetch_Row (*mysqlResult)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Fetch_Row, *mysqlResult)
EndProcedure

Procedure MySQL_Fetch_Lengths (*mysqlResult)
  ProcedureReturn CallFunctionFast (CFF_MySQL_Fetch_Lengths, *mysqlResult)         
EndProcedure

Procedure MySQL_Free_Result (*mysqlResult)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Free_Result, *mysqlResult)
EndProcedure

Procedure MySQL_Close (dbHnd)
  CallFunctionFast(CFF_MySQL_Close, dbHnd)
EndProcedure

;============================================================================================================================
; Try to initialise the database environment (Procedure MySQL_Init())
;============================================================================================================================

dbHnd = MySQL_Init()

If dbHnd
  libOpened = 1 ; Tell all routines that the library was opened okay
Else
  libOpened = 0
  Message = MessageRequester("Critical Error", "libmysql.dll not found in programs' startup directory")
  End
EndIf

;============================================================================================================================
; Main Program Loop
;============================================================================================================================

If Window_mysqltest()
  
  quitmysqltest = 0                  ; Set the program quit semaphore
  
  Gosub LoadLastLogin               ; Load the last login if it exists
  
  Repeat                            ; Now start checking for all window and gadget events
    EventID = WaitWindowEvent()
    Select EventID
      Case #PB_Event_CloseWindow
        If EventWindow() = #Window_mysqltest
          quitmysqltest = 1
        EndIf
      Case #PB_Event_Menu
        Select EventMenu()
          Case #Gadget_mysqltest_return       : Gosub CheckEnter    ; Was Enter pressed in the query box?
        EndSelect
      Case #PB_Event_Gadget
        Select EventGadget()
          Case #Gadget_mysqltest_dumpbutton   : Gosub DumpListToDisk
          Case #Gadget_mysqltest_helpbutton
          Case #Gadget_mysqltest_exitbutton   : quitmysqltest = 1
        EndSelect
    EndSelect
  Until quitmysqltest
  Gosub SaveCurrentLogin             ; Save the current login if the checkbox is selected
  CloseWindow(#Window_mysqltest)
EndIf
End

;============================================================================================================================
; Load the last login saved by the user if there was one
;============================================================================================================================

LoadLastLogin:

If OpenFile(0, "LastLogin.txt") <> 0
  SetGadgetText(#Gadget_mysqltest_hostbox,     ReadString(0))
  SetGadgetText(#Gadget_mysqltest_userbox,     ReadString(0))
  SetGadgetText(#Gadget_mysqltest_passwordbox, ReadString(0))
  SetGadgetText(#Gadget_mysqltest_databasebox, ReadString(0))
  SetGadgetText(#Gadget_mysqltest_portbox,     ReadString(0))
  If ReadString(0) = "1"
    SetGadgetState(#Gadget_mysqltest_savelogin, 1)
  EndIf
  CloseFile(0)
  SetWindowTitle(#Window_mysqltest, #MainTitle + "Last saved login loaded")
EndIf

Return

;============================================================================================================================
; Save the current login details if the user has selected the checkbox
;============================================================================================================================

SaveCurrentLogin:

If GetGadgetState(#Gadget_mysqltest_savelogin) = 1
  If CreateFile(0, "LastLogin.txt") <> 1
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_hostbox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_userbox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_passwordbox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_databasebox))
    WriteStringN(0,GetGadgetText(#Gadget_mysqltest_portbox))
    WriteStringN(0,"1")
    CloseFile(0)
  EndIf
EndIf

Return

;============================================================================================================================
; Check if the ENTER key was pressed in the SQL query box
;============================================================================================================================

CheckEnter:

FocusID = GetFocus_()                                       ; Get the id of the window/object that has focus
Select FocusID                                             ; Use the id in a gadget selection
  Case GadgetID(#Gadget_mysqltest_querybox)                 ; Gadget is the barcode box
    Query.s = GetGadgetText(#Gadget_mysqltest_querybox)     ; Get the text from the barcode box
    Gosub ConnectToDatabase                                ; Reusable data return routine
EndSelect                                                  ; End the selection

;============================================================================================================================
; Try to connect to the database
;============================================================================================================================

ConnectToDatabase:

If libOpened
  host.s    = GetGadgetText(#Gadget_mysqltest_hostbox)
  user.s    = GetGadgetText(#Gadget_mysqltest_userbox)
  passwd.s  = GetGadgetText(#Gadget_mysqltest_passwordbox)
  db.s      = GetGadgetText(#Gadget_mysqltest_databasebox)
  port.l    = Val(GetGadgetText(#Gadget_mysqltest_portbox))
  If MySQL_Real_Connect(dbHnd, host, user, passwd, db, port.l, 32) <> 0
    SetWindowTitle(#Window_mysqltest, #MainTitle + "Connected and getting your data, please wait")
    Gosub GetSqlData
  Else
    MySQL_GetError(dbHnd, 1)
    Connect.l = 0
  EndIf
Else
  SetWindowTitle(#Window_mysqltest, #MainTitle + "SQL library was never loaded so we cannot connect to the database!!")
EndIf

Return

;============================================================================================================================
; Retrieve and display the data to the user now
;============================================================================================================================

GetSqlData:

If GetGadgetState(#Gadget_mysqltest_clearlist) = 1
  ClearGadgetItems(#Gadget_mysqltest_datalist)
EndIf

Result = MySQL_Real_Query (dbHnd, Query.s)                        ; Run the given query

If Result                                                         ; If the query didn't work, give an error
  MySQL_GetError(dbHnd, 1)
Else
  ;----------------------------------------------------------------------------------------------
  *mysqlResult = MySQL_Use_Result(dbHnd)                          ; If data returned, use the result
  If *mysqlResult = 0
    If MySQL_Field_Count(dbHnd)                                   ; No fields are returned so that's an error
      MySQL_GetError(dbHnd, 1)
    Else
      MySQL_GetError(dbHnd, 1)                                    ; Fields are returned, so no error but query didn't return data
    EndIf
  Else
    ;----------------------------------------------------------------------------------------------
    affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)       ; How many rows affected
    fieldNum  = CallFunction(#libmysql,"mysql_num_fields", *mysqlResult)  ; How many fields
    rowsNum   = CallFunction(#libmysql,"mysql_num_rows", *mysqlResult)    ; How many rows
    ;--------------------------------------------------------------------------------------------
    Repeat                                                               ; Sequentially process all returned data
      *mysqlRow = MySQL_Fetch_Row(*mysqlResult)
      If *mysqlRow <> 0
        *mysqlLen = MySQL_Fetch_Lengths(*mysqlResult)         
        row = ""
        For j = 1 To fieldNum                                            ; Length of given field
          length = PeekL(*mysqlLen + 4 * (j - 1))           
          fieldptr = PeekL(*mysqlRow + 4 * (j - 1))
          If fieldptr > 0
            content.s = PeekS(fieldptr, length)
          Else
            content = "NULL"                                              ; Zero pointer returned means empty field
          EndIf
          row = row + content + ";"                                       ; Debug content (individual collumns)
        Next j
        AddGadgetItem(#Gadget_mysqltest_datalist, -1, row)              ; Dump the row to the listbox
      EndIf
    Until *mysqlRow = 0
    result = MySQL_Free_Result(*mysqlResult)
    MySQL_Close(dbHnd)                                                    ; Close the database when no longer needed
    ;--------------------------------------------------------------------------------------------
  EndIf
  ;----------------------------------------------------------------------------------------------
EndIf

SetWindowTitle(#Window_mysqltest, #MainTitle + "Found all matching data, nothing left to do.")

Return

;============================================================================================================================
; Dump the list to disk at the users' request
;============================================================================================================================

DumpListToDisk:

Lines.l = CountGadgetItems(#Gadget_mysqltest_datalist)   ; How many lines to save

DumpList.s = SaveFileRequester("Save returned query", "QueryDump.txt", "Text (*.txt)|*.txt|All files (*.*)|*.*", 0)

If CreateFile(0, DumpList.s) <> 0
  For ListItems = 0 To Lines - 1
    CurrentLine.s = GetGadgetItemText(#Gadget_mysqltest_datalist, ListItems, 0)
    WriteStringN(0,CurrentLine.s)
  Next ListItems
  CloseFile(0)
EndIf

Return

[clipper]

Hi Fang and Mx2

very nice code!
works well!

best regards
tom

[Max.²]

Guess he ran out of "a"s

[Beach]

I found the MySQL code Max posted some time ago and I am successfully using it on a project for work. Thanks again Max!

PS, it really would be nice to have this kind of connectivity as a library.

[Fangbeast]

Guess he ran out of "a"s

Maybe he thought you were an Assembler instruction?

On a serious note, your code is running my shopping list database to a MySQL server. And I'm thinking of adding more functions to that GUI I made for others so thanks once again!!

[Fangbeast]

I found the MySQL code Max posted some time ago and I am successfully using it on a project for work. Thanks again Max!

PS, it really would be nice to have this kind of connectivity as a library.

Beach, once you strip out mu GUI specific stuff, it's easy enough to modularise the code but...if you really need a library out of this stuff, why not use the library import tool in the purebasic directories? (At least, I heard there is one there).. I should ask some of the experts in the forum what can be done.

[Max.²]

I found the MySQL code Max posted some time ago and I am successfully using it on a project for work. Thanks again Max!

PS, it really would be nice to have this kind of connectivity as a library.

I am thinking a long time now about making a MySQL library; but laziness always prevent me so far to try to work through the whole code and make it a PB library compileable one.

Maybe it will work to import it with freak's LibImporter (with the advantage that new versions of the static lib can be converted easily) and to create an additional library that wraps some functions for a more convenient use. Got to check that first though.

[the.weavster]

I understand it's possible to connect to MySQL directly using TCP/IP, no ODBC and no libmysql.dll necessary.

Does anybody know how to do this?

Presumably you would use SendNetworkData() and ReceiveNetworkData() somehow.

[Max.²]

I think this would be a doable, yet an extremly timeconsuming task, not worth the efforts as there are too many things to consider (depending on the server to connect to) to make it reliably working. That is my opinion, of course.

If you really plan on doing such, grab the source code and have a look at libmysql.c and convert it to purebasic according to your needs.

If you just want to get rid of packaging the dll with your app, then a better way would be to make a PB lib out of it (I still plan to do, but by far too much work at the moment and the next few months).

If you want to sell under a non-GPL license without the need to pay for it, then it will be really tough, as I doubt that you'd be allowed to "just" convert the original source.

Anyway, this is the C code of the mysql_real_connect function:

Code: Select all

MYSQL * STDCALL
mysql_real_connect(MYSQL *mysql,const char *host, const char *user,
		   const char *passwd, const char *db,
		   uint port, const char *unix_socket,uint client_flag)
{
  char		buff[NAME_LEN+USERNAME_LENGTH+100],charset_name_buff[16];
  char		*end,*host_info,*charset_name;
  my_socket	sock;
  in_addr_t	ip_addr;
  struct	sockaddr_in sock_addr;
  ulong		pkt_length;
  NET		*net= &mysql->net;
#ifdef __WIN__
  HANDLE	hPipe=INVALID_HANDLE_VALUE;
#endif
#ifdef HAVE_SYS_UN_H
  struct	sockaddr_un UNIXaddr;
#endif
  init_sigpipe_variables
  DBUG_ENTER("mysql_real_connect");
  LINT_INIT(host_info);

  DBUG_PRINT("enter",("host: %s  db: %s  user: %s",
		      host ? host : "(Null)",
		      db ? db : "(Null)",
		      user ? user : "(Null)"));

  /* Don't give sigpipe errors if the client doesn't want them */
  set_sigpipe(mysql);
  net->vio = 0;				/* If something goes wrong */
  /* use default options */
  if (mysql->options.my_cnf_file || mysql->options.my_cnf_group)
  {
    mysql_read_default_options(&mysql->options,
			       (mysql->options.my_cnf_file ?
				mysql->options.my_cnf_file : "my"),
			       mysql->options.my_cnf_group);
    my_free(mysql->options.my_cnf_file,MYF(MY_ALLOW_ZERO_PTR));
    my_free(mysql->options.my_cnf_group,MYF(MY_ALLOW_ZERO_PTR));
    mysql->options.my_cnf_file=mysql->options.my_cnf_group=0;
  }

  /* Some empty-string-tests are done because of ODBC */
  if (!host || !host[0])
    host=mysql->options.host;
  if (!user || !user[0])
    user=mysql->options.user;
  if (!passwd)
  {
    passwd=mysql->options.password;
#ifndef DONT_USE_MYSQL_PWD
    if (!passwd)
      passwd=getenv("MYSQL_PWD");  /* get it from environment (haneke) */
#endif
  }
  if (!db || !db[0])
    db=mysql->options.db;
  if (!port)
    port=mysql->options.port;
  if (!unix_socket)
    unix_socket=mysql->options.unix_socket;

  mysql->reconnect=1;			/* Reconnect as default */
  mysql->server_status=SERVER_STATUS_AUTOCOMMIT;

  /*
  ** Grab a socket and connect it to the server
  */

#if defined(HAVE_SYS_UN_H)
  if ((!host || !strcmp(host,LOCAL_HOST)) && (unix_socket || mysql_unix_port))
  {
    host=LOCAL_HOST;
    if (!unix_socket)
      unix_socket=mysql_unix_port;
    host_info=(char*) ER(CR_LOCALHOST_CONNECTION);
    DBUG_PRINT("info",("Using UNIX sock '%s'",unix_socket));
    if ((sock = socket(AF_UNIX,SOCK_STREAM,0)) == SOCKET_ERROR)
    {
      net->last_errno=CR_SOCKET_CREATE_ERROR;
      sprintf(net->last_error,ER(net->last_errno),socket_errno);
      goto error;
    }
    net->vio = vio_new(sock, VIO_TYPE_SOCKET, TRUE);
    bzero((char*) &UNIXaddr,sizeof(UNIXaddr));
    UNIXaddr.sun_family = AF_UNIX;
    strmake(UNIXaddr.sun_path, unix_socket, sizeof(UNIXaddr.sun_path)-1);
    if (my_connect(sock,(struct sockaddr *) &UNIXaddr, sizeof(UNIXaddr),
		 mysql->options.connect_timeout) <0)
    {
      DBUG_PRINT("error",("Got error %d on connect to local server",socket_errno));
      net->last_errno=CR_CONNECTION_ERROR;
      sprintf(net->last_error,ER(net->last_errno),unix_socket,socket_errno);
      goto error;
    }
  }
  else
#elif defined(__WIN__)
  {
    if ((unix_socket ||
	 !host && is_NT() ||
	 host && !strcmp(host,LOCAL_HOST_NAMEDPIPE) ||
	 mysql->options.named_pipe || !have_tcpip))
    {
      sock=0;
      if ((hPipe=create_named_pipe(net, mysql->options.connect_timeout,
				   (char**) &host, (char**) &unix_socket)) ==
	  INVALID_HANDLE_VALUE)
      {
	DBUG_PRINT("error",
		   ("host: '%s'  socket: '%s'  named_pipe: %d  have_tcpip: %d",
		    host ? host : "<null>",
		    unix_socket ? unix_socket : "<null>",
		    (int) mysql->options.named_pipe,
		    (int) have_tcpip));
	if (mysql->options.named_pipe ||
	    (host && !strcmp(host,LOCAL_HOST_NAMEDPIPE)) ||
	    (unix_socket && !strcmp(unix_socket,MYSQL_NAMEDPIPE)))
	{
	  net->last_errno= CR_SERVER_LOST;
	  strmov(net->last_error,ER(net->last_errno));
	  goto error;		/* User only requested named pipes */
	}
	/* Try also with TCP/IP */
      }
      else
      {
	net->vio=vio_new_win32pipe(hPipe);
	sprintf(host_info=buff, ER(CR_NAMEDPIPE_CONNECTION), host,
		unix_socket);
      }
    }
  }
  if (hPipe == INVALID_HANDLE_VALUE)
#endif
  {
    unix_socket=0;				/* This is not used */
    if (!port)
      port=mysql_port;
    if (!host)
      host=LOCAL_HOST;
    sprintf(host_info=buff,ER(CR_TCP_CONNECTION),host);
    DBUG_PRINT("info",("Server name: '%s'.  TCP sock: %d", host,port));
    /* _WIN64 ;  Assume that the (int) range is enough for socket() */
    if ((sock = (my_socket) socket(AF_INET,SOCK_STREAM,0)) == SOCKET_ERROR)
    {
      net->last_errno=CR_IPSOCK_ERROR;
      sprintf(net->last_error,ER(net->last_errno),socket_errno);
      goto error;
    }
    net->vio = vio_new(sock,VIO_TYPE_TCPIP,FALSE);
    bzero((char*) &sock_addr,sizeof(sock_addr));
    sock_addr.sin_family = AF_INET;

    /*
    ** The server name may be a host name or IP address
    */

    if ((int) (ip_addr = inet_addr(host)) != (int) INADDR_NONE)
    {
      memcpy_fixed(&sock_addr.sin_addr,&ip_addr,sizeof(ip_addr));
    }
    else
    {
      int tmp_errno;
      struct hostent tmp_hostent,*hp;
      gethostbyname_buff buff2;
      hp = my_gethostbyname_r(host,&tmp_hostent,buff2.buff,sizeof(buff2),
			      &tmp_errno);
      if (!hp)
      {
	my_gethostbyname_r_free();
	net->last_errno=CR_UNKNOWN_HOST;
	sprintf(net->last_error, ER(CR_UNKNOWN_HOST), host, tmp_errno);
	goto error;
      }
      memcpy(&sock_addr.sin_addr, hp->h_addr,
             min(sizeof(sock_addr.sin_addr), (size_t) hp->h_length));
      my_gethostbyname_r_free();
    }
    sock_addr.sin_port = (ushort) htons((ushort) port);
    if (my_connect(sock,(struct sockaddr *) &sock_addr, sizeof(sock_addr),
		 mysql->options.connect_timeout) <0)
    {
      DBUG_PRINT("error",("Got error %d on connect to '%s'",socket_errno,host));
      net->last_errno= CR_CONN_HOST_ERROR;
      sprintf(net->last_error ,ER(CR_CONN_HOST_ERROR), host, socket_errno);
      goto error;
    }
  }

  if (!net->vio || my_net_init(net, net->vio))
  {
    vio_delete(net->vio);
    net->vio = 0;
    net->last_errno=CR_OUT_OF_MEMORY;
    strmov(net->last_error,ER(net->last_errno));
    goto error;
  }
  vio_keepalive(net->vio,TRUE);

  /* Get version info */
  mysql->protocol_version= PROTOCOL_VERSION;	/* Assume this */
  if (mysql->options.connect_timeout &&
      vio_poll_read(net->vio, mysql->options.connect_timeout))
  {
    net->last_errno= CR_SERVER_LOST;
    strmov(net->last_error,ER(net->last_errno));
    goto error;
  }
  if ((pkt_length=net_safe_read(mysql)) == packet_error)
    goto error;

  /* Check if version of protocoll matches current one */

  mysql->protocol_version= net->read_pos[0];
  DBUG_DUMP("packet",(char*) net->read_pos,10);
  DBUG_PRINT("info",("mysql protocol version %d, server=%d",
		     PROTOCOL_VERSION, mysql->protocol_version));
  if (mysql->protocol_version != PROTOCOL_VERSION)
  {
    net->last_errno= CR_VERSION_ERROR;
    sprintf(net->last_error, ER(CR_VERSION_ERROR), mysql->protocol_version,
	    PROTOCOL_VERSION);
    goto error;
  }
  end=strend((char*) net->read_pos+1);
  mysql->thread_id=uint4korr(end+1);
  end+=5;
  strmake(mysql->scramble_buff,end,8);
  end+=9;
  if (pkt_length >= (uint) (end+1 - (char*) net->read_pos))
    mysql->server_capabilities=uint2korr(end);
  if (pkt_length >= (uint) (end+18 - (char*) net->read_pos))
  {
    /* New protocol with 16 bytes to describe server characteristics */
    mysql->server_language=end[2];
    mysql->server_status=uint2korr(end+3);
  }

  /* Set character set */
  if ((charset_name=mysql->options.charset_name))
  {
    const char *save=charsets_dir;
    if (mysql->options.charset_dir)
      charsets_dir=mysql->options.charset_dir;
    mysql->charset=get_charset_by_name(mysql->options.charset_name,
                                       MYF(MY_WME));
    charsets_dir=save;
  }
  else if (mysql->server_language)
  {
    charset_name=charset_name_buff;
    sprintf(charset_name,"%d",mysql->server_language);	/* In case of errors */
    if (!(mysql->charset =
	  get_charset((uint8) mysql->server_language, MYF(0))))
      mysql->charset = default_charset_info; /* shouldn't be fatal */

  }
  else
    mysql->charset=default_charset_info;

  if (!mysql->charset)
  {
    net->last_errno=CR_CANT_READ_CHARSET;
    if (mysql->options.charset_dir)
      sprintf(net->last_error,ER(net->last_errno),
              charset_name ? charset_name : "unknown",
              mysql->options.charset_dir);
    else
    {
      char cs_dir_name[FN_REFLEN];
      get_charsets_dir(cs_dir_name);
      sprintf(net->last_error,ER(net->last_errno),
              charset_name ? charset_name : "unknown",
              cs_dir_name);
    }
    goto error;
  }

  /* Save connection information */
  if (!user) user="";
  if (!passwd) passwd="";
  if (!my_multi_malloc(MYF(0),
		       &mysql->host_info, (uint) strlen(host_info)+1,
		       &mysql->host,      (uint) strlen(host)+1,
		       &mysql->unix_socket,unix_socket ?
		       (uint) strlen(unix_socket)+1 : (uint) 1,
		       &mysql->server_version,
		       (uint) (end - (char*) net->read_pos),
		       NullS) ||
      !(mysql->user=my_strdup(user,MYF(0))) ||
      !(mysql->passwd=my_strdup(passwd,MYF(0))))
  {
    strmov(net->last_error, ER(net->last_errno=CR_OUT_OF_MEMORY));
    goto error;
  }
  strmov(mysql->host_info,host_info);
  strmov(mysql->host,host);
  if (unix_socket)
    strmov(mysql->unix_socket,unix_socket);
  else
    mysql->unix_socket=0;
  strmov(mysql->server_version,(char*) net->read_pos+1);
  mysql->port=port;
  client_flag|=mysql->options.client_flag;

  /* Send client information for access check */
  client_flag|=CLIENT_CAPABILITIES;

#ifdef HAVE_OPENSSL
  if (mysql->options.ssl_key || mysql->options.ssl_cert ||
      mysql->options.ssl_ca || mysql->options.ssl_capath ||
      mysql->options.ssl_cipher)
    mysql->options.use_ssl= 1;
  if (mysql->options.use_ssl)
    client_flag|=CLIENT_SSL;
#endif /* HAVE_OPENSSL */
  if (db)
    client_flag|=CLIENT_CONNECT_WITH_DB;
#ifdef HAVE_COMPRESS
  if ((mysql->server_capabilities & CLIENT_COMPRESS) &&
      (mysql->options.compress || (client_flag & CLIENT_COMPRESS)))
    client_flag|=CLIENT_COMPRESS;		/* We will use compression */
  else
#endif
    client_flag&= ~CLIENT_COMPRESS;

#ifdef HAVE_OPENSSL
  if ((mysql->server_capabilities & CLIENT_SSL) &&
      (mysql->options.use_ssl || (client_flag & CLIENT_SSL)))
  {
    DBUG_PRINT("info", ("Changing IO layer to SSL"));
    client_flag |= CLIENT_SSL;
  }
  else
  {
    if (client_flag & CLIENT_SSL)
    {
      DBUG_PRINT("info", ("Leaving IO layer intact because server doesn't support SSL"));
    }
    client_flag &= ~CLIENT_SSL;
  }
#endif /* HAVE_OPENSSL */

  int2store(buff,client_flag);
  mysql->client_flag=client_flag;

#ifdef HAVE_OPENSSL
  /*
    Oops.. are we careful enough to not send ANY information without
    encryption?
  */
  if (client_flag & CLIENT_SSL)
  {
    struct st_mysql_options *options= &mysql->options;
    if (my_net_write(net,buff,(uint) (2)) || net_flush(net))
    {
      net->last_errno= CR_SERVER_LOST;
      strmov(net->last_error,ER(net->last_errno));
      goto error;
    }
    /* Do the SSL layering. */
    if (!(mysql->connector_fd=
	  (gptr) new_VioSSLConnectorFd(options->ssl_key,
				       options->ssl_cert,
				       options->ssl_ca,
				       options->ssl_capath,
				       options->ssl_cipher)))
    {
      net->last_errno= CR_SSL_CONNECTION_ERROR;
      strmov(net->last_error,ER(net->last_errno));
      goto error;
    }
    DBUG_PRINT("info", ("IO layer change in progress..."));
    if(sslconnect((struct st_VioSSLConnectorFd*)(mysql->connector_fd),
	        mysql->net.vio, (long) (mysql->options.connect_timeout)))
    {
      net->last_errno= CR_SSL_CONNECTION_ERROR;
      strmov(net->last_error,ER(net->last_errno));
      goto error;    
    }
    DBUG_PRINT("info", ("IO layer change done!"));
  }
#endif /* HAVE_OPENSSL */

  DBUG_PRINT("info",("Server version = '%s'  capabilites: %ld  status: %d  client_flag: %d",
		     mysql->server_version,mysql->server_capabilities,
		     mysql->server_status, client_flag));

  /* This needs to be changed as it's not useful with big packets */
  int3store(buff+2,max_allowed_packet);
  if (user && user[0])
    strmake(buff+5,user,32);			/* Max user name */
  else
    read_user_name((char*) buff+5);
#ifdef _CUSTOMCONFIG_
#include "_cust_libmysql.h";
#endif
  DBUG_PRINT("info",("user: %s",buff+5));
  end=scramble(strend(buff+5)+1, mysql->scramble_buff, passwd,
	       (my_bool) (mysql->protocol_version == 9));
  if (db && (mysql->server_capabilities & CLIENT_CONNECT_WITH_DB))
  {
    end=strmake(end+1,db,NAME_LEN);
    mysql->db=my_strdup(db,MYF(MY_WME));
    db=0;
  }
  if (my_net_write(net,buff,(ulong) (end-buff)) || net_flush(net))
  {
    net->last_errno= CR_SERVER_LOST;
    strmov(net->last_error,ER(net->last_errno));
    goto error;
  }
  if (net_safe_read(mysql) == packet_error)
    goto error;
  if (client_flag & CLIENT_COMPRESS)		/* We will use compression */
    net->compress=1;
  if (mysql->options.max_allowed_packet)
    net->max_packet_size= mysql->options.max_allowed_packet;
#ifdef CHECK_LICENSE 
  if (check_license(mysql))
    goto error;
#endif
  if (db && mysql_select_db(mysql,db))
    goto error;
  if (mysql->options.init_command)
  {
    my_bool reconnect=mysql->reconnect;
    mysql->reconnect=0;
    if (mysql_query(mysql,mysql->options.init_command))
      goto error;
    mysql_free_result(mysql_use_result(mysql));
    mysql->reconnect=reconnect;
  }

  if (mysql->options.rpl_probe && mysql_rpl_probe(mysql))
    goto error;

  DBUG_PRINT("exit",("Mysql handler: %lx",mysql));
  reset_sigpipe(mysql);
  DBUG_RETURN(mysql);

error:
  reset_sigpipe(mysql);
  DBUG_PRINT("error",("message: %u (%s)",net->last_errno,net->last_error));
  {
    /* Free alloced memory */
    my_bool free_me=mysql->free_me;
    end_server(mysql);
    mysql->free_me=0;
    mysql_close(mysql);
    mysql->free_me=free_me;
  }
  DBUG_RETURN(0);
}

Congratulations for reading that far.

[Fangbeast]

I've removed the "Clear list" flag because of the following changes..

1. When the query is executed, the client checks how many fields are returned and adds the number of collumns required for each field to display nicely. Obviously, this would not work with a "Don't clear list" option because each colun might have different number of fields.

2. Between each call, the number of collumns set gets removed for the next query.

3. Columns are autosized to make it more readable.

Replace the routine from the original with the below code.

Code: Select all

;============================================================================================================================
; Retrieve and display the data to the user now
;============================================================================================================================

GetSqlData:

  ClearGadgetItemList(#Gadget_mysqltest_datalist)
  
  If fieldNum <> 0                                                  ; Remove columns from previous query
    For colremove = 1 To fieldNum - 1
      RemoveGadgetColumn(#Gadget_mysqltest_datalist, 1)
    Next colremove
    fieldNum = 0
  EndIf
    
  Result = MySQL_Real_Query (dbHnd, Query.s)                        ; Run the given query

  If Result                                                         ; If the query didn't work, give an error
    MySQL_GetError(dbHnd, 1)
  Else 
    ;----------------------------------------------------------------------------------------------
    *mysqlResult = MySQL_Use_Result(dbHnd)                          ; If data returned, use the result
    If *mysqlResult = 0
      If MySQL_Field_Count(dbHnd)                                   ; No fields are returned so that's an error
        MySQL_GetError(dbHnd, 1) 
      Else
        MySQL_GetError(dbHnd, 1)                                    ; Fields are returned, so no error but query didn't return data 
      EndIf 
    Else
    ;----------------------------------------------------------------------------------------------
      affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)       ; How many rows affected
      fieldNum  = CallFunction(#libmysql,"mysql_num_fields", *mysqlResult)  ; How many fields
      rowsNum   = CallFunction(#libmysql,"mysql_num_rows", *mysqlResult)    ; How many rows
      ;--------------------------------------------------------------------------------------------
      For coladd = 1 To fieldNum - 1
        AddGadgetColumn(#Gadget_mysqltest_datalist, 1, "Data", 100)
      Next coladd
      ;--------------------------------------------------------------------------------------------
      Repeat                                                               ; Sequentially process all returned data
        *mysqlRow = MySQL_Fetch_Row(*mysqlResult) 
        If *mysqlRow <> 0 
          *mysqlLen = MySQL_Fetch_Lengths(*mysqlResult)          
          row = "" 
          For j = 1 To fieldNum                                            ; Length of given field
            length = PeekL(*mysqlLen + 4 * (j - 1))            
            fieldptr = PeekL(*mysqlRow + 4 * (j - 1)) 
            If fieldptr > 0 
              content.s = PeekS(fieldptr, length) 
            Else 
              content = "NULL"                                              ; Zero pointer returned means empty field
            EndIf 
            row = row + content + ";"                                       ; Debug content (individual collumns)
          Next j
            rowstring.s = ReplaceString(row, ";", Chr(10), 1,1)
            AddGadgetItem(#Gadget_mysqltest_datalist, -1, rowstring.s)              ; Dump the row to the listbox
        EndIf 
      Until *mysqlRow = 0 
      result.l = MySQL_Free_Result(*mysqlResult)
      MySQL_Close(dbHnd)                                                    ; Close the database when no longer needed
      ;--------------------------------------------------------------------------------------------
    EndIf
    ;----------------------------------------------------------------------------------------------
  EndIf

  For WidthSet = 0 To fieldNum - 1
    SendMessage_(GadgetID(#Gadget_mysqltest_datalist), #LVM_SETCOLUMNWIDTH, WidthSet, #LVSCW_AUTOSIZE)
  Next WidthSet

  SetWindowTitle(#Window_mysqltest, #MainTitle + "Found all matching data, nothing left to do.")

Return

[Fangbeast]

Replace the original DumpToDisk routine with this modified one that takes into account the number of columns in the query and modifies itself each time to dump the data witht he correct number of columns.

Code: Select all

;============================================================================================================================
; Dump the list to disk at the users' request
;============================================================================================================================

DumpListToDisk:

  Lines.l = CountGadgetItems(#Gadget_mysqltest_datalist)                    ; How many lines to save
  
  DumpList.s = SaveFileRequester("Save returned query", "QueryDump.txt", "Text (*.txt)|*.txt|All files (*.*)|*.*", 0)

  If CreateFile(0, DumpList.s) <> 0
    ;----------------------------------------------------------------------------------------------
    For ListItems = 0 To Lines - 1
      ;--------------------------------------------------------------------------------------------
      For totalFields = 0 To fieldNum - 1
        currentField.s = GetGadgetItemText(#Gadget_mysqltest_datalist, ListItems, totalFields)
        outString.s + currentField.s + ";"
      Next totalFields
      WriteStringN(outString.s)
      outString.s = ""
      ;--------------------------------------------------------------------------------------------
    Next ListItems
    CloseFile(0)
    ;----------------------------------------------------------------------------------------------
  Else
    SetWindowTitle(#Window_mysqltest, #MainTitle + "Cannot save the list to disk, something went wrong")
  EndIf
  
Return

[Fangbeast]

Here is the whole changed code, along with the colours that I like (evil grin)

Code: Select all

;==============================================================================================================================
; Please note that all the MySQL direct database code I found in the PureBasic forum, done by Max2 and not by me. He has 
; given me permission to use it as I see fit in a tutorial and it is hoped that this mini program will help someone get to
; grips with using MySQL server queries directly, without having to install either an ODBC driver or having to go through
; the ODBC control panel to setup a database each time.
;
; I only put this program together using his code, no big thing. Use it as you see fit.
;
; *NOTE* There is no syntax checking on database statements, I simply don't have that sort of time on my hands, nor the
; experience needed. This should be enough for most people.
;
; If you like it, say thanks to Mx2 and (me too maybe???). If you don't like it, I don't want to know. Fang.
;==============================================================================================================================
; Predefine my global structure types so I don't have to keep adding the type throughout the program
;==============================================================================================================================

dbHnd.l     
SQL.s 
row.s 
i.l 
j.l 
affRows.l 
fieldNum.l 
rowsNum.l 

;==============================================================================================================================
; Any global variables my programs need, particularly the aliases MySQL library functions
;==============================================================================================================================

Global CFF_MySQL_Init.l,        CFF_MySQL_ERRNO.l
Global CFF_MySQL_ERROR.l,       CFF_MySQL_Real_Connect.l
Global CFF_MySQL_Real_Query.l,  CFF_MySQL_Store_Result.l
Global CFF_MySQL_Field_Count.l, CFF_MySQL_Use_Result.l
Global CFF_MySQL_Fetch_Row.l,   CFF_MySQL_Fetch_Lengths.l
Global CFF_MySQL_Free_Result.l, CFF_MySQL_Close.l
Global Yellow, Green, Blue

;==============================================================================================================================
; Get the current directory. May be used for various functions
;==============================================================================================================================

CurrentDir.s = Space(512)
Result = GetCurrentDirectory_(Len(CurrentDir), @CurrentDir)

;==============================================================================================================================
; Specific constans for the MySQL functions
;==============================================================================================================================

#libmysql               = 1 

#MySQL_CLIENT_COMPRESS  = 32 

;============================================================================================================================
; Main window title which we will overwrite with other information
;============================================================================================================================

#MainTitle = "MySQL - "

;============================================================================================================================
; Object colouring in the callback
;============================================================================================================================

;Colour  = RGB($FF,$FF,$AA)

Yellow  = CreateSolidBrush_($70DCFC)
Green   = CreateSolidBrush_($7BDF84)
Blue    = CreateSolidBrush_($E5B91A)

;============================================================================================================================
; Constants
;============================================================================================================================

Enumeration 1
  #Window_mysqltest
EndEnumeration

#WindowIndex = #PB_Compiler_EnumerationValue

Enumeration 1
  #Gadget_mysqltest_mainframe
  #Gadget_mysqltest_datalist
  #Gadget_mysqltest_controlframe
  #Gadget_mysqltest_querylabel
  #Gadget_mysqltest_querybox
  #Gadget_mysqltest_loginframe
  #Gadget_mysqltest_hostlabel
  #Gadget_mysqltest_hostbox
  #Gadget_mysqltest_userlabel
  #Gadget_mysqltest_userbox
  #Gadget_mysqltest_passwordlabel
  #Gadget_mysqltest_passwordbox
  #Gadget_mysqltest_databaselabel
  #Gadget_mysqltest_databasebox
  #Gadget_mysqltest_portlabel
  #Gadget_mysqltest_portbox
  #Gadget_mysqltest_savelogin
  #Gadget_mysqltest_dumpbutton
  #Gadget_mysqltest_otherframe
  #Gadget_mysqltest_helpbutton
  #Gadget_mysqltest_exitbutton
  #Gadget_mysqltest_return
EndEnumeration

#GadgetIndex = #PB_Compiler_EnumerationValue

;============================================================================================================================
; Cute bubble tooltips
;============================================================================================================================

Procedure BalloonTip(bWindow.l, bGadget.l, bText.s)
  ToolTipControl = CreateWindowEx_(0, "ToolTips_Class32", "", $D0000000 | $40, 0, 0, 0, 0, WindowID(bWindow), 0, GetModuleHandle_(0), 0)
  SendMessage_(ToolTipControl, 1044, 0, 0)
  SendMessage_(ToolTipControl, 1043, $F3D97A, 0)
  SendMessage_(ToolTipControl, 1048, 0, 180)
  Button.TOOLINFO\cbSize = SizeOf(TOOLINFO)
  Button\uFlags = $11
  Button\hWnd = GadgetID(bGadget)
  Button\uId = GadgetID(bGadget)
  Button\lpszText = @bText
  SendMessage_(ToolTipControl, $0404, 0, Button)
EndProcedure

;==============================================================================================================================
; Program window
;==============================================================================================================================

Procedure.l Window_mysqltest()
  If OpenWindow(#Window_mysqltest,175,0,800,555,#PB_Window_ScreenCentered|#PB_Window_Invisible,#MainTitle)
    AddKeyboardShortcut(#Window_mysqltest, #PB_Shortcut_Return, #Gadget_mysqltest_return)
    Brush.LOGBRUSH\lbColor = 7396604
    SetClassLong_(WindowID(#Window_mysqltest),#GCL_HBRBACKGROUND,CreateBrushIndirect_(Brush))
    If CreateGadgetList(WindowID(#Window_mysqltest))
      Frame3DGadget(#Gadget_mysqltest_mainframe,0,0,640,510,"")
      ListIconGadget(#Gadget_mysqltest_datalist,5,10,630,495,"itemslist",100,#PB_ListIcon_GridLines|#PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection)
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_datalist,"All items returned from a properly formatted SQL query will end up in this list and be cleared in  the next call")
        SendMessage_(GadgetID(#Gadget_mysqltest_datalist),#LVM_SETTEXTCOLOR, 0, $040404) 
        SendMessage_(GadgetID(#Gadget_mysqltest_datalist),#LVM_SETBKCOLOR,0,7396604)
        SendMessage_(GadgetID(#Gadget_mysqltest_datalist),#LVM_SETTEXTBKCOLOR,0,7396604)
      Frame3DGadget(#Gadget_mysqltest_controlframe,0,510,640,45,"")
      TextGadget(#Gadget_mysqltest_querylabel,10,530,60,15,"SQL Query")
      StringGadget(#Gadget_mysqltest_querybox,70,525,558,20,"")
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_querybox,"Type in a properly formatted SQL query in here and press ENTER/RETURN to execute it")
      Frame3DGadget(#Gadget_mysqltest_loginframe,645,0,155,510,"")
      TextGadget(#Gadget_mysqltest_hostlabel,655,15,135,15,"Host IP or host name")
      StringGadget(#Gadget_mysqltest_hostbox,655,30,135,20,"")
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_hostbox,"Type in the name of the system that hosts the database or the IP version")
      TextGadget(#Gadget_mysqltest_userlabel,655,55,135,15,"User name")
      StringGadget(#Gadget_mysqltest_userbox,655,70,135,20,"",#PB_String_LowerCase)
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_userbox,"Type in the name of the user who is registered for use with that database")
      TextGadget(#Gadget_mysqltest_passwordlabel,655,95,135,15,"Password")
      StringGadget(#Gadget_mysqltest_passwordbox,655,110,135,20,"",#PB_String_Password|#PB_String_LowerCase)
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_passwordbox,"Type in the login password of the user that is registered for use with that database")
      TextGadget(#Gadget_mysqltest_databaselabel,655,135,135,15,"Database name")
      StringGadget(#Gadget_mysqltest_databasebox,655,150,135,20,"",#PB_String_LowerCase)
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_databasebox,"Type in the name of the database that you wish to access on the remote system")
      TextGadget(#Gadget_mysqltest_portlabel,655,175,135,15,"Port number")
      StringGadget(#Gadget_mysqltest_portbox,655,190,135,20,"",#PB_String_Numeric)
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_portbox,"Type in the port number that the database server allows queries on")
      CheckBoxGadget(#Gadget_mysqltest_savelogin,655,220,135,15,"Save current login")
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_savelogin,"Check this box if you want to save the current login to be auto-loaded next time you start this program")
      ButtonGadget(#Gadget_mysqltest_dumpbutton,655,480,135,20,"Dump list to disk")
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_dumpbutton,"Press this button to dump the results of the query to a disk file")
      Frame3DGadget(#Gadget_mysqltest_otherframe,645,510,155,45,"")
      ButtonGadget(#Gadget_mysqltest_helpbutton,655,525,60,20,"Help")
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_helpbutton,"Press this button to show any help file linked to this program")
      ButtonGadget(#Gadget_mysqltest_exitbutton,730,525,60,20,"Exit")
        BalloonTip(#Window_mysqltest,#Gadget_mysqltest_exitbutton,"Press this button to exit this program immediately")
      HideWindow(#Window_mysqltest,0)
      ProcedureReturn WindowID()
    EndIf
  EndIf
EndProcedure

;==============================================================================================================================
; All of the aliased MySQL library routines and the library loader
;==============================================================================================================================

Procedure MySQL_Init() 
  Shared CurrentDir.s
  If OpenLibrary(#libmysql, "C:\Development\libmySQL.dll") 
    CFF_MySQL_Init          = IsFunction(#libmysql, "mysql_init") 
    CFF_MySQL_ErrNo         = IsFunction(#libmysql, "mysql_errno") 
    CFF_MYSQL_Error         = IsFunction(#libmysql, "mysql_error") 
    CFF_MySQL_Real_Connect  = IsFunction(#libmysql, "mysql_real_connect") 
    CFF_MySQL_Real_Query    = IsFunction(#libmysql, "mysql_real_query") 
    CFF_MySQL_Store_Result  = IsFunction(#libmysql, "mysql_store_result") 
    CFF_MySQL_Field_Count   = IsFunction(#libmysql, "mysql_field_count") 
    CFF_MySQL_Use_Result    = IsFunction(#libmysql, "mysql_use_result") 
    CFF_MySQL_Fetch_Row     = IsFunction(#libmysql, "mysql_fetch_row") 
    CFF_MySQL_Fetch_Lengths = IsFunction(#libmysql, "mysql_fetch_lengths") 
    CFF_MySQL_Free_Result   = IsFunction(#libmysql, "mysql_free_result") 
    CFF_MySQL_Close         = IsFunction(#libmysql, "mysql_close") 
    ProcedureReturn CallFunctionFast (CFF_MySQL_Init, dbHnd) 
  EndIf 
EndProcedure 

Procedure.s MySQL_GetError(db_ID, requester) 
  Protected Errormsg.s, i.l, Error.l 
  If CallFunctionFast(CFF_MySQL_ErrNo, db_ID) > 0 
    *Error = CallFunctionFast(CFF_MySQL_Error, db_ID)        
    Errormsg = PeekS(*Error) 
    If requester 
      SetWindowTitle(#Window_mysqltest, #MainTitle + Errormsg) 
    EndIf 
  EndIf 
  ProcedureReturn Errormsg 
EndProcedure 

Procedure MySQL_Real_Connect(dbHnd, host.s, user.s, password.s, db.s, port.l, options.l) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Connect, dbHnd, host, user, password.s, db, port, 0, options) 
EndProcedure 

Procedure MySQL_Real_Query(dbHnd, Query.s) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Query, dbHnd, Query, Len(Query)) 
EndProcedure 

Procedure MySQL_Store_Result(dbHnd) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Store_Result, dbHnd) 
EndProcedure 

Procedure MySQL_Field_Count(dbHnd) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Field_Count, dbHnd) 
EndProcedure 

Procedure MySQL_Use_Result(dbHnd) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Use_Result, dbHnd) 
EndProcedure 

Procedure MySQL_Fetch_Row (*mysqlResult) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Fetch_Row, *mysqlResult) 
EndProcedure 

Procedure MySQL_Fetch_Lengths (*mysqlResult) 
  ProcedureReturn CallFunctionFast (CFF_MySQL_Fetch_Lengths, *mysqlResult)          
EndProcedure 

Procedure MySQL_Free_Result (*mysqlResult) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Free_Result, *mysqlResult) 
EndProcedure 

Procedure MySQL_Close (dbHnd) 
  CallFunctionFast(CFF_MySQL_Close, dbHnd) 
EndProcedure 

;============================================================================================================================
; Callback to colour items in a ListIconGadget
;============================================================================================================================

Procedure.l NotifyCallback(WindowID.l, Message.l, wParam.l, lParam.l) 
 
  Result = #PB_ProcessPureBasicEvents

  Select Message
 
    Case #WM_CTLCOLORSTATIC
      Select lparam
        Case GadgetID(#Gadget_mysqltest_querylabel)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_hostlabel)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_userlabel)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_passwordlabel)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_databaselabel)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_portlabel)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_savelogin)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
      EndSelect
  
    Case #WM_CTLCOLOREDIT
      Select lparam
        Case GadgetID(#Gadget_mysqltest_querybox)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_hostbox)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_userbox)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_passwordbox)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_databasebox)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
        Case GadgetID(#Gadget_mysqltest_portbox)
          SetBkMode_(wParam,#TRANSPARENT)
          SetTextColor_(wParam, $040404)
          Result = Yellow
      EndSelect
      
;    Case #WM_CTLCOLORBTN
;      Select lparam
;       Case GadgetID(#Gadget_mysqltest_helpbutton)
;          SetBkMode_(wParam,#TRANSPARENT)
;          SetTextColor_(wParam, $040404)
;          Result = Blue
;      EndSelect
    
  EndSelect

  ProcedureReturn Result

EndProcedure 

;============================================================================================================================
; Try to initialise the database environment (Procedure MySQL_Init())
;============================================================================================================================

  dbHnd = MySQL_Init()

  If dbHnd
    libOpened = 1 ; Tell all routines that the library was opened okay
  Else
    libOpened = 0
    Message = MessageRequester("Critical Error", "libmysql.dll not found in programs' startup directory") 
    End
  EndIf

;============================================================================================================================
; Main Program Loop
;============================================================================================================================

If Window_mysqltest()

  SetWindowCallback(@NotifyCallback())                              ; Turn on the colouring callback

  quitmysqltest = 0                                                 ; Set the program quit semaphore
  
  Gosub LoadLastLogin                                               ; Load the last login if it exists
  
  ActivateGadget(#Gadget_mysqltest_querybox)                        ; Set the focus to the query box
  
  Repeat                                                            ; Now start checking for all window and gadget events
    EventID = WaitWindowEvent()
    Select EventID
      Case #PB_Event_CloseWindow
        If EventWindowID() = #Window_mysqltest
          quitmysqltest = 1
        EndIf
      Case #PB_Event_Menu
        Select EventMenuID()
          Case #Gadget_mysqltest_return       : Gosub CheckEnter    ; Was Enter pressed in the query box?
        EndSelect
      Case #PB_Event_Gadget
        Select EventGadgetID()
          Case #Gadget_mysqltest_dumpbutton   : Gosub DumpListToDisk
          Case #Gadget_mysqltest_helpbutton
          Case #Gadget_mysqltest_exitbutton   : quitmysqltest = 1
        EndSelect
    EndSelect
  Until quitmysqltest
  Gosub SaveCurrentLogin             ; Save the current login if the checkbox is selected
  CloseWindow(#Window_mysqltest)
EndIf
End

;============================================================================================================================
; Load the last login saved by the user if there was one
;============================================================================================================================

 LoadLastLogin:
 
  If OpenFile(0, "LastLogin.txt") <> 0
    SetGadgetText(#Gadget_mysqltest_hostbox,     ReadString())
    SetGadgetText(#Gadget_mysqltest_userbox,     ReadString())
    SetGadgetText(#Gadget_mysqltest_passwordbox, ReadString())
    SetGadgetText(#Gadget_mysqltest_databasebox, ReadString())
    SetGadgetText(#Gadget_mysqltest_portbox,     ReadString())
    If ReadString() = "1"
      SetGadgetState(#Gadget_mysqltest_savelogin, 1)
    EndIf
    CloseFile(0)
    SetWindowTitle(#Window_mysqltest, #MainTitle + "Last saved login loaded")
  Else
    SetWindowTitle(#Window_mysqltest, #MainTitle + "Couldn't find last login file")
  EndIf
  
Return

;============================================================================================================================
; Save the current login details if the user has selected the checkbox
;============================================================================================================================

SaveCurrentLogin:

  If GetGadgetState(#Gadget_mysqltest_savelogin) = 1
    If CreateFile(0, "LastLogin.txt") <> 1
      WriteStringN(GetGadgetText(#Gadget_mysqltest_hostbox))
      WriteStringN(GetGadgetText(#Gadget_mysqltest_userbox))
      WriteStringN(GetGadgetText(#Gadget_mysqltest_passwordbox))
      WriteStringN(GetGadgetText(#Gadget_mysqltest_databasebox))
      WriteStringN(GetGadgetText(#Gadget_mysqltest_portbox))
      WriteStringN("1")
      CloseFile(0)
    EndIf
  EndIf

Return

;============================================================================================================================
; Check if the ENTER key was pressed in the SQL query box
;============================================================================================================================

CheckEnter:

  FocusID = GetFocus_()                                       ; Get the id of the window/object that has focus
  Select FocusID                                             ; Use the id in a gadget selection
    Case GadgetID(#Gadget_mysqltest_querybox)                 ; Gadget is the barcode box
      Query.s = GetGadgetText(#Gadget_mysqltest_querybox)     ; Get the text from the barcode box
      Gosub ConnectToDatabase                                ; Reusable data return routine
  EndSelect                                                  ; End the selection

Return

;============================================================================================================================
; Try to connect to the database
;============================================================================================================================

ConnectToDatabase:

  If libOpened
    host.s    = GetGadgetText(#Gadget_mysqltest_hostbox)
    user.s    = GetGadgetText(#Gadget_mysqltest_userbox)
    passwd.s  = GetGadgetText(#Gadget_mysqltest_passwordbox)
    db.s      = GetGadgetText(#Gadget_mysqltest_databasebox)
    port.l    = Val(GetGadgetText(#Gadget_mysqltest_portbox))
    If MySQL_Real_Connect(dbHnd, host, user, passwd, db, port.l, 32) <> 0
      SetWindowTitle(#Window_mysqltest, #MainTitle + "Connected and getting your data, please wait")
      Gosub GetSqlData
    Else
      MySQL_GetError(dbHnd, 1)
      Connect.l = 0
    EndIf
  Else
    SetWindowTitle(#Window_mysqltest, #MainTitle + "SQL library was never loaded so we cannot connect to the database!!")
  EndIf
  
Return

;============================================================================================================================
; Retrieve and display the data to the user now
;============================================================================================================================

GetSqlData:

  ClearGadgetItemList(#Gadget_mysqltest_datalist)
  
  If fieldNum <> 0                                                  ; Remove columns from previous query
    For colremove = 1 To fieldNum - 1
      RemoveGadgetColumn(#Gadget_mysqltest_datalist, 1)
    Next colremove
    fieldNum = 0
  EndIf
    
  Result = MySQL_Real_Query (dbHnd, Query.s)                        ; Run the given query

  If Result                                                         ; If the query didn't work, give an error
    MySQL_GetError(dbHnd, 1)
  Else 
    ;----------------------------------------------------------------------------------------------
    *mysqlResult = MySQL_Use_Result(dbHnd)                          ; If data returned, use the result
    If *mysqlResult = 0
      If MySQL_Field_Count(dbHnd)                                   ; No fields are returned so that's an error
        MySQL_GetError(dbHnd, 1) 
      Else
        MySQL_GetError(dbHnd, 1)                                    ; Fields are returned, so no error but query didn't return data 
      EndIf 
    Else
    ;----------------------------------------------------------------------------------------------
      affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)       ; How many rows affected
      fieldNum  = CallFunction(#libmysql,"mysql_num_fields", *mysqlResult)  ; How many fields
      rowsNum   = CallFunction(#libmysql,"mysql_num_rows", *mysqlResult)    ; How many rows
      ;--------------------------------------------------------------------------------------------
      For coladd = 1 To fieldNum - 1
        AddGadgetColumn(#Gadget_mysqltest_datalist, 1, "Data", 100)
      Next coladd
      ;--------------------------------------------------------------------------------------------
      Repeat                                                               ; Sequentially process all returned data
        *mysqlRow = MySQL_Fetch_Row(*mysqlResult) 
        If *mysqlRow <> 0 
          *mysqlLen = MySQL_Fetch_Lengths(*mysqlResult)          
          row = "" 
          For j = 1 To fieldNum                                            ; Length of given field
            length = PeekL(*mysqlLen + 4 * (j - 1))            
            fieldptr = PeekL(*mysqlRow + 4 * (j - 1)) 
            If fieldptr > 0 
              content.s = PeekS(fieldptr, length) 
            Else 
              content = "NULL"                                              ; Zero pointer returned means empty field
            EndIf 
            row = row + content + ";"                                       ; Debug content (individual collumns)
          Next j
            rowstring.s = ReplaceString(row, ";", Chr(10), 1,1)
            AddGadgetItem(#Gadget_mysqltest_datalist, -1, rowstring.s)              ; Dump the row to the listbox
        EndIf 
      Until *mysqlRow = 0 
      result.l = MySQL_Free_Result(*mysqlResult)
      MySQL_Close(dbHnd)                                                    ; Close the database when no longer needed
      ;--------------------------------------------------------------------------------------------
    EndIf
    ;----------------------------------------------------------------------------------------------
  EndIf

  For WidthSet = 0 To fieldNum - 1
    SendMessage_(GadgetID(#Gadget_mysqltest_datalist), #LVM_SETCOLUMNWIDTH, WidthSet, #LVSCW_AUTOSIZE)
  Next WidthSet

  SetWindowTitle(#Window_mysqltest, #MainTitle + "Found all matching data, nothing left to do.")

Return

;============================================================================================================================
; Dump the list to disk at the users' request
;============================================================================================================================

DumpListToDisk:

  Lines.l = CountGadgetItems(#Gadget_mysqltest_datalist)                    ; How many lines to save
  
  DumpList.s = SaveFileRequester("Save returned query", "QueryDump.txt", "Text (*.txt)|*.txt|All files (*.*)|*.*", 0)

  If CreateFile(0, DumpList.s) <> 0
    ;----------------------------------------------------------------------------------------------
    For ListItems = 0 To Lines - 1
      ;--------------------------------------------------------------------------------------------
      For totalFields = 0 To fieldNum - 1
        currentField.s = GetGadgetItemText(#Gadget_mysqltest_datalist, ListItems, totalFields)
        outString.s + currentField.s + ";"
      Next totalFields
      WriteStringN(outString.s)
      outString.s = ""
      ;--------------------------------------------------------------------------------------------
    Next ListItems
    CloseFile(0)
    ;----------------------------------------------------------------------------------------------
  Else
    SetWindowTitle(#Window_mysqltest, #MainTitle + "Cannot save the list to disk, something went wrong")
  EndIf
  
Return

[Shannara]

Unfortunately, the code does not work at all. It claims the libmysql.dll library is not found in the startup dir. and this is not true. Anybody have a fix for this?

Nvm, turns out you have your directory paths hardcoded.. shame shame.. fixing now.

[Fangbeast]

Unfortunately, the code does not work at all. It claims the libmysql.dll library is not found in the startup dir. and this is not true. Anybody have a fix for this?

Nvm, turns out you have your directory paths hardcoded.. shame shame.. fixing now.

1. Code does work here. I never claimed it would work for anyone else. That's the idea of being a programmer, you figure out the differences and fix it to suit yourself. It was provided free with no guarantee.

2. Loads the libmysql.dll perfectly here.

3. As for hardcoding, I feel no shame. That is my preference, you don't like it, tough

4. Bite me (as my daughter says) but use salt please.

[Flype]

works perfectly at home... thank you max.2
i will make an userlib from your exemple for my project.