Page 1 of 1
Crystal report with Mysql
Posted: Tue Aug 12, 2014 12:40 pm
by Maurizio
TO Fernando González Diez
I used Crystal Report for years wiyh DBF file and DLL CRPE.
Now need to use Mysql and I want to use Crystal report with COM .
I found your example (
http://fivetechsupport.com/forums/viewt ... 12#p143336 )
I know it's for ADS but I think is equal in Mysql.
In some parts I can make it work and I wanted to ask your help
I need to change the userID and the password , I try with LogOnServer and SetLogOnInfo but I have some error.
I have error With formulas and Parametros to .
I Use :
oCrp:FormulaFields:GetItemByName( 'vAlmacen' ):Text( "**Test**" )
oCrp:RecordSelectionFormula( '{FVCAB.FACTURA} = " --Test---"' )
I use Crystal report 8.
your help is greatly appreciated
Maurizio
Re: Crystal report with Mysql
Posted: Tue Aug 12, 2014 4:19 pm
by fgondi
I do not use MySQL
Looking on google, I saw that connects to MySQL through ODBC:
oCRDatabase:LogOnServer( "pdsodbc.dll", "NAME SERVER", "NAME DATABASE" "USER", "PASS" )
oCRTable:SetLogOnInfo( "NAME SERVER", "NAME DATABASE", "USER", "PASS" )
oCRParameterField:GetItemByName('pFchIni'):AddCurrentValue( dFchIni )
The parameters used for the step of vars the ADS Procedures using the report.
send me the rpt, and try to review it.
fernando at alsisghe dot es
Re: Crystal report with Mysql
Posted: Wed Aug 13, 2014 7:40 am
by Maurizio
Thank Fernando
//// oCRDatabase:LogOnServer( "pdsodbc.dll", "NAME SERVER", "NAME DATABASE" "USER", "PASS" )
This works .
Now I try with formula and parameters , and then I tell you.
// send me the rpt, and try to review it.
Cann I have your email ?
Maurizio
www.nipeservice.com
Re: Crystal report with Mysql
Posted: Wed Aug 13, 2014 6:44 pm
by fgondi
Maurizio wrote:
Cann I have your email ?
fernando at alsisghe dot es
Re: Crystal report with Mysql
Posted: Tue May 02, 2017 4:23 am
by dutch
Dear All,
I try with this ::logonserver() to MySql via ODBC but it isn't successful.
It errors on :LogOnServer() as
Error occurred at : 02/05/17, 10:54:18
Description : Error CrystalRuntime.Application.11:OPENREPORT:DATABASE/3 DISP_E_MEMBERNOTFOUND: LOGONSERVER
Args:
[ 1] = C crdb_odbc.dll
[ 2] = C localhost
[ 3] = C databasename
[ 4] = C root
[ 5] = C 1234
Stack Calls
==========================
Called from : => TOLEAUTO:LOGONSERVER(0)
Called from : D:\V6\OBJ\EZRPT.PRG => CRREPORT(1062)
Code: Select all
cReport := MEMVAR->cCRPath+cReport // "C:\Users\dutch\Desktop\CR11\VatReport.rpt" // Report Name
oCrystal:= CreateObject("CrystalRuntime.Application.11") // Declare CreateObject
oRpt := oCrystal:OpenReport(cReport) // Open Report
// Log On Server Procedure for ODBC
cServer := "localhost"
cDatabase:= "databasename"
cUser := "root"
cPassword:= "1234"
oCRDatabase := oRpt:Database
oCRDatabase:LogOnServer ('crdb_odbc.dll', cServer, cDatabase, cUser, cPassword )
Thank you in advance for any help and suggestion.
Re: Crystal report with Mysql
Posted: Tue May 02, 2017 7:02 am
by Maurizio
I use :
Code: Select all
TRY
oCRDatabase:LogOnServer ('pdsodbc.dll',cODBC ,cDataBase, cUserName ,cPassword )
CATCH
? "Errore in Apertura dati MySQL utente o password sbagliati "
Return nil
END
Maurizio
www.nipeservice.com
Re: Crystal report with Mysql
Posted: Tue May 02, 2017 8:49 am
by AHF
May be this can be useful:
Code: Select all
for n := 1 to nTables
oDbTable := oDbTables:Item(n)
oDbTable:dllname := "crdb_ads.dll" // or "crdb_odbc.dll" or anyother
//Get and delete all connection properties
oProp := oDBTable:ConnectionProperties()
oProp:DeleteAll()
//new con props! replace 2nd parameter with con string based on DSN name
oProp:add("DatabaseName","DSN=ADS_CDX;Datadirectory="+((cLocal)+"\crwfup.add")+;
";ConnectionHandle=alltrim(str(nHandle))"+;
";Compression=Internet;DefaultType=FoxPro;ServerTypes=7"+;
";AdvantageLocking=ON;CharSet=OEM;Language=ANSI;Locking=RECORD"+;
";UID=crystalreports;PWD=YOURPWD"+;
";EncryptionPassword=YOURPWD")
next
Another alternative if we have the report configured for any specific DSN we just alter the DSN parameters at ODBC not at the report:
Code: Select all
IF ConfigDSN( 0,ODBC_CONFIG_DSN,"Microsoft FoxPro VFP Driver (*.dbf)", ;
"DSN=My Name"+chr(0)+;
"SourceDB="+(cLocal)+chr(0)+;
"Description=ODBC for my database"+chr(0)+;
"SourceType=DBF"+chr(0)+;
"BackgroundFetch=Yes"+chr(0)+;
"Exclusive=No"+chr(0)+;
"Null=Yes"+chr(0)+;
"Deleted=Yes"+chr(0)+;
"Collate=Machine"+chr(0)+;
"SetNoCountOn=No"+chr(0)) =0
MSGINFO("Inform user could not run report")
ENDIF
Yet another alternative alter location of each table:
Code: Select all
for n := 1 to nTables
oDbTable := oDbTables:Item(n)
oDbTable:location := (cLocal)+"\"+oDbtable:name+".dbf"
next
Ex ConfigDSN for VFP
Code: Select all
DLL FUNCTION ConfigDSN( hEnv AS LONG, fRequest AS WORD, ;
lpsDriverv AS LPSTR, lpsAttrib AS LPSTR ) ;
AS _INT PASCAL LIB "VFPODBC.DLL"
Re: Crystal report with Mysql
Posted: Tue May 09, 2017 5:11 am
by dutch
Dear Antonio,
Thank you so much, I got and understand to use CRXIR2 from your code but I use MySql. Your sample code login is used for ADS.
Dear Maurizio,
I try as your recommend but I cannot find pdsodbc.dll. It has only p2sodbc.dll in the internet. I try with this dll but it still show an error.
Code: Select all
Description : Error CrystalRuntime.Application.11:OPENREPORT:DATABASE/3 DISP_E_MEMBERNOTFOUND: LOGONSERVER
Args:
[ 1] = C p2sodbc.dll
[ 2] = C localhost
[ 3] = C easyfo
[ 4] = C root
[ 5] = C xxxx
But I use CR XI R2, it's document tell that the DLL file to use with ODBC is
crdb_odbc.dll.
Thank you in advance for kind help.
Re: Crystal report with Mysql
Posted: Tue May 09, 2017 7:01 am
by Maurizio
Re: Crystal report with Mysql
Posted: Tue May 09, 2017 7:45 am
by dutch
Dear Maurizio,
Thank you for your kind help. I didn't get error now. I'm putting wrong cServerName, it should be "Data Source Name" in ODBC. Now I can run the CRViewer in localhost.
But I cannot change the database location, it is still reading from localhost, even I change to Data Source to MySql on WebSite.
Code: Select all
cReport := MEMVAR->cCRPath+cReport // "C:\Users\dutch\Desktop\CR11\VatReport.rpt" // Report Name
TRY
oCrystal:= CreateObject("CrystalRuntime.Application.11") // Declare CreateObject
CATCH oError
lStart := .F.
END
if lStart
oRpt := oCrystal:OpenReport(cReport) // Open Report
// Log On Server Procedure for ODBC
TRY
cDll := 'crdb_odbc.dll'
cServer := "EZFOV6"
cDatabase:= "easyfo_fov6"
cUser := "easyfo_root"
cPassword:= "xxxx"
oCRDatabase := oRpt:Database
oCRDatabase:LogOnServer(cDll, cServer, cDatabase, cUser, cPassword )
CATCH
lStart := .F.
MsgAlert('Cannot connect to '+cServer)
END
What do I wrong?
Re: Crystal report with Mysql
Posted: Tue May 09, 2017 8:50 am
by Maurizio
Have you created DSN ODBC?
Maurizio
www.nipeservice.com
Re: Crystal report with Mysql
Posted: Tue May 09, 2017 10:04 am
by dutch
Yes, I do.
This is Connection Parameters
--------------------------------------
Data Source Name : EZFOV6
TCP/IP Server : easyfo.com Port : 3306
User : easyfo_root
Password : nimda
Database : easyfo_v6
Code: Select all
TRY
oCrystal:= CreateObject("CrystalRuntime.Application.11") // Declare CreateObject
CATCH oError
lStart := .F.
END
if lStart
oRpt := oCrystal:OpenReport(cReport) // Open Report
// Log On Server Procedure for ODBC
TRY
cDll := 'crdb_odbc.dll'
cServer := "EZFOV6"
cDatabase := "easyfo_fov6"
cUser := "easyfo_root"
cPassword := "nimda"
oCRDatabase := oRpt:Database
oCRTables := oCRDatabase:Tables
oCRTable := oCRDatabase:Tables(1)
// oCRTable:ConnectionProperties:DeleteAll()
oCRDatabase:LogOnServer( cDll, cServer, cDatabase, cUser, cPassword )
nTables := oCRTables:count()
for y := 1 to nTables
oCRTable := oCRDatabase:Tables(y)
oCRTable:ConnectionProperties:DeleteAll()
oCRTable:SetLogOnInfo( cServer, cDatabase, cUser, cPassword )
next
CATCH
lStart := .F.
MsgAlert('Cannot connect to '+cServer)
END
Now, the problem is the report can not find the table. It shows error message as below
The table "fotbl" could not be found.
Re: Crystal report with Mysql
Posted: Wed May 10, 2017 4:53 am
by dutch
I got it now. I miss 1 line.
Code: Select all
oCRTable:Location := cDatabase+"."+oCRTable:Location
Thanks for all.
Re: Crystal report with Mysql
Posted: Thu May 11, 2017 10:13 am
by dutch
Dear All,
I use ADS9 (Free table) and Crystal Report XI R2.
How can I change at runtime. I try as following code but it got an error "Invalid Argument Provided"
If I do not change the Location, the data is default in RPT form. I run fine.
Code: Select all
cReport := MEMVAR->cCRPath+cReport // "C:\Users\dutch\Desktop\CR11\VatReport.rpt" // Report Name
TRY
oCrystal:= CreateObject("CrystalRuntime.Application.11") // Declare CreateObject
CATCH oError
lStart := .F.
END
if lStart
oRpt := oCrystal:OpenReport(cReport) // Open Report
oCRDatabase := oRpt:Database
oCRTables := oCRDatabase:Tables
oCRTable := oCRDatabase:Tables(1)
oCRTable:ConnectionProperties:DeleteAll()
nTables := oCRTables:count()
for y := 1 to nTables
oCRTable := oCRTables:Item(y)
oCRTable:location := cDataPath+lower(rtrim(oCRTable:location))+".dbf" // cDataPath is where the dbf location.
next
oRpt:DiscardSavedData()
oRpt:EnableParameterPrompting("False")
oRpt:DisplayProgressDialog := 1
if lStart
DEFINE WINDOW oWnd FROM 3, 6 TO 20, 70 TITLE "Print and Preview"
oCRViewer := TActiveX():New( oWnd, "CrystalReports.ActivexReportViewer" )
oWnd:oClient := oCRViewer
oCRViewer:EnableAnimationCtrl := 0
oCRViewer:EnableExportButton := 0
oCRViewer:EnableStopButton := 1
oCRViewer:EnableNavigationControls := 1
oCRViewer:EnablePopupMenu := 0
oCRViewer:EnableCloseButton := 0
oCRViewer:EnableDrillDown := 1
oCRViewer:EnableGroupTree := 1
oCRViewer:EnableStopButton := 0
oCRViewer:EnablePrintButton := 1
oCRViewer:ReportSource := oRpt
oCrViewer:Zoom :=100
oCRViewer:ViewReport()
oWnd:oClient := oCRViewer
ACTIVATE WINDOW oWnd MAXIMIZED
end
end