Access to API helpers through ODBC

V5 Question
1

Hi all
Sorry to summon the SQL Geniuses but @QMcKay is it possible to access the API helpers through ODBC and direct EXEC?

This file monitor strategy is now at its limits and regrettably I need to talk directly to the Samba DB :frowning: Since you have confirmed Emre did not build GraphQL functions for Accounting (Account Documents) I need to use old school force.

Mate what are your thoughts?
I am a bit stuck here as I am now faced with creating an Account Transaction and cracking open Samba to map out every single field and table…

Thanks Paul.

2

No?

Please be more specific; I don’t understand your requirement. Give me a real example of what you’re trying to accomplish.

You could use GraphQL to Broadcast Message, have a Rule for that message, and fire an Action to execute JScript.

3

Thanks Q - and happy Easter (if you celebrate)

I am using FILE MON to pass CSV files to Samba. Once Samba detects the file it strips the records and creates Account Transaction Documents puts values against Entity Accounts. On large installations the process becomes unreliable due to computing environments such as Virus Software/Drive Mapping etc.

The process will also create the entity on the fly if a value is received for an Entity but does not exist.
Sample Code Entities
api.Entity(fPrimary).Create(entityType);
api.Entity(fPrimary).Data(fHeaderField[1]).Update(fields[1]);
api.Entity(fPrimary).CreateAccount(fPrimary+"-"+fields[1]) ;

Sample Code Inscript store of variables for Account Document Rule
Data.Set(‘TRNAccountName’,fPrimary+"-"+fields[1]);
Data.Set(‘TRNDescription’,fields[2]+’ [’+fPrimary+"-"+fields[1]+’]’);
Data.Set(‘TRNAmount’,fields[3]);
Data.Set(‘APIName’,fields[4]+’ API’);

@QMcKay now having said all: that using the Message Service could work if I can pass an encoded string that can be decoded breaking into fields?

Would that be easier than trying to map out tables modified and business logic for adding Entities and Account Transactions using ODBC?

Hope that helps explain things.
PS: Entities are not passed back but Value are 2 way…

4

How does ODBC come into play here? I assume you are communicating with a foreign datasource by running T-SQL for CRUD? In both directions?

You can broadcast any string as message data. The way I do it is by sending data that is properly-formatted JSON strings. Using JSON, you can easily break the string down into parts/properties and go from there.

So I take some data, put it into an Object, then do JSON.stringify(object) to turn it into JSON string, and send it with Broadcast Message. On the receiving end, we get a message (Message Received) and decode it with JSON.parse(message) to turn it back into an Object to access all the properties of the data.

And it can go both ways of course. On the “foreign” system, the work is done in code using Javascript and/or PHP, or Node (which is also JS really), or whatever your language flavor. Using PHP or Node is great because it can “listen and respond” without any interaction, while Javascript runs client-side in a Browser (which is good for an WebApp like QMX, but not as a “service”).

1 Like
5

That is what I propose adding Customers and Account Transactions via T-SQL. That would be 1 way into Samba as not sure how I would approach outgoing from Samba to another ODBC source.

Yes I see but I would be trying to use the legacy database direct that can push HTTP calls out to a Service and that would require writing a middle tier product to act as the service go between. Eventually or by paid help I could get there but would a “Service” be just as vulnerable to poor networks, windows updates, etc as FILE MON?

6

This is doable using API helpers, in particular, using host.lib() or host.type(), you can connect to another datasource using ODBC, given you have the proper driver installed for the datasource.

Poor networks, yes. But are you not using the network already? Or is your foreign datasource on the same physical machine as the SambaPOS Database?

1 Like
7

OK that is a good point :wink:

Yes I am but at least ODBC is using TCPIP and far more reliable than file networks.

I might wipe a sample database, then add a Account transaction and inspect every table to see what changes have been made. If I can achieve this then the reliability will be 100% but I will be subject to Samba version changes.

8

I wouldn’t be too worried about DB table changes in v5, especially the [AccountTransactionXXX] tables. I just don’t think that is likely to happen.

v6 will be a complete re-write, so you can probably forget about being able to upgrade from v5, unfortunately, IMHO.

9

Yes agree Q - thanks for the tips. If I eventually get ODBC working I will drop a post back here.

I guess the resolve is FILE MON is a fantastic tool for triggering an event or static data like say a price list update. Using FILE MON for dynamic data is problematic and problems can compound dramatically with networks and multiuser workstations.

10

The following isn’t using host.X() API helpers (they did not exist when this was written); instead it is using ActiveX, and it works. If I was to attempt this again, I would definitely try host.X() instead …

1 Like