Import Articles and customer oders from webhop via SQL

TeraxTerax Member Posts: 5
Hello,

We have a web shop and would like to import all article data and customer orders to navision. Is it possible to insert all the data via a sql query in Java through jdbc - mssql connector or must I use the dataport function from navision? Can I run sql queries to alter navisons data or must I use cfront to accomplish this?

The next question, we have: Is it better to install the mssql database, because we can use standard sql etc. or should we go with navision native database? The ODBC native database Driver supports only read only access, isn't it? We have 3 clients connected via Windows Terminal Services to the Server.

Thanks in advance for any help :)

Regards, Tobias

Comments

  • TeraxTerax Member Posts: 5
    please please, nobody has an answer for us? :)
  • kinekine Member Posts: 12,562
    Direct changes into DB are dangerous if you are using MS SQL - no validity checks, triggers etc... you must know what to fill... (in MS SQL you can insert lowercase into Code field, but NA will crash if you opent thist record etc...) If you are using C/Front, there are all checks, but still no triggers... if you use dataports or Application server, you can use all triggers etc... 8)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Christian_BuehlChristian_Buehl Member Posts: 145
    See http://www.mibuso.com/forum/viewtopic.php?t=2407
    for an example how to read and write from navision to the database (testet with navision 2.01) via ADODB

    The ODBC driver supports reads and writes, but as mentioned be aware what you write!

    Third way ist to write a dataport for import/export your items.

    ODBC has some limitations:
    - No triggers will work when you write
    - No access to flow fields
    - The ODBC-Driver options must be set correctly (identifiers to a-z,A-Z,0-9 should work) to avoid problems with the database you use.

    I think the best way is to write an ASCII or XML file from your Web shop and import this into Navision. Here you have full control about what you're doing. Disatvantage is that you can't do this as an online functionality (I mean you need to do anything in Navision to start the import).
  • TeraxTerax Member Posts: 5
    ...
    I think the best way is to write an ASCII or XML file from your Web shop and import this into Navision. Here you have full control about what you're doing. Disatvantage is that you can't do this as an online functionality (I mean you need to do anything in Navision to start the import).

    Ok, thanks for your answers. :)

    Is it possible to schedule a job, that do the transfer every 15 minutes from a xml file? Or must I start the job manually from navision?
  • Christian_BuehlChristian_Buehl Member Posts: 145
    It's not possible to schedule anything in the database directly (without beeing logged on).
    You need a running client (e.g on the server machine) where you can do something with the on time trigger. Search the mibuso forum or Joe Fermenas pages www.navisioner.com for some examples.
    (Sorry just have seen that the page is currently not available, maybe he's up again later)


    Another but very hard way could be to do something with C/Front (I forgot to mention this before). Here you can create external access with this C-Library.
    You can find the description for ODBC and C/Front on your Product CD.
  • ewieserewieser Member Posts: 3
    I hope, I can give you an idea, how you can to that. You can handle all you need in Navision, don't write directly to the Navision database.

    You want to Import data from an external prozess like a Webshop:

    Programm a report (No dataport, because Apps Server cannot handel dataports), witch imports imformations from a textfile (like XML File).

    Programm a Codeunit, witch start the Report. (must be single instance, because you need timer functionality)
    Install a Application Server and modify codeunit 1 in trigger 99.

    An example for timer functionality is the Email notification in the Service module.
  • DenSterDenSter Member Posts: 8,304
    If you are using XML documents to send information, you should look into Commerce Gateway, or have your NSC create something similar. You can have a NAS instance monitor MSMQ, or even a connector to BTS (pre-2004!!).
Sign In or Register to comment.