Crystal report with Mysql

Post Reply
User avatar
Maurizio
Posts: 705
Joined: Mon Oct 10, 2005 1:29 pm
Contact:

Crystal report with Mysql

Post 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
User avatar
fgondi
Posts: 636
Joined: Fri Oct 07, 2005 6:58 am
Location: Palencia, España
Contact:

Re: Crystal report with Mysql

Post 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
Un saludo
Fernando González Diez
ALSIS GHE Sistemas Informáticos
User avatar
Maurizio
Posts: 705
Joined: Mon Oct 10, 2005 1:29 pm
Contact:

Re: Crystal report with Mysql

Post 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
User avatar
fgondi
Posts: 636
Joined: Fri Oct 07, 2005 6:58 am
Location: Palencia, España
Contact:

Re: Crystal report with Mysql

Post by fgondi »

Maurizio wrote: Cann I have your email ?
fernando at alsisghe dot es
Un saludo
Fernando González Diez
ALSIS GHE Sistemas Informáticos
User avatar
dutch
Posts: 1395
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

Re: Crystal report with Mysql

Post 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.
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
Maurizio
Posts: 705
Joined: Mon Oct 10, 2005 1:29 pm
Contact:

Re: Crystal report with Mysql

Post 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
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: Crystal report with Mysql

Post 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"
 
Regards
Antonio H Ferreira
User avatar
dutch
Posts: 1395
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

Re: Crystal report with Mysql

Post 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.
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
Maurizio
Posts: 705
Joined: Mon Oct 10, 2005 1:29 pm
Contact:

Re: Crystal report with Mysql

Post by Maurizio »

User avatar
dutch
Posts: 1395
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

Re: Crystal report with Mysql

Post 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.
Maurizio wrote:Dutch ,

I use CRW 8 ,
Maybe this can help you ?
https://apps.support.sap.com/sap/suppor ... en/1218152
https://archive.sap.com/discussions/thread/3312394

Maurizio

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?
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
Maurizio
Posts: 705
Joined: Mon Oct 10, 2005 1:29 pm
Contact:

Re: Crystal report with Mysql

Post by Maurizio »

Have you created DSN ODBC?

Maurizio
www.nipeservice.com
User avatar
dutch
Posts: 1395
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

Re: Crystal report with Mysql

Post 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.
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
dutch
Posts: 1395
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

Re: Crystal report with Mysql

Post by dutch »

I got it now. I miss 1 line.

Code: Select all

oCRTable:Location := cDatabase+"."+oCRTable:Location
Thanks for all.
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
dutch
Posts: 1395
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

Re: Crystal report with Mysql

Post 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
               
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
Post Reply