SQL Queries of a remote server from a Web Browser

SqlQuery is a new library that can be used to add much improved data browsing features to your applications. It is based on new features that are being added to eXpress++ build 267 and later - expected release August, 2019.
Post Reply
Message
Author
User avatar
rdonnay
Site Admin
Posts: 4559
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

SQL Queries of a remote server from a Web Browser

#1 Post by 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
The eXpress train is coming - and it has more cars.

User avatar
slobodan1949
Posts: 37
Joined: Mon Apr 25, 2011 8:57 am
Location: SERBIA
Contact:

Re: SQL Queries of a remote server from a Web Browser

#2 Post by 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.

User avatar
rdonnay
Site Admin
Posts: 4559
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: SQL Queries of a remote server from a Web Browser

#3 Post by 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.
The eXpress train is coming - and it has more cars.

User avatar
hz_scotty
Posts: 107
Joined: Thu Jan 28, 2010 8:20 am
Location: Wr.Neustadt / Österreich

Re: SQL Queries of a remote server from a Web Browser

#4 Post by hz_scotty »

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

User avatar
rdonnay
Site Admin
Posts: 4559
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: SQL Queries of a remote server from a Web Browser

#5 Post by 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.
The eXpress train is coming - and it has more cars.

Post Reply