{{tag>category:"OpenInsight 32-Bit" author:"Steve Botes" author:"[url=http://www.sprezzatura.com]The Sprezzatura Group[/url]" author:"Bob Carten"}}
[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]
==== Translate MySQL SQL statements for use in OpenInsight (OpenInsight 32-Bit) ====
=== At 08 OCT 2008 04:50:45PM Steve Botes wrote: ===
We have a PHP/MySQL program on a web server that we would like to interface with a OpenInsight database. The PHP program is a 3rd party program and I don't want to rewrite all of the database calls.
I put a hook in the PHP code to take the SQL statement and issue a XML request to OICGI. Now I need to translate the SQL to an RBasic statement. The results would be returned as XML.
Is there a way to execute MySQL SQL SELECT statements into RBasic statements?
I started to write my own sql statement conversion routine but some of the SQL statements can get complex. Are there any libraries I can utilize to convert the SQL to RBasic?
----
=== At 08 OCT 2008 09:13PM [url=http://www.sprezzatura.com]The Sprezzatura Group[/url] wrote: ===
[url=http://www.revelation.com/__85256DC100249E21.nsf/0/46701B29E238A3CF852565370049A95D?Open]Might this help?[/url]
[url=http://www.sprezzatura.com]The Sprezzatura Group[/url]
[i]World leaders in all things RevSoft[/i]
[img]http://www.sprezzatura.com/zz.gif[/img]
----
=== At 08 OCT 2008 11:22PM Bob Carten wrote: ===
Steve,
A few years ago I experimented with the example code that Sprezz referenced above. I cooked up a function which executes SQL, returns an XML representation which .Net was happy to use.
Note, this is all read only, most likely to succeed with relatively simple SQL statements.
pre.code {
background-color: #E5E5E5;
border: 1px solid #000000;
width: 640px;
padding: 5px;
font-family: courier, verdana, arial, serif;
margin: 0px 10px auto;
}
* An adaptation of the Code in the Whitepaper
Function SQL_DELIMITED(script, Format)
/*
* Purpose: Given an SQL Query, return the result as delimted text
*
* Parameters:
*
* Script (in) =SQL Select Statement, e.g. SELECT TITLE, AUTHOR FROM BOOKS
* Format (in) =a keyword from the list "TAB HTML XML SCHEMA"
*
* Return Value:
*
*
*
* 02-17-02 mtr Created
* 12-26-03 rjc Delimited version
* 01-06-05 rjc An Epiphany! add Schema
*/
$insert SQLStates_Hdr
$insert FSErrors_Hdr
$insert FSErrors_100
$insert SysErrors_1000
Declare Function Utility
equ TRUE$ TO 1
equ FALSE$ TO 0
equ OTHERWISE$ TO 1
equ CrLf$ to \0D0A\
Equ tab$ To \09\
equ valid_formats$ to 'TAB HTML XML SCHEMA'
declare subroutine Rev_SQL, SQL_PostQuery, Set_Status, Set_FSError
Declare Function Get_Property, Set_Property, Send_Message, Inet_Queryparam,Create_Xml_Xsd_Schema
If assigned(script) Else script='
If assigned(format) Else format='
err='
result='
Swap char(13): char(10) with ' ' in script
if len(script) else
err := ' Empty Script'
end
If Len(format) Else format=TAB'
locate format in valid_formats$ using ' ' setting fmt_pos else
err := ' Invalid Format'
end
if len(err) then
return err
end
gosub extract_table_info
begin case
case format=SCHEMA'
gosub get_schema
case otherwise$
gosub get_data
end case
return result
******
get_Schema:
* Hack -- let it write, pull it back
Dos_Path=.\'
DosFile=SQL_DELIM_':time():len(script)
call Create_Xml_Xsd_Schema( table, colNames, Dos_Path, DosFile)
osRead result from dosfile else result='
osDelete dosfile
return
get_Data:
RowList="
Rev_SQL(Script, Status)
if Status<SQLCODE$> < 0 then
Detail=Status<DETAIL$>
convert @vm to @fm in Detail
Set_Status(TRUE$, "SQL": Status<SQLCODE$>, Detail)
end else
if @List.Active then
EOF =FALSE$
loop
readnext @id,whichval using 0 else
EOF=TRUE$
if @file_error<FSCODE$> # FS_READNEXT_DONE$ then
Set_FSError()
EOF=TRUE$
end
End
until EOF
* rjc 11-14-03 add @MV
@MV=whichval
RowExists=TRUE$
if @reduction_done then
reado @record from cursor 0, @id else
if @file_error<FSCODE$>=FS_REC_DNE$ then
RowExists=FALSE$
end else
Set_Status(TRUE$, SYS_SQL_READ_ERR$, @id)
EOF=TRUE$
end
end
end
until EOF
if RowExists then
transfer @record To this_row
Begin Case
Case format=EDT'
* Demote
Convert \FFFEFDFCFAF0\ To \FEFDFCFAF0F9\ In this_row
if len(result) Then result := @fm
result := this_row
Case format=TAB'
if len(result) Then result := crlf$
Convert @fm To tab$ In this_row
result := this_Row
Case format=HTML'
If Len(result) Then
result := crlf$
End else
result := '<table>' : crlf$
end
Swap @fm With '</td><td>' In this_row
result := '<tr><td>':this_row:'</td></tr>'
Case format=XML'
If len(result) Else
result=<XmlDS>"
end
result := crlf$
xml_row=<' : table :'>'
pos=1
cnt=0
For i=1 To colcnt
cnt +=1
this_val=trim(this_rowpos, @fm)
swap '&' with "&" in this_val
swap '>' with ">" in this_val
swap '<' with "<" in this_val
pos=col2()+1
tag=colNames<cnt>
If Len(tag) Else
tag=column_' : i
end
xml_row := '<':tag:'>' : this_val : '</' : tag : '>'
Next
xml_row := '</':table:'>'
result := xml_row
Case 1='
if Len(result) Then result := @rm
result := this_row
End case
end
repeat
Begin case
Case format=HTML'
result := '</table>'
Case format=XML'
result := crlf$ : '</XmlDS>'
End case
SQL_PostQuery(0, TRUE$, Status)
end
if Status<sqlcode$> then
Set_Status(FALSE$, "SQL": Status<SQLCODE$>, Status<DETAIL$>)
end else
* SQL0 status - return rows affected.
Set_Status(FALSE$, "SQL": Status<SQLCODE$>, Status<ROWS_EFF$>)
end
end
Return
Extract_table_info:
*---
* Get the table name
*---
table = ''
columns='
p1=indexc(script, '.', 1)
col_headers='
colnames='
if p1 then
tscript=script
convert @lower.case to @upper.case in tscript
swap 'FROM ' WITH @FM IN tscript
swap crlf$ with ' ' in script
table=tscript<2>1, ' '
colinfo=trim(tscript<1>)
swap 'SELECT ' with '' in colinfo
*---
* select just the columns then typed in
*---
convert ',' to @vm in colinfo
col='
loop
remove colname from colinfo at col setting flag
Colname=TRIM(Colname)
table=colname1,'.'
colname=field(colname,'.',2)
header=xlate('DICT.':TABLE, Colname, 3, 'X')
convert @vm to ' ' in header
if len(col_headers) then col_headers:=@vm:header else col_headers=header
colnames<-1>=colname
while flag
repeat
end else
*---
* no dots, so assume 1 table
*---
tscript=script
convert @lower.case to @upper.case in tscript
swap 'FROM ' WITH @FM IN tscript
swap crlf$ with ' ' in script
table=tscript<2>1, ' '
colinfo=trim(tscript<1>)
colnames='
swap 'SELECT ' with '' in colinfo
if colinfo=*' then
*---
* extract the dictionary
*---
Clearselect
Call rlist('SELECT DICT.':TABLE:' BY TYPE BY FMC BY @ID WITH @ID NOT STARTING "%" WITH TYPE EQ "F"', 5, '', '', '')
done='
loop
readnext id else done=1
until done
header=xlate('DICT.':TABLE, id, 3, 'X')
col_headers<1, -1>=header
colnames<-1>=id
repeat
end else
*---
* select just the columns then typed in
*---
convert ',' to @vm in colinfo
col='
loop
remove colname from colinfo at col setting flag
Colname=TRIM(Colname)
header=xlate('DICT.':TABLE, Colname, 3, 'X')
convert @vm to ' ' in header
if len(col_headers) then col_headers:=@vm:header else col_headers=header
colnames<-1>=colname
while flag
Repeat
end
colcnt=Count(colnames, @fm) + ( colnames # '' )
End
Return
* A wrapper to let you call it from the internet
Function Inet_Sql(request)
$Insert Inet_Equates
$Insert Logical
equ crlf$ to \0D0A\
Declare function SQL_Delimited, Inet_Queryparam, Inet_Msg
If assigned(request) else request='
cmd=Inet_Queryparam(request, 'CMD')
format=Inet_Queryparam(request, 'FORMAT', 'HTML')
recordset=SQL_DELIMITED(cmd,format)
if format _eqc 'XML' then
result=Content-type: text/xml' : crlf$
result := 'Content-length: ' : len(recordset) : crlf$
result := crlf$
result := recordset
end else
transfer recordset to result
end
return result
[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=B79199FD3D262800852574DC007282C5|View this thread on the Works forum...]]