REST Endpoint Definitions

I use the Samsung web browser by default. Here is a screen shot using Chrome.

Michael,

I reviewed the import scenario training guides and nowhere did it mention using a DB as the data source. I saw a post where someone was using a MySQL view as the data source.

At this point, my REST migration method makes the most sense.

It seems if you use a $filter to return a Customer it is returned as a single element array. If you put the CustomerID after the endpoint, it's returned in { } brackets.

In OBO, when returning a list of Customers in a REST response, the endpoint entity name is used to wrap the list of returned Customers in the array.. 

This broke my json2sba() function passing only a response of arrays. Here is what it should look like IMO. This allows me to use UBOUND() and reference a customer by its array index.

PRINT json{"Customer"}{1}{"CustomerID"}{"value"},"\n"

json = "{\"Customer\":" & json & "}"

Customer
   [1]
      id = 6a113b2c-d87f-e411-beca-00b56d0561c2
      rowNumber = 1
      note
         value =
      AccountRef = undef
      ApplyOverdueCharges
         value = FALSE
      AutoApplyPayments
         value = FALSE
      BAccountID
         value = 4899
      BillingAddressOverride
         value = TRUE
      BillingContactOverride
         value = TRUE
      CreatedDateTime
         value = 2023-12-31T16:00:00-08:00
      CreditLimit
         value = 0
      CurrencyID
         value = USD
      CurrencyRateType = undef
      CustomerCategory
         value = I
      CustomerClass
         value = KEY
      CustomerID
         value = ABARTENDE
      CustomerName
         value = USA Bartending School
      Email
         value = barkeep@usabartend.con
      EnableCurrencyOverride
         value = TRUE
      EnableRateOverride
         value = FALSE
      EnableWriteOffs
         value = TRUE
      EntityUsageType
         value = Default
      FOBPoint = undef
      LastModifiedDateTime
         value = 2023-12-31T16:00:00-08:00
      LeadTimedays
         value = 0
      LocationName
         value = Primary Location
      MainContact
         id = 3d705d30-05d9-4b67-877b-887b32d4c445
         rowNumber = 1
         note = undef
         Address
            id = 0f7e5000-e3b7-4ea1-afa8-1516c124a06c
            rowNumber = 1
            note = undef
            AddressLine1
               value = 201 Lower Notch Rd
            AddressLine2 = undef
            City
               value = Little Falls
            Country
               value = US
            PostalCode
               value = 07424
            State
               value = NJ
            custom = undef
         Attention
            value = Fadi Kabuk
         CompanyName
            value = USA Bartending School
         ContactID
            value = 9713
         DisplayName
            value = USA Bartending School
         Email
            value = barkeep@usabartend.con
         Fax = undef
         FirstName = undef
         FullName
            value = USA Bartending School
         JobTitle
            value = Fadi Kabuk
         LastName = undef
         MiddleName = undef
         Phone1
            value = +1 (908) 532-9522
         Phone1Type
            value = Business 1
         Phone2 = undef
         Phone2Type
            value = Business 2
         Title = undef
         WebSite = undef
         custom = undef
      MultiCurrencyStatements
         value = FALSE
      NoteID
         value = 6a113b2c-d87f-e411-beca-00b56d0561c2
      OrderPriority
         value = 10
      ParentRecord = undef
      PriceClassID = undef
      PrimaryContactID
         value = 9977
      PrintDunningLetters
         value = FALSE
      PrintInvoices
         value = FALSE
      PrintStatements
         value = FALSE
      ResidentialDelivery
         value = FALSE
      RestrictVisibilityTo
         value = PROD
      SaturdayDelivery
         value = FALSE
      SendDunningLettersbyEmail
         value = TRUE
      SendInvoicesbyEmail
         value = TRUE
      SendStatementsbyEmail
         value = FALSE
      ShippingAddressOverride = undef
      ShippingBranch = undef
      ShippingContactOverride = undef
      ShippingRule
         value = Back Order Allowed
      ShippingTerms
         value = CFR
      ShippingZoneID = undef
      ShipVia = undef
      StatementCycleID
         value = EOM
      StatementType
         value = Open Item
      Status
         value = Active
      TaxExemptionNumber = undef
      TaxRegistrationID = undef
      TaxZone = undef
      Terms
         value = 30D
      WarehouseID = undef
      WriteOffLimit
         value = 20
      custom = undef
      _links
         self = /acumaticadb/entity/Default/24.200.001/Customer/6a113b2c-d87f-e411-beca-00b56d0561c2
         files:put = /acumaticadb/entity/Default/24.200.001/files/PX.Objects.AR.CustomerMaint/BAccount/6a113b2c-d87f-e411-beca-00b56d0561c2/{filename}

I have been using the 32 bit Windows console version of ScriptBasic for my REST testing of Acumatica. This version has a COM/OLE extension module that can be used with any COM complaint 32 bit Windows application. (Excel, Access, ...) The COM extension module also works with the ScriptBasic web server.

A local ScriptBasic web server that runs as a Windows service could be setup to run pages that would take Acumatica REST data and interface with a server based COM complaint application. A 32 bit COM bridge with a REST interface.

Is there a file tree map that identifies what each file is / does?

I tried to mysqldump my Acumatica DB to a .sql  text file. It ended being  1.5 GB in size. (2500 + tables) I'm getting syntax errors on the import to MySQL on Linux. It seems there are tables with HTML as column content. The HTML has /*  comment */ which is also the .sql comment designator.

I'm thinking a backup of the DB and restore may be a better route.

After doing a bit of research there is no alternative backup / restore other than to a .sql text file. 

I'm going to try copying the Acumatica database files to Linux and see if that works.

I have another Idea that might work. For the tables with HTML code in the data, use MySQL on Windoes to do the query and insert the data on Linux in its like table.

I created a backup DB on my Windows MySQL server to see if my mysqldump .sql text file would import. This time I made sure the character set and collect types matched the Acumatica created DB. I also used the SOURCE C:\acumatica\acumaticadb.sql feature within the MySQL console utility. These are the errors that were returned. MySQL console seemed to disconnect and reconnect to the server multiple times. There were also duplicate warnings.On a positive note this method didn’t abort on the first error.

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    282
Current database: acumaticadb_backup

ERROR 1305 (42000): FUNCTION acumaticadb_backup.pp_conv2smallInt does not exist

ERROR 1231 (42000): Variable 'time_zone' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'foreign_key_checks' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'unique_checks' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'
Query OK, 0 rows affected (0.00 sec)

ERROR 1231 (42000): Variable 'collation_connection' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'sql_notes' can't be set to the value of 'NULL'

I opened the backup DB in MySQL Workbench and looked at a few master file tables and they looked fine.

I was able to get my Linux MySQL Acumatica DB built. The trick was using the correct character set and collation. Here are the errors I recieved. The first error  happened a half a dozen times during the import.

--------------
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trig_bi_SMTeamsNotification` BEFORE INSERT ON `SMTeamsNotification` FOR EACH ROW
TriggerStart: BEGIN
IF new.`NotificationID` = 0x7FFFFFff THEN SET new.`NotificationID` = (SELECT COALESCE((SELECT `AUTO_INCREMENT` FROM information_schema.tables WHERE table_schema = DATABASE() AND TABLE_NAME = 'SMTeamsNotification' LIMIT 1), 1)); END IF;
END */
--------------

ERROR 1146 (42S02) at line 74632 in file: '/home/orangepi/acumatica/acumaticadb.sql': Table 'acumaticadb.SMTeamsNotification' doesn't exist

--------------
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `aparord` AS select `pp_conv2smallInt`(0) AS `ord` union all select `pp_conv2smallInt`(1) AS `pp_conv2smallInt(1)` */
--------------

ERROR 1305 (42000) at line 85379 in file: '/home/orangepi/acumatica/acumaticadb.sql': FUNCTION acumaticadb.pp_conv2smallInt does not exist

Here is a screen shot of the customer table in Webmin MySQL management on my Orange Pi Zero.

I'm going to setup a new tenant on my Windows Acumatica and point the DB to my 🍊 Pi Zero MySQL DB. 🤞 IF this works it becomes a zero Microsoft solution.

I was able to connect with the Linux MySQL after setting up a new user. I’m not sure what Acumatica is complaining abut here.

This is my Windows MySQL info.

My guess at this point is Acumatica setup can create a R25.1 MySQL DB but attaching to an existing R25.1 doesn't recognize R25.1 as a valid release.

Another thought is I'm running the demo version of Acumatica. (2 users / watermarked forms and reports) Maybe the demo version doesn't allow to connect to external database servers?

I found this in a search.

Connecting to a remote database server with the demo version of Acumatica

Based on the information available, while Acumatica demo instances are typically installed on a single machine with a local SQL Server, it is possible to configure a remote database connection for demonstration purposes. 

However, it's essential to understand that Acumatica demo versions have certain limitations:

It doesn't look like this is a demo version restriction connecting to an external DB server. Another developer I know was able to connect to a remote DB server using R26.1 demo.

If anyone has an idea what the error dialog is telling me , it would be much appreciated.

I was able to connect with the Linux MySQL server from my Windows laptop using ODBC.

I was able to use a ODBC browser I have written in VB6 to connect to the Acumatica Customer table. Notice the error after the results were given.

I wasn’t able to connect with the Acumatica DB with ScriptBasic returning an execute error.

I was able to connect and query the MySQL demo database ClassicModels on Linux with ScriptBasic without issue.

I’m wondering if a mysqldump and restore actually works with Acumatica?

I may try creating a new tenant from scratch using the Sales (default) DB but pointing to the Linux server rather than local on my laptop.

I was able to get the Linux MySQL server working by creating a new tenant. This might be a lower cost  solution (no Microsoft) and gives you control over your data. I’m not sure if a SaaS install allows you to point to a remote DB or not. Using AWS S3 to backup your data is a plus.

My Orange Pi Zero portable Linux server is being used as the remote DB server. It is connected to my local network via 5G WIFI WLAN. It runs as fast or faster than my local MySQL DB. 

This is a screenshot of my Orange Pi Zero Webmin dashboard.

I tried ODBC from Windows to the Orange Pi Zero MySQL server and wouldn’t connect. My guess is the DB is too large for ODBC to handle. Fortunately the ScriptBasic MySQL C extension module works great. This is a test script getting the first 10 rows of the Contact table. It also displays connection info the extension module provides. I timed the execution of the script and it was about a 1/3 of a second.

' MySQL Demo Program

INCLUDE mysql.bas

dbh = mysql::RealConnect("localhost","root","orangepi","Acumatica")
mysql::query(dbh,"SELECT * FROM Contact WHERE ContactType = 'PN' LIMIT 10")


WHILE mysql::FetchHash(dbh,column)
  PRINT column{"ContactID"}," - ",column{"FullName"},"\n"
WEND

PRINTNL
PRINT "The database handle is: ",dbh,"\n"
PRINT "Affected rows by SELECT: ",mysql::AffectedRows(dbh),"\n"
PRINT "Character set name is: ",mysql::CharacterSetName(dbh),"\n"
PRINT "Last error is: ",mysql::ErrorMessage(dbh),"\n"
PRINT "Client info is: ",mysql::GetClientInfo(),"\n"
PRINT "Host info is: ",mysql::GetHostInfo(dbh),"\n"
PRINT "Proto info is: ",mysql::GetProtoInfo(dbh),"\n"
PRINT "Server info is: ",mysql::GetServerInfo(dbh),"\n"
PRINT "PING result: ",mysql::Ping(dbh),"\n"
PRINT "Thread ID: ",mysql::ThreadId(dbh),"\n"
PRINT "Status is: ",mysql::Stat(dbh),"\n"

mysql::Close(dbh)

Output

orangepi@zero-dev:~/sb/examples$ time sbc mysql_acumatica.sb
9810 - Data Source Software Corp
9811 - deVere and Partners (UK) Ltd
9819 - Advanced Concepts Of Engineering & Software Inc
9825 - Roxell
9834 - RFI Informatique
9847 - Workless
9848 - Hotel Supplies Inc
9850 - USA Bartending School
9851 - USA Bartending School
9852 - ABC Studios Inc

The database handle is: 1
Affected rows by SELECT: 10
Character set name is: utf8mb4
Last error is:
Client info is: 3.3.16
Host info is: Localhost via UNIX socket
Proto info is: 10
Server info is: 10.11.13-MariaDB-0ubuntu0.24.04.1
PING result: -1
Thread ID: 0
Status is: Uptime: 74186  Threads: 4  Questions: 17299057  Slow queries: 23  Opens: 185902  Open tables: 2000  Queries per second avg: 233.184

real    0m0.033s
user    0m0.010s
sys     0m0.018s
orangepi@zero-dev:~/sb/examples$

I tried using the Windows MySQL Workbench to access the Orange Pi Zero MySQL Acumatica DB. It closed the program loading the DB. I’m able to to view the local MySQL Acumatica DB with Workbench.

I wish Acumatica wasn’t so GUID based. 😒 It makes portability of the data difficult at the table level. 

I remember when Microsoft used Visual Basic classic to explain how C# and their other emerging languages worked. 

I'm doing the same with ScriptBasic here on the forum. 

Since my mysqldump and restore failed, I checked into how a snapshot would work as a backup.

Acumatica Docs

Because the process of taking a snapshot could result in errors, we do not recommend that you use snapshots to back up data. We recommend that you instead use database management applications to back up the database of your Acumatica ERP instance.

We recommend that you avoid restoring unsafe snapshots in a production system, because these snapshots can create corrupted data.

I'm going to offer Linux based MySQL Acumatica hosting as sub-domains on Clouds-R.Us (my AWS server domain) with S3 backup and SQL administration with phppmyadmin using a Plesk user interface.

Here is what the Customer table looks like using the mysql console utility.

https://johnspikowski.com/acumatica/Customer_Schema_MySQL.txt

I may offer storing copies of Acumatica created PDF files on the client's sub-,domain.

I will offer MS SQL migrations to MySQL as well.

This is an example what the phpmyadmin MySQL database browse and maintenance utility looks like. It’s built into Plesk.

Plesk backs up the sub-domain into a .tarz file that a copy would be stored on AWS S3. (long term cheap storage) This is done automatically with the Plesk backup scheduler.

This backup can be restored to any other site using Plesk. This eliminates Acumatica's fragile backup and limited access to your data.

The AWS S3 storage would be owned by the client with permission given to allow your sib-domain to upload backup files.

I'm going to setup a demo Clouds-R.Us sub-domain that you can connect to from your Acumatica instance. This will allow you to compare to what you are using now.

Contact me on LinkedIn and I can set up a demo.

NGINX vs Apache: Working with Both

Now that we’ve explored the advantages and disadvantages of NGINX and Apache, you could be in a better position to understand whether Apache or NGINX is best for you. But a lot of users discover they can leverage both server’s benefits by utilizing them together.

Traditional configuration for using NGINX and Apache in unison is to position NGINX ahead of Apache. This way, it serves as a reverse proxy — enabling it to accommodate every client request. Why is this important? Because it takes advantage of the quick processing speeds and NGINX’s capabilities to handle a lot of connections at the same time.

This is how the ScriptBasic server works. It uses Nginx as a front end proxy to ScriptBasic and Apache handles media and non-ScriptBasic code. Nigix also handles the translation of the ScriptBasic web server being HTTP while 'public' access to the site is HTTPS.