Help with rdoParameters and stored procedures

Help with rdoParameters and stored procedures

Post by Steven Watanab » Tue, 05 Nov 1996 04:00:00



I'm trying to use RDO PreparedStatements to run SQL Server 6.5
stored procedures.  One of the stored procedures updates several fields
in the database.  I can't set the value of one of the parameters (whose
type is rdTypeLONGVARCHAR) to a string -- Visual Basic tells me
"Can't assign value to unbound parameter".  The way I read this error
message, I can't set a parameter corresponding to an SQL Server Text
field.  Is that correct?  If so, then how should I update this field from
RDO if I'm supposed to use stored procedures to access the database?

I'm using VB4-32 and RDO 1.0b, if it's important.

Thanks.
--

 
 
 

Help with rdoParameters and stored procedures

Post by Simon Jone » Thu, 07 Nov 1996 04:00:00


If rdTypeLONGVARCHAR is a SQL Server TEXT column (and I think it is) then
the answer is that you can't do this. RDO 1.x can't have "large" columns as
parameters.

There is a binding threshold parameter which controls what is considered to
be "large" but in SQL Server you have CHAR and VARCHAR which can be up to
255 characters wide and then TEXT and IMAGE which are up to 2Gb wide.

To manipulate TEXT and IMAGE data you are supposed to use the GetChunk and
AppendChunk methods. (Note that there is a bug and if you use AppendChunk
you MUST assign a value to another (small) column at the same time or the
data won't be saved.)

If the data isn't too big (no more than the SQL statement length limit)
then you can usually construct a SQL Insert, Update or Exec statement and
use the Execute method on that.
--
Regards,
Simon Jones



Quote:> I'm trying to use RDO PreparedStatements to run SQL Server 6.5
> stored procedures.  One of the stored procedures updates several fields
> in the database.  I can't set the value of one of the parameters (whose
> type is rdTypeLONGVARCHAR) to a string -- Visual Basic tells me
> "Can't assign value to unbound parameter".  The way I read this error
> message, I can't set a parameter corresponding to an SQL Server Text
> field.  Is that correct?  If so, then how should I update this field from
> RDO if I'm supposed to use stored procedures to access the database?


 
 
 

1. HELP: Stored procedure calling stored procedure

I have two stored procedures, one of which calls the other (see sample
below). When I provide data that I know will generate an error in add_a, I
get this:

Msg 266, Level 16, State 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is
missing. Previous count = 1, Current count = 0.
Msg 3903, Level 16, State 1
The rollback transaction request has no corresponding BEGIN TRANSACTION.

If I wrap the calling code in a begin/commit transaction, I get two of these
error messages. Anyone know why?

===================
Procedures sample
===================

create procedure add_a



as
begin transaction

    .... (do some sql stuff) ....


        rollback transaction

    end
commit transaction

and

create procedure add_b



as
begin transaction



        rollback transaction

    end
commit transaction

2. UserID and Password Info from connection object

3. Help with stored procedure calling another stored procedure.

4. SP fails when placed in a Job

5. Calling a Java Stored Procedure from another Java Stored Stored Procedure

6. Rolling back transactions with a SQL server

7. Stored Procedure using another Stored Procedure

8. Why use PDOXW at all

9. How to call an Oracle Stored procedure from MSSQL Server Stored Procedure

10. Using Stored Procedure's results in a Stored Procedure

11. Calling a stored procedure with parameters from another stored procedure

12. Many Simple Stored Procedures VS. Few Complex Stored Procedures

13. Calling a stored procedure with parameters from another stored procedure