what is command used to run the the database query sripts?

[Former Member]

Hi all,

i got some clue from another thread and i observed the command is like

sqlcli -d DBNAME -u SQLUSER,SQLPASS -i filename.sql -c <delimiter> is there,

when i use this the only first query is successfully executing,

this might be problem with delimiter.

can any body help how to use delimiter in the script file with an example.

thanks in advance.

--Hari

[former_member229109 · Accepted solution]

Hello Hari,

1. Please Review the information at:

http://dev.mysql.com/doc/maxdb

2. http://dev.mysql.com/doc/maxdb

( on the right in the Section 'MaxDB Tutorials' )

-> SQL Tutorial: Using SQL with MaxDB

3. The documentation about SQLCLI tool available in

MAXDB library -> Tools -> SQLCLI

-> Overview of the Call Options

For example, options:

-i <file> - Imports commands from the batch file <file>

-c <separator> - When importing commands from a file,

the SQLCLI uses the <separator> to separate the

individual commands.

The default value is //

4. Could you please give more info or link to the referereced thread .

Thank you and best regards, Natalia Khlopina

[Former Member · Accepted solution]

Natalia,

Thanks for the info. but i still getting problem with formatting.

could you please give example format (<script_file.sql>) with 2 to 3 insert statements with separator and the syntax of the sqlcli command to run the update (full).

thanks in advance.

--Hari

[former_member229109 · Accepted solution]

Hello Hari,

1. Please let me know the database version & sqlcli tool version, which you are using:

sdbregview -l

< review the installed the database software packages >

xinstinfo <DBNAME>

< to check the Kernelversion of your database >

sqlcli -v

< to check the version of sqlcli >

2. Please update with the options/statements you was running.

What errors/problems did you get?

3. For example, I run on the local server:

sqlcli -d NLK -u sapr3,sap

....

sqlcli NLK=> \i c:\test\run_1.sql

0 rows affected (3 msec)

0 rows affected (2 msec)

sqlcli NLK=> \i c:\test\run_2.sql

1 row affected (3 msec)

1 row affected (2 msec)

sqlcli NLK=> \i c:\test\run_3.sql

0 rows affected (4 msec)

1 row affected (2 msec)

1 row affected (2 msec)

1 row affected (2 msec)

sqlcli NLK=> select * from a3

COL1	COL2
56	49
44	12
48	12

3 rows selected (2 msec)

...

Run_1:

create table a1 (col1 int NOT NULL default '0', col2 int NOT NULL default '0')

//

create table a2 (col1 int NOT NULL default '0', col2 int NOT NULL default '0')

Run_2:

INSERT INTO a1 values (56,49)

//

insert into a2 values (44,12)

Run_3:

create table a3 (col1 int NOT NULL default '0', col2 int NOT NULL default '0')

//

INSERT INTO a3 values (56,49)

//

insert into a3 values (44,12)

//

insert into a3 values (48,12)

4. To use option '-c <separator>' example:

sqlcli -d NLK -u sapr3,sap -c /# -i c:\test\run_4.sql

COL1	COL2
56	49
56	49
44	12
48	12

Run_4::

create table a3 (col1 int NOT NULL default '0', col2 int NOT NULL default '0')

/#

INSERT INTO a3 values (56,49)

/#

insert into a3 values (44,12)

/#

insert into a3 values (48,12)

/#

commit

/#

select * from a3

Thank you and best regards, Natalia Khlopina

Add number 4. example.

Message was edited by:

Natalia Khlopina

[Former Member]

Hari,

in the batch file, the individual commands are in individual rows and separated by a separator.

You can specify the separator used in the batch file between two commands using the -c <separator> call option. The default value is //

Do not enter additional spaces in the batch file. The two separators // and <blank>//, for example, are not identical for the database system:

Example of a batch file (for e.g., filename.sql):

CREATE TABLE city

(zip CHAR (5) KEY CONSTRAINT zip BETWEEN '10000' AND '99999',

name CHAR(20) NOT NULL,

state CHAR(2) NOT NULL)

//

CREATE TABLE customer

(cno FIXED (4) KEY CONSTRAINT cno BETWEEN 1 AND 9999,

title CHAR (7) CONSTRAINT title IN ('Mr', 'Mrs', 'Company'),

firstname CHAR (10) ,

name CHAR (10) NOT NULL,

zip CHAR (5) CONSTRAINT zip BETWEEN '10000' AND '99999',

address CHAR (25) NOT NULL,

FOREIGN KEY (zip) REFERENCES city ON DELETE RESTRICT)

//

COMMIT

In this example, AUTOCOMMIT mode is deactivated. To ensure that the SQLCLI executes the SQL statements immediately after the batch file has been imported, the batch file must contain an explicit COMMIT.

Please let me know whether this works.

Thanks,

Ashwath