How to mix databases types or connections with RDDSQL
Maurizio
different type at the same time, I mean can I connect to :
- a Myslq and a Postgree server
- or two different Myslq servers for example
- a Mysql and a sqlmix array database, just to make various queries to
a sql server, and create a temporary array database to process and
browse it.
I cannot imagine which is the sequence of the RDDI instructions to
make things like I described earlier
Thank you in advance
Maurizio
Massimo Belgrano
please post your final works for help next user
nconn1=RDDINFO( RDDI_CONNECT, {"MYSQL", "localhost",
"root","password", "testHB"} )
use test
dbedit()
? "Alias:", ALIAS()
nconn2=RDDINFO( RDDI_CONNECT, { "FIREBIRD","LOCALHOST", "SYSDBA",
"password",CURDIR()+"\TESTHB.fdb" } )
use test
dbedit()
? "Alias:", ALIAS()
nconn3=RDDINFO( RDDI_CONNECT, { "ODBC", "Driver={Microsoft Access
Driver (*.mdb)};DBQ=" + CURDIR() + "\testHB.mdb" } )
use test
dbedit
? "Alias:", ALIAS()
RDDINFO( RDDI_CONNECT, {
"ODBC","Driver={SQLServer};Server=LOCALHOST;Database=TESTHB;user=sa;password=password";"}'
)
use test
dbedit
? "Alias:", ALIAS()
RDDINFO(RDDI_DISCONNECT, nConn1)
RDDINFO(RDDI_DISCONNECT, nConn2)
RDDINFO(RDDI_DISCONNECT, nConn3)
2010/9/16 Maurizio <mau...@adinet.com.uy>:
--
Massimo Belgrano
Bruno Luciani
can you edit ? , and how to update if , I am not wrong sqlrdd not update the records
only generates de query
Bruno
Massimo Belgrano
you have two way
1) with "RDDINFO(RDDI_EXECUTE" you can execute sql sting thad create,add,read
2)using minimal rdd support with
DO WHILE ! EOF()
aI := ARRAY( FCOUNT() )
FOR nI := 1 TO FCOUNT()
aI[nI] := FIELDGET( nI )
NEXT
? RECNO(), HB_VALTOEXP( aI )
DBSKIP()
ENDDO
is read and write if original source table (given with sql string) allow this
-------------------------test2.prg
REQUEST SQLMIX, SDDODBC
PROC main()
LOCAL nConnection, nI, aI
RDDSETDEFAULT( "SQLMIX" )
SET( _SET_DATEFORMAT, "yyyy-mm-dd" )
nConnection := RDDINFO( RDDI_CONNECT, { "ODBC",
"Server=localhost;Driver={MySQL ODBC 5.1
Driver};dsn=;User=test;database=test;" } )
IF nConnection == 0
? "Unable connect to server", RDDINFO( RDDI_ERRORNO ), RDDINFO(
RDDI_ERROR )
RETURN
ENDIF
? nConnection
? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME
char(50), RESIDENTS int(11))")
? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU',
'Lithuania', 3369600),('USA', 'United States of America', 305397000),
('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS',
'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia',
141900000)")
? DBUSEAREA( .T.,, "SELECT * FROM country", "country" )
? "LASTREC:", LASTREC()
DO WHILE ! EOF()
aI := ARRAY( FCOUNT() )
FOR nI := 1 TO FCOUNT()
aI[nI] := FIELDGET( nI )
NEXT
? RECNO(), HB_VALTOEXP( aI )
DBSKIP()
ENDDO
? "LASTREC:", LASTREC()
DBCLOSEALL()
RETURN
-------
/*
* $Id: test1.prg 14222 2010-03-24 09:33:58Z vszakats $
*/
#include "dbinfo.ch"
#include "error.ch"
#include "hbrddsql.ch"
REQUEST SDDMY, SQLMIX
ANNOUNCE RDDSYS
FIELD RESIDENTS
PROCEDURE main()
RDDSETDEFAULT("SQLMIX")
AEVAL(RDDLIST(), {|X| QOUT(X)})
IF RDDINFO(RDDI_CONNECT, {"MYSQL", "localhost", "test",, "test"}) == 0
? "Unable connect to the server"
RETURN
ENDIF
CreateTable()
? "Let's browse table (press any key)"
INKEY(0)
DBUSEAREA( .T.,, "SELECT * FROM country", "country" )
Browse()
? "Let's browse table ordered by resident count (press any key)"
INKEY(0)
INDEX ON RESIDENTS TAG residents TO country
Browse()
DBCLOSEALL()
RETURN
STATIC PROC CreateTable()
? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME
char(50), RESIDENTS int(11))")
? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU',
'Lithuania', 3369600), ('USA', 'United States of America', 305397000),
('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS',
'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia',
141900000)")
RETURN
2010/9/16 Bruno Luciani <bruno....@gmail.com>:
--
Massimo Belgrano
Maurizio Faccio adinet
RDDSQL have two parts,
- One that give you the power of manage your queries, using RDD interface, this mean that once you make the query with DBUSEAREA, then you can browse, dbeval, dbskip, the cached query, but the query is only a view of the query, cached in your local memory, I do not know the size limit of this, but I've browse a 800000 records query!!.
- Via RDDINFO, you can connect to multiple database engines, query, and execute any sql statements
PROC main()
LOCAL hConn
%% Previous to call RDDINFO you should set default RDD to SQLMIX, after that you can set the default RDD to the one that you want, I use mixed MYSQL, and dbfntx, so afther the %% RDDINFO I set again RDDSETDEFAULT to DBFNTX, and when I wish to query MYSQL, i just tell that it should use SQLMIX as RDD
%% ( DBUSEAREA( .T.,"SQLMIX", "SELECT * FROM country", "country" ) )
RDDSETDEFAULT("SQLMIX")
AEVAL(RDDLIST(), {|X| QOUT(X)})
IF RDDINFO(RDDI_CONNECT, {"MYSQL", "localhost", "test",, "test"}) == 0
? "Unable connect to the server"
RETURN
ENDIF
CreateTable()
%% Query the database engine.
? "Let's browse table (press any key)"
INKEY(0)
DBUSEAREA( .T.,, "SELECT * FROM country", "country" )
Browse()
? "Let's browse table ordered by resident count (press any key)"
INKEY(0)
INDEX ON RESIDENTS TAG residents TO country
Browse()
DBCLOSEALL()
RETURN
STATIC PROC CreateTable()
%% Delete tables
? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME char(50), RESIDENTS int(11))")
? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU', 'Lithuania', 3369600), ('USA', 'United States of America', 305397000), ('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS', 'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia', 141900000)")
RETURN
Massimo Belgrano
// -lsddmy -lrddsql
/*
* RDDSQL - Harbour
* Copyright 2008 Belgrano Massimo
* Copyright 2008 Grigory Filatov <gfil...@freemail.ru>
*
* Based on RDDSQL sample included in Harbour distribution
* based on Slight Modifications By Roberto Lopez (2009.04.01)
*
*/
#include "dbinfo.ch"
#include "error.ch"
#define DBI_QUERY 1001
#define RDDI_CONNECT 1001
#define RDDI_DISCONNECT 1002
#define RDDI_EXECUTE 1003
#define RDDI_ERROR 1004
#define RDDI_ERRORNO 1005
#define RDDI_NEWID 1006
#define RDDI_AFFECTEDROWS 1007
#define RDDI_QUERY 1008
ANNOUNCE RDDSYS
REQUEST SDDMY, SQLMIX
*--------------------------------------------------------*
Function Main()
*--------------------------------------------------------*
set(80,25)
RDDSETDEFAULT( "SQLMIX" )
IF RDDINFO( RDDI_CONNECT, {"MYSQL", "localhost",
"root","passwordroot", "test"} ) == 0
? "Unable connect to the server!"
Return nil
ENDIF
OpenTable()
AddRecord()
dbedit()
CloseTable()
Return nil
*--------------------------------------------------------*
Procedure OpenTable
*--------------------------------------------------------*
If CreateTable()
DBUSEAREA( .T.,, "SELECT * FROM country", "country" )
INDEX ON FIELD->RESIDENTS TAG residents TO country
GO TOP
endIf
Return
*--------------------------------------------------------*
Procedure CloseTable
*--------------------------------------------------------*
DBCLOSEALL()
Return
*--------------------------------------------------------*
Procedure AddRecord
*--------------------------------------------------------*
APPEND BLANK
REPLACE CODE WITH 'ARG'
REPLACE NAME WITH 'Argentina'
REPLACE RESIDENTS WITH 38740000
GO TOP
Return
*--------------------------------------------------------*
Function CreateTable
*--------------------------------------------------------*
Local ret := .T.
RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
If RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME
char(50), RESIDENTS int(11))")
If ! RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU',
'Lithuania', 3369600), ('USA', 'United States of America', 305397000),
('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS',
'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia',
141900000)")
? "Can't fill table Country!"
ret := .F.
EndIf
Else
? "Can't create table Country!"
ret := .F.
EndIf
Return ret
2010/9/16 Maurizio Faccio adinet <mau...@adinet.com.uy>:
--
Massimo Belgrano
Iscritto all'albo dei CTU presso il Tribunale di Novara per materia Informatica
Delta Informatica S.r.l. (http://www.deltain.it/) (+39 0321 455962)
Analisi e sviluppo software per Lan e Web - Consulenza informatica - Formazione
Massimo Belgrano
or read and write?
2010/9/16 Maurizio Faccio adinet <mau...@adinet.com.uy>:
> - One that give you the power of manage your queries, using RDD interface,
> this mean that once you make the query with DBUSEAREA, then you can browse,
> dbeval, dbskip, the cached query, but the query is only a view of the query,
> cached in your local memory, I do not know the size limit of this, but I've
> browse a 800000 records query!!.
In my sample i see argentina so .. also append seem works
APPEND BLANK
REPLACE CODE WITH 'ARG'
REPLACE NAME WITH 'Argentina'
REPLACE RESIDENTS WITH 38740000
GO TOP
Return
--
Massimo Belgrano
Massimo Belgrano
2010/9/16 Massimo Belgrano <mbel...@deltain.it>:
--
Massimo Belgrano
Maurizio Faccio adinet
El 16/09/2010 10:57 a.m., Massimo Belgrano escribi�:
As says in RDDSQL readme,
%%%%%%%%%%%%%%%%%%%%%%
3. Modifying database
SSI presents a query result via RDD interface and generates no hidden
SQL queries. So, how database can be changed? Does DBAPPEND() and FIELDPUT()
works, or is it readonly SQL interface?
DBAPPEND(), FIELDPUT() and other similiar functions work on cached query
result, i.e. query can be appended by new rows and field values can be
changed, but SQL database is not changed. DBCREATE() function can also be
used to create an "empty query result" but no table is created on SQL
server.
So, SSI can also be used as implementation of "array RDD".
The programmer must call SQL command explicitly to modify SQL tables.
SSI provides a method to detect which cached rows was changed or appended.
%%%%%%%%%%%%%%%%%%%%
From this i understand that append works but onli con the cached query
result, you see that you have added Argentina, but if you query the
database i understand that that record is not there. You should use
RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('ARG', 'Argentina',
38740000)")
to effectively add that value to the engine table.
Maurizio
Massimo Belgrano
i confirm that my sql database is not updated!
2010/9/16 Maurizio Faccio adinet <mau...@adinet.com.uy>:
>
>
--
Bruno Luciani
any sample of this ¿?
Bruno
Vailton Renato
Check if this server is not configured to receive an "COMMIT" to force
the writing data. Try
RDDINFO(RDDI_EXECUTE, "COMMIT" )
Regards,
Vailton Renato
2010/9/16 Massimo Belgrano <mbel...@deltain.it>:
Maurizio Faccio adinet
Excuse me I've send the wrong test program, the correct one, have
disconnection of both servers after closing the area used with the query.
As I can test, you should ensure that all areas must be closed, and that
the default rdd must be SQLMIX previous to call RDDINFO(..)
El 17/09/2010 06:14 p.m., Maurizio Faccio adinet escribi�:
>
>
> RDD works fine conecting to two database engines, and generate a third
> "database" in array (no database table), please review my sample with
> two mysql, (you should have two mysql servers or create two different
> tables in test database (Createtable funcions)
>
> If I try this way, and monitoring mysql connections with SHOW FULL
> PROCESSLIST; in mysql monitor command line.
> I have trouble disconnecting from the first database, and also passing
> connection to dbusearea (8 parameter).
> The sample works bad if I
>
> _conn1:=RDDINFO(RDDI_CONNECT, {"MYSQL", "server1", "test",
> "","test" ))
> CreateTable()
> _conn2:=RDDINFO(RDDI_CONNECT, {"MYSQL", "server2", "test",
> "","test" ))
> CreateTable2()
>
> DBUSEAREA( .T.,, "SELECT * FROM country", "country",,,,_conn1 )
> Browse()
> DBUSEAREA( .T.,, "SELECT * FROM country", "country2",,,,_conn2 )
> Browse()
>
> .
> .
> .
>
> DBCLOSEALL()
> RDDINFO(RDDI_DISCONNECT,_conn1)
> RDDINFO(RDDI_DISCONNECT,_conn2)
>
> The server1 connection is not released, and the data cames only from
> server2
>
> Thank you
>
> Maurizio Faccio
>
>
>
>
>
>
>
>
--
________________________
Ing. Maurizio Faccio
Merbe Ltda.
Av. Italia 2346. Paysand�, Uruguay
web: www.merbe.com.uy
Tel : 598-472-29043 Int. 130
Fax: 598-472-22591
Cel: 598-99-007030
Maurizio Faccio adinet
trouble is still there but with this workarround the sample works well
releasing connections.
I have to create the connection, create the query, use it, close it, and
disconnect from first server, then do the same with the second server
>RDD works fine conecting to two database engines, and generate a third
> "database" in array (no database table), please review my sample with
> two mysql, (you should have two mysql servers or create two different
> tables in test database (Createtable funcions)
>
> If I try this way, and monitoring mysql connections with SHOW FULL
> PROCESSLIST; in mysql monitor command line.
> I have trouble disconnecting from the first database, and also passing
> connection to dbusearea (8 parameter).
-------
Massimo Belgrano
using RDDSETDEFAULT("SQLMIX")
/*
* $Id: test1.prg 12948 2009-11-20 12:45:41Z vszakats $
* modified by maufacc
*/
#include "dbinfo.ch"
#include "error.ch"
#include "hbrddsql.ch"
REQUEST SDDMY, SQLMIX
FIELD RESIDENTS
PROC main()
LOCAL _Conn1,_conn2
RDDSETDEFAULT("SQLMIX")
DBCREATE("country3", {{"CODE", "C", 3, 0}, {"NAME", "C", 50, 0},
{"RESIDENTS", "N", 11, 0}},, .T., "country3")
IF (conn1:=RDDINFO(RDDI_CONNECT, {"MYSQL", "server1", "test",
"","test" })) == 0
? "Unable connect to the server"
RETURN
ENDIF
? "View connections server1...."
inkey(0)
CreateTable()
DBUSEAREA( .T.,, "SELECT * FROM country", "country",,,,_conn1 )
Browse()
SELECT("COUNTRY")
DBEVAL({|| COUNTRY3->(DBAPPEND()),COUNTRY3->code:=code,COUNTRY3->name:=name,COUNTRY3->residents:=residents})
DBCLOSEAREA("COUNTRY")
RDDINFO(RDDI_DISCONNECT,_conn1)
IF (_conn2:=RDDINFO(RDDI_CONNECT, {"MYSQL", "server2", "test",
"","test" })) == 0
? "Unable connect to the server"
RETURN
ENDIF
? "View connections server2...."
inkey(0)
CreateTable2()
? "Let's browse table (press any key)"
INKEY(0)
DBUSEAREA( .T.,, "SELECT * FROM country", "country2",,,,_conn2 )
Browse()
SELECT("COUNTRY2")
DBEVAL({|| COUNTRY3->(DBAPPEND()),COUNTRY3->code:=code,COUNTRY3->name:=name,COUNTRY3->residents:=residents})
SELECT("COUNTRY3")
DBGOTOP()
BROWSE()
DBCLOSEALL()
RDDINFO(RDDI_DISCONNECT,_conn2)
? "Check connection is closed in both mysql "
INKEY(0)
cls
RETURN
STATIC PROC CreateTable()
? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME
char(50), RESIDENTS int(11))")
? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU',
'Lithuania', 3369600), ('USA', 'United States of America', 305397000),
('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS',
'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia',
141900000)")
RETURN
STATIC PROC CreateTable2()
? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME
char(50), RESIDENTS int(11))")
? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('UY ',
'Uruguay', 3494382)")
RETURN
PROC RDDSYS(); RETURN
2010/9/18 Maurizio Faccio adinet <mau...@adinet.com.uy>:
--
Maurizio Faccio adinet
Due to a typo where i make a mistake between conn1, and _conn1 in RDDI_CONNECT stage, everything works fine now.
Here I post a more complete sample to show how to use RDD, and how to manage connections to the database.
El 18/09/2010 12:58 p.m., Massimo Belgrano escribi�:
> /*
> * $Id: test1.prg 12948 2009-11-20 12:45:41Z vszakats $
> * modified by maufacc
> */
>
>
> IF (conn1:=RDDINFO(RDDI_CONNECT, {"MYSQL", "server1", "test",
> "","test" })) == 0
> ? "Unable connect to the server"
> RETURN
> ENDIF
> DBUSEAREA( .T.,, "SELECT * FROM country", "country",,,,_conn1 )
>
* $Id: test1.prg 12948 2009-11-20 12:45:41Z vszakats $
* modified by maufacc at adinet.com.uy to show different uses
* - Connecting to two servers simultaneously, managing connections.
* - Use of an array database.
* - Different uses of RDDINFO.
*/
#include "dbinfo.ch"
#include "error.ch"
#include "hbrddsql.ch"
REQUEST SDDMY, SQLMIX
FIELD RESIDENTS
PROC main()
LOCAL _Conn1,_conn2
&& RDDINFO( nIndex, xValue, cDriver, nConnection )
&& Passing RDD driver to RDDINFO
RDDSETDEFAULT("SQLMIX")
&& RDDINFO(RDDI_CONNECT, {"MYSQL", "server", "user", "password","database" },[driver])
IF (_conn1:=RDDINFO(RDDI_CONNECT, {"MYSQL", "server1", "test", "","test" },"SQLMIX")) == 0
? "Unable connect to the server"
RETURN
ENDIF
? "View connections server1...."
? _conn1
inkey(0)
IF (_conn2:=RDDINFO(RDDI_CONNECT, {"MYSQL", "server2", "test", "","test" },"SQLMIX")) == 0
? "Unable connect to the server"
RETURN
ENDIF
? "View connections server2...."
? _conn2
inkey(0)
&& Select the default connection.
? RDDINFO(RDDI_CONNECTION,,,_conn1)
cls
CreateTable()
? RDDINFO(RDDI_CONNECTION,,,_conn2)
cls
CreateTable2()
? "Let's browse table (press any key)"
INKEY(0)
cls
&& Query a table on server1, specifing the RDD
DBUSEAREA( .T.,"SQLMIX", "SELECT * FROM country", "country",,,,_conn1 )
Browse()
? "Let's browse table (press any key)"
INKEY(0)
cls
&& Query a table on server2
DBUSEAREA( .T.,"SQLMIX", "SELECT * FROM country", "country2",,,,_conn2 )
Browse()
RDDSETDEFAULT("SQLMIX")
&& Create an array "database" using SQLMIX RDD.
DBCREATE("country3", {{"CODE", "C", 3, 0}, {"NAME", "C", 50, 0}, {"RESIDENTS", "N", 11, 0}},, .T., "country3")
&& Append data from the other two tables.
SELECT("COUNTRY")
DBEVAL({|| COUNTRY3->(DBAPPEND()),COUNTRY3->code:=code,COUNTRY3->name:=name,COUNTRY3->residents:=residents})
SELECT("COUNTRY2")
DBEVAL({|| COUNTRY3->(DBAPPEND()),COUNTRY3->code:=code,COUNTRY3->name:=name,COUNTRY3->residents:=residents})
SELECT("COUNTRY3")
DBGOTOP()
BROWSE()
DBCLOSEALL()
? RDDINFO(RDDI_DISCONNECT,,,_conn1)
? RDDINFO(RDDI_DISCONNECT,,,_conn2)
? "Check connection is closed in both mysql "
INKEY(0)
cls
cls
RETURN
STATIC PROC CreateTable()
&& Create tables in server1
? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME char(50), RESIDENTS int(11))")
? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU', 'Lithuania', 3369600), ('USA', 'United States of America', 305397000), ('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS', 'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia', 141900000)")
RETURN
STATIC PROC CreateTable2()
&& Create tables in server2
? RDDINFO(RDDI_EXECUTE, "DROP TABLE country","SQLMIX")
? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME char(50), RESIDENTS int(11))","SQLMIX")
? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('UY ', 'Uruguay', 3494382)","SQLMIX")
RETURN
PROC RDDSYS(); RETURN