Is is possible to connect to an ADO XML file in the same fashion as ODBC?

[Former Member]

Hi,

We have a client server application which sends report data to the client machine for display using Crystal reports. When building reports for use with the application the user selects an ODBC data source for a Crystal report to extract the data and then they can grant access to other users to use their report. As part of the install of our application we configure USER DSNs on the client machine to use the users local temporary path e.g. C:\Users\Dom\AppData\Local\AppName... In this way the report will always work correctly using the data source irrespective of whose machine the report is developed on or what user is running the report.

I am wondering is it possible to do a similar thing with the ADO (.NET) XML connection. When creating a report with the ADO (.NET) XML connection you are asked for a File Path to the XML file. So if my path was C:\Users\Dom\AppData\Local\AppName\Temp\ado.xml and I save the report, this is where the reports always looks for the data. If user John logs in on the same machine or on their own machine wanted to run the report then it would need to look in C:\Users\John\AppData\Local\AppName\Temp\ado.xml. Is there a way to configure this type of setup in the report when you are designing it so that the connection is not directory dependent?

Thanks,

Dominic

[former_member183750 · Accepted solution]

No, not really. But in your app you could add code to find out the user path, e.g.; C:\Users\John\...

and then direct the report to look for the xml there. The code to set the report to look for an xml path would be something like in the following KBA:

1203980 - How to change the location of the .XSD and .XML files at runtime in a .NET application

Now, your report would have be using the crdb_xml.dll or possibly the crdb_adoplus.dll. For crdb_xml, see the following:

1197681 - Distributing Crystal Reports .NET application created using XML Native Driver (CRDB_XML) t...

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

[Former Member · Accepted solution]

Hi Ludek,

Thanks for your response. I have looked at the VS code and tried to interpret it for C++ (which is what we are using) with the craxdrt.dll.

We drill down through the IDatabase to IDatabaseTables to IDatabaseTable. At this point we call

        if (table->get_ConnectBufferString(&connect) != S_OK)

        {

                ReportError("Failed To Get Connection Information For Table");

                return(false);

        }

From this we get the connection string which is:

     connect = :"Internal Connection ID={5fa9e982-58ef-4c73-bd00-6bc03606ded7};;File Path =C:\\Users\\Dom\AppData\\Local\\ReportData\\xmldata.xml"

Then we are able to change the connect information to be

     connect = :"Internal Connection ID={5fa9e982-58ef-4c73-bd00-6bc03606ded7};;File Path =C:\\Temp\\xmldata.xml"

where C:\Temp\xmldata.xml is a second copy of the XML file. After this we call

        if (table->set_ConnectBufferString(connect) != S_OK)

        {

                 ReportError("Failed To Update Connection information For Table");

                return(false);

        }

to update the connection string with the file path to the new location which successfully returns.

The problem now is that when running the report we receive a "Logon Failed" error. We have tried a large number of things to resolve this but are clearly missing something. Are you able to assist with identifying what we have missed that now causes the login to fail?

Thanks,

Dominic

[former_member183750 · Accepted solution]

Well, my assumption was that you were using "SAP Crystal Reports, Developer Version for Visual Studio .NET" and VS 2010 or later(?).

What version of CR and VS are you using?

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

We are using Crystal 11.5 at the moment due to our understanding that this was the last version which supported craxdrt.dl without us having to change development environments.

We are not using Visual Studio, our development environment is Borland C++ Builder.

We use the QueryInterface to provide the report functionality that we require. At present we support ODBC Text Driver reports but are trying to expand support to ADO because Crystal loads the ADO recordset significantly faster.

I tried a large number of combinations of the provided IDatabsaeTable set_ functions to try to get this to work but everything I tried resulted in the same "Logon Failed" message.

Thanks,

Dominic

[former_member183750 · Accepted solution]

OK, good. I moved the post to the SAP Crystal Reports - Legacy SDKs  forum.

Your understanding re RDC support is correct, however along with that comes the understanding that you are working with an SDK that may not work on the latest Operating Systems, etc., etc.

The following wiki has links to a number of sample apps. One of the links is "Passing an ADO Recordset to a Report" (under Database heading) and it may be worth having a look at. The wiki:

http://wiki.scn.sap.com/wiki/x/WABmBQ

You can connect directly to an XML file also, see KBA 1205003 - How to report off of XML files through the Report Designer Component (RDC).

While we're at KBAs, this one may be of some interest (probably not, but just in case):

1212017 - Creating a report using Unbound Fields and ADO

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

Thanks for the articles, I have reviewed these and have not yet been able to resolve my issue. The article suggests that by setting the File Path property things should work.

myReport.Database.Tables(1).ConnectionProperties("file path") = "C:\crystal\ChangeReport.XML"

I believe that ! am doing this but am still receiving the "Logon failed" message. It would seem that I am still missing something or the way I am setting this is not correct even though the set is returning S_OK. I am including a small snippet of code which does this in the hope that you are able to identify what I have overlooked.

        TCOMIApplication   CrystalApp = NULL;

        IReport                         *Report = NULL;

        IDatabase                    *database;

        IDatabaseTables       *tables;

        IDatabaseTable         *table;

        /* First we create the print engine, with the supplied report file.

        */

        if (Appl.Create(CrystalApp) != S_OK)

        {

                ReportError("Failed to locate crystal dll. Please Check installation\ndirectory for craxdrt.dll and crviewer.dll");

                return(false);

        }

        if (CrystalApp->OpenReport(StringToOleStr(argv[0]), empty, &Report) != S_OK)

        {

                ReportError(("Failed to open report " + (AnsiString) argv[0]).c_str());

                return(false);

        }

        /* Discard any data saved with the report

        */

        if (Report->DiscardSavedData() != S_OK)

        {

                ReportError("Unable to discard data saved with report");

                return(false);

        }

        if (Report->get_Database(&database) != S_OK)

        {

                ReportError("Failed To Get Report Database Information");

                return(false);

        }

        if (database->get_Tables(&tables) != S_OK)

        {

                ReportError("Failed To Get Report Tables");

                return(false);

        }

        if (tables->get_Count(&count) != S_OK)

        {

                ReportError("Failed To Get Tables Count");

                return(false);

        }

        /* We are looking specifically for ADO files so the count should be one.

        ** Anything else we do not need to check

        */

        if (count > 1)

        {         

             tables->Release();

             database->Release();

             return(true);

        }

        if (tables->get_Item(1, &table) != S_OK)

        {

                ReportError("Failed To Get Table");

                return(false);

        }

        if (table->get_ConnectBufferString(&connect) != S_OK)

        {

                ReportError("Failed To Get Connection Information For Table");

                return(false);

        }

        /* We look for the part of the string that has the File Path.

        */

        conninfo = (AnsiString) connect;

        if ((index = conninfo.Pos("File Path =")) > 0 && conninfo.Pos("xmldata.xml") > 0)

        {

                index += 11;

                filepath = conninfo.SubString(index, conninfo.Length() - index + 1);

                start = conninfo.SubString(1, index - 1);

                rest = "";

                if ((index = filepath.Pos(";")) > 0)

                {

                        rest = filepath.SubString(index, filepath.Length() - index + 1);

                        filepath.SetLength(index);

                }

                newpath = "C:\\Temp\\xmldata.xml";

                conninfo = start + newpath + rest;

                connect = WideString(conninfo);

                if (table->set_ConnectBufferString(connect) != S_OK)

                {

                     ReportError("Failed To Update Connection information For Table");

                     return(false);

                }

        } 

        table->Release();

        tables->Release();

        database->Release();

        return(true);

When I do the get for the connection string it returns the following

connect = :"Internal Connection ID={5fa9e982-58ef-4c73-bd00-6bc03606ded7};File Path =C:\\Users\\Dom\AppData\\Local\\ReportData\\xmldata.xml"

At this point I am not sure if the Internal Connection ID part is required for the set or not. I have tried setting the connection string by both retaining this part and also removing it as follows.

connect = "Internal Connection ID={5fa9e982-58ef-4c73-bd00-6bc03606ded7};File Path =C:\\Temp\\xmldata.xml"

connect = "File Path =C:\\Temp\\xmldata.xml"

In both cases the connection string updates without error and then when I display the report in both cases I still get the same "Logon failed." error.

                if (Report->QueryInterface(IID_IUnknown, (void **) &unknown) != S_OK)

                    return(false);

                Form->CrystalViewer->ControlInterface->set_ReportSource(unknown);

                unknown->Release();

                Form->CrystalViewer->ViewReport();

If you are able to suggest anything I may have missed I would be extremely appreciative.

Thanks,

Dominic

[former_member183750 · Accepted solution]

Hi Dominic

Couple of ideas;

1) See if adding code to point to the XSD (if applicable) will help:

myReport.Database.Tables(1).ConnectionProperties("local xml file") = "C:\test\george.xml"

myReport.Database.Tables(1).ConnectionProperties("local schema file") = "C:\test\george.xsd"

2) Enable the option "Verify on 1st refresh". This option you can find in the CR designer under File | Report Options.

Now theoretically, if you are connecting to the same XML (same path) as what the report was created off of, you should not need any code to set the path to the XML as the report "remembers" that. So, this may be a good test also. If you are trying to connect to a different XML and / or an XML in a different path, try to do the same connection in the CR designer. Does that work? These are just tests, not a way to proceed in the actual app, but this may give us some indication of what is going on.

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

1. With ADO recordsets the schema is is included at the top of the recordset and so there is no xsd file. I did try try setting just the local xml file but this did not work.
2. I also tried setting the Verify on First Refresh option but this made no difference.
   
   

I then tried your couple of suggestions through the designer. I was able to log off and on again to the same XML file be it in the same location or in a difference location without issue.

I then thought I would try resetting the connection string to be the connection string that was returned from the get call to see if this made any difference. i.e.

        if (table->get_ConnectBufferString(&connect) != S_OK)

        {

                ReportError("Failed To Get Connection Information For Table");

                return(false);

        }

        if (table->set_ConnectBufferString(connect) != S_OK)

        {

                ReportError("Failed To Set Connection information For Table");

                return(false);

        }

Once again this made none so I am stumped as to what to do given the connection string is exactly the same. It would seem that the set is triggering something that expects something else to be set but I can't get what that would be given the only exposed set functions are

set_ConnectBufferString

set_DllName

set_Name

set_Location

looking at these I thought that perhaps after setting the connection string I may need to also reset these so I tried the following:

                if (table->get_DllName(&dllname) != S_OK)

                {

                        ReportError("Failed To Get DLL Name");

                        return(false);

                }

                if (table->get_Name(&dbname) != S_OK)

                {

                        ReportError("Failed To Get Database Name For Table");

                        return(false);

                }

                if (table->get_Location(&dblocation) != S_OK)

                {

                        ReportError("Failed To Get Table Location");

                        return(false);

                }

                if (table->get_ConnectBufferString(&connect) != S_OK)

                {

                        ReportError("Failed To Get Connection information For Table");

                        return(false);

                }

                if (table->set_ConnectBufferString(connect) != S_OK)

                {

                        ReportError("Failed To Set Connection information For Table");

                        return(false);

                }

                if (table->set_Name(dbname) != S_OK)

                {

                        ReportError("Failed To Set Database Name For Table");

                        return(false);

                }

                if (table->set_DllName(dllname) != S_OK)

                {

                        ReportError("Failed To Set DLL Name");

                        return(false);

                }

                if (table->set_Location(dblocation) != S_OK)

                {

                        ReportError("Failed To Set Table Location");

                        return(false);

                }

The sets for ConnectBufferString, Name and DllName were all fine but the set for Location failed meaning that this is not the solution either (but I did try running it without setting Location which resulted in no change).

Regards,

Dominic

[former_member183750 · Accepted solution]

Hi Dominic

As you can probably tell, my C++ is next to zero these days, not that it ever was much more than a "Hello World" level. I do know that we could connect to XML using VB, so C++ should not be any different.  But couple of things I wonder about.

If you have a report that works in the designer, and take that report and create a new app with the following pseudocode, it should just work;

Load report

View Report

Use no other code, use a report with no subreports.

I also wonder about the exact version of CR XI R2 that you are using. The craxdrt.dll should have a version of 11.5.12.1838. If it does not, let's get it updated by following the steps in this blog:

Oh, a doc I dug up - not sure if it's worth anything, but it documents C++ code a bit:

http://www.sdn.sap.com/irj/boc/go/portal/prtroot/docs/library/uuid/d07d2ec2-721e-2b10-73ba-d0f237f61...

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

Thanks again for your suggestions.

The report worked fine if I didn't try to to change the ConnectBufferString (with or without any extra code).

I was running with the latest dll for all my development and testing.

On reviewing the documentation you had suggested again I decided to get the IConnectionProperty interface. This allowed me to get the property for File Path and to then set it value independently of the ConnectBufferString. This resulted in the report then working.

        if (table->get_ConnectionProperties(&info) != S_OK)

        {

                ReportError("Failed To Get Table Connection Properties");

                return(false);

        }

        /* If we don't has a "File Path" then we are also not an ADO report

        */

        if (info->get_Item(WideString("File Path"), &property) != S_OK)

                return(true);

        dispatch = property.pdispVal;

        if (dispatch->QueryInterface(IID_IConnectionProperty, (void **) &connection) != S_OK)

        {

                ReportError("Failed To Get Connection Property Interface For File Path");

                return(false);

        }

        filepath.vt = VT_BSTR;

        filepath.bstrVal = WideString("");

        if (connection->get_Value(&filepath) != S_OK)

        {

                ReportError("Failed To Get Database File Path For Table");

                connection->Release();

                return(false);

        }

        /* We look for an ADO recordset. This is identified by a connection to

        ** the file xmldata.xml

        */

        connpath = (AnsiString) filepath.bstrVal;

        if (connpath.Pos("xmldata.xml") > 0)

       {

            filepath.bstrVal = WideString("C:\\Temp\\xmldata.xml");

            if (connection->set_Value(filepath) != S_OK)

            {

                   ReportError("Failed To Reset Database File Path For Table");

                   connection->Release();

                  return(false);

            }

       }

       connection->Release();

When initially working on this I had called get_NameIDs to find the names of the Properties. When I saw that the individual properties were the same as what was in the ConnectBufferString I made the incorrect assumption that setting these via the ConnectBufferString would have the same effect as setting each of these individually. Setting the individual property using set_Value resolved the issue.

Thanks for your assistance.

Regards,

Dominic

[former_member183750 · Accepted solution]

I love seeing post like this 1st thing in the morning

Happy coding,

- Ludek