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.
Question about running multiple SQL scripts with vsql
Moderator: NorbertKrupa
Re: Question about running multiple SQL scripts with vsql
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
Vertica Consultant, Zazz Technologies LLC
Re: Question about running multiple SQL scripts with vsql
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:
createrunner.sql:
createTable.sql:
When I do this, the table is getting created in my public schema, not my "sandbox" schema
Thanks for your help!
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
Code: Select all
\set ON_ERROR_STOP on
\set search_path to sandbox
\i createTable.sql
Code: Select all
CREATE TABLE MyTestTable
(
id char(36) NOT NULL,
gender varchar(100)
);
Thanks for your help!
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Question about running multiple SQL scripts with vsql
Hi,
Try changing the createrunner.sql file to:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Question about running multiple SQL scripts with vsql
Thanks, I'll give it a try... any explanation as to why the set in the search_path doesn't use "\"?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Question about running multiple SQL scripts with vsql
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!
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Question about running multiple SQL scripts with vsql
Thanks Jim!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!