Hmmm... Your find() operation is not quite the same as your query. Besides the obvious of using a single input parameter for the find and the three input parameters for the query, the find operation is based off the ORG_ID field and the query is not using that field at all. The query seems to be using CUST_ORG_ID, ooid, and sor fields. So, I don't know if we're really comparing apples to apples. But, then I don't know your schema and app all that well...
In your first note, you claim that the generated SQL works in a command prompt. You mention that you only have one database, but what about multiple tables? I don't know Oracle all that well, but is there a chance that you have multiple ORGANIZATION tables qualified by different schema names? So, the command line access is going against one table and the application is accidentally going against an empty table? But, then your find() probably wouldn't find anything, even with the multiple id fields... I'm grabbing at straws. But, given what you've told us thus far, I can't figure out how generated SQL would produce different results from within JPA vs a command line. Not sure if it will tell me anything of interest, but could you provide your OpenJPA SQL trace file? Thanks, Kevin On Mon, Jul 13, 2009 at 9:43 AM, jewettdiane <[email protected]> wrote: > > It finds the record if I just run getEntityManager().find(entityClass, id): > > SELECT t0.LST_UPDT_CD, t0.LST_UPDT_TMP, t0.LST_UPDT_USER_ID, > t0.ADP_ACCOUNT_TYPE_CD, t0.BILLING_CYCLE_CD, > t0.BILLING_FIRST_DT, t0.CONTRACT_SIGNED_DT, t0.CUST_ORG_ID, > t0.DUNS_NBR, t0.fein, t0.INTEGRATION_HOST_SYS_ID, > t0.INTEGRATION_TYPE_CD, t0.NAICS_CD, t0.NETSECURE_CLIENT_ID, > t0.NETSECURE_STATUS, t0.ooid, t0.ORG_DBA_NAME, t0.ORG_NAME, > t0.ORG_TYPE_ID, t0.ORG_URL, t0.EMPLOYER_ORG_ID, > t0.PARENT_ORG_ID, t0.PAYROLL_VENDOR_CD, t0.SERVICES_BEGIN_DT, > t0.SERVICES_END_DT, t0.SIC_CD, t0.sor > FROM ORGANIZATION t0 > WHERE t0.ORG_ID = ? > [params=(long) 2] > > I only have one database running. > I am running in RAD 7 with a local Oracle database. > > Here is the code: > @NamedQuery ( > name = "Organization.findOrgByCustOoidSOR", > query = "SELECT o FROM Organization o WHERE o.custOrgId > = :custOrgId > AND o.ooid = :ooid AND o.sor = :sor" > ) > > public Organization findOrganizationByCustSOR(String custOrgId, > String ooid, String sor) { > Map<String, Object> namedParams = new HashMap<String, > Object>(); > namedParams.put("custOrgId", custOrgId.trim()); > namedParams.put("ooid", ooid.trim()); > namedParams.put("sor", sor.trim()); > return > findSingleByNamedQueryAndNamedParams("Organization.findOrgByCustOoidSOR", > namedParams); > } > > > public T findSingleByNamedQueryAndNamedParams(String queryName, > Map<String, Object> params) { > Query query = > getEntityManager().createNamedQuery(queryName); > for (String param : params.keySet()) { > Object obj = params.get(param); > if (obj instanceof Date) { > query.setParameter(param, (Date) obj, > TemporalType.TIMESTAMP); > } else { > query.setParameter(param, obj); > } > } > try { > > return (T) query.getSingleResult(); > } catch (NoResultException noResult) { > LOGGER.warn("No Single Result"); > } > return null; > } > > > > Kevin Sutter wrote: > > > > Hate to state the obvious, but is it possible that your sql window is > > accessing the same database instance as your jpa application (production > > vs > > test vs sandbox, etc)? Your persistence.xml doesn't have the url for the > > database, so is it possible that your application configuration is going > > to > > a different instance? > > > > Sorry, but that's about the only reason that I can come up with. You > have > > sql trace turned on, so you should be able to see if any results are > being > > returned by jdbc. And, any results should be massaged into objects for > > the > > application. > > > > Kevin > > > > On Fri, Jul 10, 2009 at 2:29 PM, jewettdiane <[email protected]> > > wrote: > > > >> > >> I have a named query that produces the following sql: > >> SELECT t0.ORG_ID, t0.LST_UPDT_CD, t0.LST_UPDT_TMP, t0.LST_UPDT_USER_ID, > >> t0.ADP_ACCOUNT_TYPE_CD, t0.BILLING_CYCLE_CD, > >> t0.BILLING_FIRST_DT, t0.CONTRACT_SIGNED_DT, t0.CUST_ORG_ID, > >> t0.DUNS_NBR, t0.fein, t0.INTEGRATION_HOST_SYS_ID, > >> t0.INTEGRATION_TYPE_CD, t0.NAICS_CD, t0.NETSECURE_CLIENT_ID, > >> t0.NETSECURE_STATUS, t0.ooid, t0.ORG_DBA_NAME, t0.ORG_NAME, > >> t0.ORG_TYPE_ID, t0.ORG_URL, t0.EMPLOYER_ORG_ID, > >> t0.PARENT_ORG_ID, t0.PAYROLL_VENDOR_CD, t0.SERVICES_BEGIN_DT, > >> t0.SERVICES_END_DT, t0.SIC_CD, t0.sor > >> FROM ORGANIZATION t0 > >> WHERE (t0.CUST_ORG_ID = ? AND t0.ooid = ? AND t0.sor = ?) > >> [params=(String) AR, (String) ABC123123, (String) Enterprise] > >> > >> When I paste this sql in my database sql window. I get a result but when > >> I > >> run it on my server it returns a NoResultException. > >> > >> My persistence.xml: > >> <persistence-unit name="EI9DS" transaction-type="RESOURCE_LOCAL"> > >> <provider> > >> org.apache.openjpa.persistence.PersistenceProviderImpl > >> </provider> > >> > >> <class>com.adp.ei9.common.entity.EntityBase</class> > >> <class>com.adp.ei9.common.entity.Employee</class> > >> <class>com.adp.ei9.common.entity.Employment</class> > >> <class>com.adp.ei9.common.entity.I9User</class> > >> <class>com.adp.ei9.common.entity.Organization</class> > >> <class>com.adp.ei9.common.entity.OrgType</class> > >> <properties> > >> <property name="openjpa.TransactionMode" > >> value="managed" /> > >> <property name="openjpa.ConnectionFactoryMode" > >> value="managed" /> > >> <property name="openjpa.Log" > >> value="File=stdout, DefaultLevel=TRACE, > >> SQL=TRACE" /> > >> <property name="openjpa.jdbc.DBDictionary" > >> > >> value="org.apache.openjpa.jdbc.sql.OracleDictionary" /> > >> <property name="openjpa.ConnectionFactoryProperties" > >> value="PrettyPrint=true, PrettyPrintLineLength=72" /> > >> </properties> > >> </persistence-unit> > >> > >> What else should I try? It creates good sql that works. Is there a > reason > >> objects won't return when the sql works? > >> > >> Thanks > >> -- > >> View this message in context: > >> > http://n2.nabble.com/Named-Query-fails-but-SQL-works-tp3237379p3237379.html > >> Sent from the OpenJPA Users mailing list archive at Nabble.com. > >> > > > > > > -- > View this message in context: > http://n2.nabble.com/Named-Query-fails-but-SQL-works-tp3237379p3250844.html > Sent from the OpenJPA Users mailing list archive at Nabble.com. >
