• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

SQLite: What is wrong with my query?

What is wrong with my query for SQLite?

I have attached the database and the query.
q.txt
forex.txt
0
hankknight
Asked:
hankknight
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
I have not looked at the database, but the INSERT query appears to be using double quotes (") to identify columns as well as literal data. If your columns do not use reserved words or spaces, I would drop the quotes. Moreover, the literal values should be using single quotes (') if I am not mistaken.

P.S. the number of columns looked suspect to me, but I did not mention anything. However, I just had a chance to download the database and a simple SELECT from the trades table yields the following error.
Error: malformed database schema (trades) - too many columns on trades
Therefore, you may have multiple issues going on. In fact, this may be the main error. Single quotes are the typical delimiter for literals—at least for those of us who also use SQL Server. However, I tried the following simple test in a new database.
CREATE TABLE "trades2"(
    "OpenedTime" INTEGER,
    "Touched" INTEGER,
    "Size" REAL,
    "MarginLevel" REAL,
    "Pair" TEXT,
    "Balance" REAL
);

Open in new window

INSERT INTO "trades2" (
    "OpenedTime",
    "Touched",
    "Size",
    "MarginLevel",
    "Pair",
    "Balance"
) VALUES (
    "1357251341",
    "1357251341",
    "-0.67",
    "15660.100690485",
    "GBPCHF",
    "1000108.29"
);

Open in new window

Everything worked fine. When I try the same two queries in your database I get the error above even though I am not querying the trades table. It appears it corrupts the entire database, which makes sense as the error is regarding the schema.
0
 
hankknightAuthor Commented:
I don't think that the problem is because there are too many columns.

The database works, it just won't take my SQL to insert additional items.

Please test the attached database, thanks.
q2.txt
forexWorkingDB.txt
0
 
Kevin CrossChief Technology OfficerCommented:
I am running Windows 7 Professional 64-bit. Whenever I download the database and try to run a simple SELECT statement I get the error I displayed above. Therefore, I am unable to offer other suggestions unless you can provide information on the error you get. What exactly is going wrong on your system?

EDIT: did you try my smaller query? Did it work? Just trying to get a baseline for what works and what doesn't.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now