The variable name '@p1' has already been declared

[lcoelho]

I'm writing a migration wizard to convert many projects from BDE to SDAC. One of the forms has a query component with the SQL text shown at the end of this posting. It uses two parameters. After converting to SDAC, when the query is run, the error 'The variable name '@p1' has already been declared' occurs. However, this error does not occur in the BDE version of the program.

The only thing migrated in this form is the BDE TQuery component to a TMSQuery component. Interestingly, the error does not occur for the other parameter (@SchemeRef). If I remove the 'declare @p1 integer' from the SQL text, then it works.

To narrow down the problem, I created a blank project, placed a TMSQuery component on it and, for the SQL attribute, placed the following code:

declare @SchemeRef char(8)
declare @p1 integer

select @Schemeref = :Schemeref

Even with only the above code, I still get 'The variable name @p1 has already been declared'.

If I remove the 'select @Schemeref = :Schemeref' line, then the error does not occur.

Incidentally, if I change the name of @p1 to anything else (e.g. @p2, @paul, @jack), then the problem does not occur.

What is wrong with @p1 variable using SDAC?

Here is the complete SQL text for the TMSQuery component:

declare @SchemeRef char(8)

declare @p1 integer

select @Schemeref = :Schemeref


select @p1 = :P1



Create table #list1
(
SchemeRef char(8) null,
EmploymentNbr char(8) null,
Salary1 money null,
Salary2 money null
)

insert into #list1
(Schemeref,EmploymentNbr,Salary1,Salary2)

select SchemeMembers.Schemeref,SchemeMembers.Employmentnbr,0,0
from Employment,Person,Company,SchemeMembers
where SchemeMembers.Schemeref = @SchemeRef
and SchemeMembers.EmploymentNbr = Employment.EmploymentNbr
and Employment.companyRef = Company.Companyref
and Employment.Personref =Person.Personref
and ((ExitStatus = 'N') or (ExitStatus is null))


update #list1
set Salary1 = salary
from SalaryDetails,Employment,Person,Company,#list1
where SalaryDetails.Schemeref = @SchemeRef
and DatePart(year,DtSalEffective) * 100 + DatePart(month,DtSalEffective) < @p1
and DtSalEffective in
(select max(DtSalEffective) from SalaryDetails T1
where t1.Schemeref = SalaryDetails.Schemeref
and t1.Employmentnbr = SalaryDetails.Employmentnbr
and DatePart(year,t1.DtSalEffective) * 100 + DatePart(month,t1.DtSalEffective) < @p1)
and SalaryDetails.EmploymentNbr = Employment.EmploymentNbr
and Employment.companyRef = Company.Companyref
and Employment.Personref =Person.Personref
and SalaryDetails.Schemeref = #list1.Schemeref
and SalaryDetails.EmploymentNbr = #list1.EmploymentNbr



update #list1
set Salary2 = salary
from SalaryDetails,Employment,Person,Company,#list1
where SalaryDetails.Schemeref = @SchemeRef
and DatePart(year,DtSalEffective) * 100 + DatePart(month,DtSalEffective) = @p1
and DtSalEffective in
(select max(DtSalEffective) from SalaryDetails T1
where t1.Schemeref = SalaryDetails.Schemeref
and t1.Employmentnbr = SalaryDetails.Employmentnbr
and DatePart(year,t1.DtSalEffective) * 100 + DatePart(month,t1.DtSalEffective) = @p1)
and SalaryDetails.EmploymentNbr = Employment.EmploymentNbr
and Employment.companyRef = Company.Companyref
and Employment.Personref =Person.Personref
and SalaryDetails.Schemeref = #list1.Schemeref
and SalaryDetails.EmploymentNbr = #list1.EmploymentNbr


select Surname,Initials,IDNo,rtrim(CompanyName) + ' ' + rtrim(CompanyName1) CompanyName,
Salary1,Salary2,EmployeeNo,'N' SelectedYN, SchemeMembers.Schemeref,SchemeMembers.EmploymentNbr,
Person.PersonRef
from Employment,Person,Company,SchemeMembers,#list1
where Employment.companyRef = Company.Companyref
and Employment.Personref =Person.Personref
and #list1.Schemeref = SchemeMembers.Schemeref
and #list1.EmploymentNbr = SchemeMembers.EmploymentNbr
and #list1.EmploymentNbr = Employment.EmploymentNbr
order by Salary2,Salary1


drop table #list1

I'm running SDACE 5.00.0.5 for Delphi 5

Thank you for your assistance.[/b]

[AndreyZ]

Hello,

This problem is connected with parameters usage. BDE doesn't pass parameters to SQL Server, it passes plain SQL text, and that's why it works. When you are trying to execute the following SQL code using SDAC:

Code: Select all

declare @SchemeRef char(8) 
declare @p1 integer
select @Schemeref = :Schemeref

, SDAC sends the following code to the server:

Code: Select all

exec sp_executesql N'declare @SchemeRef char(8) 
declare @p1 integer 
select @Schemeref = @P1',N'@P1 nvarchar(4000)',NULL

As you can see, @P1 is used to work with a parameter you input in the SQL code. To avoid this problem, you shouldn't use variables with names like @P in your SQL code (if you are using parameters).

[AndreyZ]

Please specify why you cannot use our Migration Wizard. Maybe you can suggest some features to implement to it.

[lcoelho]

Hi AndreyZ,

Thank you for your response. So, as I understand, @P1 is internally used by SDAC so cannot be used. Thanks for that.

I appreciate very much the BDE/ADO Migration Wizard that comes with SDAC. However it seems that it only converts the different component types to the equivalent SDAC component (e.g. TQuery to TMSQuery) without removing incompatible properties from the converted components.

There are also some syntax changes that are required. For example, in BDE, when using a StoreProcedure with parameters, there is no PrepareSQL before making use of the ParamByName property. However, for to work correctly, SDAC requires that PrepareSQL be used before parameters are used. So my migration wizard adds that into the code.

Also, take the example above, where the projects have parameter names @P1. I will have to implement this in my migration wizard to replace occurences of @P1 with something else.

After using the Migration Wizard that ships with SDAC, BDE TDatabase components get converted to SDAC TMSConnection. But, some of the TDatabase properties that are not used (e.g. AliasName and KeepConnection) remain. Other properties that remain after converting that belong to the BDE components are NarrowSearch, SessionState, Session, ValidateWithMask, IndexName, and others. These also need to be removed.

Additionally, if a component's SQL code creates a temporary table and a 'prepare' statement is used, an error occurs that 'statement(s) cannot be prepared' after converting to SDAC. This was not the case in BDE. So the 'prepare' statement needs to be removed from the .PAS file.

In BDE, in the DFM file, parameter names are preceded with @ in BDE. This does not work with SDAC as it gives 'parameter not found' error. So my wizard removes @ from before the parameter name.

Something else my wizard does is allows the option of migrating the whole project to a different folder so that one can still have the old version in place and the updated version in another location.

These are a few of the things I've implemented into this additional wizard. I trust you understand.

Thanks once again for your kind assistance.