Error: 'CREATE VIEW' must be the first statement in a query batch

Have you ever gotten this error?

Error: 'CREATE VIEW' must be the first statement in a query batch.
SQLState: S0001
ErrorCode: 111

or maybe

Error: 'CREATE TRIGGER' must be the first statement in a query batch.

Well, do not worry. Here is how you can solve this issue: Just wrap your query with exec(''). For example, lets say your view is defined as

CREATE VIEW theView AS
   SELECT  tableA.id + '_' tableA.name AS ID_NAME,
      tableB.* FROM tableA a
   INNER JOIN tableB b ON a.a_id = b.b_Id

Well then just change your call as follows:

exec('CREATE VIEW theView AS
   SELECT  tableA.id + ''_'' tableA.name AS ID_NAME,
      tableB.* FROM tableA a
   INNER JOIN tableB b ON a.a_id = b.b_Id')

Notice that single quotes origninally in my CREATE statement are escaped as two consecutive single quotes after it is wrapped in the call to exec('').

You will get the error above, as I did, when executing the CREATE from within SQuirreL SQL, so apparently SQuirreL is attempting to do some 'innocent' things before the CREATE statement. Wrapping the CREATE in an exec forces SQuirreL to put the CREATE statement first in a separate execution plan.

Hope that helps you.

Fall Leaves

See those fall leaves falling from those fall trees? See the leaves falling? They are yellow, green, purple, and brown. Don't frown. Turn your frown upside down. Don't be a clown, 'cuz if your singing, you'll be slinging your love. See the fall leaves falling? Are you stalling? I think not, 'cuz it's getting alot better than I thought. Good night!

by Kara Singleton

Bubbles and smiles ...

Friends
Fruits
Up close
Exercise much?
Lounging
Sisters
Pucker
Bubbles
Giggles and smiles

Beach time ...

First, some watermelon ...
What is that?
Let's go to the beach!
Ooooo - kay ...
Are we there yet?
We are here!
Big sisters ...
Sea shells ...
Sunsets ...
Yay!

deploying with sftp or scp using ant ... Could not load a dependent class com/jcraft/jsch/UserInfo

Have you ever gotten this error?

Could not load a dependent class com/jcraft/jsch/UserInfo

You are not alone.

You will find a page here that is supposed to help you, but well, let's just say that technical people have a problem communicating sometimes. This problem can also occur if you are trying to run an ant task from within Eclipse that tries to scp or sftp some files.

So below, I will show you how I solved this problem by downloading the Java Secure Channel jar from here, and putting that jar in the $ANT_HOME/lib directory. For the solution to this same problem from within Eclipse, see the section below entitled Solving the problem for Eclipse.

The error and the solution:

The following box shows the build.xml file used, then the ant command line used to reproduce the error, which is immediately followed by the commands needed to solve the issue, and then the successful sftp of files to a machine called, "remoteBox".

$ cat build.xml
<project name="Life" basedir=".">
   <target name="deploy">
      <scp todir="${user}:${pass}@remoteBox.com:." sftp="true" >
         <fileset dir=".">
            <include name="**/foo.*"/>
         </fileset>
      </scp>
   </target>
</project>

$ ant -Duser=geek -Dpass=g33k deploy
Buildfile: build.xml

deploy:

BUILD FAILED
build.xml:4: Problem: failed to create task or type scp
Cause: Could not load a dependent class com/jcraft/jsch/UserInfo
       It is not enough to have Ant's optional JARs
       you need the JAR files that the optional tasks depend upon.
       Ant's optional task dependencies are listed in the manual.
Action: Determine what extra JAR files are needed, and place them in one of:
        -$ANT_HOME\lib
        -$HOME\.ant\lib
        -a directory added on the command line with the -lib argument

Do not panic, this is a common problem.
The commonest cause is a missing JAR.

This is not a bug; it is a configuration problem


Total time: 0 seconds

$ cp jsch-0.1.41.jar $ANT_HOME/lib/.

$ ant -Duser=geek -Dpass=g33k deploy
Buildfile: build.xml

deploy:
      [scp] Connecting to remoteBox.com:22
      [scp] done.

BUILD SUCCESSFUL
Total time: 2 seconds

So, the procedure above shows:
1. the ant build target I used with the scp task
2. the failure that will occur without the use of the Java Secure Channel jar
3. copying the jar to your $ANT_HOME/lib directory
4. the successful transfer of files using sftp

This has been tested with ant 1.6.5 and 1.7.1, although you will get the following error if you use sftp="true" with ant 1.6.5:

$ ant -Duser=geek -Dpass=g33k deploy
Buildfile: build.xml

deploy:

BUILD FAILED
build.xml:4: The  type doesn't support the "sftp" attribute.

Total time: 0 seconds

If you simply remove the sftp="true" attribute from the scp task, then the file tranfer works fine under ant 1.6.5 using the build target as shown above.

Solving the problem for Eclipse

Depending on your version of Eclipse, the exact menu option may vary, but it goes something like this in the Galileo version of Eclipse: Go to Window > Preferences > Ant > Runtime > Classpath (tab), select “Ant Home Entries (default)” and click “Add External JARs…”, then add the Java Secure Channel jar from above Then click Apply and Ok. Now Eclipse should be able to run ant tasks which scp or sftp files as noted above.

Hope that helps.

Going to the park ...

Here's looking at you, kid ...On the slide.On the swingIn Him.

Someone's Birthday ...

.
In the trees again

And again






It looks so high
Tickle giggles