Mediator / Listbox

[Jeff Barnes]

Hi Everybody,


After many failed attempts to connect and edit / modify records via MySql I have tried Mediator (http://www.otc.pl)
It seems to be working and was fairly simple to set up.
Also, they are providing a free unlimited Rdd to connect to MySql for harbour/xharbour.

I have come across a problem and was wondering if anyone else has see this...

I have a listbox that uses fields from a table opened via MEDNTX (their rdd) and I cannot use the mouse to click on the rows in the listbox (I can use the up/down arrow keys then double click the highlighted item but cannot use the mouse to select a row)

If I change the FIELDS clause to ITEMS and try I can click on the rows.

Example: (non-working)

Code: Select all  Expand view  RUN

  @ 7,1 LISTBOX oLbx ;
         FIELDS Patients->MRN,Patients->LastName,Patients->FirstName ;
         SIZE 780,350 ;
         of oSearch
 

Example: (working)

Code: Select all  Expand view  RUN

  aItems := { "One", "Two","Three" }
   @ 7,1 LISTBOX oLbx ;
         ITEMS aItems ;
         SIZE 780,350 ;
         of oSearch
 

Anyone seen this before? Anything I can do to get it working?

[dutch]

Dear Jeff,

I try Mediator and able to read and write. I try listbox but it able to show only FIRST RECORD on the listbox only.

Code: Select all  Expand view  RUN

#include 'fivewin.ch'
#include 'mediator.ch'

static oWnd, oLbx

Function main
local numtabs, aTabNames
request medntx
rddsetdefault("MEDNTX")

numtabs := MedGetTabs()

Msginfo( numtabs )

aTabNames := array( numtabs )

MedGetTabs( aTabNames )

MsgList( aTabNames )

use rsvn alias rsvn

DEFINE WINDOW oWnd TITLE 'Test Mediator'

  @ 1, 1 LISTBOX oLbx FIELDS RSVN->ROOMNO, RSVN->FIRSTNAME, RSVN->LASTNAME ;
                 HEADERS 'Room', 'Firstname', 'Lastname' ;
                 SIZE 600,400 ;
                 OF oWNd

ACTIVATE WINDOW oWnd MAXIMIZED

use rsvn
close all
return nil

If you can do any forward, pls let me know. Thanks, bcos, I try to use MySql too.

Regards,
Dutch

[Jeff Barnes]

Hi Dutch,

I have it working now ... I think it was a table issue. I recreated a test table and all is ok.

Here is the code I used to create the test table:

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "mediator.ch" 
request medntx     

Function Main()
   Local aDBF, i
   RDDSETDEFAULT("MEDNTX") 
   aDbf := {}
   AADD(aDbf, { "MRN",        "C", 30,   0 })
   AADD(aDbf, { "LastName",   "C", 30,   0 })
   AADD(aDbf, { "FirstName",  "C", 30,   0 })
   DBCREATE("Patients", aDbf, "MEDNTX")
   USE
   USE PATIENTS NEW alias "PAT"
   For i = 1 to 100
       APPEND BLANK
       Pat->MRN := STR(i)
       Pat->LastName := "Test"+str(i)
       Pat->FirstName := "Patient"+str(i)
   Next
   USE
   Msginfo("Patient Databasae Created")
Return Nil
 

As for my listbox code:

Code: Select all  Expand view  RUN

#include "FiveWin.ch"
#include "Mediator.ch"
request medntx     

Function Main()
   RDDSETDEFAULT("MEDNTX") 
   OpenFiles()
   Search()
Return Nil

Function OpenFiles()
  Use Patients Shared New
  GO TOP
Return Nil

Function Search()
   Define Window......

      @ 7,1  LISTBOX oLbx;
          FIELDS Patients->MRN,Patients->LastName,Patients->FirstName;
          HEAD "MRN","Last Name","First Name" ;
          SIZE 780,350 ;
          FIELDSIZES 150,150,150  ;
          ON DBLCLICK EditPatient() ;    
          of oSearch
      oLbx:Gotop()
   Activate Window.......
Return Nil
 

I hope this helps

[dutch]

Dear Jeff,

I try ADO from Daniel Garcia-Gil example. It doesn't too hard for starting.
The Mediator is good but I have to access the databae (MySql) share with WebBase Application (Third Party) and Mediator create and write database in thier way. If other application access and write in thier way too. The database will might be complete. The ADO is a pure connect from xHarbour+Fivewin, it is a good choice but if it is not easy Mediator will be better choice. I will try both (ADODB and Mediator) and let you know the testing.

Thanks a lot, I will try your code and it works ok now. I feel the Mediator will look at Command first. "Go Top"

Code: Select all  Expand view  RUN

/*
* test connection with ADODB
*/

#include "FiveWin.Ch"
#include "XBrowse.Ch"
#include "ado.Ch"


//----------------------------------------------------------------------------//

function main()

   execConnection()

return( 0 )

FUNCTION execConnection

LOCAL oCon, oRs, oError


oCon := MySQLConnect()

* MakeTable(oCon)

oRs  := MySQLRecordSet( oCon )

IF oRs:recordCount() > 0
   oRs:moveFirst()
ENDIF

TxBrowseShow( oRs )

oRs:close()

oCon:close()

RETURN( .T. )

/*
* --------------------------------------------------------------------------*
* --------------------------------------------------------------------------*
*/
FUNCTION MySQLConnect

LOCAL oError, oCon, cConnectionString


TRY

   oCon := TOleAuto():new( "ADODB.Connection" )

CATCH oError

   msgStop( "No se pudo crear la conexion" )
   RETURN( .F. )

END

cConnectionString := "Driver={MySQL ODBC 5.1 Driver}" + ;
                     ";Server=localhost"       + ;  //                      
             ";DefaultDatabase=php362"       + ;
                     ";Database=php362"            + ;
                     ";Port=3306"                     + ;
                     ";User=root"                  + ;
                     ";Password=pass"              + ;
                     ";Option=3;"

oCon:connectionString := cConnectionString

TRY

   oCon:open()
   ? "Connection ok!"

CATCH oError

   msgInfo( "No se pudo crear la conexion" )
   showError( oError )
   RETURN( .F. )

END

RETURN( oCon )

/*
* --------------------------------------------------------------------------*
* --------------------------------------------------------------------------*
*/
FUNCTION MySQLRecordSet( oCon )

LOCAL oRs, oError, cQuerySql


TRY
   oRs := TOleAuto():new( "ADODB.RecordSet" )

CATCH oError

   msgStop( "No hay RS" )
   showError( oError )
   oRs := NIL
   RETURN( .F. )

END

&& -- Realizando uma query no cadastro de empresas
cQuerySql := "SELECT * FROM RSVN"

oRs:cursorLocation := adUseClient // 3
oRs:lockType       := adLockOptimistic // 3
oRs:cursorType     := adOpenDynamic // 2

oRs:source         := cQuerySql

oRs:activeConnection( oCon )

TRY

   oRs:open()
   ? oRs:source,,, "oRs:recordCount()", oRs:recordCount()

CATCH oError

   msgStop( "No Query '" + cQuerySql + "'" )
   showError( oError )
   RETURN( .F. )

END

RETURN( oRs )

/*
* --------------------------------------------------------------------------*
* --------------------------------------------------------------------------*
*/
PROCEDURE showError( oError )

LOCAL cMensagemRetorno := ""

cMensagemRetorno := "[Subsystem]"         + CRLF + oError:SubSystem                 + CRLF + CRLF + ;
                    "[SubCode]"           + CRLF + alltrim( str( oError:SubCode ) ) + CRLF + CRLF + ;
                    "[Operacion]"         + CRLF + oError:Operation                 + CRLF + CRLF + ;
                    "[Descripcion]"       + CRLF + oError:Description               + CRLF + CRLF + ;
                    "[Argumentos]"        + CRLF + valToPrg( oError:Args )

msgStop( cMensagemRetorno )
RETURN

/*
* --------------------------------------------------------------------------*
* --------------------------------------------------------------------------*
*/
FUNCTION TxBrowseShow( oRs )

   local oWnd, oBrw, oCol

   DEFINE WINDOW ownd OF WndMain() TITLE "Test"

   @ 0,0 XBROWSE oBrw OF oWnd RECSET oRs AUTOCOLS AUTOSORT FOOTERS FASTEDIT ;
         LINES CELL

   oBrw:createFromCode()
   oWnd:oClient := oBrw

   ACTIVATE WINDOW oWnd ON INIT oBrw:setFocus()

return nil

function MakeTable( oCon )
local cCmdSql := "CREATE TABLE IF NOT EXISTS TEST ("+;
                 "ROW_ID INTEGER    NOT NULL AUTO_INCREMENT COMMENT 'LLAVE',"+;
                 "CODIGO CHAR(5)    NOT NULL COMMENT 'CODIGO CLIENTE',"+;
                 "NOMBRE CHAR(30)   NOT NULL COMMENT 'NOMBRE CLIENTE',"+;
                 "ACTIVO BIT        NOT NULL COMMENT 'ACTIVO',"+;
                 "PRIMARY KEY(ROW_ID))"+;
                 "ENGINE = InnoDB COMMENT 'Test';"
                 
oCon:Execute( cCmdSql )

cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('001','NOMBRE01',0);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('002','NOMBRE02',1);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('003','NOMBRE03',0);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('004','NOMBRE04',1);"
oCon:Execute( cCmdSql )
return nil
 

Regards,
Dutch

[dutch]

Dear Jeff,

I've got good help from William Morales and Wang Shuming. Both guys are very kindness and helpful.
I can connect to MySql Server with TMySql (mysql.lib and libmysql.lib). It can connect without ODBC setup and without Mediator Server (Third party application).
It means xHarbour+FWH+(mysql.lib and libmysql.lib) only. I quite simple. If you are interesting, just add to library in your batch file before Fivehx.lib and Fivehc.lib.

Code: Select all  Expand view  RUN

(buildx.bat)
...
echo %hdirl%\mysql.lib + >> b32.bc
echo %hdirl%\libmysql.lib + >> b32.bc
echo %fwh%\lib\Fivehx.lib %fwh%\lib\FiveHC.lib + >> b32.bc
...
 

Example to connect to MySql
========================

Code: Select all  Expand view  RUN

#include 'fivewin.ch'

static oserver, odb1

Function Main

     LOCAL cHost := "localhost" // "incos...net"
     LOCAL cUser := "root"   // "dutch"
     LOCAL cPsw  := "nimda"  // "dutch"
     LOCAL cPort := "3306"

     oServer:= TMySQLServer():New( cHost, cUser, cPsw, cPort )
     if oServer:NetErr()
        MsgAlert (oServer:Error(),"MySQL Error")
        quit
     endif

     oserver:Selectdb("php362")
     IF oServer:lError
        ? "No used database"
        RETURN NIL
     ENDIF

     odb1:=oServer:Query("select * from rsvn")  // employee where name='Steven'")
     msginfo( odb1:fieldget( "lastname" ) )
     oserver:end()

return nil
 

[Jeff Barnes]

Thanks Dutch.

[FranciscoA]

Dear Dutch:
I'm trying to start using MySql, and I'm very confused about the code below. I have two days without getting work it.
Can this code work only with MySql + xHarbour?, or other component I need?. Can you help?

I reviewed the class tmysql I have, and I do not see anywhere, the command :Execute (), but compiled without errors.
The problem is that it creates not the table, and the table do not exists, and the conection to the server is working well.

function MakeTable( oCon )
local cCmdSql := "CREATE TABLE IF NOT EXISTS TEST ("+;
"ROW_ID INTEGER NOT NULL AUTO_INCREMENT COMMENT 'LLAVE',"+;
"CODIGO CHAR(5) NOT NULL COMMENT 'CODIGO CLIENTE',"+;
"NOMBRE CHAR(30) NOT NULL COMMENT 'NOMBRE CLIENTE',"+;
"ACTIVO BIT NOT NULL COMMENT 'ACTIVO',"+;
"PRIMARY KEY(ROW_ID))"+;
"ENGINE = InnoDB COMMENT 'Test';"

oCon:Execute( cCmdSql )

cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('001','NOMBRE01',0);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('002','NOMBRE02',1);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('003','NOMBRE03',0);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('004','NOMBRE04',1);"
oCon:Execute( cCmdSql )
return nil

Regards,
Francisco.

[nageswaragunupudi]

Mr Duch

>>
TxBrowseShow( oRs )
>>

It is really not necessary to have this function written by yourself.

Simply,

Code: Select all  Expand view  RUN

xbrowse( oRs )

will do.

[dutch]

Dear Francisco,

This function is used for ADO not for TMySql. If you need to use TMySql just add 2 library MYSQL.LIB and LIBMYSQL.LIB before FIVEHX.LIB and FIVEHC.LIB. Then try the sample. I just test to connect and read the database. You can search TMYSQL in the forum.

I reviewed the class tmysql I have, and I do not see anywhere, the command :Execute (), but compiled without errors.
The problem is that it creates not the table, and the table do not exists, and the conection to the server is working well.

function MakeTable( oCon )
local cCmdSql := "CREATE TABLE IF NOT EXISTS TEST ("+;
"ROW_ID INTEGER NOT NULL AUTO_INCREMENT COMMENT 'LLAVE',"+;
"CODIGO CHAR(5) NOT NULL COMMENT 'CODIGO CLIENTE',"+;
"NOMBRE CHAR(30) NOT NULL COMMENT 'NOMBRE CLIENTE',"+;
"ACTIVO BIT NOT NULL COMMENT 'ACTIVO',"+;
"PRIMARY KEY(ROW_ID))"+;
"ENGINE = InnoDB COMMENT 'Test';"

oCon:Execute( cCmdSql )

cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('001','NOMBRE01',0);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('002','NOMBRE02',1);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('003','NOMBRE03',0);"
oCon:Execute( cCmdSql )
cCmdSql := "INSERT INTO TEST (CODIGO,NOMBRE,ACTIVO) VALUES('004','NOMBRE04',1);"
oCon:Execute( cCmdSql )
return nil

Regards,
Francisco.

Regards,
Dutch

[FranciscoA]

Dutch: ¡Thanks!