7 Replies Latest reply on Aug 19, 2008 7:13 PM by jschellSomeoneStoleMyAlias

    Query using the SQL 'go' command  on a JAVA code

    843859
      Hi,
      I am trying to create a new database on MS SQL and at the same time verify whether the data base exist already and then add a new table. The query statement works well on the query windows on MS SQL, but when the query is place using a JAVA code it gives the following error:

      java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'go'.
      java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'go'.
           at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
           at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
           at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
           at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
           at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
           at DataBaseCreator.main(DataBaseCreator.java:30)

      I have to add that if I only query: "CREATE DATABASE NameOfDatabase" the new data base is created without a problem using JAVA, but when I try to use the following query, I got the error message.

      "USE Master "
                          + "IF EXISTS (SELECT * FROM SysDatabases WHERE NAME='DatesTemps') "
                          + " DROP DATABASE DatesTemps"
                          + " go "
                          + " CREATE DATABASE DatesTemps22 "
                          + " go"
                          + " USE DatesTemps "
                          + " CREATE TABLE Fable ( "
                          + " FableID INT NOT NULL CONSTRAINT FablePK PRIMARY KEY NONCLUSTERED, "
                          + " Title VARCHAR(50) NOT NULL, "
                          + " Moral VARCHAR(100) NOT NULL, "
                          + " FableText VARCHAR(1536) NOT NULL, "
                          + " BlobType CHAR(3) NULL DEFAULT 'doc', "
                          + " Blob IMAGE NULL DEFAULT NULL )"
                          );

      If it is useful my complete code is the following, I appreciate in advance your comments.

      import java.sql.Connection;
      import java.sql.Statement;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.text.DateFormat;
      import java.text.SimpleDateFormat;
      import java.util.*;

      import java.util.Calendar;
      import java.util.GregorianCalendar;


      public class DataBaseCreator {
           public static void main (String[] args)
           {
                Connection Time =null;
                Statement stmt = null;
                String data = "jdbc:odbc:DataBaseCreation";
                try
                {
                     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                     Time= DriverManager.getConnection(data,"","");
                     stmt = Time.createStatement();
                     //String query;
                     //java.sql.Timestamp ts1 = new java.sql.Timestamp(((3*60)+58)*60*1000);
                // System.out.println(ts1 + " This is a Time Stamp");
                     
                
                ResultSet rec = stmt.executeQuery(
                
                          "USE Master "
                          + "IF EXISTS (SELECT * FROM SysDatabases WHERE NAME='DatesTemps') "
                          + " DROP DATABASE DatesTemps"
                          + " go "
                          + " CREATE DATABASE DatesTemps22 "
                          + " go"
                          + " USE DatesTemps "
                          + " CREATE TABLE Fable ( "
                          + " FableID INT NOT NULL CONSTRAINT FablePK PRIMARY KEY NONCLUSTERED, "
                          + " Title VARCHAR(50) NOT NULL, "
                          + " Moral VARCHAR(100) NOT NULL, "
                          + " FableText VARCHAR(1536) NOT NULL, "
                          + " BlobType CHAR(3) NULL DEFAULT 'doc', "
                          + " Blob IMAGE NULL DEFAULT NULL )"
                          );
                }
                catch( Exception e )
                     {
                          System.err.println( e );
                          e.printStackTrace();
                     }
                finally
                     {
                          try
                     {
                               stmt.close();
                               Time.close();
                     }
                catch( Exception e )
                     {
                          System.err.println( e );
                     }
                }
           }
      }
        • 1. Re: Query using the SQL 'go' command  on a JAVA code
          843859
          first, you need to verify your sql works outside of java before running in thru JDBC
          second, you need to use code tags when you post code
          third, and finally, don't do this! don't put DDL statements in Java code.
          to this day, i've never seen a good reason (or a success story) to do this
          yet we seem to get these questions daily
          what is your requirement here? why do you want to do this?
          • 2. Re: Query using the SQL 'go' command  on a JAVA code
            843859
            Ok, first of all thanks for your answer, now what I want to do is the following:

            1) I need to input ((retrieve) every minute some data (Date and Temperature), that I will get from and external device, this is not part of this code.
            2) I want to store that data (Date and Temp) on a data base, here where I need the command to create the data base and to verify whether that data base already exists. In case it has already been created then create new table(s) on it. If it doesn't exist then create and then create new tables.
            3) Each Day (at yyyy mm dd 00:00:00:000 will create a new table, and the name of this table will be yyyymmdd.
            4) Then every minute the java code will retrieve the data and add it to the table in the data base.
            5) That will be a close loop that will be running until the user interrupt it.

            I haven't make the communication code yet, in order to test my code I was thinking to retrieve the data from another data base. This would be just to verify that the JAVA sequence is able to retrieve the data every minute and create new data base and tables.

            For the record I am able to send the queries and retrieve information from SQL running the code in eclipse, that will cover your first observation.
            I am quite new in the forum I am sorry I didn't get the use of code tags.
            Sorry again as I said I am quite new on this, what's a DDL statement.

            Thanks.
            • 3. Re: Query using the SQL 'go' command  on a JAVA code
              jschellSomeoneStoleMyAlias
              Reading the documentation can be useful. The following is taken from the MS SQL Server Books online.

              - - - - - - - - - - - -
              GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
              - - - - - - - - - - - -

              Since java is obviously none of the three utilities mentioned above we can be sure that will never work.

              You might keep in mind that most if not all database gui tools have commands that are not part of the SQL that the database uses.


              I am trying to create a new database on MS SQL.
              And why are you attempting to create a database in java?

              I am not interested in the ultimate goal of the system you are building. What I want to know is why you must create the database itself using java. Why do you just not create a sql script and apply it via a tool (like one of the ones mentioned in the documentation for 'go')?
              • 4. Re: Query using the SQL 'go' command  on a JAVA code
                843859
                Roberto_Guevara wrote:
                3) Each Day (at yyyy mm dd 00:00:00:000 will create a new table, and the name of this table will be yyyymmdd.
                Why not just a single table with a column whose value represents the current day?

                Creating a new table every day is just stupid.
                • 5. Re: Query using the SQL 'go' command  on a JAVA code
                  843859
                  I agree, it is not necessary to create a new table every day
                  • 6. Re: Query using the SQL 'go' command  on a JAVA code
                    843859
                    Thanks, no wonder "Go" is not working.

                    Actually the reason I am using JAVA is because I have already made one code to retrieve the info from a SQL data base, and at first glance I try to recycle my first code and try to adapt to this solution.
                    The second reason I used is because I am familiar with JAVA and later I will have to retrieve the information from a FHEM and also create a web interface.
                    • 7. Re: Query using the SQL 'go' command  on a JAVA code
                      jschellSomeoneStoleMyAlias
                      Ooops forgot to mention how to get around the 'go' problem.

                      There are several ways.

                      The go just represents a block of statements to be executed. However the type of statement must be considered.

                      For example consider the following

                      create database X ...
                      use X;

                      In the following the second statement is basically just connecting to the database. Doing that is obvious something that you would not do in a JDBC statement.

                      However with something like the following.

                      select * from mytable1
                      select * from mytable2

                      This are standard type statements and can be run in a single jdbc statement by surrounding them with a begin/end.

                      The exact syntax of the SQL and usage in JDBC depends on the statements and the database, but the general idea applies.