Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

"Error talking to database" mysql

Help
quixote9
2011-12-17
2013-04-24
  • quixote9
    quixote9
    2011-12-17

    Okay, next problem :D. I can't figure out how to import quotes. Every time I get

    Error talking to database: unexpected token: ) in statement  

    (it could also be saying "symbol IN ()" I can't tell at the small font size of the error message)

    I do have mysql installed, and it works for my local apache server. Under File > Preferences > Quote Source > Database I have the following default settings which I haven't changed:
    Driver: com.mysql.jdbc.Driver
    host: db
    port: 3306
    Username:
    Password: ****
    Database name: Shares

    I'm assuming the default setup is there so that MOV can run without further fiddling. Or should I give it my mysql username & password? If so, does it store that encrypted? Should I be giving it some other port? And if so, how would I find out which one might work on my system?

    What I'd like to use MOV for is simply to make a point-and-figure chart for hourly S&P500 quotes. 

     
  • Mark Hummel
    Mark Hummel
    2011-12-17

    It sounds as if Venice is connecting to the database without any problems.  So your settings seem to be ok. That error sounds like a bug. Were you importing from files or from the internet?

    Unfortunately, the database password is stored in plaintext. Something to fix rather urgently I think.

     
  • quixote9
    quixote9
    2011-12-18

    From the internet. I was trying to get SP500 end-of-day quotes from the default finance.yahoo.com and also from finance.google.com.

     
  • Mark Hummel
    Mark Hummel
    2011-12-18

    Thank you for that information. Can you also tell me the symbols (and/or prefixes) and the date ranges you used?

    I haven't replicated the problem yet, but our conditions are different. (ie your database is presumably empty). I'm going to
    try a few things, but in the meantime that extra information will really help.

    Thanks again,
    Mark.

     
  • quixote9
    quixote9
    2011-12-18

    The date range was the one that came up by default. A date in July to the current date. 07-17-2011 to 12-17-2011 ? The symbol I tried first was SPX, but gradually realized that Yahoo used something else. ^GSPC was what I ultimately used. On finance.google.com, I think I used SPX.

     
  • Mark Hummel
    Mark Hummel
    2011-12-18

    I downloaded a fresh copy as an entirely new user. I had to change the database settings to match (changed host, username, password and database name) but was able to import ^GSPC from Yahoo. No luck yet :(

    Have you likewise changed the database settings? You do have to supply a username, and password if that's what your database server expects. Similarly, a database of the name matching the field must be there (although the tables don't have to exist)

    Do you have mysql running locally or are you accessing a remote server? If it's local, then the host should be set to 'localhost'. If you are running MySQL locally (or have shell access to your remote instance) ensure you can connect to the database by typing 'connect $DATABASE_NAME' (where $DATABASE_NAME is the name of the database) in the MySQL command line client.

     
  • quixote9
    quixote9
    2011-12-18

    At least I'm getting different error messages. One thing: as I was changing the database values in prefs, I notice the "Use database" button was not ticked, so I ticked it. So more than the settings have changed…. Sorry about that. (Maybe that was the whole problem??) 

    I changed "Host" from the default "db" to "localhost". Username and password to my mysql admin login. And database name to "spx500pf". I created that db in mysql with (I thought) nothing in it. Both the admin user and my regular user have access to it. (I think. I'm terrible with mysql.) MOV was installed by regular user, not root. These were the messages:

    Error talking to database: table 'venice_alerts' already exists.
    Error talking to database: You have an error in your SQL server syntax. check the manual … for the right syntax to use near ')' at line 1.

    The first error message sounds like it's talking to a default db of its own??

    When I tried telling it to import end-of-day quotes for ^GSPC, it gave me that second error message again, but then reported having imported 29 quotes. from 7 Nov 2011 to 17 Dec 2011.  So, it looks like it's doing its job!  Date format is European (not a problem) which means my earlier idea that the range started at July 11 was wrong.

    Not sure what to make of the messages. Just ignore them?

     
  • quixote9
    quixote9
    2011-12-18

    There's something screwy going on. I tried expanding the range to start Jan 1 2011, it downloaded 214 quotes, but still only displays starting Nov 7. I don't see any options to change that in prefs or anywhere else. Also, the magnify and reduce buttons on the graph window are greyed out. Maybe I just have a funky install? Try reinstalling?

     
  • Mark Hummel
    Mark Hummel
    2011-12-18

    Ok, the first error message is a bug which I actually found last night.  It's not serious - the code was just trying to create tables
    twice. So you won't that message again.

    That second error message shouldn't be showing up, but it looks as if the impact is low. Double check that all the data is there by going to 'Table->Stocks->List All'. Let me know if anything else odd occurs.

    While I was testing, I got bitten myself by the whole "change db settings, forget to select 'Use Database'". So a bit of a usability issue if it trips up both new and experienced users! (Maybe a prompt reminding users to check the button if they change database settings and it's not already checked)

    Thanks for your feedback,
    Mark.

     
  • Mark Hummel
    Mark Hummel
    2011-12-18

    To get the new data showing up after import,  try flushing the quote cache by:
    1. 'File->Preferences';
    2. Select the 'Tuning' tab
    3. Press the 'Flush Cache' button.

    If that works let me know. It means that the code is not explicity flushing the quote cache after an import.
    If it doesn't work, instead of reinstalling the application, I'd try dropping the database and create it again.

     
  • quixote9
    quixote9
    2011-12-19

    I think just restarting the program must have refreshed the cache, because this morning it's displaying the whole range of numbers, even without flushing. I tried to "Sync" to get intraday (hourly) numbers for ^GSPC, but they haven't shown up. I tried flushing the cache then, but either I did something wrong or there's some other problem.

    In any case, as far as I can tell, it's working as it should. Now I can start figuring out how to use it :D.  Thanks ever so much for all your help!

     
  • quixote9
    quixote9
    2011-12-19

    Oops. I see intraday charting is not (yet) supported. /*Jumps up and down shouting "Request for feature! Request for feature!*/

     
  • Mark Hummel
    Mark Hummel
    2011-12-19

    Intraday charting is coming. I do in fact have a branch which implements a very primitive form of intraday charting. It just needs a lot of work, and there's tons of other stuff to do in the meantime ;)   (I didn't add intraday quotes myself; I don't know what people were doing with intraday quotes without charts. Maybe they used the paper trade function?)

    The cache is memory based, not disk, so naturally restarting the program would flush the cache. In fact, before the flush cache feature was added, restarting the program was how you got new data to appear. Which was annoying when you're in the  middle of something and have lots of windows open.

    As for help, you're welcome. Thank you for providing feedback; we've found lots of bugs and issues to work on.