Hi Prabath.
To specified a little more this problem. We have a function in postgresql that look similar to this: schema.function1(String,String,String,String,String) The second parameter it´s a string that represent a part of another query, ex: WHERE Id = 5. We pass this string inside the function and them we can build an entire sql consult to obtain some data. Our problem it´s that this WHERE id =5 string it´s not static but dynamically build, and we capture the Id value using a IN parameter in a DS so we put this: schema.function1(,WHERE Id =||:Id,,,) in the sql tag but it didnt work. If we do the same with a sql consult like: <sql>INSERT INTO datos(ci,nombre,notafinal) VALUES(:ci,'esteestunombre: '||:nombre,:notafinal)</sql> We see that it work just fine, no problem. Saludos, Ing. Jorge Infante Osorio. J´Dpto Soluciones SOA. CDAE. Fac. 5. UCI. De: dev-boun...@wso2.org [mailto:dev-boun...@wso2.org] En nombre de Prabath Abeysekera Enviado el: miércoles, 07 de marzo de 2012 12:39 Para: Noel Marquez Batista CC: carbon-...@wso2.org Asunto: Re: [Dev] [Carbon-dev] Issue in a DS when I try to pass a parameter into a function in postgresql. On Wed, Mar 7, 2012 at 9:24 PM, Noel Marquez Batista <nmarq...@uci.cu> wrote: Hi Prabath Abeysekera I did not meet with what I suggest, keeps giving me errors I was able to execute a dataservice operation which wraps a SQL statement similar to what you;ve mentioned in your previous mail, successfully. Are you getting the same exceptions or is it something else that you experienced this time? ----- Mensaje original ----- De: "Prabath Abeysekera" <praba...@wso2.com> Para: carbon-...@wso2.org Enviados: Lunes, 27 de Febrero 2012 15:26:06 Asunto: Re: [Carbon-dev] Issue in a DS when I try to pass a parameter into a function in postgresql. Hi Noel, Please see my comments inline. On Fri, Feb 24, 2012 at 8:20 AM, Noel Marquez Batista < nmarq...@uci.cu > wrote: HI ALL, I´VE CREATED A CLIENT THAT ACCESS DIRECTLY INTO A DB USING JDBC AND IT´S WORKS PERFECTLY, BY I WANT TO DO THE SAME WITH A DATA SERVICE IN AS, AND IT IS NOT WORKING AT ALL. HERE IS THE QUERY: String parameter = "'100-047'"; Select * from schema.function_get_tree(' table.id = 1 AND table.number = '"+parameter+"'') SO WHEN I PUT THIS QUERY INTO THE SERVICE I SPPECIFIED THE PARAMETER IN THIS WAY: Select * from schema.function_get_tree(' table.id = 1 AND table.number = :parameter') If you carefully look at the aforementioned query, the parameter notation ":parameter" is specified within the string literal " table.id =1...." . If you define a named parameter in a dataservice query that will internally be mapped into a standard SQL query string containing "?" notations corresponding to the places in the query where the parameter value assignment is required. If we take your query as an example, it will be mapped into the following form if you try to define it the way you already did. Select * from schema.function_get_tree(' table.id = 1 AND table.number = ?') Since you have specified the parameter within quotation marks, it will not be interpreted as an input field to which a value should be assigned even though you register an input parameter to assign values, thereby throwing those exceptions. Not only that, you also need to use proper string concatinations when you provide parameter values as a combination of string literals and parameters. Therefore, mentioned below is the proper way you should define your query in order to make it compatible with postgres. Select * from schema.function_get_tree(' table.id = 1 AND table.number =' || :parameter) AND THIS IS THE ERROR WHEN I TRY IT: TID: [0] [WSO2 Application Server] [2012-02-23 15:50:34,777] ERROR {org.apache.axiom.om.impl.llom.OMSourcedElementImpl} - Could not get parser from data source for element { http://empresa.co }Grupos {org.apache.axiom.om.impl.llom.OMSourcedElementImpl}javax.xml.stream.XMLStre amException: DS Fault Message: Error in 'SQLQuery.processNormalQuery' DS Code: DATABASE_ERROR Source Data Service:- Name: PregradoWS Location: /PregradoWS.dbs Description: N/A Default Namespace: http://empresa.co Current Request Name: ObtenerGrupoDadoIdGrupo Current Params: {parameter="'100-047'"} Nested Exception:- DS Fault Message: Error in 'createProcessedPreparedStatement' DS Code: UNKNOWN_ERROR Nested Exception:- org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0. at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.execute(DSOMDataSour ce.java:105) at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.serialize(DSOMDataSo urce.java:110) at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.getReader(DSOMDataSo urce.java:116) at org.apache.axiom.om.impl.llom.OMSourcedElementImpl.getDirectReader(OMSourced ElementImpl.java:225) at org.apache.axiom.om.impl.llom.OMSourcedElementImpl.forceExpand(OMSourcedElem entImpl.java:254) at org.apache.axiom.om.impl.llom.OMSourcedElementImpl.getFirstOMChild(OMSourced ElementImpl.java:867) at HELP, thanks. Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU! http://www.antiterroristas.cu http://justiciaparaloscinco.wordpress.com _______________________________________________ Carbon-dev mailing list carbon-...@wso2.org http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev -- Prabath Abeysekara Software Engineer WSO2 Inc. Email: prabatha @wso2.com Mobile: +94774171471 <tel:%2B94774171471> _______________________________________________ Carbon-dev mailing list carbon-...@wso2.org http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU! http://www.antiterroristas.cu http://justiciaparaloscinco.wordpress.com _______________________________________________ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Prabath Abeysekara Software Engineer WSO2 Inc. Email: praba...@wso2.com <mailto:harsha...@wso2.com> Mobile: +94774171471 Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU! http://www.antiterroristas.cu http://justiciaparaloscinco.wordpress.com
_______________________________________________ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
