Is this somehow possible?

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

asked 02 Jun '23, 07:54

Baron's gravatar image

Baron
2.1k134148177
accept rate: 48%


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...

permanent link

answered 02 Jun '23, 08:08

Volker%20Barth's gravatar image

Volker Barth
40.2k361549822
accept rate: 34%

edited 02 Jun '23, 08:14

1

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.

(03 Jun '23, 13:39) Chris Keating
Replies hidden

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');
(06 Nov '23, 05:50) Baron

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.

(06 Nov '23, 06:14) Volker Barth

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

(06 Nov '23, 06:34) Volker Barth
1

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.

(06 Nov '23, 10:01) Chris Keating

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?

(06 Nov '23, 15:44) Baron

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.

(06 Nov '23, 16:43) Chris Keating
showing 4 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×7

question asked: 02 Jun '23, 07:54

question was seen: 375 times

last updated: 06 Nov '23, 16:44