|
JCapper Message Board JCapper 101
|
| Home |
Register
Log In |
| By | SQL Data Window | |
| JimG 9/24/2009 11:03:47 AM | Good Morning Jeff, I know I am doing something wrong. In SQL mode, I am having no issues getting my udm's to show up on the report, however when I run the sql expressions through the data window and hit execute, I am getting no data. I am sure I am leaving something out. Any thoughts? Jim UPDATE...Never mind Jeff, I figured it out from your earlier videos. ~Edited by: JimG on: 9/24/2009 at: 11:03:47 AM~
| |
| JustRalph 9/24/2009 11:40:48 AM | fill me in.......... I am stuck here too..... I am obviously not populating something let me explain it this way.... normally where I would point at a database to run the UDM against.......is where where I fail.....how do I choose what database to run it against? ~Edited by: JustRalph on: 9/24/2009 at: 11:40:48 AM~
| |
| jeff 9/24/2009 12:38:55 PM | Ralph, Read my posts in these two threads... and watch the videos: New Program Update Thread - JCapper Platinum http://www.jcapper.com/messageboard/TopicReader.asp?topic=554&forum=Downloads Getting Playlist File Data Into Access http://www.jcapper.com/messageboard/TopicReader.asp?topic=563&forum=Private Requirements for making it work: 1. Install a SQL Enabled Program Version 9/20/2009 or later. For the very first install ONLY, you MUST check the box on the installer to get a new JCapper2.mdb file during the install. Do NOT check the box on subsequent installs. From my posts you'll understand why. 2. Go into System Settings System Definitions and: a. Check the box to Display Report Summary b. Do Factor Setup for your 35 factors 3. Rebuild at least one database folder from scratch 4. Run Data Window Export(s) to the StarterHistory Table using your rebuilt databases as the source. The Data Window will populate the StarterHistory Table during the export. From there, flip the Data Window into SQL Mode. When you hit the SQL Button to run Data Window queries, there's no need to select a folder. The Data Window will query the data sitting in the StarterHistory Table... which got there from whatever folder(s) you used during the Export to StarterHistory Table routine. -jp .
| |
| JustRalph 9/24/2009 1:37:12 PM | ok....... I was rebuilt some databases I am exporting to the starterhistory table in the old pl text mode.......using the "all" button for all factors....... it shows data going out to it.......gives me results.....etc..... but when i run the query....still all zeros for data........... I even tried the default query from the help button.........all zeros..... time for a nap........ i opened the starterhistory in jcapper2.mdb and it is empty...... I am doing something wrong. Will review after some sleep. Thanks for putting up with me....... ~Edited by: JustRalph on: 9/24/2009 at: 1:37:12 PM~
| |
| jeff 9/24/2009 1:45:12 PM | First best guess... 1. Start out by executing the following sql expression: SELECT * FROM STARTERHISTORY 2. If you physically look in the table for data... using Access... open the JCapper2.mdb file in the following folder: c:\JCapper\Exe And look in the StarterHistory Table. -jp .
| |
| jeff 9/24/2009 7:08:01 PM | I've created a section devoted to running JCapper in SQL Mode on the JCapper Podcasts page: http://www.jcapper.com/podcasts/podcasts.asp?#v06 Scroll Down until you come to the section labeled: "Running JCapper In SQL Mode" Useful links and videos... all in one place. -jp . ~Edited by: jeff on: 9/24/2009 at: 7:08:01 PM~
| |
| JDL 9/24/2009 8:02:01 PM | Hi Jeff: First... I'm having the same problem Jim described above.I don't see any data in Jcapper2.MBD after running the Starter History 2nd .... Question: I'm in SQL mode. I built a database in folder for 9/18. I loaded DAT file and did a Calculate Races. The results for the races do not not show on the subsequent HTML report. Is this by design or am I doing something wrong. Regards, JDL ~Edited by: JDL on: 9/24/2009 at: 8:02:01 PM~
| |
| JustRalph 9/24/2009 10:08:03 PM | still same problem here....... the data acts as if it is being passed off to the starter history table.....but it is all zeros............when opened in access. I am getting a warning about allowing it to be changed in access....... windows 64bit here..... another thing that occurs.....if I go back and try to use the same database to export from again......it comes back with a "type mismatch" error 2nd time around.........then i have to rebuild it......... .... ~Edited by: JustRalph on: 9/24/2009 at: 10:05:58 PM~ ~Edited by: JustRalph on: 9/24/2009 at: 10:08:03 PM~
| |
| jeff 9/24/2009 11:06:08 PM | Maybe it's time to burn some cell phone minutes... 8 5 8 - 8 6 9 - 7 2 6 9 Hopefully a 10 minute phone call can straighten things out. It's only 8:00 here on the west coast. Call me. -jp . ~Edited by: jeff on: 9/24/2009 at: 11:06:08 PM~
| |
| pdavid6 9/24/2009 11:15:22 PM | If you all figure it out, please post what you find out because I seem to be getting the same results. Thanks
| |
| jeff 9/25/2009 12:41:37 AM | If you are having trouble running a Data Window Export and getting data into your StarterHistory Table - head over to the Downloads & Tech Support Area of the site - look for the Downloads Link in the upper right hand area of the browser window - and give the Downloads Link a Click. I've just put up a download link to a special version of the Data Window only. Save the download to your c:\JCapperBuild folder and run the Extractor. It will install the special Data Window and launch it. Here's what the special Data Window does: When you run a StarterHistory Table Export a log file is created. The log file contains info about the state of the Data Window, the state of your JCapper2.mdb file, and the state of the recordset object I'm using to write to the table. After installing the special Data Window, try running a StarterHistory Clear First Data Window Export using the ALL button. The full path to the log file is as follows: c:\JCapper\Exe\DataWindowExportLOG.txt Attach a copy of the log file to an email and fire it off to me. jeff @ jcapper . com (remove the blank spaces first) It'll help me to see what's going on and I can go from there. -jp . ~Edited by: jeff on: 9/25/2009 at: 12:41:37 AM~
| |
| JustRalph 9/25/2009 1:03:45 AM | Ok......got mine to work. After installing the special data window I think I figured out what I was doing I was doing the "clear first export" then running a "starterhistory append" I skipped the append ......now I get data from the table....... apparently the "append" option clears the table......?
| |
| JustRalph 9/25/2009 1:11:34 AM | since installing the new data window..... i cannot replicate my problem.......??? hmmmm......the plot thickens now only 89 more UDM's to re-write here is the log file.......which probably won't help since it works now Form1.Caption=JCapper Data Window c:\2009 Odds Cap: None Date/Time=9/25/2009 1:09:09 AM CONN_PROVIDER=Microsoft.Jet.OLEDB.4.0 App.Path=C:\JCapper\exe bolSQLMode=True bolCreatePlayHistory=False bolReadStarterHistoryTable=True RS SUPPORTS bolAddNew=False RS SUPPORTS bolUpdate=False ~Edited by: JustRalph on: 9/25/2009 at: 1:11:34 AM~
| |
| jeff 9/25/2009 2:04:27 AM | Thanks Ralph! Here's how the Clear First / Append options work - and it's the same with all Data Window Exports... CXN, Track Profile, StarterHistory... all of them: Run a CLEAR FIRST when you want to clear out the contents of the table before the export starts. Just select Clear First... you'll see text on the title bar along the very top of the Data Window telling you that an Export has been toggled "ON"... Then just hit the ALL or UDM button and run your query. The export populates the table with each record returned by the query. When the query is finished and you see results... at that point in time you should have data sitting in one of the export tables. Use the APPEND option whenever you want to keep data already sitting in an export table intact so that you can ADD to it. Example: You run a CLEAR FIRST export on folder number 1... And then run an APPEND export on folder number 2... And another APPEND export on folder number 3, etc... Until you have data in an export table for whatever folders you were trying to capture data for. I think the confusing part about exports... and it's not well documented... is that once an export is toggled "on"... you can toggle it "off" simply by going back to the menu and clicking the same type of export. I think a message box prompting the user something like "Toggle Export OFF Y/N?" might provide better/more intuitive UI... Right now, unless you know better... you'd be unaware that you were toggling the export "off" by clicking the menu option that second time... (Hope I explained that in a way that makes sense.) Bottom Line though... You're able to run Data Window Exports on a 64-bit machine. Sweet.  -jp .
| |
| jeff 9/25/2009 2:11:12 AM | JDL, In answer to your second question... I'm thinking support in the new program version to render odds and payoffs using a .DAT file might have been added the day after you did your initial download. When you get the chance just get the latest program version. But if you've spent time setting up factors and/or a Custom HTML Report... DO NOT, repeat do not check the overwrite JCapper2.mdb box on the Installer. -jp .
| |
| JustRalph 9/25/2009 2:34:29 AM | IN STANDARD MODE TOMORROWS BELMONT CARD......ALMOST 90 SECONDS TO RUN THE CARD.......IN A CALC RACES IN SQL MODE 18 SECONDS THUMBS UP......WE LEARN, WE GROW. ONWARD AND UPWARD
| |
| jeff 9/25/2009 2:35:20 AM | This falls into the area of best practices: Keep in mind that with large Data Window Exports... And StarterHistory Exports can certainly qualify as large... that the JCapper2.mdb file has the ability to grow in file size VERY quickly. It can be a very good idea to run a compact and repair database on the c:\JCapper\Exe\JCapper2.mdb file after each export. IOW... clear first/run export/compact & repair... followed by: append/run export/compact & repair... followed by: append/run export/compact & repair, etc. The idea is to keep the file size small. Also in the best practices area: Make regular back up copies of your c:\JCapper\Exe\JCapper2.mdb file... Just like you would your c:\2004\JCapper.mdb -jp .
| |
| jeff 9/25/2009 2:40:26 AM | Ralph, Once you add all those other UDMs it'll slow down some. Right now SQL Mode Calc Races is only slightly faster at my end given identical UDM sets for either mode. However, I'm exploring a few strategies on that front. -jp .
| |
| mikejlb 9/25/2009 10:46:06 PM | http://www.w3schools.com/SQl/sql_quickref.asp This site might help others like myself that are lost with making simple SQL statements. It seems that there is quit a bit of free info there.
| |
| jeff 9/26/2009 12:14:15 AM | Mike, Yes... that's a very good link. So good in fact that I linked to it in the section devoted to running JCapper in SQL Mode on the JCapper Podcasts page... "Running JCapper In SQL Mode" http://www.jcapper.com/podcasts/podcasts.asp?#v06 Many useful links and videos... all in one place. -jp . ~Edited by: jeff on: 9/26/2009 at: 12:14:15 AM~
| |
| pdavid6 9/26/2009 2:24:07 AM | Thanks for all the information and for the videos, as I finally got it. It took a while to figure it out, but once I got it, WOW!, is all I can say. Put some work into this puppy and you have a customized weapon of mass destruction for handicapping. I can't wait to play with it some more, but after staying up late, I think maybe I'll let my brain get some much needed rest.
| |
| JDL 9/26/2009 9:17:14 PM | Hi Jim or Steve: I've created two SQL UDM's. I can excute both on the SQL activated data window. They show as a SQL UDM in the UDM Wizard. However, neither shows on the SQL HTML Report. What am I over-looking. JDL
| |
| jeff 9/26/2009 9:54:17 PM | JDL, My first best guess: Q. Do the SQL UDMs use odds based factors such as BettorsToteProb, E~BettorsToteProb, E~ZScoreBettorsToteProb, OR3, Odds, RankOdds, etc? If so, they will show in the Data Window because the odds are known at the point in time that Build Database routines are run. But the odds aren't known in the Profile Marker on race day when the Calc Races button is clicked. If this is the case, remove the odds based factor constriants from the SQL Expressions... Example: change something like this: AND EBETTORSTOTEPROB >= 1.15 to this: AND EBETTORSTOTEPROB >= 0 and the UDM should show up on the report. Hint: For odds based factors key a note to yourself in the BettingInstructions Field. Something like "EB 1.15" without the quotes would work for the above example. -jp .
| |
| JDL 9/26/2009 10:43:01 PM | No odds based factors. This is a copy from the UDM Wizard. SELECT * FROM STARTERHISTORY WHERE RANKODDS > 1 AND ODDS >= 2.5 AND RANKUPR = 1 AND UPR >= 75 AND RANKF01 <= 3 AND VALF01 >= 70 AND GAPF01 >= -35 AND MLINE >= 3 AND FIELDSIZE >= 7 JDL
| |
| jeff 9/26/2009 11:23:13 PM | WHERE RANKODDS > 1 AND ODDS >= 2.5 Change those two lines to: WHERE RANKODDS >= 0 AND ODDS >= 0 And then try a Calc Races again. I suggest leaving the revised lines in the UDM Def. That way you have a "trigger" built into the UDM Def. When you look at the UDM in the Data Window the 0's should trigger you to re-evaluate rankodds and odds... allowing you to adjust how you treat plays from the UDM on race day. Also, key something like the following into the BettingInstructions field so that you'll know what to do on race day: NoFavs minOdds 2.5 -jp . ~Edited by: jeff on: 9/26/2009 at: 11:23:13 PM~
| |
| JDL 9/27/2009 3:01:22 AM | Got it. Thank you
| |
| JDL 10/2/2009 6:21:13 PM | Hi Jeff or JCAPPER members: Goal>>> I wanted to see the SQL UDM performance for 9/27. I created 3 SQL UDM's. They show on the HTML report. I wanted to see how the SQL' UDM's performed on 9/27. I downloaded results and built a database just for the 9/27 race file. From the database window I pointed the file at the 9/27 folder. The SQL results appear to be coming from my 2009 Database folder. I said to myself no problem. I went to system settings and changed the default data folder. I went back to the data window, pressed SQL and selected the SQL UDM. The results are still not for the 9/27 folder. How do I get the performance results for SQL UDM's for a specific date? Thanks for your help. I'm having trouble with the SQL side of my brain. JDL ~Edited by: JDL on: 10/2/2009 at: 6:21:13 PM~
| |
| beissner 10/2/2009 6:38:45 PM | Place JCAPPER into the playlist mode....go to your data window, select dates then go in to the SQL mode and your date will be ok....I had the same problem and finally found a way to get dates I wanted for SQL MODE.....Regards Fred
| |
| jeff 10/2/2009 10:38:17 PM | In SQL Mode it doesn't matter what folder you have the Data Window pointed to. When you run the Data Window in SQL Mode you are executing a SQL Expression directly against the StarterHistory Table. In Playlist File Mode you point the Data Window at a target folder - and then run a query against a comma delimited text file sitting on the target folder. The two are very different processes. In the current version there is only one StarterHistory Table. It resides in your c:\JCapper\Exe\JCapper2.mdb file. Q. How does data get there in the first place? A. Data Window Export. To get data from a target folder's playlist file into the StarterHistory Table, you have to first run a StarterHistory Table Data Window Export - Clear First (or Append) on the target folder. That exports data from the target playlist or target quickIndex file into the StarterHistory Table... And from there you can get at it while in SQL Mode. As of right now that's the only way to populate the table. However, I'm currently working on SQL Mode Database Builds... and once that is published... then you'll be able to populate the table via a Build Database routine... both from scratch and as an append. But for right now - the way to populate the table is through Data Window Export. One other minor point... By now hopefully everyone is using the Data Folder Manager to control the Default Data Folder. It's easier and more robust than the original interface. -jp . ~Edited by: jeff on: 10/2/2009 at: 10:38:17 PM~
| |
| jeff 10/2/2009 10:30:37 PM | Once you have data from the target folder sitting in the StarterHistory Table... just use sql to display it for a given date/time period. Example, let's say for the sake of argument you have a SQL UDM with the following complete UDM Definition: SELECT * FROM STARTERHISTORY WHERE RANKUPR = 1 AND RANKF01 <= 2 AND MLOR3 >= 1.7 ...and you want to see how the UDM performed on a specific date... just add the following... No, not to the stored UDM Definition... just to the SQL Expression that you run through the Data Window: AND YEAR = 2009 AND MONTH = 9 AND DAY(DATE) = 27 When you execute the sql expression the Data Window will pull records for the specific date 09/27/2009 only. A little explanation... YEAR is a field name in the table schema. MONTH is also a field name in the table schema. I put them there to facilitate having the Data Window show you results for specific time periods. DATE is a field name in the table schema. It contains the race date in short date format like this: mo/dy/year In the above sql expression DAY is a function. The way that I used it tells the Data Window to first evaluate records in the DATE field... and pull only those records from the table where the DAY part (dy) is equal to 27. -jp . ~Edited by: jeff on: 10/2/2009 at: 10:30:37 PM~
| |
| bettheoverlay 10/3/2009 3:54:00 PM | I think I have finally mastered my 220 times table! -
|
| Reply |
|
Copyright © 2018 JCapper Software back to the JCapper Message Board www.JCapper.com |