use of the Read statement with a variable.

[Former Member]

I am trying to create a script to run scripts from a file and would like to be able to declare a "rootDirectory" variable. Example:

BEGIN ATOMIC
DECLARE @rootDir VARCHAR(255) = 'D:\\MyScriptDir\\Dev\\';
READ @rootDir + 'MyDevScript1.sql';
READ @rootDir + 'MyDevScript2.sql';
-- etc..
END;


I keep getting a syntax error and can't figure out where/what it is?

[MarkCulp]

The READ statement is a client-side dbisql[c] statement and cannot be used in a server-side batch or procedure.

Try using xp_read_file or read_client_file. xp_read_file() will read a file on the server, while read_client_file() will read a file on the client (but the data is automatically sent to the server). See the docs for more information and examples.

HTH

[Breck_Carter]

As Mark indicates, you can't do exactly what you are asking, but perhaps you can do what you want with the PARAMETERS statement; see READ and PARAMETERS.

Here's an example...

run_dbisql16_READ_master.bat

"%SQLANY16%\\bin64\\dbisql.com"^
   -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql"^
   READ ENCODING Cp1252 "master.sql" [c:/temp/]

master.sql

PARAMETERS rootDir;
READ '{rootDir}MyDevScript1.sql';
READ '{rootDir}MyDevScript2.sql';

c:\\temp\\MyDevScript1.sql

MESSAGE 'MyDevScript1.sql has been executed.' TO CONSOLE;

c:\\temp\\MyDevScript2.sql

MESSAGE 'MyDevScript2.sql has been executed.' TO CONSOLE;

dbsrv16 console output...

MyDevScript1.sql has been executed.
MyDevScript2.sql has been executed.

[Former Member]

Breck, it looks like you're right. I'm still getting a syntax error using "read_client_file()" it seems that any declared variable is being treated as a literal. The problem is that I need the script to be as simple and straight forward as possible without having to run/maintain external batch files.

[Breck_Carter]

If you are having a problem with read_client_file(), please post your exact code in a new question, along with the exact error message.