ERROR java.sql.SQLException: near "-": syntax error

Question

I am developing a Java tools to convert StarDict database to SQLite database for use with an Android Dictionary app. But I get the following error:

java.sql.SQLException: near "-": syntax error at
org.sqlite.DB.throwex(DB.java:288) at
org.sqlite.NativeDB.prepare(Native Method) at
org.sqlite.DB.prepare(DB.java:114) at
org.sqlite.Stmt.executeUpdate(Stmt.java:102) at
com.trivisionsc.ConvertData.createData(ConvertData.java:353) at
com.trivisionsc.ConvertData.excuteConvert(ConvertData.java:314) at
com.trivisionsc.ConvertData$1.actionPerformed(ConvertData.java:116) at
javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at
javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at
javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source) at
javax.swing.DefaultButtonModel.setPressed(Unknown Source) at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source) at
java.awt.Component.processMouseEvent(Unknown Source) at
javax.swing.JComponent.processMouseEvent(Unknown Source) at
java.awt.Component.processEvent(Unknown Source) at
java.awt.Container.processEvent(Unknown Source) at
java.awt.Component.dispatchEventImpl(Unknown Source) at
java.awt.Container.dispatchEventImpl(Unknown Source) at
java.awt.Component.dispatchEvent(Unknown Source) at
java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source) at
java.awt.LightweightDispatcher.processMouseEvent(Unknown Source) at
java.awt.LightweightDispatcher.dispatchEvent(Unknown Source) at
java.awt.Container.dispatchEventImpl(Unknown Source) at
java.awt.Window.dispatchEventImpl(Unknown Source) at
java.awt.Component.dispatchEvent(Unknown Source) at
java.awt.EventQueue.dispatchEvent(Unknown Source) at
java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) at
java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) at
java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at
java.awt.EventDispatchThread.pumpEvents(Unknown Source) at
java.awt.EventDispatchThread.pumpEvents(Unknown Source) at
java.awt.EventDispatchThread.run(Unknown Source)

And here is the source:

package com.trivisionsc;

import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.awt.Dimension;
import java.awt.Image;
import java.awt.Point;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import javax.swing.BorderFactory;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.SwingConstants;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.WindowConstants;


public class ConvertData extends JFrame

{
  // Variables declaration
  int count=0;
  private static final long serialVersionUID = 1L;
  private JLabel lblIndexFilePath;
  private JLabel lblDictFilePath;
  private JLabel lblDisplayProcess;
  private JTextField txtSourceFilePath;
  private JTextField txtDbFilePath;
  private JButton btnPerform;
  private JButton btnStop;
  private JButton btnIndexFileSelect;
  private JButton btnDictFileSelect;
  private JPanel contentPane;
  private JFileChooser fc;
  private String messageResult;
  private int start;
  private int countWord;
  private int numberWord;
  int result;
  boolean stop;

  // End of variables declaration

  public ConvertData()

  {
    super();
    createLayout();
    this.setVisible(true);

  }


  private void createLayout()

  {

    // Initialize
    lblIndexFilePath = new JLabel();
    lblDictFilePath = new JLabel();
    lblDisplayProcess= new JLabel();
    txtSourceFilePath = new JTextField();
    txtDbFilePath = new JTextField();
    btnPerform = new JButton();
    btnStop=new JButton();
    btnIndexFileSelect=new JButton();
    btnDictFileSelect=new JButton();
       contentPane = (JPanel)this.getContentPane();
      fc = new JFileChooser();
      fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
       //lblDisplayProcess
      lblDisplayProcess.setForeground(new Color(255, 0, 0));
    lblDisplayProcess.setHorizontalAlignment(SwingConstants.LEFT);

    //lblIndexFilePath
    lblDictFilePath.setHorizontalAlignment(SwingConstants.LEFT);
    lblIndexFilePath.setText(" Path store source dict");

    //lblDictFilePath
    lblDictFilePath.setHorizontalAlignment(SwingConstants.LEFT);
    lblDictFilePath.setText(" Path store database");

    // txtSourceFilePath
    txtSourceFilePath.setForeground(new Color(0, 0, 255));
    txtSourceFilePath.setToolTipText("Enter path store source dict");

    // txtDbFilePath
    txtDbFilePath.setForeground(new Color(0, 0, 255));
    txtDbFilePath.setToolTipText("Enter path store database");

    // btnPerform
    btnPerform.setText("Convert");
    btnPerform.addActionListener(new ActionListener(){
      public void actionPerformed(ActionEvent e)

      {
         //insert database
         excuteConvert();
      }

      });
    // btnStop
    btnStop.setText("Stop");
    btnStop.setEnabled(false);
    btnStop.addActionListener(new ActionListener(){
      public void actionPerformed(ActionEvent e)

      {
        stop=true;
      }

      });

    // btnIndexFileSelect
    btnIndexFileSelect.setText("Browser...");
    btnIndexFileSelect.addActionListener(new ActionListener(){
      public void actionPerformed(ActionEvent e)

      {
        int returnVal = fc.showOpenDialog(ConvertData.this);


              if (returnVal == JFileChooser.APPROVE_OPTION)
              {
                  File file = fc.getSelectedFile();
                  txtSourceFilePath.setText(file.getAbsolutePath());

              }
      }

      });
    // btnDictFileSelect
    btnDictFileSelect.setText("Browser...");
    btnDictFileSelect.addActionListener(new ActionListener(){
      public void actionPerformed(ActionEvent e)

      {
        int returnVal = fc.showOpenDialog(ConvertData.this);

              if (returnVal == JFileChooser.APPROVE_OPTION)
              {
                  File file = fc.getSelectedFile();
                  txtDbFilePath.setText(file.getAbsolutePath());
              }
      }

      });  

    // contentPane
    contentPane.setLayout(null);
    contentPane.setBorder(BorderFactory.createEtchedBorder());
    // add component for Frame
    addComponent(contentPane, lblIndexFilePath, 35,10,126,18);
    addComponent(contentPane, lblDictFilePath, 35,47,126,18);
    addComponent(contentPane, txtSourceFilePath, 160,10,203,22);
    addComponent(contentPane, btnIndexFileSelect, 365,9,80,25);
    addComponent(contentPane, txtDbFilePath, 160,45,203,22);
    addComponent(contentPane, btnDictFileSelect, 365,44,80,25);
    addComponent(contentPane, btnPerform, 160,75,90,30);
    addComponent(contentPane, btnStop, 250,75,90,30);
    addComponent(contentPane, lblDisplayProcess, 35,110,250,18);

    //set title for program
    this.setTitle("Convert data for TDict");
    // set icon for program
    ImageIcon receivedIcon = new ImageIcon("resource\\images\\tri.png");
    Image logoImg=receivedIcon.getImage();
    this.setIconImage(logoImg);
    //set position display
    this.setLocation(new Point(400, 300));
    //set size display
    this.setSize(new Dimension(500, 200));
    //set event close window
    this.setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
    //set disable resize
    this.setResizable(false);

  }


  /** Add Component Without a Layout Manager (Absolute Positioning) */

  private void addComponent(Container container,Component c,int x,int y,int width,int height)

  {
    c.setBounds(x,y,width,height);
    container.add(c);
  }
  public void excuteConvert()
  {
    //set empty for display result run
    lblDisplayProcess.setText("");  
       //name dictionary
       String dictName=null;
       // path to file
       String sourceStorePath=txtSourceFilePath.getText();
    String dbStorePath=txtDbFilePath.getText();
    // path file source dictionary don't exits
    File directorySource = new File(sourceStorePath);  

    // path file source dictionary don't exits
    if (sourceStorePath==null || sourceStorePath.equals("")||!directorySource.exists())
    {
      JOptionPane.showMessageDialog(ConvertData.this,"Path is invalid!","Error",JOptionPane.ERROR_MESSAGE,null);
      txtSourceFilePath.requestFocus();
      return;
    }

    // path file database don't exits
    File directoryDb = new File(dbStorePath);  
    if (dbStorePath==null || dbStorePath.equals("")||!directoryDb.exists())
    {
      JOptionPane.showMessageDialog(ConvertData.this,"Path is invalid!","Error",JOptionPane.ERROR_MESSAGE,null);
      txtDbFilePath.requestFocus();
      return;
    }  

    // check in source directory include: file index,dictionary and information?
    File[] files = directorySource.listFiles();  
    int check=0;
    if (files.length>0)
    {
      for (int i = 0; i < files.length; i++)  
      {  
        if(files[i].getName().endsWith(".idx"))
        {
          check++;
          dictName=files[i].getName().replaceAll(".idx", "");
        }
        if(files[i].getName().endsWith(".dict"))
          check++;
        if(files[i].getName().endsWith(".ifo"))
          check++;
      }
    }
    else
    {
      // Folder don't include any file
      JOptionPane.showMessageDialog(ConvertData.this,"Source directory have to include files: .idx,.dict and .ifo","Error",JOptionPane.ERROR_MESSAGE,null);
      txtSourceFilePath.requestFocus();
      return;
    }
    if(check<3)
    {
      // Folder don't include full file
      JOptionPane.showMessageDialog(ConvertData.this,"Source directory have to include files: .idx,.dict and .ifo","Error",JOptionPane.ERROR_MESSAGE,null);
      txtSourceFilePath.requestFocus();
      return;
    }

        // path file information
       String infoFilePath=sourceStorePath+"\\"+dictName+".ifo";
    // path file index
       String indexFilePath=sourceStorePath+"\\"+dictName+".idx";
       //path file content
       String dataFilePath=sourceStorePath+"\\"+dictName+".dict";
       // read file information to get max size file index
       int idxFileSize=0;
       try
       {
         // open stream read file
         FileInputStream fstream=new FileInputStream(infoFilePath);
         DataInputStream in = new DataInputStream(fstream);
         BufferedReader  input = new BufferedReader (new InputStreamReader(in));
         String strLine;
         int k=0;
           //Read File Line By Line
           while ((strLine = input.readLine()) != null)   {
             if (strLine.contains("idxfilesize="))
             {
               String valueStr=strLine.replaceAll("idxfilesize=", "");
               idxFileSize = Integer.parseInt(valueStr.trim());
               k++;
                }
             if (strLine.contains("wordcount="))
             {
               String valueStr=strLine.replaceAll("wordcount=", "");
               numberWord = Integer.parseInt(valueStr.trim());
               k++;
                }
             if(k>1) break;

           }
           //Close the input stream
           input.close();
       }
       catch(Exception e)
       {
         JOptionPane.showMessageDialog(ConvertData.this,"System error!Please try again","Error",JOptionPane.ERROR_MESSAGE,null);
      setActive();
         return;  
       }
       // perform convert
       this.createData(dbStorePath,indexFilePath,dataFilePath,idxFileSize,dictName);

   }

  @SuppressWarnings("deprecation")
  public void createData(String dbStorePath,String indexFilePath,String dataFilePath,int idxFileSize,String dictName){
     // Open connect database SQLite
     try
     {

        File checkFile = new File(dbStorePath+"\\"+dictName+".db");
         if (checkFile.exists())
         {

           result =JOptionPane.showConfirmDialog((ConvertData.this), "Are you sure to overwrite?", "File existed!", JOptionPane.YES_NO_OPTION);
           if (result==1)
           {
             setActive();
             return;  
           }
           else
           {
             boolean isDeleteSuccess=checkFile.delete();
             if (!isDeleteSuccess)
               {
                 JOptionPane.showMessageDialog(ConvertData.this,"Delete file unsuccessfully. Please restart program. ","Error",JOptionPane.ERROR_MESSAGE,null);
                 setActive();
                 return;  
               }


           }
         }

        //Disable controls before insert database
      setUnActive();
        Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbStorePath+"\\"+dictName+".db");
        final Statement st = conn.createStatement();
        // Create table and index
        st.executeUpdate("CREATE TABLE IF NOT EXISTS "+dictName+"(Word TEXT NOT NULL PRIMARY KEY, Content TEXT,Id INTEGER NOT NULL);");
        st.executeUpdate("CREATE INDEX wrod_idx ON "+dictName+"(Id);");
        // Read file
        FileInputStream fileIndex=new FileInputStream(indexFilePath);
        final BufferedInputStream input = new BufferedInputStream(fileIndex);
          // File content
          FileInputStream fileDict=new FileInputStream(dataFilePath);
          final BufferedInputStream dictInput = new BufferedInputStream(fileDict);
          // Array store data read form File index
          final byte[] data = new byte[idxFileSize];
          input.read(data);

          final String dictionName=dictName;
          countWord=0;
      // Read data
          new Thread(new Runnable() {
             public void run() {
                for (int i=0; i < data.length; i++)
                {
                  if (data[i] == '\0')
                  {
                      try
                      {
                        //Read data form index
                            int lengthOfData = i - start;
                            byte[] tmp = new byte[lengthOfData];
                            System.arraycopy(data, start, tmp, 0, lengthOfData);
                            int length = byteArrayToInt(data, i+5);
                            //Word
                            String word = new String(tmp, "UTF-8");
                            //Read content from file data
                            byte[] value = new byte[length];
                            dictInput.read(value);
                            //Content
                            String content = new String(value,"UTF-8");
                            i += 9;
                            start = i;
                             // insert into database
                             st.executeUpdate("INSERT INTO "+dictionName+"(Word,Content,Id) VALUES ('"+Utility.encodeContent(word)+"','"+Utility.encodeContent(content)+"',"+countWord+")");
                             messageResult="Executing .... Insert element "+countWord+"/"+numberWord;
                             countWord++;
                             if (stop)
                             {
                               setActive();
                               messageResult="Cancel";
                          i=data.length;
                          st.close();
                           }

                      }
                      catch(Exception e)
                      {
                        ;
                       }

                      SwingUtilities.invokeLater(
                         new Runnable()
                         {
                           public void run()
                           {
                             lblDisplayProcess.setText(messageResult);
                           }
                         }
                      );
                      try
                      {
                       Thread.sleep(1);

                      } catch(Exception e)
                      {
                        JOptionPane.showMessageDialog(ConvertData.this,"System error!Please try again","Error",JOptionPane.ERROR_MESSAGE,null);
                      setActive();
                       return;
                      }
                  }

                }
                try
                {
                  if(!stop&&countWord==numberWord)
                  {
                    setActive();
                      lblDisplayProcess.setText("Completed! ");
                      st.close();
                  }
                }
                catch(Exception e)
                  {
                  JOptionPane.showMessageDialog(ConvertData.this,"System error!Please try again","Error",JOptionPane.ERROR_MESSAGE,null);
                  setActive();
                   return;
                }
                ///
              }
          }).start();


     }catch(Exception e)
     {
       JOptionPane.showMessageDialog(ConvertData.this,"System error!Please try again","Error",JOptionPane.ERROR_MESSAGE,null);
       setActive();
         return;
     }

    }

   //method to calculate value of byteArray
    public static int byteArrayToInt(byte[] b, int offset) {
        int value = 0;
        for (int i = 0; i < 4; i++) {
            int shift = (4 - 1 - i) * 8;
            value += (b[i + offset] & 0x000000FF) << shift;
        }
        return value;
    }
    public void updateControl() throws Throwable
    {
               for (int i=0; i<100; i++) {
               System.out.println("thread "
                  +Thread.currentThread().getName()+" step "+i);
               Thread.sleep(500);
            }

    }
    public final  void setActive()
    {
       //set for control enable
        txtDbFilePath.setEnabled(true);
       txtSourceFilePath.setEnabled(true);
       btnIndexFileSelect.setEnabled(true);
       btnDictFileSelect.setEnabled(true);
       btnPerform.setEnabled(true);
       btnStop.setEnabled(false);
    }
    public final void setUnActive()
    {
      //set for control disable
        txtDbFilePath.setEnabled(false);
       txtSourceFilePath.setEnabled(false);
       btnIndexFileSelect.setEnabled(false);
       btnDictFileSelect.setEnabled(false);
       btnPerform.setEnabled(false);
       btnStop.setEnabled(true);
    }
  public static void main(String[] args)

  {
    //set Look and Feel
    JFrame.setDefaultLookAndFeelDecorated(true);
    JDialog.setDefaultLookAndFeelDecorated(true);
    try
    {
      UIManager.setLookAndFeel("com.sun.java.swing.plaf.windows.WindowsLookAndFeel");
    }
    catch (Exception ex)
    {
      System.out.println("Failed loading L&F: ");
      System.out.println(ex);
    }
    //perform run program convert data for TDict
    new ConvertData();

    };

}

Any ideas? Thanks a lot.

Solution 1

The problem is on the line that says:

st.executeUpdate("CREATE TABLE IF NOT EXISTS "+dictName+"(Word TEXT NOT NULL PRIMARY KEY, Content TEXT,Id INTEGER NOT NULL);");

At a guess, dictName contains a - character, which is not a legal part of a bare literal in SQL. Sanitize, or put "double quote" characters around it. They'll need backslash quoting to be part of a Java string. But sanitizing (e.g., by stripping all non-alphabetic characters) is much better.

Solution 2

This is line 353 of your code:

st.executeUpdate("CREATE TABLE IF NOT EXISTS " +dictName+"(Word TEXT NOT NULL PRIMARY KEY, Content TEXT,Id INTEGER NOT NULL);");

The SQL has a syntax error in it. I'm going to guess that dictName has spaces and a - in it. You need to fix that.

Comments

[Niamh Doyle]

Superb! It's exactly the problem! The dictName has a "-", and the converting process works like a charm when this funny dash is removed. You rock! Thank you very much.

[Donal Fellows]

@user998032: Glad we could help. I also note that your code is using string manipulation to build the INSERT statements; that's unwise because it's both easy to forget to do it right and slower because it forces the SQL code to be recompiled each time. Use a prepared statement, please. (Won't work for the CREATE TABLE though; table names are one of the things that can't be parameterized over.)

[Niamh Doyle]

Could you please be specific? Actually I have re-written this piece of code as follows but the converting process appears to be so slow: // Create table and index st.executeUpdate("CREATE TABLE IF NOT EXISTS "+dictName+"(id INTEGER NOT NULL PRIMARY KEY, word TEXT NOT NULL, content TEXT);"); st.executeUpdate("CREATE INDEX id_idx ON "+dictName+"(id);"); st.executeUpdate("CREATE INDEX word_idx ON "+dictName+"(word);");

[Donal Fellows]

The CREATE TABLE and CREATE INDEX are absolutely fine (now that the table name is sane). It's the INSERT INTO from later in the function that's an issue.

[Niamh Doyle]

Yes, you're absolutely right. The converting process is extremely slow. How should I recode the INSERT INTO: // insert into database st.executeUpdate("INSERT INTO "+dictionName+"(word,content,id) VALUES ('"+Utility.encodeContent(word)+"','"+Utility.encodeContent(‌​content)+"',"+countW‌​ord+")"); messageResult="Executing .... Insert Element "+countWord+"/"+numberWord; countWord++;

[Donal Fellows]

@user998032: That would be better asked as another question (though it has been answered before, many times). Basically, the SQL part will be "INSERT INTO "+dictionName+"(word,content,id) VALUES (?,?,?)" and then there will be some steps to efficiently fill in those placeholders each time round the loop. Prepared statements (plus putting the whole thing in a transaction) will help a lot with the speed.