#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Perth, Western Australia
    Posts
    8
    Rep Power
    0

    Zope Oracle and Ms Access - should i have posted it here?


    I posted this in the general Database discussion area, but perhaps it should have been here...
    Last edited by julcla; July 4th, 2003 at 12:43 AM.
  2. #2
  3. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69

    What about them..


    If it involves Zope then sure, Zope isn't actually a database per'say. Mmmm so what about them or do you just like the words, like me hehe?

    Have fun,
    Mark.
    Last edited by netytan; July 4th, 2003 at 02:41 AM.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Perth, Western Australia
    Posts
    8
    Rep Power
    0
    Heheh, I guess that I should've quoted myself...
    here goes...

    Well - the words Zope and Oracle I like... but hey.. Access is the tool that this tool (/me) has to work with.


    Zope Ms Access and Oracle
    Hello all.

    I'm trying to select some data via a dtml method, from some Oracle tables, and have them inserted into a Ms Access table.
    (I'm not allowed to play with the real software)

    I'm attempting to do this with a ZSQL select - then an insert, something along these lines...

    Form
    <form name='form1' action='meth_step_3'>
    <tr>
    <td>Start Date</td>
    <td><input type='text' name='start_date'></input>
    <td>End Date <input type='text' name='end_date'></input>
    <input type='submit' name='Go'></input></td>
    </form>


    Oracle Select
    select
    H.mobile_no as mobile_number,
    H.DEALER_NO as dealer_code,
    SCD.ENTRY_DATE as date_added
    from
    jan.sc_detail SCD,
    jan.handset H
    where
    SCD.handset_code = H.code and
    SCD.comments = 'SERVICE ADDED' and
    SCD.ENTRY_DATE > ('<dtml-var start_date>') and
    SCD.ENTRY_DATE < ('<dtml-var end_date>') and
    (H.status = 'A' or H.status = 'B') and
    (H.DEALER_NO NOT BETWEEN '0000' and '0004')

    Dtml method
    <!--#with sql -->
    <!--#call select_all_connects -->
    <!--#call insert_all_connects -->
    <!--#/with -->

    MS Access Insert

    INSERT into mConnectList
    (
    dealer_code,
    mobile_number,
    date_added
    )
    values
    ( <!--#sqlvar dealer_code type="string" -->,
    <!--#sqlvar mobile_number type="string" -->,
    <!--#sqlvar date_added type="string" -->
    )

    I'd love to do it in a single action - select into or the like, but for the fact that a ZSQL method can only connect to one DB at a time.

    I'm not sure if there is a way of doing this with a dtml/ZSql method, but I'm hoping.
  6. #4
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69

    Connection


    Ok two quick questions, can you connect to the databases in general? i.e. have you connected to them before. I was just wondering if you need anouther Zope module to connect to Oracle and or to Access?

    if you can. here are the basic steps you need to make:

    select the desired data from a db,
    store the data from the db however its normally stored while in DTML, (i admit it, in a Zope newbie , ZTP may be better for this step)
    storethe data back in another db.

    Hope i can help,
    Mark.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Perth, Western Australia
    Posts
    8
    Rep Power
    0
    Mark,
    There's no problem connecting to the DB's in general, I can select the data, and I can insert data...
    I just can't seem to insert selected data.

    I've tried storing the data in the DTML method in between, but I didn't quite get it right.

    Like this...

    <!--#with sql -->
    <!-- in select_all_connects -->
    <INPUT type='text' name='dealer_code' value='<!--#var dealer_code -->'></input>
    <INPUT type='text' name='mobile_number' value='<!--#var mobile_number -->'></input>
    <INPUT type='text' name='date_added' value='<!--#var date_added -->'></input>
    <!--#call insert_all_connects -->
    <!-- /in -->
    <!--#/with -->
    Last edited by julcla; July 4th, 2003 at 03:23 AM.
  10. #6
  11. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69

    Python product?


    Hi again,

    I've been thinking about your problem, not having used zSQL before im not intirly sure how it works, I'm assuming you do of course. It seems that it may be easier to write a Python script or product to do this rather than trying to do it with DTML or ZTP.

    Not sure how you access zSQL (or other products) from Python scripts though..

    To interesting links, one on Zope and databases and the other on Packages:

    http://www.linuxjournal.com/article.php?sid=5849
    http://www.linuxjournal.com/article.php?sid=5687

    Hope this is of some help.

    Have fun,
    Mark.
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Perth, Western Australia
    Posts
    8
    Rep Power
    0
    Mark,

    Unfortunately, i don't know enough about Python to try to implement this. (though it is on my to-do list for future learning)

    zSQL and DTML should be enough to implement it.

    Those links have given me a little food for thought, I'll have to take a closer look at them when I return to work.

    Cheers,

    Julian.
    Last edited by julcla; July 5th, 2003 at 08:52 PM.
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Perth, Western Australia
    Posts
    8
    Rep Power
    0
    not much joy so far.

    I've tried changing the insert statement to <!--#call --> the select statement, but alas, this isn't iving me any joy.

    I tried to have the select statement connect to a link table from within access, but this managed to crash my zope process.

    ::shrugs::

    I'm feeling that I really should be adressing this in the database forum, though it doesn't really fit neatly anywhere.
  16. #9
  17. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    I know what you mean, and most people dont even have the two batabases your using, so its nearly impossible to try it ourselves without going to alot of trouble .

    It seems to me the way you loop through printing out results with dtml could be the key but thats just a thought. instead of printing could you not send the data to a zSQL method as data? This method will insert the data?

    why are you trying to transfer data anyway? just out of interest.

    Sorry i cant help .
    Mark.
  18. #10
  19. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Perth, Western Australia
    Posts
    8
    Rep Power
    0
    Bulk Databases, I guess that I must be lucky.

    I've been trying a variation on what I initially posted, by attempting to call the zSQL Select from within the insert statement, but I seem to be messing it up.
    Seems to be some syntactical errors within my SQL/DTML

    The reason for the transfer is that I've been asked to build a system which allocates $$ against sales made, then makes those $$ available for marketing purposes to the appropriate dealers. I need to update this automatically - or atleast semi-automatically - as I really don't want to be running Macros on the Access DB unless i can absolutely help it - though I'll probably be doing this until I solve this problem.

    I haven't got write access in the oracle tables - Nor do I want/need it, I'd probably break something

    The MS Access DB I have full control over, so I can add & remove tables/data as I need to.

    That's it in a nutshell (Very small nutshell - definately not a walnut)

    Thanks for your feedback so far.

    Cheers,
    Julian

IMN logo majestic logo threadwatch logo seochat tools logo