Command line InfoStore Query Builder with export to Excel

A free command line tool that runs query against CMS database and saves the result in Excel or CSV.

Options

-cms: BO server. Default is localhost
-username: BO user name. Default is Administrator
-password: User password. Default is blank
-auth: Authentification. Default is secEnterprise

-query: CMS query (the same as you run in Query Builder). See example.
-file: CMS query will be read from the file.
You should either specify -query: or -file: but not both.

-excel: Output excel file name. It will contain two spreadsheets: one for simple properties
and another for containers. See containers.
-csv: Output cvs file for simple properties. See containers.
-csv2: Output cvs file for containers properties. See containers.
If neither -excel: nor -csv: are specified, the simple properties will be printed to the standard output in CSV format.

Example

java -jar querybuilder.jar -query:"select si_name from ci_infoobjects"

Containers

There are simple properties of CMS objects e.g. SI_ID, SI_NAME, and containers – properties that have subproperties. For instance, the property SI_PROMPTS has subproperties SI_NUM_PROMPTS, SI_PROMPT1, SI_PROMPT2 etc. In turn SI_PROMPT1 has subproperties: SI_PROMPT, SI_PROMPT_TYPE, SI_OPTIONS etc. The containers are exported in the form: infoobject id, infoobject title, property path, property value.

Download

The tool has been desupported. Check Biclever CMS Query Builder.

21 thoughts on “Command line InfoStore Query Builder with export to Excel”

  1. mtt

    Problem : use it on xir2 – first try -simple query to test connection

    C:\Documents and Settings\ACATUS_MT\Mes documents\doc\audit>java -jar querybuild
    er.jar -cms:”xxxxx” -username:”uername-adm” -password
    :”pwd” -query:”select * from CI_APPOBJECTS”
    Wrong argument: audit_generateurrequete.xlsx

    solutions or documentation to found error

    Like

  3. Darren

    Great tool, very useful, been trying to add to the CMS as a program object and schedule it through the CMC. Have you tried scheduling in a similar manner ?

    Many Thanks.

    java.lang.UnsupportedClassVersionError: Bad version number in .class file
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:620)
    at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:260)
    at java.net.URLClassLoader.access$100(URLClassLoader.java:56)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:195)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
    at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
    Exception in thread “main”

    Like

    1. dmytro Post author

      Interesting. I will try to schedule from CMC.

      The issue is probably that the Java version that I used to compile the code is higher than the BO Java version.

      I will try to recompile (or at least publish the source code)

      Like

  4. Darren

    Hi, did you have any success scheduling the jar through the CMC ?
    If you could share your master bat file used as the main program object that would be great. That’s the way I did it at least with the jar and bat that you have as associated files. This might not be best practice though.

    Many Thanks

    Like

  7. Shal

    Hi, Just installed BI 4.1SP2, I was using this on 4.0SP5P1 without issues. Now have the below problem. Please help,

    java -cp “querybuilder.jar;poi-3.8-20120326.jar;C:\SAP BusinessObjects Enterprise XI 4.0\java\lib\*” org.bukhantsov.quer
    ybuilder.Program -cms:XXX -username:YYY -password:ZZZ -query:”select * from ci_infoobjects”
    Exception in thread “main” java.lang.UnsupportedClassVersionError: org/bukhantso
    v/querybuilder/Program (Unsupported major.minor version 50.0)
    at java.lang.ClassLoader.defineClass0(Native Method)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:539)
    at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:12
    3)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:251)
    at java.net.URLClassLoader.access$100(URLClassLoader.java:55)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:194)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:187)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:289)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:235)
    at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)

    JAVA VERSION

    java version “1.4.2_13”
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_13-b06)
    Java HotSpot(TM) Client VM (build 1.4.2_13-b06, mixed mode)

    Like

    1. dmytro Post author

      1.4 is too ancient version of Java. It not supported. Maybe you just need to update the environment variable PATH to point to the latest Java version.

      I believe the JAR was compiled with 1.6.

      Like

      1. Shal

        We are using the same version of java on 4.0, no issues there.

        I tried this….pointing to SAP JVM which is this version
        java -version
        java version “1.6.0_37”
        Java(TM) SE Runtime Environment (build 6.1.044)
        SAP Java Server VM (build 6.1.044 21.1-b06, Oct 31 2012 10:44:56 – 61_REL – optU
        – windows amd64 – 6 – bas2:182728 (mixed mode))

        Now i get the result however it runs with some messages before throwing the actual output like below. How do i suppress these messages, any flags that can be used,

        log4j: Could not find root logger information. Is this OK?
        log4j: Parsing for [com.businessobjects.js.child.webi] with value=[INFO,PSSAPPEN
        DER].
        log4j: Level token is [INFO].
        log4j: Category com.businessobjects.js.child.webi set to INFO
        log4j: Parsing appender named “PSSAPPENDER”.
        log4j: Parsing layout options for “PSSAPPENDER”.
        log4j: Setting property [conversionPattern] to [%-4r %-5p %c{2} %M.%L %x – %m%n]
        .
        log4j: End of parsing for “PSSAPPENDER”.
        log4j: Setting property [maxBackupIndex] to [100].
        log4j: Setting property [maxFileSize] to [512KB].
        log4j: Setting property [file] to [/.log].
        log4j: setFile called: /_20131129_121319.log, true
        log4j: setFile ended
        log4j: Parsed “PSSAPPENDER” options.
        log4j: Handling log4j.additivity.com.businessobjects.js.child.webi=[null]
        log4j: Finished configuring.
        log4j: Reading configuration from URL jar:file:/C:/SAP%20BusinessObjects/SAP%20B
        usinessObjects%20Enterprise%20XI%204.0/java/lib/pub_processing.jar!/META-INF/Bus
        inessObjects.Logging/log4j/log4j.properties
        log4j: Could not find root logger information. Is this OK?
        log4j: Parsing for [com.businessobjects.publisher] with value=[DEBUG, A1].
        log4j: Level token is [DEBUG].
        log4j: Category com.businessobjects.publisher set to DEBUG
        log4j: Parsing appender named “A1”.
        log4j: Parsing layout options for “A1”.
        log4j: Setting property [conversionPattern] to [%-4r %-5p [%t] %37c %3x – %m%n].

        Like

      2. dmytro Post author

        Very interesting. I have never seen such output. Try following.

        Create file log4j.properties with the line:
        log4j.rootLogger=OFF

        Run the tool with the command:
        java -Dlog4j.configuration=log4j.properties -cp “querybuilder.jar;poi-3.8-20120326.jar;C:\SAP BusinessObjects Enterprise XI 4.0\java\lib\*” org.bukhantsov.querybuilder.Program -cms:XXX -username:YYY -password:ZZZ -query:”select * from ci_infoobjects”

        Like

      3. Shal

        I created a one liner in the folder where i am running querybuilder.bat from. still same messages. Where would this log4j.properties have to be placed.

        I am sorry if its a rookie question. I am not a Java developer, i have very little experience in java.

        Like

  8. Reinaldo Nunez

    Hi, it is indeed a wonderful tool. I’m trying to set two complex queries and seems I face the wall because don’t know the tool limitations.

    1) First, I ‘m trying to get the format output of the scheduled job with this:

    SELECT SI_CREATION_TIME, SI_NEXTRUNTIME, SI_KIND, SI_INSTANCE, SI_SIZE, SI_CUID, SI_NAME, SI_ID, SI_OWNER, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_OUTCOME, SI_SCHEDULEINFO.SI_NAME , SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_ENDTIME, SI_ProcessInfo.SI_SIZE, SI_ProcessInfo.SI_HAS_PROMPTS FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where (SI_INSTANCE = 1) AND (SI_RECURRING = 1)

    AND it works fine, but when I add this “SI_ProcessInfo.SI_FORMAT_INFO.SI_FORMAT” in the select statements, it seems to ignore it. Do you know why?

    2) To retrieve all reports connected to a Commissions Universe then the jar file blows(I believe it is because the double quotes “):

    SELECT SI_ID, SI_KIND, SI_NAME, SI_WEBI, SI_OWNER, SI_CUID FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where (PARENTS (“SI_NAME = ‘Webi-Universe'”, “SI_NAME = ‘XI Commissions'”))

    Please advice
    Reinaldo

    Like

    1. dmytro Post author

      1) I believe you should use SI_FORMAT_INFO.SI_FORMAT without SI_PROCESSINFO.
      2) You can escape quotes in command line with \, e.g. 

      java -jar querybuilder.jar -query:"select si_name from ci_infoobjects where si_name='\"text in quotes\"'"

      Like

      1. reinaldo Nunez

        Thanks a lot for the answers, definitely I’ll try the scape sequence for #2.

        I tried #1, and it works when using it on Query Builder, but not when using thru the command line. I’ll try it again

        Again, Thanks
        Reinaldo Nunez

        Like

  9. Edgardo Tatsuta

    I must tell you that it’s hard to find your posts in google, i found this one
    on 18 spot, you should build some quality backlinks in order to rank your webiste, i know how
    to help you, just type in google – k2 seo tips and tricks

    Like

  10. Sakthi Ganesh

    Hey Dmytro… Thanks for sharing this… works like a charm… i just have one question… would it be possible to share the source code for the command line query builder? I have for the application but want to check out the code for this one… Thanks in advance…

    Like

    1. ChalBal

      Hi. Thanks for your program. I’m getting the following issue. Can you provide any guidance please?

      D:\Software\querybuilder_min\querybuilder>java -jar querybuilder.jar -cms:CMSNAME -query:”select si_name from ci_infoobjects”
      Exception in thread “main” java.lang.NoClassDefFoundError: com/crystaldecisions/
      sdk/exception/SDKException
      at java.lang.Class.getDeclaredMethods0(Native Method)
      at java.lang.Class.privateGetDeclaredMethods(Unknown Source)
      at java.lang.Class.getMethod0(Unknown Source)
      at java.lang.Class.getMethod(Unknown Source)
      at sun.launcher.LauncherHelper.getMainMethod(Unknown Source)
      at sun.launcher.LauncherHelper.checkAndLoadMain(Unknown Source)
      Caused by: java.lang.ClassNotFoundException: com.crystaldecisions.sdk.exception.
      SDKException
      at java.net.URLClassLoader$1.run(Unknown Source)
      at java.net.URLClassLoader$1.run(Unknown Source)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.net.URLClassLoader.findClass(Unknown Source)
      at java.lang.ClassLoader.loadClass(Unknown Source)
      at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
      at java.lang.ClassLoader.loadClass(Unknown Source)
      … 6 more

      Like

Comments are closed.