x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 103 subscribers
  • Views 1095 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
Rodriguez Jamaal

Anyone with Sage 100 Experience?

SUGGESTED
Posted By

I am a contract web developer and currently have a painting company asking if I could create a web app that automatically pulls and manipulate data from their database. I went to a meeting with them and realized that they use a program called “sage 100” so I researched it a bit and found out it’s an SQL server.

I have experience using mysql for my web apps but I can’t seem to figure out how to access the raw database at their company. Is the Sage 100 sql server on the same server as the app or does the app connect to another server that stores the data? Would gladly take any advice.

Thanks. 

  • FormerMember
    0 FormerMember

    Create a 32 bit ODBC system DSN that includes the company code, user ID and pssword. Use SOTAMAS90 as a template.

    This is a read only connection. If you need to write to the 100 database you will need to do that through OLE automaton using Sage's BOI.

  • 0
    SUGGESTED

    Sage 100 has two different database options. 

    The Standard and Advanced versions run on a back-end called Providex, where the ODBC access is read-only. (This is the SOTAMAS90 connection John mentions).

    If they run Sage 100 Premium, the back end is in MS SQL, but you still do not want to edit data directly through SQL commands.  The business logic is handled through the Sage 100's API (called the BOI / Business Object Interface), which is not quick to learn but must be used so you don't corrupt the data.

    I believe someone (MBA?) created a REST API as a bridge to Sage 100 BOI, and there is a program called Insynch we've used to push / pull data to and from websites.  Sage is also developing an interface through Office 365 based connectors.  I'm sure I'm forgetting to mention other options and there are more than a few threads here on Sage City about integrating Sage 100 with web functions.  You'd be well served by reading as many of them as you can find.

  • FormerMember
    0 FormerMember

    When I need to create an interactive (READ / WRITE) web interface with Sage 100 I use the ScriptBasic application server as a proxy  to Apache running on a Linux server. (main web site) Here is an example of using BOI to create a customer contact list for the ABC demo company

    ' BOI Customer Contact - Web Page

IMPORT cgi.sbi
IMPORT com.sbi

cgi::Header 200,"text/html"
cgi::FinishHeader

PRINT """
<html>
<header>
<title>ScriptBasic 100 BOI</title>
</header>
<body>
<table>
<center><h1>ABC - Customer Contacts</h1></center>
<table style="width:100%">
  <tr>
    <th>Customer Number</th>
    <th>Company Name</th>
    <th>Phone Number</th>
  </tr>
"""

oscript = COM::CREATE(:SET, "ProvideX.Script")
COM::CBN oScript, "Init", :CALL, "C:\\Sage\\Sage 100 Standard\\MAS90\\Home"
osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
COM::CBN osession, "nSetUser", :CALL, "UserID", "Password"
COM::CBN osession, "nsetcompany", :CALL, "ABC"
COM::CBN osession, "nSetDate", :CALL, "A/R", "20210722"
COM::CBN osession, "nSetModule", :CALL, "A/R"
ocust = COM::CBN(oscript, "NewObject", :SET, "AR_Customer_svc",  osession)
 
COM::CBN ocust,"nMoveFirst"
DO UNTIL COM::CBN(ocust, "nEOF", :GET)
  PRINT "  <tr>\n"
  PRINT "    <td>", COM::CBN(ocust, "sARDIVISIONNO", :GET), "-", COM::CBN(ocust, "sCUSTOMERNO", :GET), "</td>\n"
  PRINT "    <td>", COM::CBN(ocust, "sCUSTOMERNAME", :GET), "</td>\n"
  PRINT "    <td>", COM::CBN(ocust, "sTELEPHONENO", :GET), "</td>\n"
  COM::CBN ocust, "nMoveNext"
  PRINT "  </tr>\n"
LOOP

PRINT """
</table>
</body>
</html>
"""

COM::CBN ocust, "DropObject"
COM::RELEASE ocust
COM::CBN osession, "DropObject"
COM::RELEASE osession
COM::RELEASE oscript

    This webodbc example produces the same output but using ODBC. (Read Only Solution)

    ' ODBC Customer Contact - Web Page

IMPORT cgi.sbi
IMPORT odbc.sbi

dbh = odbc::RealConnect("SAGE100","","")
odbc::query(dbh,"SELECT * FROM AR_Customer")

cgi::Header 200,"text/html"
cgi::FinishHeader

PRINT """
<html>
<header>
<title>ScriptBasic 100 ODBC</title>
</header>
<body>
<table>
<center><h1>ABC - Customer Contacts</h1></center>
<table style="width:100%">
  <tr>
    <th>Customer Number</th>
    <th>Company Name</th>
    <th>Phone Number</th>
  </tr>
"""

WHILE odbc::FetchHash(dbh, column)
  PRINT "  <tr>\n"
  PRINT "    <td>", column{"ARDivisionNo"}, "-", column{"CustomerNo"}, "</td>\n"
  PRINT "    <td>", column{"CustomerName"}, "</td>\n"
  PRINT "    <td>", column{"TelephoneNo"}, "</td>\n"
  PRINT "  </tr>\n"
WEND

PRINT """
</table>
</body>
</html>
"""

odbc::Close(dbh)

*Community Hub is the new name for Sage City