Insert data into Excel does not work

From: Werner Nussbaumer (w.nussbaumer_at_datacomm.ch)
Date: 07/27/04 

Date: 27 Jul 2004 02:31:48 -0700

Hello

I tried to execute the above code. It connects to DB2, but when it
tries to create an EXCEL table, an error message appears:

java.sql.SQLException: [Microsoft][ODBC Excel Driver] The INSERT INTO
statement contains the following unknown field name: 'F2'. Make sure
you have typed the name correctly, and try the operation again.

However I used also CREATE TABLE without success. How can I create an
EXCEL *** and create the field names in the first column? It creates
a field F1, but no further fields.

Thanks for any help
kind regards
Werner Nussbaumer

/*********************************/

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;

public class Frame1 extends JFrame {
  JPanel contentPane;
  JMenuBar jMenuBar1 = new JMenuBar();
  JMenu jMenuFile = new JMenu();
  JMenuItem jMenuFileExit = new JMenuItem();
  JMenu jMenuHelp = new JMenu();
  JMenuItem jMenuHelpAbout = new JMenuItem();
  JMenuItem connect = new JMenuItem();
  JLabel userid = new JLabel();
  JTextField userid_inp = new JTextField();
  JLabel password = new JLabel();
  JPasswordField password_inp = new JPasswordField();

  public Frame1() {
    enableEvents(AWTEvent.WINDOW_EVENT_MASK);
    try {
      jbInit();
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }

  private void jbInit() throws Exception {
    contentPane = (JPanel) this.getContentPane();
    contentPane.setLayout(null);
    this.setSize(new Dimension(400, 300));
    this.setTitle("Frame-Title");
    jMenuFile.setText("File");
    jMenuFileExit.setText("Exit");
    jMenuFileExit.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        jMenuFileExit_actionPerformed(e);
      }
    });
    jMenuHelp.setText("Help");
    jMenuHelpAbout.setText("Info");
    jMenuHelpAbout.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        jMenuHelpAbout_actionPerformed(e);
      }
    });
    connect.setText("Connect");
    connect.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(ActionEvent e) {
        connect_actionPerformed(e);
      }
    });
    userid.setDisplayedMnemonic('0');
    userid.setText("Userid:");
    userid.setBounds(new Rectangle(28, 40, 41, 17));
    userid_inp.setText("Userid");
    userid_inp.setBounds(new Rectangle(97, 36, 63, 21));
    password.setText("Password:");
    password.setBounds(new Rectangle(27, 78, 66, 17));
    password_inp.setBounds(new Rectangle(97, 74, 63, 21));
    password_inp.setText("");
    jMenuFile.add(connect);
    jMenuFile.add(jMenuFileExit);
    jMenuHelp.add(jMenuHelpAbout);
    jMenuBar1.add(jMenuFile);
    jMenuBar1.add(jMenuHelp);
    contentPane.add(userid, null);
    contentPane.add(userid_inp, null);
    contentPane.add(password_inp, null);
    contentPane.add(password, null);
    this.setJMenuBar(jMenuBar1);
  }

  public void jMenuFileExit_actionPerformed(ActionEvent e) {
    System.exit(0);
  }

  public void jMenuHelpAbout_actionPerformed(ActionEvent e) {
    Frame1_Infodialog dlg = new Frame1_Infodialog(this);
    Dimension dlgSize = dlg.getPreferredSize();
    Dimension frmSize = getSize();
    Point loc = getLocation();
    dlg.setLocation((frmSize.width - dlgSize.width) / 2 + loc.x,
(frmSize.height - dlgSize.height) / 2 + loc.y);
    dlg.setModal(true);
    dlg.show();
  }

  protected void processWindowEvent(WindowEvent e) {
    super.processWindowEvent(e);
    if (e.getID() == WindowEvent.WINDOW_CLOSING) {
      jMenuFileExit_actionPerformed(null);
    }
  }

  void connect_actionPerformed(ActionEvent e) {
     String url_db2 = "jdbc:odbc:TEST_UD";
     String url_excel = "jdbc:odbc:EXCEL";
     String sqlcmd = "";
     int i;
     String elements = "";
     Connection con_db2 = null;
     Connection con_excel = null;
     Statement stmt_db2 = null;
     Statement stmt_excel = null;
     String strSQL = null;
     try
     {
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       con_db2 = DriverManager.getConnection(url_db2,userid_inp.getText(),String.valueOf(password_inp.getPassword()));
       stmt_db2 = con_db2.createStatement();
       con_excel = DriverManager.getConnection(url_excel,"","");
       stmt_excel = con_excel.createStatement();
       ResultSet rs = stmt_db2.executeQuery("SELECT * FROM
@STATS.VAPPLGRPS WHERE APPLGRP='ZAVD' ORDER BY APPLGRP ");
       ResultSetMetaData rsmd = rs.getMetaData();
       System.out.print("Titles: ");
       elements = "";
       for(i=1; i<=rsmd.getColumnCount();i++)
       {
         elements = elements + rsmd.getColumnName(i)+" char(1)";
         if(i <rsmd.getColumnCount())
           elements = elements + ", ";
         System.out.print(rsmd.getColumnName(i)+" ");
         System.out.print(rsmd.getColumnType(i)+" ");
         System.out.print(rsmd.getColumnTypeName(i)+" ");
       }

       System.out.println("\n");
       System.out.println(elements);
       while (rs.next()) {
          System.out.print("Data : ");
          for(i=1; i<=rsmd.getColumnCount();i++)
          {
             System.out.print(rs.getString(i)+" ");
          }
          System.out.println("\n");
       }
       sqlcmd = "CREATE TABLE [Sheet1$] ("+elements+")";
       stmt_excel.executeUpdate(sqlcmd);
       System.out.println(sqlcmd);
       i = stmt_excel.executeUpdate("INSERT INTO [Sheet1$] (F1, F2,
F3, F4) values ('1000','TEST','DASD','F')");
       stmt_excel.close();
       stmt_db2.close();
     }
     catch (Exception e2)
     {
       System.out.println(e2.toString());
     }
  }
}