No connection to DB SAP Hana

[Former Member]

Hello,

I have a problem trying to connect a report to a SAP Hana DataBase. My code looks like this:

private void _conecta(ReportDocument aRpt)
{
        string sServidor;
        TableLogOnInfo tablaLI;

        //sServidor = "Driver={LIBODBCHDB32};ServerNode=" + _sConexion.sServidor + ";Database=" + _sConexion.sBD;
        //sServidor = "Driver={B1CRHPROXY32};ServerNode=" + _sConexion.sServidor + ";Database=" + _sConexion.sBD;
        //sServidor = "Driver={B1CRHPROXY32};ServerNode=" + _sConexion.sServidor + ";CS=" + _sConexion.sBD;
        sServidor = "Driver={HDBODBC32};ServerNode=" + _sConexion.sServidor + ";CS=" + _sConexion.sBD;

    foreach (Table tabla in aRpt.Database.Tables)
    {
        tablaLI = tabla.LogOnInfo;

        //tablaLI.ConnectionInfo.ServerName = _sConexion.sServidor;
        tablaLI.ConnectionInfo.ServerName = sServidor;
        tablaLI.ConnectionInfo.DatabaseName = _sConexion.sBD;
        tablaLI.ConnectionInfo.UserID = _sConexion.sUsuario;
        tablaLI.ConnectionInfo.Password = _sConexion.sPwd;
        tabla.ApplyLogOnInfo(tablaLI);
    }
    aRpt.Refresh();
}

This code works perfectly with a SQL Server DB.

I think the problem is that I want to connect the report to a schema of the DB instead of to a DB because SAP Hana has only one DB with many schemas.

I saw in other dicussion that I have to pass the conection string in the server property, but this not work for me. As you can see I've tried diferent ways to pass the connection string trying to use the ODBC connection (HDBODBC32) instead of CR driver connector (B1CRHPROXY32).

As the result I get:

Then if I write the password for the SYSTEM I get the message "It's not possible to connect. Try again".

Thanks in advance.

[Former Member · Accepted solution]

Hello,

Apparently the correct solution is:

string strConnection = "DRIVER= {B1CRHProxy32};UID=" + _sConexion.sUsuario;



    strConnection += ";PWD=" + _sConexion.sPwd + ";SERVERNODE=" + _sConexion.sServidor;
    strConnection += ";DATABASE=" + _sConexion.sBD + ";";

    NameValuePairs2 logonProps2 = aRpt.DataSourceConnections[0].LogonProperties;
    logonProps2.Set("Provider", "B1CRHProxy32");
    logonProps2.Set("Server Type", "B1CRHProxy32");
    logonProps2.Set("Connection String", strConnection);
    logonProps2.Set("Locale Identifier", "1033");
    aRpt.DataSourceConnections[0].SetLogonProperties(logonProps2);

    aRpt.DataSourceConnections[0].SetConnection(_sConexion.sServidor, _sConexion.sBD,



   
 _sConexion.sUsuario, _sConexion.sPwd);

Best regards.

[former_member11696]

Hi Carlos,

I have not tried this in a while now but it should work. I also noticed you mentioned the B1CRHPROXY32 which suggests you are using Business One on Hana?

If so I've never tried that driver, if you want to pursue it search for the Business One forum and post your question there.

Using the 32 bit Hana ODBC driver does work, the Database name you are correct, should be the Schema.Table.

So if you are compiling your app in 32 bit mode make sure you created your ODBC DSN in the \SysWow64 odbcadm32.exe Admininstrator. Could simply be your app can't find the DSN name which is what happens when you try to preview the report and the Server Name and Database name is grayed out as in your screen shot.

You also have the option to create a case for Business One.

Don

[Former Member]

Hi Don,

Tanks for your answer, but it's not possible to use the 32 bit ODB driver in CR Designer because it doesn't admit the schema, the only way is to wirte down the schema in the SQL command of the report, but if I use the schema in the SQL command the report won't work in other schema.

In my opinion this is not a problem for SAP Business One (SBO), not even a problem of development of SBO, since we have an AddOn that uses the runtime of CR to preview reports; therefore it is an issue of CR for Visual Studio.

I think the key is that you can not specify the schema in the ODBC driver.

Moreover, SBO has well resolved because you can attach the same report in different companies (schemas) and when launching the report is able to change the schema internally. How can we do that? I'll try to post this issue in the SBO forum!

Thanks in advance.

[former_member11696]

Hi Carlos,

CR Designer is a 32 bit application so it will not work unless you use a 32 bit DSN.

This should work in a Command Object also, I tested this and simply replaced the Schema with the new Schema in the Command SQL:

SELECT "CUSTOMER"."CUSTOMERNAME", "CUSTOMER"."LASTYEARSSALES"
FROM "XTREME"."CUSTOMER" "CUSTOMER"

with

SELECT "CUSTOMER"."CUSTOMERNAME", "CUSTOMER"."LASTYEARSSALES"
FROM "I818946TEST"."CUSTOMER" "CUSTOMER"

And refreshed the report and it worked fine.

And it does work in CRD, the above SQL was create off a direct table connection and then copied into a Command.

So it could be a few issues, you may be on an older version of the ODBC driver or possibly your permissions are not configured for the other schema.

If you are not a Hana Admin check with the Admin to confirm you do have access to the other Schema's.

Don