How to properly change database name in SQL

[ertank]

Hi,

Using Delphi 10.1, MSSQL server 2014. I have that SQL saved in a text file which runs a select statement getting records from two databases on same server.

Code: Select all

select a.STKBARKOD as "Barkod",
       b.STKCINSI as "BarkodAciklamasi", 
	   b.STKKOD as "UrunKodu", 
	   b.STKCINSI as "UrunAciklamasi", 
	   b.STKOZKOD1 as "KisaAd", 
	   b.STKOZKOD2 as "GrupKodu", 
	   d.KDVKISORAN as "KdvOran", 
	   c.STKFIYTUTAR as "Fiyat", 
	   b.STKBIRIM as "Birim" 
from STKBARKOD a, STKKART b, STKFIYAT c, [:MASTERDB].[dbo].[KDVKISIM] d
where
a.STKBARFIYTIP = 2
and
c.STKFIYNO = a.STKBARFIYTIP and c.STKFIYTUTAR > 0
and
b.STKKOD = a.STKBARSTKKOD
and
c.STKFIYSTKKOD = a.STKBARSTKKOD
and
d.KDVKISNO = c.STKFIYKDVNO

I need to change second database at run-time. So, I try to use second database name as a parameter.

Below code receives an error saying "Argument out of range".

Code: Select all

UniQuery1.Close();
UnQuery1.SQL.LoadFromFile('SQLFILE.TXT');
UniQuery1.Params[0].AsString := 'OTHERDBNAME';
UniQuery1.Open();

Changing "Params[0]" with "ParamByName('MASTERDB')" gives error saying "Parameter MASTERDB not found"

However, if I put that SQL in a TUniQuery at design time, I can see parameter MASTERDB in the Object Inspector at design time. Using that design time query:

Code: Select all

UniQuery1.Close();
UniQuery1.Params[0].AsString := 'OTHERDBNAME';
UniQuery1.Open();

I get no error for Params[0]. Though, error I receive this time is "Invalid object name ':MASTERDB.dbo.KDVKISIM'."

My questions are:
1- Why I cannot use parameters if SQL is loaded from a text file?
2- What is the proper way of changing database name at run-time?

Thanks.

[azyk]

SQL Server has no support for capability to set database name as a parameter value in the FROM section of a SQL query. And we can't affect this.

You can use UniDAC macros, in order to set database name in the FROM section of a SQL query at runtime. See more details about UniDAC macros in our online-documentation: https://www.devart.com/unidac/docs/?work_macros.htm

[ertank]

Using macro solved my problem. I could use them nearly same way I use Params.

Thanks.