Crystal report with Mysql

[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

[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

[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

[fgondi]

Cann I have your email ?

fernando at alsisghe dot es

[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.

[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]

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"
 

[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.

[Maurizio]

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

[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.

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?

[Maurizio]

Have you created DSN ODBC?

Maurizio
www.nipeservice.com

[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.

[dutch]

I got it now. I miss 1 line.

Code: Select all

oCRTable:Location := cDatabase+"."+oCRTable:Location

Thanks for all.

[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