Question Re: execute immediate for read command in Technology Q&A

execute immediate for read command

Baron — Fri, 02 Jun 2023 07:54:15 GMT

Is this somehow possible?

execute immediate ('read c:\\\\temp\\\\myScript.sql');

Re: execute immediate for read command

VolkerBarth — Fri, 02 Jun 2023 08:08:18 GMT

No, READ is a DBISQL command (like INPUT and OUTPUT) whereas EXECUTE IMMEDIATE is executed in the database engine. It's the same reason why stored procedures cannot contain OUTPUT commands.

What exactly are you trying to achieve?

Note, you can use PARAMETERS within DBISQL scripts and therefore parametrize the statements within command files, if that's what you up to.

And of course, within your SQL code, you can build command lines for DBISQL and any script files and then use xp_cmdshell to run them...

Re: execute immediate for read command

chris_keating — Sat, 03 Jun 2023 13:39:19 GMT

You could use xp_read_file to supply the content to execute immediate. For example;

execute immediate(xp_read_file( 'c:\\temp\\myScript.sql' ))

This assumes that the myScript.sql is accessible on the same machine as the database server. If not, you could use the READ_CLIENT_FILE procedure but that would require a more involved setup.

Re: execute immediate for read command

Baron — Mon, 06 Nov 2023 05:50:28 GMT

The point is:

I want to encapsulate the read command inside a procedure, so that a user (not dba) can install updates (read sql scripts in database):

Using xp_read_file can I get the content of my sql script, but again, I dont hava any means to execute it.

I tried something like this:

create or replace procedure dba.installupdates(sqlfilepath varchar(100))
begin
declare filecontent long varchar;
set filecontent = xp_read_file (fullpath);
execute immediate (filecontent);
end;
grant execute on dba.installupdates to myuser

and then I try to install the update so:

setuser myuser;
call dba.installupdates('c:\\\\temp\\\\myscript.sql');

Re: execute immediate for read command

VolkerBarth — Mon, 06 Nov 2023 06:14:34 GMT

If you want to execute a given SQL script against a SQL Anywhere database, you can certainly use DBISQL (or the older dbisqlc) with the script as parameter. Within your procedure, you could build a fitting DBISQL(c) command line and execute that via xp_cmdshell (and check the return value!).

You might also consider the ALTER DATABASE UPGRADE SCRIPT FILE ... statement to execute user-defined updates.

Nevertheless, running a user-supplied script (or a script a user could modify before it is "automatically" executed with DBA privilege) raises some security concerns...

FWIW, in case you use SQL Remote or MobiLink, schema updates on remotes would usually be done via SQL Remote's PASSTHROUGH mode resp. ML's ALTER DATABASE SCHEMA FROM FILE.. faclility.

Re: execute immediate for read command

VolkerBarth — Mon, 06 Nov 2023 06:34:25 GMT

You said you tried that, so what is the result?

Re: execute immediate for read command

chris_keating — Mon, 06 Nov 2023 10:01:22 GMT

Note that the procedure argument is sqlfilepath but fullpath is provided to xp_read_file? Assuming that is corrected, this should work with these assumptions

The myScrypt.sql contains valid SQL and is read (inside the procedure context). I would likely want to check that the filecontent variable length > 0 or debug the procedure to check the variable. Note that xp_read_file assumes that the c:\\temp\\myScript.sql is on the same machine as the database server otherwise READ_CLIENT_FILE would need to be used.
The procedure is running with SQL SECURITY DEFINER (assuming that the myScript.sql cannot be directly run by myuser (i.e., insufficient role privileges).

In either case, there will be error messages that would help to explain the issue you are encountering.

Re: execute immediate for read command

Baron — Mon, 06 Nov 2023 15:44:19 GMT

Thanks for the reply and hints.

It works now, The problem was that my sql file was coded as UTF-16, I recoded it as UTF-8. Is there a way to make xp_read_file able to read UTF-16 files without disruption?

Re: execute immediate for read command

chris_keating — Mon, 06 Nov 2023 16:43:58 GMT

You can use csconvert for this. You will need to know whether the source file is big-endian or little-endian. For example, toe convert from utf16 little-endian to utf8,

set stmt = xp_read_file( fullpath ); set stmt = csconvert( stmt, 'utf-8', 'utf-16le' );

Change le to be if it is big-endian.