The SQLRDD library for Harbour and MiniGUI

[gisjr134]

Hi To all ,

Excelent Work! It Can be used in Hmg ?

[gfilatov]

Are you talking about a fork of sqlrdd from M.A.Gambeta?
There are issues in this fork regarding Firebird 4/5:
Undefined SQL datatype
Unsupported data type at column DATE

Yes, it is.

Sorry, but I only ran tests with MySQL server.
And everything is fine.

[gfilatov]

Hi To all ,

Excelent Work! It Can be used in Hmg ?

Yes, sure.
SQLRDD library is related to SQL database management, so the GUI is not important.

[gisjr134]

Excelent , One more Question.

Is it compatible With the format 'Use ... Via ' , DbCloseArea(), DbuseArea(),Etc ??
SomeThing Like:

nConnection := RDDINFO( RDDI_CONNECT, { "MYSQL", cServer , cUserDb , cPassDb , "sisdb"} )


If nConnection = 0
MsgInfo("Unable connect to the server!"+Chr(13)+Chr(10)+Str(RDDINFO( RDDI_ERRORNO ))+Chr(13)+Chr(10)+RDDINFO( RDDI_ERROR , .f. ))
Return .f.
End If

If Select(cAlias) > 0
dbSelectArea( cAlias )
Use
End If


dbUseArea( .T.,, cSqlBrw, cAlias )
dbSelectArea( cAlias )
dbGoTop()

[gfilatov]

Is it compatible With the format 'Use ... Via ' , DbCloseArea(), DbuseArea(),Etc ??
SomeThing Like:

Exactly.

Please take a look at the example code below:

Code: Select all

/*
* SQLRDD info
* Sample application to get extended database info
* Copyright (c) 2003 - Marcelo Lombardo  <lombardo@uol.com.br>
* All Rights Reserved
*/

#define _HMG_OUTLOG
#include <minigui.ch>
#include "sqlrdd.ch"
#include "dbinfo.ch"

#define RECORDS_IN_TEST       100
FIELD CODE_ID, DAYS

FUNCTION MAIN(cRDD, cDsn)

   LOCAL aStruct := { ;
                     {"CODE_ID" , "C",  8, 0}, ;
                     {"CARDID"  , "C",  1, 0}, ;
                     {"DESCR"   , "C", 50, 0}, ;
                     {"PERCENT" , "N",  8, 2}, ;
                     {"DAYS"    , "N",  6, 0}, ;
                     {"DATE_LIM", "D",  8, 0}, ;
                     {"ENABLE"  , "L",  1, 0}, ;
                     {"OBS"     , "M", 10, 0}, ;
                     {"VALUE"   , "N", 18, 6} ;
                    }
   LOCAL i
   LOCAL oSql

   DEFAULT cRDD := "SQLRDD"

   Connect(@cRDD, cDSN)    // see connect.prg

   ? "Connected to ", SR_GetConnectionInfo(, SQL_DBMS_NAME), SR_GetConnectionInfo(, SQL_DBMS_VER)
   ? "RDD in use          :", cRDD
   ? "Creating table"

   oSql := SR_GetConnection()

   dbCreate("TEST_TABLE3", aStruct, cRDD)
   USE TEST_TABLE3 via cRDD
   INDEX ON CODE_ID TO TBL3_INDX
   INDEX ON CODE_ID TAG CODE_ID FOR DAYS < 20

   IF reccount() < RECORDS_IN_TEST
      FOR i := 1 TO RECORDS_IN_TEST
         APPEND BLANK
         REPLACE CODE_ID  WITH strZero(i, 5)
         REPLACE DESCR    WITH dtoc(date()) + " - " + time()
         REPLACE DAYS     WITH (RECORDS_IN_TEST - i)
         REPLACE DATE_LIM WITH date()
         REPLACE ENABLE   WITH .T.
         REPLACE OBS      WITH "This is a memo field. Seconds since midnight : " + alltrim(str(seconds()))
      NEXT i
   ENDIF

   dbGoTop()
   i := select()

   ? "Workarea number :", i
   ? "RDD Version     :", dbInfo(DBI_RDD_VERSION)
   ? "RDD Build       :", dbInfo(DBI_RDD_BUILD)
   ? "Current table   :", dbInfo(DBI_FULLPATH)
   ? "Is table shared :", dbInfo(DBI_SHARED)
   SKIP -1
   ? "Bof             :", dbInfo(DBI_BOF)
   ? "Eof             :", dbInfo(DBI_EOF)
   dbGoBottom()
   SKIP
   ? "Bof             :", dbInfo(DBI_BOF)
   ? "Eof             :", dbInfo(DBI_EOF)
   dbGoTop()

   ? "Host Database   :", dbInfo(DBI_DB_VERSION), "(see sqlrdd.ch for details)"
   ? "WorkArea Object :", (i)->(dbInfo(DBI_INTERNAL_OBJECT):classname())
   ? "Connection Obj  :", (i)->(dbInfo(DBI_INTERNAL_OBJECT):oSql:classname())
   ? ""
   ? "Locking a, b    :", SR_SetLocks({"a", "b"})
   ? "RecSize()       :", RecSize()
   ? "RecNo()         :", RecNo()
   ? "RecCount()      :", RecCount()
   ? "OrdKeyCount()   :", OrdKeyCount()
   ? "SQLRDD Conn ID  :", SR_GetnConnection()

   SET ORDER TO 2
   ? "OrdCount()      :", OrdCount()
   ? "IndexOrd()      :", IndexOrd()
   (i)->(dbCloseArea())

   dbUseArea(.F., cRDD, "TEST_TABLE3", "TEST")

   EDIT EXTENDED

   DbCloseAll()
   ? "Connection ID   :", oSql:GetConnectionID()
   ? "Kill Connection :", oSql:KillConnectionID(9999)

RETURN NIL

/*------------------------------------------------------------------------*/

#include "connect.prg"

/*------------------------------------------------------------------------*/

and the following program output:

Connected to MYSQL NATIVE 80030
RDD in use : SQLRDD
Creating table
Workarea number : 1
RDD Version : MGMNT 1.72
RDD Build : 15
Current table : TEST_TABLE3
Is table shared : .F.
Bof : .T.
Eof : .F.
Bof : .F.
Eof : .T.
Host Database : 12 (see sqlrdd.ch for details)
WorkArea Object : SR_WORKAREA
Connection Obj : SR_MYSQL
''
Locking a, b : .F.
RecSize() : 131
RecNo() : 1
RecCount() : 100
OrdKeyCount() : 100
SQLRDD Conn ID : 1
OrdCount() : 2
IndexOrd() : 2
Connection ID : 38
Kill Connection : 1

[mol]

could you place compiled library here? (for HMG - MinGW)

[gfilatov]

could you place compiled library here? (for HMG - MinGW)

Yes, sure. Here it is.

[mol]

Thank you!

[gfilatov]

Hi All,

I have prepared a simple example showing how to migrate from DBFCDX to SQLRDD using the standard BROWSE control (see attachment).

You can compare the codes to see the difference.
Using SQLRDD requires the following changes:
1) adding a connection to the SQL server (MySQL in that case) and
2) calling the dbunlock() function after adding a record.

Thank you for your attention.

[gfilatov]

Hi All,

I have prepared the following SQLRDD example showing how to retrieve data from an existing 'Country' table in the MySQL database 'World' that is distributed with the MySQL installation.

Since the above table is not a native SQLRDD table, we need to run a SQL query on this table using the built-in SQLRDD SQL parser and SQL code generator.

Please look at the following OpenTable procedure code:

Code: Select all

PROCEDURE OpenTable

   LOCAL cRDD
   LOCAL cComm
   LOCAL apCode
   LOCAL nErr
   LOCAL nPos
   LOCAL n

   DEFAULT cRDD := "SQLRDD"

   Connect( @cRDD ) // see connect.prg

   // Request data for Chart 1
   //
   cComm := "SELECT * FROM Country ORDER BY Population DESC"
   apCode := SR_SQLParse( cComm, @nErr, @nPos )
   cComm := SR_SQLCodeGen( apCode )

   dbUseArea( .F., "SQLRDD", cComm, "t1" )

   // One serie data
   aSer1 := Array( 10, 1 )
   aSerVal1 := Array( 10 )
   aSerName1 := Array( 10 )

   n := 0
   t1->( dbGoTop() )
   DO WHILE t1->( RecNo() ) <= 10 // top 10 values
      aSer1[ ++n, 1 ] := t1->Population / 1000
      aSerVal1[ n ] := t1->NAME
      aSerName1[ n ] := aSerVal1[ n ]
      t1->( dbSkip() )
   ENDDO
   dbCloseArea()

   // Request data for Chart 2
   //
   cComm := "SELECT * FROM Country ORDER BY SurfaceArea DESC"
   apCode := SR_SQLParse( cComm, @nErr, @nPos )
   cComm := SR_SQLCodeGen( apCode )

   dbUseArea( .F., "SQLRDD", cComm, "t2" )

   // One serie data
   aSer2 := Array( 10 )
   aSerName2 := Array( 10 )

   n := 0
   t2->( dbGoTop() )
   DO WHILE t2->( RecNo() ) <= 10 // top 10 values
      aSer2[ ++n ] := Round( t2->SurfaceArea / 1000, 2 )
      aSerName2[ n ] := Trim( t2->Name )
      t2->( dbSkip() )
   ENDDO
   dbCloseArea()

   // Request data for Chart 3
   //
   cComm := "SELECT Name, Population / SurfaceArea as off FROM Country WHERE SurfaceArea > 250 ORDER BY off DESC"
   apCode := SR_SQLParse( cComm, @nErr, @nPos )
   cComm := SR_SQLCodeGen( apCode )

   dbUseArea( .F., "SQLRDD", cComm, "t3" )

   // One serie data
   aSer3 := Array( 20, 1 )
   aSerVal3 := Array( 20 )
   aSerName3 := Array( 20 )

   n := 0
   t3->( dbGoTop() )
   WHILE t3->( RecNo() ) <= 40 .AND. t3->Off > 5000
      t3->( dbSkip() )
   END
   DO WHILE t3->( RecNo() ) <= 40 // top 40 values
      IF n > 19
         EXIT
      ENDIF
      aSer3[ ++n, 1 ] := Round( t3->OFF, 3 )
      aSerVal3[ n ] := t3->NAME
      aSerName3[ n ] := Transform( aSer3[ n, 1 ], "9 999.999" ) + ' ' + aSerVal3[ n ]
      t3->( dbSkip() )
   ENDDO
   dbCloseArea()

   // First chart drawing
   DrawChart_1( aser1 )

RETURN

The result of the program is shown below.

program screenshot

image.png (22.47 KiB) Viewed 123884 times

Your feedback is welcome.