Stored procedre question.
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.