Forum Discussion

rodg's avatar
rodg
Frequent Visitor
1 year ago
Solved

connect to Informix from report builder failed

Hi,

I am trying to connect to an Informix database using Report Builder, but I’m encountering an issue. Here’s what I have done so far:

  1. Installed the Informix ODBC driver (both 32-bit and 64-bit).
  2. Created a 64-bit ODBC connection.
  3. Successfully tested the connection to the database using this ODBC setup.

In Report Builder:

  • I created a new data source using the configured ODBC connection.
  • Entered the correct username and password.
  • However, the test connection failed with an error (see screenshot).

Does anyone know how to resolve this?

 

 

 

 

 

informix obcc report builder
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, rodg 

    Try to paste the query into Text to run, ODBC connector only supports? To occupy a placeholder for a parameter in Power BI Report Builder:

     

    The order of the parameter pages will determine the ? location.

    In stored procedures, we usually can't define one? as a query variable, so you're getting an error. Try writing a query inside the Report builder.

     

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, rodg 

    First, add an Informix database connection correctly in ODBC as shown in the following image:

     

    Connect to it using ODBC in report builder:

     

     

     

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

    • rodg's avatar
      rodg
      Frequent Visitor
      1 year ago

      Hi Jianpeng Li,

       

      The connection to the database is now active in Report Builder.

      However, I’m facing an issue when trying to use a stored procedure (on Informix) with parameters.

      Dataset Definition:

      • The dataset is correctly configured to call the stored procedure.

      • Parameter names match those in the stored procedure.

      Despite this, I keep encountering the following error:

      => ERROR [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred.

       

      I’ve set the LoggingTraceLevel in Report Builder to "Verbose," but unfortunately, the log file doesn’t provide any additional insights.

      Do you  have ideas on what might be causing this error?


      Could it be related to syntax, parameter handling, or some driver issue?

      Note: My OS is in Dutch, so the Report Builder UI is also in Dutch—apologies if any screenshots include Dutch text.

       

      Thanks in advance for your help!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, rodg 

        Try to paste the query into Text to run, ODBC connector only supports? To occupy a placeholder for a parameter in Power BI Report Builder:

         

        The order of the parameter pages will determine the ? location.

        In stored procedures, we usually can't define one? as a query variable, so you're getting an error. Try writing a query inside the Report builder.

         

         

         

        Best Regards

        Jianpeng Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

         

  • rodg's avatar
    rodg
    Frequent Visitor
    1 year ago

    Hi Jianpeng Li

     

    I have a working version.

    Thanks !

    Ronny