Question about SQL

[driessen]

Hello,

I am thinking about transferring my DBF-files in my FWH-application to a SQL-database.

Until now, I have no experience at all with SQL.

What do I need to do ?

Or where can I find a good manual to do the transfer ?

Thank you in advance.

[Rick Lipkin]

Driessen

ADO is the best choice for me .. there is the 'free' ado rdd available on this forum or you can do it the way I do and code everything manually with recordsets.

I wrote a thread on the subject with samples:

http://fivetechsoft.com/forums/viewtopi ... &highlight

If I can be of any help just drop me a message.

Rick Lipkin
SC Dept of Health, USA
lipkinrm29063@yahoo.com

[driessen]

Rick,

Thanks a lot for your answer.

I'll read your topic very carrefully. If necessary I'll send you a mail for some more help.

Thanks.

[Rick Lipkin]

Michel

Here is some specific code and listbox for MS Sql server .. note the connection string .. :

// public defined in Main.prg:

xPROVIDER := "SQLOLEDB"
xSOURCE := "MSSQL01"
xCATALOG := "PCAS"
xUSERID := "pcasuser"
xPASSWORD := "pcas"



// grpbrow.prg
//
#INCLUDE "FIVEWIN.CH"
STATIC oBROW,oGRP
STATIC lOK
//----------------------------
FUNCTION _GRPbrow( oWndMDI )
LOCAL SAYING, oRs,oErr, cSQL, cTITLE
lOK := .F.
IF xSUPER = 'Y' .or. xADMIN = 'Y'
ELSE
SAYING := "SORRY ... Supervisor or Admin Rights only"
MsgAlert( SAYING )
_CleanUp()
RETURN(.F.)
ENDIF

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic
IF xADMIN = 'Y'
cSQL := "SELECT * FROM GROUPS ORDER BY PROGID,CODE"
cTITLE := "Group Records Browse for ALL"
ELSE
cSQL := "SELECT * FROM GROUPS where progid = '"+xPROGID+"' ORDER BY CODE"
cTITLE := "Group Records Browse for Progid "+xPROGID
ENDIF
TRY
oRs:Open( cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error in Opening GROUPS table" )
_CleanUp()
RETURN(.F.)
END TRY
IF oRs:EOF
oRs:Close()
SAYING := "Could not find any matching records for "+cSQL
MsgInfo( SAYING )
_CleanUp()
RETURN(.F.)
ENDIF
oRs:MoveFirst()
DEFINE WINDOW oGRP ;
FROM 2,2 to 30,70 ;
of oWndMDI ;
TITLE cTITLE ;
MENU BuildMenu( oRs ) ;
NOMINIMIZE ;
NOZOOM ;
MDICHILD
@ 0, 0 LISTBOX oBrow FIELDS ;
oRs:Fields("CODE"):Value, ;
oRs:Fields("DESC"):Value, ;
oRs:Fields("progid"):Value ;
SIZES 80,250,80 ;
HEADERS "Code", ;
"Description", ;
"ProgID" ;
of oGRP ;
ON DBLCLICK( _GrpView( "V", oRs )) ;
UPDATE
oBrow:bLogicLen := { || oRs:RecordCount }
oBrow:bGoTop := { || oRs:MoveFirst() }
oBrow:bGoBottom := { || oRs:MoveLast() }
oBrow:bSkip := { | nSkip | Skipper( oRs, nSkip ) }
oBrow:cAlias := "ARRAY"
oGrp:oClient := oBROW
oGrp:SetControl( oBrow )

ACTIVATE WINDOW oGRP ;
VALID ( IIF( !lOK, GrpCLose( .T. , oRs ), .F. ))
RETURN( NIL )
//-------------------------------
Static Func _Cleanup()
oBROW := NIL
oGRP := NIL
RETURN(.T.)
//------------------//
static FUNCTION BuildMenu( oRs )
LOCAL oMenu1
MENU oMenu1
MENUITEM "&Add ..." ;
ACTION ( _GRPVIEW( "A", oRs ), ;
oBROW:Refresh() ) ;
MESSAGE "Add a new record"
MENUITEM "&Edit ..." ;
ACTION ( _GRPVIEW( "E", oRs ), ;
oBROW:ReFresh() ) ;
MESSAGE "Edit this record"
MENUITEM "&Delete ..." ;
ACTION GRPDEL( oRs) ;
MESSAGE "Delete this record"
MENUITEM "&View ..." ;
ACTION _GRPVIEW( "V", oRs ) ;
MESSAGE "View this record"
MENUITEM "&Quit" ;
ACTION oGRP:END()
ENDMENU
RETURN( oMenu1 )
//------------------------
Static FUNCTION GRPCLOSE(lCLEAN, oRs )
IF lCLEAN = .T.
lOK := .T.
oBROW:cALIAS := nil
oRs:Close()
_CleanUp()
ENDIF
RETURN(lOK)
//--------------------
Static FUNCTION GRPDEL( oRs )
LOCAL SAYING
IF xSUPER = 'Y' .or. xADMIN = 'Y'
ELSE
SAYING := "Sorry ... Supervisor or Admin Rights Only"
MsgAlert( SAYING )
RETURN(NIL)
ENDIF
IF oRs:EOF
SAYING := "Sorry ... Before you can Delete a "
SAYING += "record, you have to Add one first"
MsgAlert( SAYING )
RETURN(NIL)
ENDIF
IF MsgYesNo( "Are you SURE you want to DELETE this?" )
oRs:Delete()
oRs:MoveNext()
IF oRs:eof .and. .not. oRs:bof
oRs:MoveFirst()
ENDIF
ENDIF

oBROW:Refresh(.T.)
SysReFresh()
RETURN(NIL)
//-------------------------------
STATIC FUNCTION SKIPPER( oRsx, nSkip )
LOCAL nRec := oRsx:AbsolutePosition
oRsx:Move( nSkip )
IF oRsx:EOF; oRsx:MoveLast(); ENDIF
IF oRsx:BOF; oRsx:MoveFirst(); ENDIF
RETURN( oRsx:AbsolutePosition - nRec )
// end GRPBROW.PRG

[Enrico Maria Giordano]

Rick, do you have a connection string for MySQL remote?

EMG

[Antonio Linares]

Enrico,

There is one included in the ADORDD samples

[Enrico Maria Giordano]

The following sample stops with DISP_E_UNKNOWNNAME:

Code: Select all

FUNCTION MAIN()

    LOCAL oRs := CREATEOBJECT( "ADODB.Recordset" )

    oRs:Open( "SELECT * FROM Table", "DRIVER={MySQL ODBC 3.51 Driver};server=xxx.xxx.xxx.xxx;database=dbname;uid=username;pwd=password", 0, 3 )

    oRS:Close()

    RETURN NIL

Do I have to install something MySQL related on the client?

EMG

[Antonio Linares]

Enrico,

> Do I have to install something MySQL related on the client?

Yes, you have to install the MySQL client software

[Enrico Maria Giordano]

Do you have the link at hand?

EMG

[Antonio Linares]

Enrico,

No, sorry

As far as I remember you can download it from the MySQL website.

Its a long time since we tested it

[Enrico Maria Giordano]

Already found, thank you.

EMG

[Antonio Linares]

Enrico,

Would you mind to post here the download url ? Thanks!

[Enrico Maria Giordano]

Here it is:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

EMG

[Antonio Linares]

Enrico,

Thanks!

[ShumingWang]

Use harbour\contrib\Tmysql.prg ,without any windows setting ,direct TCP/IP using, like Xbase.
t1:=oserver:query("select * from table1 ")
t1:gotop()
t1:skip(1)
t1:fieldname1:="aaaa"
t1:save()
t1:delete()
t1:end()
...

Our modified tmysql.prg:
http://www.xtech.com.cn/down/tmysql.prg
http://www.xtech.com.cn/down/sample1.prg
http://www.xtech.com.cn/down/sample2.prg

Regards!
Shuming Wang