1 / 28

From MySQL to SiR via ODBC

From MySQL to SiR via ODBC. John S. Lemon. How did it start ?. Interdivisional ‘rivalry’ / ‘power complex’- “Calls are being delayed longer this year in your division and this is reflecting on the service” Had to prove it in 24 hours before weekly division managers meeting. . Summary.

read
Download Presentation

From MySQL to SiR via ODBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. FromMySQLtoSiRviaODBC John S. Lemon

  2. How did it start ? Interdivisional ‘rivalry’ / ‘power complex’- “Calls are being delayed longer this year in your division and this is reflecting on the service”Had to prove it in 24 hours before weekly division managers meeting.

  3. Summary • Initial crude solution – ODBC & SPSS • ‘hoist by own petard’ - too successful • MySQL extractions no help – bugs / features • Re-think • Need to ‘automate’ / ‘replicate’ • Use vPQL + ODBC > SiR > SPSS

  4. Problem • Needed to know poor response rates. • System provided reports and charts were no help

  5. Problem • Charts and tables geared towards ‘management’ • Gave overall views but fail to answer detailed questions • Why ?

  6. Problem • Organisation is the problem • Calls can ‘enter’ at any point not just ‘top’ • Then migrate anywhere

  7. Problem • Charts and tables geared towards ‘management’ • Didn’t show movement between subgroups • Only times were ‘Open’ and ‘Close’ • So if call closed by subgroup ‘X’ then reports allocated total time to ‘X’ even if 90% was in ‘Y’ !!!

  8. ODBC and SPSS • Use ODBC from SPSS to extract data • Create two files and manipulate within SPSS • Merge and Aggregate create data file • Still not good enough as limited ‘cross record functions’

  9. ODBC and SPSS • Use my SPSS2SiR routines ? • No – ‘one-off’ and time constrained – hadn’t used for some years so ….. ? • ‘Save As’ fixed ASCII from SPSS • Create simple data base in SiR • Allowed cross record functions

  10. ODBC and SPSS • Crude but effective solution • Unfortunately not end of problem • ‘Hoist by my own petard’ • “A petard is an explosive device used to break down doors or walls with a ‘slow burning fuse’ “ • Sometimes the burn was not as slow as the bomb placer would like.

  11. My ‘Petard’ • Met the deadline – but …. • Management requested more detail and complexity • Actually heard that the phrase was – “If it is not your ….ing division that needs a ‘kick up the a..e’ ! Then whose does.”

  12. My ‘Petard’ • Also wanted weekly updates • Using SPSS route wasn’t practical • Cumbersome • Crude • Had tailored process for one division • So couldn’t be easily extended or ‘automated’ • Back to drawing board

  13. Next Step – First thoughts • MySQL data base - so obvious answer • Use MySQL queries to generate data files • Even though updates were via SiR vPQL generated SQL code thought might work • Few more grey hairs later – no success • Dump data out and read into SiR • ‘Feature’ – no last column !!

  14. Next Step – Second thoughts • ODBC worked for MySQL to SPSS so why not use for this • Dave Doulton uses a lot so … it must be good ! • How to do it ? • Direct into SiR

  15. Next Step – Second thoughts • Appeared to work well • However • Many very short records for describing the action • Also many ‘actions’ missing • Why ? • Took some while to ‘crack’ the reason

  16. Next Step – Second thoughts LF / CR

  17. Next Step – Second thoughts • Why was this important ? • Whatever I was doing ( right or wrong ) SiR treated it as ‘end of record’ • Truncated MySQL records and so missed out the important bit !!

  18. Next Step – Second thoughts

  19. Next Step – Third thoughts • Back to the ‘drawing board’ • Have to write a vPQL programme • How ? • Obvious thing RTFM • Read The Fine Manual • You thought I was going say Flipping ! • Or something else !!

  20. Next Step – Third thoughts • Actually not a manual • ( how many are nowadays ? ) • Help files – complete with sample program • RTFHF doesn’t flow as easily off the tongue as RTFM • Didn’t quite understand all the options • Now for a bit of ‘history’

  21. The ‘Good’ old days ? PROGRAM INTEGER*4 errid conid statid rnum cnum STRING*20 cname colval STRING*80 qtext errstr CONNECT conid SERVER 'ODBC' DATABASE 'Company' USER 'me' PASSWORD 'mypwd,COMPANY,HIGH,HIGH' PREFIX 'c:\SIR2002\TEST\' ERROR errid STATEMENT statid CONNECT conid ERROR errid WRITE errid PREPARE STATEMENT statid CONNECT conid COMMAND 'SELECT * FROM EMPLOYEE' ERROR errid WRITE errid EXECUTE STATEMENT statid CONNECT conid ERROR errid WRITE errid COMPUTE cnum = COLCOUNT (conid,statid) COMPUTE rnum = ROWCOUNT (conid,statid) WRITE 'Columns returned ' cnum ' Rows returned ' rnum FOR I = 1,cnum . COMPUTE cname = COLNAME (conid,statid,i) . WRITE cname END FOR SET J (0) LOOP . COMPUTE j = j+1 . COMPUTE res = NEXTROW (conid,statid) . IF (res LE 0) EXIT LOOP . FOR I = 1,cnum . IFTHEN (COLTYPE(conid,statid,i) eq 1) . COMPUTE colval = COLVALS (conid,statid,j,i) . ELSE . COMPUTE colval = FORMAT (COLVALN (conid,statid,j,i)) . ENDIF . WRITE colval . END FOR END LOOP DELETE STATEMENT statid CONNECT conid DISCONNECT conid END PROGRAM

  22. Progress ? • Number of basic ‘crucial commands to get ODBC data • CONNECT • STATEMENT • PREPARE STATEMENT • EXECUTE STATEMENT • DELETE STATEMENT • DISCONNECT • After ‘RTFHFing’ still had problems

  23. Progress ? • Just couldn’t get a ‘connection’ or anything • Solution – E-mail David Baxter !!! • As usual came to rescue – problem solved • Still not sure why – just happy it does

  24. Progress ? • This is the help file suggestion CONNECT conid SERVER 'ODBC' DATABASE 'Company' USER 'me' PASSWORD 'mypwd,COMPANY,HIGH,HIGH' PREFIX 'c:\SIR2002\TEST\' ERROR errid • This is what I (we!!) got to work CONNECT conid SERVER 'ODBC‘ DATABASE 'Supportworks ODBC‘ USER 'train01‘ PASSWORD 'train01‘ PREFIX '‘ ERROR errid

  25. Progress ? • Can’t ‘re-read’ / ‘re-extract’ a column • Why would you want tocom045 - user identifiercom - department code • If needed then extract twice ODBC doesn’t complain !!! • Or extract to string and ‘chop up’ which is what I did for Date/Time vars

  26. Progress ? COMPUTE TEMP468= COLVALS ( conid, statid, ROWNUM, 4 ); TEMP4D = CDATE ( ( SBST ( TEMP468, 1, 10 ) ), 'DDIMMIYYYY' ); TEMP4T = CTIME ( ( SBST ( TEMP468, 12, 8 ) ), 'HHIMMISS' ); • Extract the MySQL Date/Time variable into a string ( COLVALS ) • Then ‘chop out’ the bit you want and convert to date or time • Numeric values need COLVALN COMPUTE TEMP2 = COLVALN ( conid, statid, ROWNUM, 2 );

  27. SiR wins • Typical output but presents problem • Start date and end date but no ‘intermediates’ • Generate ‘dummy’ records

  28. Hindsight • Would I use ODBC again ? • Yes – with what I know now • I would not attempt extracting data from two MySQL tables and merge them in ODBC query statement • SiR does it more ‘cleanly’ • Would be more careful in the planning.

More Related