January 4, 2002 at 11:19 am
Does anyone have a table to convert SQL's xtype to ADO's data type constant?
e.g.
sql int
xtype = 56
ADO DataTypeEnum = 3
January 4, 2002 at 3:02 pm
I dont know of a lookup table off the top of my head, but I'll look. I dont have the ids, but here are a few mappings:
text data type in ADO is adLongVarChar
char datatype is adChar
varchar datatype is adVarChar
What are you trying to achieve by having this lookup table?
January 4, 2002 at 3:40 pm
UGH! I just typed an explination but lost it because I wasn't logged in.
We have a report application that builds itself based on tables.
Report
Parameters
Validations
ParameterValidations
Report - stored procedure name.
Parameters - parameter name, length, data type, direction, input label and output label.
Validations - java script validations that can be applied at a parameter level.
I'm working on the program to populate the Report and Parameters tables. I'll still need to collect the label information from the user, but, for the parameter info, I thought it'd be slick if I did something like this:
select lngadotype
from sysobjects o
join syscolumns c on o.id = c.id
join lookup_ado a on c.xtype = a.xtype
where o.name = @myStoredProc
I'd be surprised if I'm the first to do something like this, so I thought I'd ask...
January 4, 2002 at 5:02 pm
If you are querying through ADO, you can use sp_help 'procedurename' and see the 2nd recordset for the parameters.
Steve Jones
January 4, 2002 at 7:19 pm
If you really want to dig, take a look at the table master.dbo.spt_datatype_info. Probably will be easier if you look at the information schema view Columns.
Andy
January 4, 2002 at 8:22 pm
Thanks guys, but I think I'll end up just using parameters.refresh in ADO. It's not as slick as doing it all in SQL, but it works and there's one less table to maintain.
This will get me to where I need to be:
Dim con, cmd
set con = CreateObject("adodb.Connection")
set cmd = CreateObject("adodb.Command")
con.open ""
set cmd.activeconnection = con
cmd.commandtext = request.item("param")
cmd.commandType = 4 'adCmdStoredProc
cmd.Parameters.Refresh
Dim x
For x = 0 to cmd.Parameters.count -1
Response.write cmd.paramters(x).type & "<br>"
next
set cmd = nothing
set con = nothing
January 4, 2002 at 9:12 pm
Parameters.refresh is expensive - means an extra round trip. If you're just trying to generate the parameters collection manually MS published a VB add in that does a great job. I think Bill Vaughn has a modified copy of his site at http://www.betav.com. Will probably discuss in a column soon as a follow up to my beginner ado series.
Andy
January 5, 2002 at 1:08 pm
I'd never use parameters.refresh as a production method. I'm only trying to populate the parameters table mentioned in my second post. I need to get the values from the parameters collection into a sql table for future reference.
Incidently, Bill Vaughn is using Parameters.Refresh in the ADO Stored Procedure Add In.
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply