February 24, 2005 at 4:01 am
I'm using ADO/VB 6 to connect to Access, SQL 7 and 2000, and MySQL. I create parameter objects and attach them to the command to send it to the DB.
What I need to know is if there is a efficient and speedy way to build the stored procedure calls that is compatible with at least Access and SQL Server. Currently I have to correct for certain syntax differences (@'s or []'s).
More of an ADO problem but this seems to be the right place to ask.
russell
February 24, 2005 at 9:47 am
A code example would be helpful
February 24, 2005 at 7:03 pm
The objects are standard ADO objects so here's some pseucdocode.
sub select_unit_PK(p_unitPK,p_two,p_three)
4 arrays
parameter_name
parameter_value
parameter_type
parameter_size
fill arrays with information for each parameter
call append_parameters(query_name,4 arrays passed in)
command_object.Execute
end sub
sub append_parameters(p_s,4 arrays)
select case(db_type)
case SQL7
with command_object
set .Type = storedProc
set .name = query_name
for each parameter
.append( .create_parameter("[" & name & "]",type,value,size)
end with
case MS_Access
with command_object
set .Type = storedProc
set .name = query_name
.paramters.refresh //somehow necessary for access
for each parameter
.append( .create_parameter("@" & name,type,value,size)
end with
end select
end sub
This works but I need to speed the process up.
Is there some way that I can get the parameter syntax from the connection or command object instead of coding it for each DB type. ADO is supposed to be DB interface to the code right?
February 25, 2005 at 2:03 am
ADO is supposed to be DB interface to the code right?
Yes, just like a phone is an interface to another person, however, you don't expect a phone to do translation or to indicate what language the other person will use.
Technically, if you had to get the parameter syntax from the connection or the command object, you would have to know a) how to request it and b) the format of the result. After all, you would need to pass a command string to the object and get a result string back and then parse the result string. You would also need to know the little bit about refreshing parameters as well..
What I would tend to do is to write separate procedures for each system you use and put the case in sub select_unit_PK(p_unitPK,p_two,p_three) This then has the advantage that for a specific system, you can easily strip out redundant code or for a generic system you can leave it all in.
February 25, 2005 at 2:32 am
I run the script below in query analyser and then copy and paste the results into the vb project.
set concat_null_yields_null off
select
'Parm String' ='Set adParm = .CreateParameter("@' + RIGHT(name,LEN(name)) + '",'
+ CASE
WHEN type_name(xusertype) = 'varchar' THEN ' advarchar, adparaminput, ' + CONVERT(Varchar,length) + ', null)'
WHEN type_name(xusertype) = 'char' THEN ' adchar, adparaminput, ' + CONVERT(Varchar,length) + ', null)'
WHEN type_name(xusertype) = 'smalldatetime' THEN ' addate, adparaminput, , null)'
WHEN type_name(xusertype) = 'int' THEN ' adinteger, adparaminput, , null)'
WHEN type_name(xusertype) = 'bit' THEN ' adboolean, adparaminput, , null)'
WHEN type_name(xusertype) = 'decimal' THEN ' addecimal, adparaminput, , null)'
END
+ CHAR(9) + CHAR(10) /*+ char(13)*/ + CHAR(9) + CHAR(9) + '.parameters.append adparm'+ CHAR(10)
from syscolumns where id = object_id (tablename)
order by colid asc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply