emillerphx

My ignorance is embarassing. That fixed it! thanks. Now I need to go and research so I understand what is going with ' vs " and $P{} vs $P!{}. You have made a lot of project engineers happy. They can now do wildcard searches on their cost accounts. Eric

I want to do a query for all projects with a given string in their project number. I am doing a query on MS SQL Server The a query that works very well has: WHERE projnum LIKE "%PAD%" This gives me all projects with PAD in the name OK, but of course I want the users to be able to pick a string. I want to prompt the user for that string and put it nto a parameter: $P{PN} So my SQL statement in iReport is: WHERE projnum LIKE "%" + $P{PN} +"%" This returns nothing. No error, just didn't find any records. What is really annoying, is if I use the copy to clipboard button in the Report Query window in iReport and past it into dbVisualizer I get: WHERE projnum LIKE "%" + 'PAD' +"%" And that query works in dbVisualizer. I'll admit, I don't know java well at all. Is there some way to escape or double quote or something so that JasperReport makes the right query? I've also tried setting $P{PN} to "%PAD%" Same behavior. A related question: how do I see what the actual query is that gets sent to my database whe I run it? Thanks!

I have gotten it to work. i was using the wrong syntax, ignorant of the difference between a DSN and a Database Name: JDBC Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver JDBC URL: jdbc:sqlserver://underdog;DSN=Dynamics Now I'm off to generate pages of useful project accounting reports! Eric

I've tried that driver as well. Works on my windows machine but not on the Linux machine. I'm begining to think it must be some security setting somewhere that keeps me from connecting. Could it be something in SQL Server where it only allows windows machines to connect? Thank you for your suggestion. FYI, under the microsoft driver I get a little different error: Exception Message: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database requested in login 'DYNAMICS'. Login fails. Level: SEVER I've double checked the password as well. Eric Post Edited by Eric Miller at 11/14/08 22:02

I'll begin by confessing. I'm a mechanical engineer who grew up on Fortran. I'm enjoying my journey into database reporting but struggling with this whole Java OO thing... what does not kill me will make me stronger. One set of reports I'm making do some fancy queries on our MS Dynamics accounting system. MS SQL Server on a windows server. I get a good data connection in iReport on my XP64 windows desktop. But our JasperReports server is on a Linux machine. If I use the same JDBC driver and URL, and when I test the connection it does not work. I then tried it in iReport because you get an error message there. Details below. I can see the machine from the Linux machine (ping) Wrong driver? Wrong syntax between windows and linux? Stupid mistake? As always, any pointers would be appreciated. Eric Details: JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver JDBC URL: jdbc:odbc://underdog;DSN=Dynamics Error whenI test my Connection: Message: java.sql.SQLException: [unixODBC][Driver Manager]Data source name not found, and no default driver specified Level: SEVERE Stack Trace: sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958) sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115) sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3074) sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323) sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174) it.businesslogic.ireport.connection.JDBCConnection.getConnection(JDBCConnection.java:105) it.businesslogic.ireport.connection.JDBCConnection.test(JDBCConnection.java:404) it.businesslogic.ireport.connection.gui.ConnectionDialog.jButtonTestActionPerformed(ConnectionDialog.java:322) it.businesslogic.ireport.connection.gui.ConnectionDialog.access$300(ConnectionDialog.java:24) it.businesslogic.ireport.connection.gui.ConnectionDialog$4.actionPerformed(ConnectionDialog.java:274) javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849) javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169) javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) java.awt.Component.processMouseEvent(Component.java:5517) javax.swing.JComponent.processMouseEvent(JComponent.java:3135) java.awt.Component.processEvent(Component.java:5282) java.awt.Container.processEvent(Container.java:1966) java.awt.Component.dispatchEventImpl(Component.java:3984) java.awt.Container.dispatchEventImpl(Container.java:2024) java.awt.Component.dispatchEvent(Component.java:3819) java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212) java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892) java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822) java.awt.Container.dispatchEventImpl(Container.java:2010) java.awt.Window.dispatchEventImpl(Window.java:1791) java.awt.Component.dispatchEvent(Component.java:3819) java.awt.EventQueue.dispatchEvent(EventQueue.java:463) java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242) java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163) java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:153) java.awt.Dialog$1.run(Dialog.java:535) java.awt.Dialog$2.run(Dialog.java:565) java.security.AccessController.doPrivileged(Native Method) java.awt.Dialog.show(Dialog.java:563) java.awt.Component.show(Component.java:1302) java.awt.Component.setVisible(Component.java:1255) it.businesslogic.ireport.gui.ConnectionsDialog.jButtonNewParameterActionPerformed(ConnectionsDialog.java:550) it.businesslogic.ireport.gui.ConnectionsDialog.access$200(ConnectionsDialog.java:53) it.businesslogic.ireport.gui.ConnectionsDialog$6.actionPerformed(ConnectionsDialog.java:216) javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849) javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169) javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) java.awt.Component.processMouseEvent(Component.java:5517) javax.swing.JComponent.processMouseEvent(JComponent.java:3135) java.awt.Component.processEvent(Component.java:5282) java.awt.Container.processEvent(Container.java:1966) java.awt.Component.dispatchEventImpl(Component.java:3984) java.awt.Container.dispatchEventImpl(Container.java:2024) java.awt.Component.dispatchEvent(Component.java:3819) java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212) java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892) java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822) java.awt.Container.dispatchEventImpl(Container.java:2010) java.awt.Window.dispatchEventImpl(Window.java:1791) java.awt.Component.dispatchEvent(Component.java:3819) java.awt.EventQueue.dispatchEvent(EventQueue.java:463) java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242) java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163) java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:153) java.awt.Dialog$1.run(Dialog.java:535) java.awt.Dialog$2.run(Dialog.java:565) java.security.AccessController.doPrivileged(Native Method) java.awt.Dialog.show(Dialog.java:563) java.awt.Component.show(Component.java:1302) java.awt.Component.setVisible(Component.java:1255) it.businesslogic.ireport.gui.ConnectionsDialog.setVisible(ConnectionsDialog.java:623) it.businesslogic.ireport.gui.ToolbarConnectionPanel.jButtonDatasourcesActionPerformed(ToolbarConnectionPanel.java:114) it.businesslogic.ireport.gui.ToolbarConnectionPanel.access$100(ToolbarConnectionPanel.java:45) it.businesslogic.ireport.gui.ToolbarConnectionPanel$2.actionPerformed(ToolbarConnectionPanel.java:100) javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849) javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169) javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231) java.awt.Component.processMouseEvent(Component.java:5517) javax.swing.JComponent.processMouseEvent(JComponent.java:3135) java.awt.Component.processEvent(Component.java:5282) java.awt.Container.processEvent(Container.java:1966) java.awt.Component.dispatchEventImpl(Component.java:3984) java.awt.Container.dispatchEventImpl(Container.java:2024) java.awt.Component.dispatchEvent(Component.java:3819) java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212) java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892) java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822) java.awt.Container.dispatchEventImpl(Container.java:2010) java.awt.Window.dispatchEventImpl(Window.java:1791) java.awt.Component.dispatchEvent(Component.java:3819) java.awt.EventQueue.dispatchEvent(EventQueue.java:463) java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242) java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163) java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157) java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149) java.awt.EventDispatchThread.run(EventDispatchThread.java:110)

OK, unfolding here. I got in there an noticed that my $-signs were showing up as blocks... looking at the pattern some weird character was defined. I changed it to a $ and now everything looks good.

Quick update. I went in and uninstalled XML and Schemaand now it works just fine. I am not a Java guy so I'm clueless as to what I just did. But it looks like that fixes it. Eric

I just ran into the same problem. Saved my report, got out to go to a meeting. Restarted iReport and got the error. I installed the Plugin XML and Schema as suggested and... I don't get the error but now it sits there saying Loading Report in the Report Inspector. - Eric

I got all excited because I figured out how to get iReport to connect to my SQL Server database. Then I got un-excited because when I go into the Query Designer and doubleclick on a table, nothing happens. I tried the same thing on a mySQL database and that works find. Is it something to do with the fact that the tables are under dbo.tablename? Or is this another SQL Server issue? I've attached a screen shot. Any suggestions would be apprectiated. I kind of got used to graphically creating my queries... I'll type them in till this gets resolved. Thanks Eric