I have been using other people's work on this site for a number of years as I tried to learn how to code in this environment. I recently retired from a career in data collection programming involving writing applications to capture data from the shop floor and updating a remote sql server mostly using a handheld device and vbdotnet. So one of the first things I wanted to try in B4A was to do the same thing; send a sql statement to a remote SQL server and get the tabular results back. In all the tutorials and examples on the site, I have not seen anything that did that other than the Java Web service. I am not smart enough on the networking side to get around all the security protections to get that going on my PC so I was not able to get that working. It also had some negatives in that you have to define the fields coming back from your sql statement to store them in anything (as far as I could tell).
So I set out to more or less simulate what the SQL Server Service does using udp sockets.
I ended up writing a Windows Service that will receive a SQL Statement and return whatever it generates against a SQL Database. I use sockets to process the data and arrays within the B4A example program attached. It has some limitations in that it only deals with strings as far as passing the data goes, so for numeric fields, you have to convert them to strings in the sql statement (Example: SELECT CONVERT(VARCHAR(10),RECORDNO) AS RECORDNO) and convert the data back to the desired type when it returns if necessary.
But you can create any kind of sql statement you want and it will return any number of columns and rows you want. You can do inserts, deletes, updates, and selects. It will even run stored procedures. The only thing different is that the program is looking for a response in the form of a select statement at the end of the stored procedure. For example SELECT 'PROCESS WORKED' OR SELECT 'PROCESS FAILED' after whatever else you want to do inside the SP. For INSERTS, UPDATES, AND DELETES the program just returns OK if the SQL Statement did not error. You can give custom responses by wrapping them in a stored procedure and returning whatever message you desire in the form of a SQL statemient.
I put all the array processing details in a module so it can be called from multiple activities.
The VBDotnet Server Application Service Source, a B4A example program, the source/compiled socket service program folder and the scripts to create the demo tables/stored procedure in a sql database are included below. Getting a windows service installed can be tricky, so there is a readme file in there to help you get around some of the things I encountered.
I have tested this the best I can in my home where it seems to work as desired with multiple devices hitting it, but it has had no widespread testing. Once I compiled it for release it seemed pretty fast pulling 200 records in about 1 second.
I also included socket tester that I use in debugging. Be careful having the tester listen to an ip and port as the service cannot connect if the tester is listening and vice versa
I hope this is useful to someone.
It appears the source code of the server program is to large to attach below so I included only the compiled code in the debug folder below. I am happy to share the source if anyone wants it.
The Socket Test program was also too large, but is called SocketTest 3.0 and is free to download off the internet.
So I set out to more or less simulate what the SQL Server Service does using udp sockets.
I ended up writing a Windows Service that will receive a SQL Statement and return whatever it generates against a SQL Database. I use sockets to process the data and arrays within the B4A example program attached. It has some limitations in that it only deals with strings as far as passing the data goes, so for numeric fields, you have to convert them to strings in the sql statement (Example: SELECT CONVERT(VARCHAR(10),RECORDNO) AS RECORDNO) and convert the data back to the desired type when it returns if necessary.
But you can create any kind of sql statement you want and it will return any number of columns and rows you want. You can do inserts, deletes, updates, and selects. It will even run stored procedures. The only thing different is that the program is looking for a response in the form of a select statement at the end of the stored procedure. For example SELECT 'PROCESS WORKED' OR SELECT 'PROCESS FAILED' after whatever else you want to do inside the SP. For INSERTS, UPDATES, AND DELETES the program just returns OK if the SQL Statement did not error. You can give custom responses by wrapping them in a stored procedure and returning whatever message you desire in the form of a SQL statemient.
I put all the array processing details in a module so it can be called from multiple activities.
The VBDotnet Server Application Service Source, a B4A example program, the source/compiled socket service program folder and the scripts to create the demo tables/stored procedure in a sql database are included below. Getting a windows service installed can be tricky, so there is a readme file in there to help you get around some of the things I encountered.
I have tested this the best I can in my home where it seems to work as desired with multiple devices hitting it, but it has had no widespread testing. Once I compiled it for release it seemed pretty fast pulling 200 records in about 1 second.
I also included socket tester that I use in debugging. Be careful having the tester listen to an ip and port as the service cannot connect if the tester is listening and vice versa
I hope this is useful to someone.
It appears the source code of the server program is to large to attach below so I included only the compiled code in the debug folder below. I am happy to share the source if anyone wants it.
The Socket Test program was also too large, but is called SocketTest 3.0 and is free to download off the internet.