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:
World leaders in all things RevSoft
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 "&amp;" in this_val swap '>' with "&gt;" in this_val swap '<' with "&lt;" 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