Thread: postgresql equivalent to ms access parameter query
What is the postgresql equivalent to an ms access 2002 parameter query?
Postgresql functions written in SQL that return sets from select queries
seem closest.
Table stores has columns of storeid, name, address, etc.
A postgresql SQL procedure retrieves records based on a single column
parameter:
CREATE OR REPLACE FUNCTION public.state(varchar)
RETURNS SETOF stores AS
'
SELECT * from stores where stateorprovince = $1;
'
LANGUAGE 'sql' VOLATILE;
The following SQL statement runs as expected from psql or the pgAdminIII
query window:
SELECT storeid,storename from state('TN');
How is a parameter passed from access to postgresql in a pass-through query
calling a function?
A standard access query (non pass-through) to the postgresql table can
reference a text box control on a form:
SELECT public_stores.storeid, public_stores.storename,
public_stores.address, public_stores.city, public_stores.stateorprovince,
public_stores.postalcode
FROM public_stores
WHERE (((public_stores.stateorprovince)=[Forms]![Form1]![Text0]));
Thanks,
David P. Lurie
--- "David P. Lurie" <dbase4@hotmail.com> wrote:
> What is the postgresql equivalent to an ms access
> 2002 parameter query?
>
> Postgresql functions written in SQL that return sets
> from select queries
> seem closest.
If you like. But see below.
>
> Table stores has columns of storeid, name, address,
> etc.
>
> A postgresql SQL procedure retrieves records based
> on a single column
> parameter:
>
> CREATE OR REPLACE FUNCTION public.state(varchar)
> RETURNS SETOF stores AS
> '
> SELECT * from stores where stateorprovince = $1;
> '
> LANGUAGE 'sql' VOLATILE;
>
> The following SQL statement runs as expected from
> psql or the pgAdminIII
> query window:
>
> SELECT storeid,storename from state('TN');
>
> How is a parameter passed from access to postgresql
> in a pass-through query
> calling a function?
You've unwittingly provided yourself with the answer
below, i.e. construct a query string using the value
of some control(s).
You just need a piece of code that makes a connection
to the server, constructs a query string, then sends
the query to the server. ADO is good for this.
So you don't really need a function, because either
way you are doing the same thing, i.e. creating a
query string using passed in values.
>
> A standard access query (non pass-through) to the
> postgresql table can
> reference a text box control on a form:
>
> SELECT public_stores.storeid,
> public_stores.storename,
> public_stores.address, public_stores.city,
> public_stores.stateorprovince,
> public_stores.postalcode
> FROM public_stores
> WHERE
>
(((public_stores.stateorprovince)=[Forms]![Form1]![Text0]));
>
> Thanks,
>
> David P. Lurie
>
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to
> majordomo@postgresql.org so that your
> message can get through to the mailing list
cleanly
__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html
"Jeff Eckermann" <jeff_eckermann@yahoo.com> wrote in message news:20040323152604.94169.qmail@web20801.mail.yahoo.com... > You've unwittingly provided yourself with the answer > below, i.e. construct a query string using the value > of some control(s). > > You just need a piece of code that makes a connection > to the server, constructs a query string, then sends > the query to the server. ADO is good for this. > > So you don't really need a function, because either > way you are doing the same thing, i.e. creating a > query string using passed in values. > Thanks, that looks like the answer. Hope to have some time in the next day or two to try it out. The Access Linked Table Manager only displays DSN=xxx and DATABASE=yyy from the connection string. It must get the other psqlodbc parameters from the preconfigured DSN. Passing the mouse pointer over the linked table icon in the database window in Access displays what appears to be the entire psqlodbc connection string, including ODBC as the first parameter, and TABLE=zzz as the last parameter. I presume that a connection string would be something like: connection_name.Open "Provider=ODBC; DSN=xxx ;DATABASE=yyy;" An ADO recordset equivalent for the TABLE=zzz parameter could be: recordset1.Open ''zzz", connection_name David P. Lurie
Got a code snippet to work by modifying a module from Chapter 2 in
"Programming MS Access 2002". Didn't realize that the recordset string had
to be an SQL statement like SELECT rather than a table or view name:
Sub OpenMyDB()
Dim cnn1 As New Connection
Dim rst1 As Recordset
'Create the connection.
cnn1.Open "DSN=PostgreSQL30;" & _
"Database=test;"
'Create recordset reference, and set its properties.
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
'Open recordset, and print a test record.
rst1.Open "select * from public.stores;", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value,
rst1.Fields(2).Value; rst1.Fields(3).Value; rst1.Fields(4).Value;
'Clean up objects.
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
End Sub
--- "David P. Lurie" <dbase4@hotmail.com> wrote: > > > I presume that a connection string would be > something like: > > connection_name.Open "Provider=ODBC; DSN=xxx > ;DATABASE=yyy;" At a minimum, you can get away with just "DSN=xxx". The database name would usually be specified in the DSN, and ADO assumes ODBC by default. I personally like the simplicity of working with DSN's, but that does add a portability issue, being the need to create a DSN wherever the application is deployed. That may or may not be an issue for you, depending on the environment in which you are working. Some people like to do without DSN's by specifying all necessary parameters in the connection string. I haven't messed much with this, but AFAIK the psqlodbc driver will use default values for all settings not explicitly specified, so you don't need to provide a full connection string in any case. > > An ADO recordset equivalent for the TABLE=zzz > parameter could be: > > recordset1.Open ''zzz", connection_name Yes. > > > David P. Lurie > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose > an index scan if your > joining column's datatypes do not match __________________________________ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html