"Zlatko Matiæ" <zlatko.matic1@xxxxxxxxxxx> wrote in message news:d3tarq$s98$1@xxxxxxxxxxxxxxxxx > Hello. > > While I was working with Access Projects (Access front-end with MSDE) I > was able to call stored procedures by using ADO command and parameters > object. > Now I am trying to migrate my database from MSDE to Postgre and I'm > wondering about stored procedures on Postgre...I couldn't find such > expression in Pg documentation, so I suppose that there are no such thing > on Postgre. On the other hand, I could see that functions could play such > role. Am I right? Yes. Functions can be written to take pretty much whatever parameters you want, and can return a value, a row, a resultset, or nothing if you wish. You also have a wide choice of languages to use. The documentation has plenty more on this. > If that is so, how can I call such functions from the client (MS Access, > for example) ? I couldn't call MSDE functions from MS Access using ADO, > because ADO recognize only stored procedures (adCmdStoredProc), so I > suppose that the same problem could be with Postgre functions... > How can I use Postgre functions as recordset for my forms and reports in > MS Access? How can I call and execute parameterized functions? The usual syntax for calling functions is "select myfunction(param1, param2...);". For set returning functions, you need to write "select * from myfunction(param1, param2...);". Note that a function has to return a datatype recognised as such by the database. Every table automatically has created a type which corresponds to its rowtype; you can also create any type you want. In Access, there are two approaches you can use to run a paramaterised query, with the parameters set at runtime: 1. Construct a query string using the supplied parameters, and then explicitly set the SQL property of your querydef to that string, or: 2. Send the query string to the backend as a passthrough query. You may have trouble getting an updatable recordset this way (certainly with #2), which will give you trouble updating when using bound controls. But if you don't mind writing some code, you can handle updates/inserts easily enough in your app. You may find that using SQL for your updates and inserts, rather than ADO methods, is more robust. Some versions of the ODBC driver have trouble with queries using set returning functions, because they don't recognize that the function name does not apply to a table. I believe that this has been fixed in recent driver versions, but I haven't tested this. This problem would not apply in any case to passthrough queries. I suggest that you search the archives of the pgsql-odbc list for information, and direct any further questions to that list, where you are more likely to get good answers. HTH > > Thanks. > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
