You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by dexter195 <de...@yahoo.com> on 2008/02/14 13:20:13 UTC
sql format in *.sql files
Hi
I have a load of sql files that i need to be able to import into derby.
What delimiter do i need at the end of the statements in order for derby to
see it as a seperate query. i presumed that this would be ; but this doesnt
work. I've tried all variants i can think of but none of them work.
insert into MeasurementColumn values
("test","test",1,"test",2,3,4,5,"test","test","test","test",6);
insert into MeasurementColumn values
("test1","test1",1,"test1",2,3,4,5,"test1","test1","test1","test1",6);
ERROR 42X01: Syntax error: Encountered ";" at line 2, column 129.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at com.test.CreateDatabase.insertTestData(CreateDatabase.java:153)
at com.test.CreateDatabase.main(CreateDatabase.java:247)
If i leave it as a blank line between the statements it doesnt recogonise
the second statement.
insert into MeasurementColumn values
("test","test",1,"test",2,3,4,5,"test","test","test","test",6)
insert into MeasurementColumn values
("test1","test1",1,"test1",2,3,4,5,"test1","test1","test1","test1",6)
ERROR 42X01: Syntax error: Encountered "insert" at line 3, column 1.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at com.test.CreateDatabase.insertTestData(CreateDatabase.java:153)
at com.test.CreateDatabase.main(CreateDatabase.java:247)
Thanks for the help
Dexter
--
View this message in context: http://www.nabble.com/sql-format-in-*.sql-files-tp15479296p15479296.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: sql format in *.sql files
Posted by dexter195 <dl...@yahoo.co.uk>.
thanks for the help
Knut Anders Hatlen wrote:
>
> dexter195 <dl...@yahoo.co.uk> writes:
>
>> Ok, so il just have to split them up and run them in seperatly then.
>> Thanks
>> for that.
>
> Alternatively, you could use the runScript() method in
> org.apache.derby.tools.ij, which does exactly that for you. See the
> javadoc here:
>
> http://db.apache.org/derby/docs/10.3/publishedapi/jdbc4/org/apache/derby/tools/ij.html#runScript(java.sql.Connection,%20java.io.InputStream,%20java.lang.String,%20java.io.OutputStream,%20java.lang.String)
>
> --
> Knut Anders
>
>
--
View this message in context: http://www.nabble.com/sql-format-in-*.sql-files-tp15479296p15544262.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: sql format in *.sql files
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
dexter195 <dl...@yahoo.co.uk> writes:
> Ok, so il just have to split them up and run them in seperatly then. Thanks
> for that.
Alternatively, you could use the runScript() method in
org.apache.derby.tools.ij, which does exactly that for you. See the
javadoc here:
http://db.apache.org/derby/docs/10.3/publishedapi/jdbc4/org/apache/derby/tools/ij.html#runScript(java.sql.Connection,%20java.io.InputStream,%20java.lang.String,%20java.io.OutputStream,%20java.lang.String)
--
Knut Anders
Re: sql format in *.sql files
Posted by dexter195 <dl...@yahoo.co.uk>.
Ok, so il just have to split them up and run them in seperatly then. Thanks
for that.
Dexter
Donald McLean-3 wrote:
>
> In a tool like "ij" the semicolons are used to delimit individual
> statements. In JDBC you don't use them because you can only run one
> statement at a time.
>
> Donald
>
>
--
View this message in context: http://www.nabble.com/sql-format-in-*.sql-files-tp15479296p15495825.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: sql format in *.sql files
Posted by Donald McLean <dm...@gmail.com>.
In a tool like "ij" the semicolons are used to delimit individual
statements. In JDBC you don't use them because you can only run one
statement at a time.
Donald
Re: sql format in *.sql files
Posted by dexter195 <de...@yahoo.com>.
Hi Bernt,
Thanks for the help. I have changed those quotes but im still getting the
same problem, that the semi colons arnt being recognised at the end of the
sql statements.
This is what im doing. Am i inserting this correctly?
Dexter
SQL File:
insert into MeasurementColumn values
('MTABLEID','DATANAME',1,'DATATYPE',2,3,4,5,'INDEXES','DESCRIPTION','DATAID','RELEASEID',6);
insert into MeasurementColumn values
('MTABLEID123','DATANAME',1,'DATATYPE',2,3,4,5,'INDEXES','DESCRIPTION','DATAID','RELEASEID',6);
// This method takes in a statement object and the SQL filename
// it then calls the readSQL file and then executes the statements contained
in the SQL file.
public void insertTestData( Statement stmnt, String strFileName ) throws
Exception
{
String sqlInserts = readSQLFile( strFileName );
try
{
{
stmnt.execute( sqlInserts );
}
}
catch( Exception e )
{
e.printStackTrace();
}
System.out.println( "Table Information Inserted." );
}
private static String readSQLFile( String strFileName )
{
StringBuffer strFileContents = new StringBuffer();
BufferedReader input = null;
try
{
input = new BufferedReader( new FileReader(strFileName) );
String line = null;
while ( (line = input.readLine() ) != null )
{
strFileContents.append( line );
strFileContents.append( System.getProperty("line.separator") );
}
}
catch Statements.......
}
ERROR 42X01: Syntax error: Encountered ";" at line 3, column 92.
Bernt M. Johnsen wrote:
>
>>>>>>>>>>>>> dexter195 wrote (2008-02-14 04:20:13):
>>
>> Hi
>>
>> I have a load of sql files that i need to be able to import into derby.
>>
>> What delimiter do i need at the end of the statements in order for derby
>> to
>> see it as a seperate query. i presumed that this would be ; but this
>> doesnt
>> work. I've tried all variants i can think of but none of them work.
>>
>> insert into MeasurementColumn values
>> ("test","test",1,"test",2,3,4,5,"test","test","test","test",6);
>> insert into MeasurementColumn values
>> ("test1","test1",1,"test1",2,3,4,5,"test1","test1","test1","test1",6);
>>
>> ERROR 42X01: Syntax error: Encountered ";" at line 2, column 129.
>
> The statement is syntactically incorrect. You should use ' not " to
> delimit strings in SQL, like this:
>
> insert into MeasurementColumn values
> ('test','test',1,'test',2,3,4,5,'test','test','test','test',6);
> insert into MeasurementColumn values
> ('test1','test1',1,'test1',2,3,4,5,'test1','test1','test1','test1',6);
>
>
> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Derby/Java DB
> Sun Microsystems, Trondheim, Norway
>
>
>
--
View this message in context: http://www.nabble.com/sql-format-in-*.sql-files-tp15479296p15480001.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: sql format in *.sql files
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> dexter195 wrote (2008-02-14 04:20:13):
>
> Hi
>
> I have a load of sql files that i need to be able to import into derby.
>
> What delimiter do i need at the end of the statements in order for derby to
> see it as a seperate query. i presumed that this would be ; but this doesnt
> work. I've tried all variants i can think of but none of them work.
>
> insert into MeasurementColumn values
> ("test","test",1,"test",2,3,4,5,"test","test","test","test",6);
> insert into MeasurementColumn values
> ("test1","test1",1,"test1",2,3,4,5,"test1","test1","test1","test1",6);
>
> ERROR 42X01: Syntax error: Encountered ";" at line 2, column 129.
The statement is syntactically incorrect. You should use ' not " to
delimit strings in SQL, like this:
insert into MeasurementColumn values
('test','test',1,'test',2,3,4,5,'test','test','test','test',6);
insert into MeasurementColumn values
('test1','test1',1,'test1',2,3,4,5,'test1','test1','test1','test1',6);
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway