Implicit conversion from data type sql_variant to char

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Implicit conversion from data type sql_variant to char

Post by lcoelho » Wed 13 Apr 2011 14:26

I have a paramaterized stored procedure in a DataModule that gets called from a function. Here is the function:

Procedure tfClientFocusDM.PrepareData
(SchemeRef,YearNbr,MonthNbr,SortOrder : string);
begin
with spMemberListing do
begin
close;
Params.ParseSQL(SQL.Text, True);
PrepareSQL;
paramByName('@SchemeRef').asstring :=
SchemeRef;
paramByName('@Order').asstring :=
SortOrder;
prepare;
open;
end;

When this function runs the first time, the results from the stored procedure are correctly returned and displayed in a grid. However, if the function is run again straight away, the following message appears:

"Implicit conversion from data type sql_variant to char is not allowed. Use CONVERT function to run this query".

The code stops at the 'open' statement.

Could you kindly assist with this problem?

Here is the opening portion of the stored procedure that is called:


ALTER PROCEDURE SchemeMemberList1
(
@SchemeRef char(8),
@Order char(15),
@OnePayCentreYN char(1),
@OnePayCentreCompanyRef char(8),
@OneCompanyOnlyYN char(1),
@OneCompanyRef char(8)
)

AS


Create Table #TempList
(
YearID char(4) null,
MonthID char(2) null,
Sort char(50) null,
Sort1 char(50) null,
Surname varchar(50) null,

AndreyZ

Post by AndreyZ » Thu 14 Apr 2011 08:00

Hello,

This problem is caused by the ParseSQL method that fills Params without data types. To solve the problem, you should remove the "Params.ParseSQL(SQL.Text, True);" code. You shouldn't call it because the PrepareSQL method correctly fills Params with data types.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Thu 14 Apr 2011 11:15

Dear AndreyZ,

Thank you for your quick reply. You mention that to solve the problem I need to remove the Params.ParseSQL... statement. Initially I did try that but the following error occurs when the Params.ParseSQL statement is removed:

'Parameter SchemeRef not found'.

If, however, I change the code to:

Procedure tfClientFocusDM.PrepareData
(SchemeRef,YearNbr,MonthNbr,SortOrder : string);
begin
with spMemberListing do
begin
close;
Params[1].AsString := SchemeRef; // Instead of: ParamByName('@SchemeRef').AsString := SchemeRef;
Params[2].AsString := SortOrder; // Instead of: ParamByName('@Order').AsString := SortOrder;
prepare;
open;
end;

Then it works.

How can I get the 'ParamByName...' code to work?

AndreyZ

Post by AndreyZ » Thu 14 Apr 2011 13:49

I cannot reproduce the problem. Please try composing a small sample to demonstrate the problem and send it to andreyz*devart*com, including a full script to create a stored procedure. Also please specify the following:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of your IDE.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Thu 14 Apr 2011 14:55

AndreyZ,

I think I solved the problem but am not sure why this is the cause. Let me explain. I'm busy migrating an application from BDE to use SDAC components. The version of Delhi is 5 and SDAC is 5.00.0.5.

The StoredProcName under BDE was 'dbo.SchemeMemberList1' (precedded by the dbowner). When I remove 'dbo' so that the StoredProcName is only 'SchemeMemberList1', then it works with the following code in my function:

Procedure tfClientFocusDM.PrepareData
(SchemeRef,YearNbr,MonthNbr,SortOrder : string);
begin
with spMemberListing do
begin
close;
PrepareSQL;
paramByName('SchemeRef').asstring := SchemeRef;
paramByName('Order').asstring := SortOrder;
prepare;
open;
end;

Why did BDE need 'dbo' and not SDAC?

AndreyZ

Post by AndreyZ » Fri 15 Apr 2011 12:03

I cannot reproduce the problem. SDAC works with schemas correctly.
SDAC and BDE use different ways to obtain parameters of a stored procedure. When you set the TMSStoredProc.StoredProcName property to 'SchemeMemberList1', it means that the dbo schema will be used. Please check if you have a stored procedure with the same name but with different parameters in some other schema.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Tue 19 Apr 2011 11:14

AndreyZ,

Thanks for your reply and help. I forgot to mention that I am using SQL Server 2000, could that be the reason why removing the dbo. from before the StoredProcName causes a problem?

I checked and there is no other stored procedure with the name SchemeMemberList1 under another schema. There is only the one which has as 'owner' dbo.

AndreyZ

Post by AndreyZ » Wed 20 Apr 2011 07:50

I still cannot reproduce the problem. I've checked SDAC 5.00.0.5 on Delphi 5 with SQL Server 2000 and there were no problems.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Wed 20 Apr 2011 13:42

Hi AndreyZ,

Thank you so much for your continued help and support. I think I know what causes the problem and wonder if there is a solution. It has nothing to do with the code nore removing the dbo. from in front of the StoredPrcoName. Let me explain:

I am writing a more extensive migration wizard than the one that ships with SDAC, to migrate projects that use BDE to SDAC.

The wizard parses all the .dfm and .pas files and makes the necessary conversions. One of these changes is to change database component's (TQuery, TStoredProc, etc.) DataBase field to Connection field and then to fill in the connection value.

The conversion works perfectly but, if after the conversion I merely compile and run the migrated project, then the error mentioned in the beginning i.e. 'Parameter not found...' occurs. I then open the migrated project in the Delphi IDE, select the form with the problematic TMSStoredProc component and select the TMSStoredProc so that I can view it's attributes in the Object Inspector. I then deletet the StoredProcName value (dbo.SchemeMemberList1), click on another attribute of the component and then go back to the StoredProcName attribute and re-enter the StoredProcName value (dbo.SchemeMemberList1), then compile and re-run the application, then everything works perfectly.

This is a little frustrating because, if there is no solution to this, we will have to open each an every form, after migrating, and cut and paste the StoredProcName from all the TMSStoredProc components.

Do you know what the problem could be?

Thank you,

Luis

AndreyZ

Post by AndreyZ » Wed 20 Apr 2011 15:31

Please create a new project that uses BDE and works with the SchemeMemberList1 stored procedure, convert this project with your migration wizard to SDAC, and send both projects (before and after conversion) to andreyz*devart*com for investigation.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Wed 20 Apr 2011 19:12

Dear AndreyZ,

The problem was with my migration routine on the .dfm file.

With BDE, in the .dfm file, parameters name values are preceded by the @ character. This is not the case with SDAC. With the migration, I failed to remove the @ in front of the name value of the parameter. Hence the error 'Parameter not found'. Now, when I parse the .dfm file in the migration wizard, the @ character is removed from the name.

For example:
ParamData = <
item
DataType = ftString
Name = '@SchemeRef'
ParamType = ptInput
end

must be migrated to:

ParamData = <
item
DataType = ftString
Name = 'SchemeRef'
ParamType = ptInput
end

Thank you for all your input. If you know of any other issues relating to migrating from BDE to SDAC, I would be happy to know.

Thank you once again for your kind assistance.

Luis

AndreyZ

Post by AndreyZ » Thu 21 Apr 2011 08:27

It's good to see that you've found a solution. If any other questions come up, please contact us.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Thu 21 Apr 2011 09:35

Dear AndreyZ,

Thank you to for your quick and response and help.

Luis :D

Post Reply