SQL Queries of a remote server from a Web Browser

[rdonnay]

I wrote a few modifications to my SqlQuery program so it can be started as a Web Server.

I then added a new function to eXpress++ name DC_WorkArea2Html().

With this new code, I can now query data on a remote server by entering a SQL statement in a web browser.

This is the HTML form:

Code: Select all

<form action="http://192.168.0.53:8001/sqlservice/query">
Enter SQL SELECT Statement:<br>
<textarea rows=4 cols=100 name="sql">
select mednum as Medallion, Make, Model, Type, Fuel from mn_mdn where Left(make,4) = 'Ford'
</textarea>
<p>
<input TYPE="submit" NAME="SqlStatement" VALUE="Run Sql!">
</form>

I added a few new features to SqlQuery to use the command-line interface to send the query statement and produce an output from the workarea as HTML, XLS or CSV.

This output is then returned to the web browser, which can be on a tablet or a phone.

If you are using SqlQuery and have an interest in this capability, please let me know.
I would like to improve on this and make it more robust.

This uses the HttpEndPoint and WebHandler features of Xbase++ 2.0 and is very simple to implement with a few lines of code.

Look at how the changes to the command line interface are used to invoke SqlQuery to run the SQL statement and produce the output:

Code: Select all

METHOD SqlService:query( cSql )

LOCAL cParams, cHtml

cParams := '/co:ADS_1 /sq:"' + cSql + '" /ee:reply.htm'

Runshell(cParams,'sqlquery.exe')

IF File('reply.htm')
  cHtml := MemoRead('reply.htm')
ELSE
  cHtml := 'Cannot run your query'
ENDIF

RETURN cHtml

[slobodan1949]

Hi Roger,

"If you are using SqlQuery and have an interest in this capability, please let me know.
I would like to improve on this and make it more robust."

In my version of eXpress ++ in SQLQUERY.DLL I don't have that change.
I would love to test that. I'm reporting as a beta tester.
Please obtain these changes and instructions if any.

[rdonnay]

Here are 3 files for you.

http://bb.donnay-software.com/ds/sqlweb.zip

Copy SQLStart.prg and SQLQuery.prg to \exp20\source\sqlquery.
Run BUILD20.BAT to rebuild Sqlquery.exe and Sqlquery.dll.

Copy _DCFunct.prg to \exp20\source\dclipx.
Run BUILD20.BAT to rebuild DClipx.dll.

SQLQuery /? will show you the command line parameters.

These are the 2 new commands:

Examples:

Code: Select all

SqlQuery.exe /ws:8001 - Start up Webserver  
  - Will invoke a new instance of SqlQuery that listens for input from a web browser on port 8001

SqlQuery.exe /co:ADS_1 /sq:"Select * from users" /ee:users.xls 
  - Connects to SQL Server defined in ADS_1 of SqlQuery.Ini and exports SQL cursor to file users.xls

You will need to know a little HTML to use the web server feature if you want to give your users a page to invoke their own queries.
See the sample HTML code in the first posting of this thread.

[hz_scotty]

Cannot compile with 1.90.355 SL1!
Need "adsdbe.ch", "sqlcmd.ch" and "odbcdbe.ch"
please

[rdonnay]

This new WebServer feature will not work with 1.9 because it uses functions only available in 2.0.

Also, it appears that you have only the foundation version of 1.9.
You can't use SqlQuery at all unless you have the professional version.

[PedroAlex]

Hello Roger.

Can i use SqlQuery to create a query to a dbf/cdx file and display the result in a browser on an android tablet?
Can you provide a sample to do this!?
many thanks.

[rdonnay]

Code: Select all

Can i use SqlQuery to create a query to a dbf/cdx file and display the result in a browser on an android tablet?

Creating a query and saving it to a DBF/CDX is the easy part with SqlQuery.

Displaying the result in a browser on a tablet requires that you have a web server, like CX/P.