topic-29045

Board index FiveWin for Harbour/xHarbour Crystal report with Mysql

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Crystal report with Mysql
Posted: Tue Aug 12, 2014 12:40 PM

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

Maurizio
www.nipeservice.com

fgondi

Posts: 694

Joined: Fri Oct 07, 2005 06:58 AM

Re: Crystal report with Mysql
Posted: Tue Aug 12, 2014 04:19 PM

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 Sistemas Informáticos

Maurizio

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Re: Crystal report with Mysql
Posted: Wed Aug 13, 2014 07:40 AM

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

Maurizio
www.nipeservice.com

fgondi

Posts: 694

Joined: Fri Oct 07, 2005 06:58 AM

Re: Crystal report with Mysql
Posted: Wed Aug 13, 2014 06:44 PM

Maurizio wrote:
Cann I have your email ?

fernando at alsisghe dot es

Un saludo

Fernando González Diez

ALSIS Sistemas Informáticos

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Crystal report with Mysql
Posted: Tue May 02, 2017 04:23 AM

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 (fw): Select all Collapse

    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 2304 / 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)

Maurizio

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Re: Crystal report with Mysql
Posted: Tue May 02, 2017 07:02 AM

I use :

Code (fw): Select all Collapse

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: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: Crystal report with Mysql
Posted: Tue May 02, 2017 08:49 AM

May be this can be useful:

Code (fw): Select all Collapse

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 (fw): Select all Collapse

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 (fw): Select all Collapse

for n := 1 to nTables
    oDbTable := oDbTables:Item(n)
    oDbTable:location := (cLocal)+"\"+oDbtable:name+".dbf"
next

Ex ConfigDSN for VFP

Code (fw): Select all Collapse

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

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Crystal report with Mysql
Posted: Tue May 09, 2017 05:11 AM

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 (fw): Select all Collapse

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 2304 / 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)

Maurizio

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Re: Crystal report with Mysql
Posted: Tue May 09, 2017 07:01 AM

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

Maurizio
www.nipeservice.com

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Crystal report with Mysql
Posted: Tue May 09, 2017 07:45 AM

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 (fw): Select all Collapse

   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 2304 / 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)

Maurizio

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Re: Crystal report with Mysql
Posted: Tue May 09, 2017 08:50 AM

Have you created DSN ODBC?

Maurizio

www.nipeservice.com

Maurizio
www.nipeservice.com

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Crystal report with Mysql
Posted: Tue May 09, 2017 10:04 AM

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 (fw): Select all Collapse

   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 2304 / 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)

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Crystal report with Mysql
Posted: Wed May 10, 2017 04:53 AM

I got it now. I miss 1 line.

Code (fw): Select all Collapse

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

Thanks for all.

Regards,

Dutch

FWH 2304 / 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)

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Crystal report with Mysql
Posted: Thu May 11, 2017 10:13 AM

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 (fw): Select all Collapse

   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 2304 / 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)

FiveTech Support Forums

Continue the discussion