[MAS-tips] uppercase symbol problem with a database

[Jim C. <jt...@di...>]

By George you're right!

I changed the initial query to select LOWER(Scode) and feed mas with =
lower case stock symbols, then it started complaining about embedded =
errors in the SQL for the other query (I had forgotten to change =
"selecter" back to "select").  Once I changed this to use correct =
syntax, it started working :-)

Now I just have to learn about the rest of the process!

Thanks for all your help,

Matt.

- --=20
Invalid thought detected. Close all mental processes and restart=20
body.

- ----- Original Message -----=20
...

> > Hi Jim,
> >=20
> > Thanks for that.
> >=20
> > I've now had a chance to run it, and below is the output.  It still =
=3D
> > looks to me like it's not getting to the point of running the data =
=3D
> > selection query.
> >=20
> > I've also attached my mas_dbrc file.  In the daily data query, the =
Date =3D
> > + 0 construct is used to put the Date column into a numerical (e.g. =
=3D
> > 20030519 ) format, rather than a string format ("2003-05-19").
>=20
> Hi Matt.
>=20
> First, I worked with Pedro on his problem for a while on Friday night
> and it appears that part of the problem was caused by mas not handling
> upper-case symbols correctly.  (This is a bug that needs fixing, at
> some point.)  Most likely that is also the problem you are having.
> One way to fix this is to convert all the symbols in your database to
> lower case.  If you can't do this, perhaps there's a SQL function to
> convert the case.  I think what you need is a to-lower conversion for =
the
> symbol query.  I believe the <uppersymbol> construct does work, for =
the
> stock data query, but if not, you could try a to-upper there.
>=20
> For the date, mas wants an integer, so if you're converting it in the
> query into an integer from a date, I believe that will work.
>=20
> When working with Pedro, I produced another version of mas that prints =
more
> information, which I'll send to you in a separate email.  You can run =
it to
> verify that the symbol case is causing the problem - it should output
> something like: Symbol 'xyz' is neither a valid stock symbol, nor
> a valid derivative symbol.  Also, if there's another problem in your =
setup,
> this more verbose version could help with that, too.
>=20
> Don't forget to run it with the -debug option.
>=20
>=20
> Jim
>=20
> >=20
> > Thanks,
> >=20
> > Matt.
> >=20
> >=20
> > Output :-
> >=20
> > bin/mas -debug -p
> > Connected.
> > Disconnected.
> > Connected.
> > Executing database query 'select Scode from Stocks order by Scode;'
> > Database statement has results.
> > Number of columns in result: 1
> > Database query returned 10 rows.
> > Symbol list:
> > AMP
> > ANZ
> > BHP
> > CBA
> > LHG
> > MIM
> > NAB
> > NCP
> > RIO
> > WBC
> > Welcome to the Market Analysis Server console! (Hit <Enter> to =
continue)
> >=20
> > main_menu starting.
> > process_main_menu starting.
> > initialize_current_tradable starting.
> > initialize_current_tradable - tradable list handler has 10 elements.
> > executing 'current_tradable :=3D3D tradable_list_handler.item =3D
> > (current_period_type)
> > starting TRADABLE_DISPENSER.valid_period_type.
> > starting TRADABLE_LIST_HANDLER.period_types.
> > period_types (daily list) - searching for symbol 'amp'
> > starting TRADABLE_LIST.search_by_symbol.
> > search_by_symbol found symbol amp.
> > returning from TRADABLE_LIST.search_by_symbol.
> > Executing 't :=3D3D daily_market_list.item'
> > starting TRADABLE_LIST.item.
> > Calling update_and_load_data.
> > TRADABLE_LIST.update_and_load_data calling load_data.
> > TRADABLE_LIST.load_data calling setup_input_medium.
> > starting DB_TRADABLE_LIST.setup_input_medium.
> > db.fatal_error: False
> > Connected to database - retrieving daily data.
> > starting MAS_DB_SERVICES.daily_data_for_symbol.
> > Executing database query 'select Scode from Stocks order by Scode;'
> > Database statement has results.
> > Number of columns in result: 1
> > Database query returned 10 rows.
> > returning from MAS_DB_SERVICES.daily_data_for_symbol.
> > Retrieval failed.
> > Failed to connect to database.
> > Error occurred while processing : starting =3D
> > DB_TRADABLE_LIST.close_input_medium.
> > returning from DB_TRADABLE_LIST.close_input_medium.
> > returning from DB_TRADABLE_LIST.setup_input_medium.
> > load_data: fatal error.
> > returning from TRADABLE_LIST.item.
> > period_types - retrieval of tradable failed.
> > Failure status - not creating period-type list.
> > returning from TRADABLE_LIST_HANDLER.period_types.
> > returning from TRADABLE_DISPENSER.valid_period_type.
> > TRADABLE_DISPENSER.item: period type was NOT valid.
> > Call to tradable_list_handler.item failed, exiting ...
> > Error occurred retrieving non-intraday period types for amp
> > Aborting the server.
> > Disconnected.
> >=20
> >=20
> >=20
> > --=3D20
> > "And, of course, you have the commercials where savvy businesspeople =
Get =3D
> > Ahead by using their MacIntosh computers to create the ultimate =
American =3D
> > business product: a really sharp-looking report."
> > -- Dave Barry
> >=20
> > ----- Original Message -----=3D20
...
> >=20
> >=20
> > > Pedro and Matt.
> > >=3D20
> > > Here is the debugging executable and indicators_persist and
> > > generators_persist files compatible with it.  Make sure you run it =
=3D
> > with the
> > > -debug option (and -p, of course) and save the output and send it =
to =3D
> > me.
> > >=3D20
> > > Also, please send me the mas_dbrc files you're using.  If I get =
time I =3D
> > may
> > > try running mas with your config. files to see if I can duplicate =
the
> > > problem.  If not, they still could be helpful in analyzing the =3D
> > debugging
> > > output.
> > >=3D20
> > >=3D20
> > > Jim
> > >=3D20
> > >=20
> > ------=3D_NextPart_000_08DC_01C31E19.B7137FF0
> > Content-Type: application/octet-stream;
> > name=3D"mas_dbrc"
> > Content-Transfer-Encoding: 7bit
> > Content-Disposition: attachment;
> > filename=3D"mas_dbrc"
> >=20
> > # MAS database configuration file
> > # Format: "label_string<tab>setting"
> > # ODBC data source name for MAS database
> > data_source_name test
> > # ID of user for MAS database
> > user id user
> > # Password for above user
> > password pass
> > # Query for stock symbols
> > #stock symbol query select symbol from stock_information order by =
symbol
> > # ('\' at the end of the line continues the string on the next line)
> > stock symbol query select Scode from Stocks order by Scode;
> > #stock symbol query select Code from FloatInfo where LENGTH(Code < =
5) order
>  by Code;
> > #watch_list where category =3D 'all' \
> > #order by symbol
> > # Query for derivatives symbols
> > #derivative symbol query select code from Stocks order by Scode
> > # \
> > #order by symbol
> >=20
> > # Start and end date specifications for queries.  If eod_end_date is =
not
> > # specified, it defaults to "now".  This also applies to =
intraday_end_date.
> > eod_start_date now - 3 years
> > #eod_end_date now
> > #intraday_start_date now - 5 days
> >=20
> > # Query for stock splits
> > # Note: query result fields must be: date, symbol, split-value; and =
the
> > # results must be sorted by date, ascending
> > #stock split query select Date, Code, (Before / After) from =
Announcements=20
> order by Date
> > # Query for stock name
> > # <symbol> indicates where to plug in the actual stock symbol and =
must
> > # occur somewhere in the query string.  (Use <uppersymbol> if the =
symbol
> > # needs to be converted to upper-case for the query.)
> > #stock name query select name from stock_information where symbol =
=3D <symb
> ol>
> > # Query for derivative instrument name
> > # <symbol> indicates where to plug in the actual stock symbol and =
must
> > # occur somewhere in the query string.
> > #derivative name query select name from derivative_information where =
\
> > #symbol =3D <symbol>
> > daily stock data command selecter Date + 0, Open, High, Low, Last, =
Volum
> e from \
> > FloatInfo where Code =3D '<uppersymbol>' and Date >=3D =
<eod_start_date> order by=20
> Date;
> > #intraday stock data command select date, time, open_price, =
high_price, \
> > #low_price, close_price, volume from intraday_stock_data where =
symbol =3D \
> > #'<symbol>' and date >=3D <intraday_start_date>
> > #daily derivative data command select date, open_price, high_price, =
\
> > #low_price, close_price, volume, open_interest from \
> > #daily_derivative_instrument_data where symbol =3D '<symbol>' and \
> > #date >=3D <eod_start_date>
> > #intraday derivative data command select date, time, open_price, \
> > #high_price, low_price, close_price, volume, open_interest from \
> > #intraday_derivative_instrument_data where symbol =3D '<symbol>' and =
date >=3D \
> > #<intraday_start_date>
> >=20
> > ------=3D_NextPart_000_08DC_01C31E19.B7137FF0--
> >=20
>