Question about running multiple SQL scripts with vsql

Moderator: NorbertKrupa

Post Reply
mikep17
Newbie
Newbie
Posts: 4
Joined: Fri Dec 04, 2015 3:11 pm

Question about running multiple SQL scripts with vsql

Post by mikep17 » Fri Dec 04, 2015 3:18 pm

Disclaimer: I'm new to Vertica.
I have a shell script that runs a SQL script with vsql
i.e.
runVSQL.sh:
<code>
#/bin/sh
vsql -f FILELIST.sql
</code>

FILELIST contains a list of other SQL scripts to run.
i.e.
FILELIST.sql:
<code>
\i insert.sql
</code>

This works fine when the SQL scripts it's running contains DDL to create schema objects i.e. CREATE table .....
but when I try to run DML
i.e.
insert.sql:
<code>
INSERT INTO SomeTable (something1,something2) VALUES ( somevalue1,somevalue2 );
</code>

vsql fails complaining about syntax.... Syntax error at or near "INSERT" at character 1

If I run the insert.sql ( vsql -f insert.sql ) script directly, it works fine. It fails when calling from another script using \i insert.sql

I'm sure it's something silly, but what am I missing? Am I going about this all wrong?
Any input would be great.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Question about running multiple SQL scripts with vsql

Post by scutter » Fri Dec 04, 2015 4:22 pm

Does FILELIST.SQL run if you remove the \i insert.sql line? I’ll guess that there’s an error on the prevoius sql - a missing semi-colon or something similar.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

mikep17
Newbie
Newbie
Posts: 4
Joined: Fri Dec 04, 2015 3:11 pm

Re: Question about running multiple SQL scripts with vsql

Post by mikep17 » Fri Dec 04, 2015 6:50 pm

Thanks for the response..
I think I found the problem. I was using \set ON_ERROR_STOP in FILELIST.sql and then I had some characters in the insert file that weren't visible. Once I cleaned up the insert.sql file ( I used the dos2unix command ), things worked.

Now next question is, how can I set a particular schema so I don't have to have the schema specified in my create and insert statements... Here is an example of what I'm trying to do.
I run a shell script

run.sh:

Code: Select all

#!/bin/sh

set -e 

/opt/vertica/bin/vsql -c "CREATE schema IF NOT EXISTS sandbox"
/opt/vertica/bin/vsql -f createrunner.sql
createrunner.sql:

Code: Select all

\set ON_ERROR_STOP on
\set search_path to sandbox

\i createTable.sql
createTable.sql:

Code: Select all

CREATE TABLE MyTestTable
(
    id char(36) NOT NULL,
    gender varchar(100)
);
When I do this, the table is getting created in my public schema, not my "sandbox" schema

Thanks for your help!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Question about running multiple SQL scripts with vsql

Post by JimKnicely » Fri Dec 04, 2015 8:23 pm

Hi,

Try changing the createrunner.sql file to:

Code: Select all

\set ON_ERROR_STOP on
set search_path to sandbox;

\i createTable.sql
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

mikep17
Newbie
Newbie
Posts: 4
Joined: Fri Dec 04, 2015 3:11 pm

Re: Question about running multiple SQL scripts with vsql

Post by mikep17 » Sat Dec 05, 2015 6:58 am

Thanks, I'll give it a try... any explanation as to why the set in the search_path doesn't use "\"?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Question about running multiple SQL scripts with vsql

Post by JimKnicely » Sat Dec 05, 2015 5:05 pm

Hi,

The vsql \set meta-command is used to set variables (ON_ERROR_STOP is a variable):

See: http://my.vertica.com/docs/7.2.x/HTML/i ... iables.htm

While the SET command sets "run-time parameters" for the current session. SEARCH_PATH is one of these parameters.

See: http://my.vertica.com/docs/7.2.x/HTML/i ... s/SHOW.htm

Thanks!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

mikep17
Newbie
Newbie
Posts: 4
Joined: Fri Dec 04, 2015 3:11 pm

Re: Question about running multiple SQL scripts with vsql

Post by mikep17 » Sun Dec 06, 2015 5:13 am

JimKnicely wrote:Hi,

The vsql \set meta-command is used to set variables (ON_ERROR_STOP is a variable):

See: http://my.vertica.com/docs/7.2.x/HTML/i ... iables.htm

While the SET command sets "run-time parameters" for the current session. SEARCH_PATH is one of these parameters.

See: http://my.vertica.com/docs/7.2.x/HTML/i ... s/SHOW.htm

Thanks!
Thanks Jim!

Post Reply

Return to “New to Vertica SQL”