Stored procedre question.
A poster asked how to name the columns a stored procedure returns, since VB saw (expression) for each column. Thomas Parsli suggested SELECT procedure(...) AS name, but the poster said that did not work when VB calls the procedure and posted an example procedure with a RETURNING list. No solution was given in the visible messages.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
When a strored procedure returns data is there any way to label the data columns so that external apps developed with VB will see column names in a recordset rather than just (expression) as the name of all the columns? Sent via Deja.com http://www.deja.com/ Before you buy.
neilrbcl@my-deja.com writes: > When a strored procedure returns data is there any way to label the > data columns so that external apps developed with VB will see column > names in a recordset rather than just (expression) as the name of all > the columns? RTFM? SELECT procedure(foobar) AS name_for_vb FROM ... AS is not mandatory btw. Thomas
In article <k8am4lde.fsf@148.122.203.63.dhcp.nextra.no>, Thomas Parsli <thomas.parsli@startsiden.no> wrote: > neilrbcl@my-deja.com writes: > > > When a strored procedure returns data is there any way to label the > > data columns so that external apps developed with VB will see column > > names in a recordset rather than just (expression) as the name of all > > the columns? > > RTFM? > > SELECT procedure(foobar) AS name_for_vb > FROM ... > > AS is not mandatory btw. > > Thomas > I have RTFM several times and as I expected what you said does not work when VB is calling a SP. how can aliasing columns work when SELECTing INTO variables for use in a RETURN? Am I mising something? Neil. Sent via Deja.com http://www.deja.com/ Before you buy.
neilrbcl@my-deja.com writes: > In article <k8am4lde.fsf@148.122.203.63.dhcp.nextra.no>, > Thomas Parsli <thomas.parsli@startsiden.no> wrote: > > neilrbcl@my-deja.com writes: > > > > > When a strored procedure returns data is there any way to label the > > > data columns so that external apps developed with VB will see column > > > names in a recordset rather than just (expression) as the name of > all > > > the columns? > > > > RTFM? > > > > SELECT procedure(foobar) AS name_for_vb > > FROM ... > > > > AS is not mandatory btw. > > > > Thomas > > > > I have RTFM several times and as I expected what you said does not work > when VB is calling a SP. > > how can aliasing columns work when SELECTing INTO variables for use in > a RETURN? Am I mising something? So you want to call your SPL with execute and that SPL should return _several_ values? Thomas
neilrbcl@my-deja.com writes: > In article <k8am4lde.fsf@148.122.203.63.dhcp.nextra.no>, > Thomas Parsli <thomas.parsli@startsiden.no> wrote: > > neilrbcl@my-deja.com writes: > > > > > When a strored procedure returns data is there any way to label the > > > data columns so that external apps developed with VB will see column > > > names in a recordset rather than just (expression) as the name of > all > > > the columns? > > > > RTFM? > > > > SELECT procedure(foobar) AS name_for_vb > > FROM ... > > > > AS is not mandatory btw. > > > > Thomas > > > > I have RTFM several times and as I expected what you said does not work > when VB is calling a SP. > > how can aliasing columns work when SELECTing INTO variables for use in > a RETURN? Am I mising something? You're missing an 's' btw:) Thomas
In article <g0la4cag.fsf@148.122.203.63.dhcp.nextra.no>,
Thomas Parsli <thomas.parsli@startsiden.no> wrote:
> neilrbcl@my-deja.com writes:
>
> > In article <k8am4lde.fsf@148.122.203.63.dhcp.nextra.no>,
> > Thomas Parsli <thomas.parsli@startsiden.no> wrote:
> > > neilrbcl@my-deja.com writes:
> > >
> > > > When a strored procedure returns data is there any way to label
the
> > > > data columns so that external apps developed with VB will see
column
> > > > names in a recordset rather than just (expression) as the name
of
> > all
> > > > the columns?
> > >
> > > RTFM?
> > >
> > > SELECT procedure(foobar) AS name_for_vb
> > > FROM ...
> > >
> > > AS is not mandatory btw.
> > >
> > > Thomas
> > >
> >
> > I have RTFM several times and as I expected what you said does not
work
> > when VB is calling a SP.
> >
> > how can aliasing columns work when SELECTing INTO variables for use
in
> > a RETURN? Am I mising something?
>
> So you want to call your SPL with execute and that SPL should return
> _several_ values?
>
> Thomas
>
Ok here's an example..
--------------------
CREATE PROCEDURE n_get_step ( p_req_step_id likestep.step_id DEFAULT 0 , p_req_step_ver like step.step_ver DEFAULT
0.000 )
RETURNING
INTEGER,
DECIMAL(9,3),
DATETIME YEAR TO SECOND,
INTEGER,
DECIMAL(9,3),
SMALLINT,
INTEGER,
VARCHAR(80,10),
REFERENCES TEXT,
REFERENCES TEXT;
Define p_step_id like step.step_id;
Define p_step_ver like step.step_ver;
Define p_step_vdate like step.step_vdate;
Define p_test_id like step.test_id;
Define p_test_ver like step.test_ver;
Define p_step_active like step.step_active;
Define p_step_seq like step.step_seq;
Define p_step_desc like step.step_desc;
Define p_step_action like step.step_action;
Define p_step_result like step.step_result;
FOREACH
SELECT
step_id, step_ver, step_vdate, test_id, test_ver,
step_active, step_seq, step_desc, step_action, step_result
INTO
p_step_id, p_step_ver, p_step_vdate, p_test_id, p_test_ver,
p_step_active, p_step_seq, p_step_desc, p_step_action, p_step_result
FROM step
WHERE step_id = p_req_step_id AND step_ver = p_req_step_ver
RETURN
p_step_id, p_step_ver, p_step_vdate, p_test_id, p_test_ver,
p_step_active, p_step_seq, p_step_desc, p_step_action, p_step_result
;
END FOREACH;
END PROCEDURE;
----------------
I get back all the data that I want in an ADO recordset in VB by
executing the ADODB.COMMAND object but all the columns are called
(expression). This makes it rather hard to sort/filter/find the
returned recordset!
Thanks for pointing out the misssing S.
Neil..
Sent via Deja.com http://www.deja.com/
Before you buy.