Open Database On-Rev Examples?

[Pierre Sahores]

Hi Rick,

Should this example help ?

> function MySQL_idesk_lib  
> tparam1 
> ,tparam2,tparam3,tparam4,tparam5,tparam6,tparam7,tparam8,tparam9
> 	put revOpenDatabase  
> ("mysql","localhost","user","db_name","password") into myDatabaseID
> 	if myDatabaseID is not a number
> 	then return "There was an error connecting to the SQL database!"
> 	else
> 	
> 		if tparam1 is "unselect_grid" then
> 		
> 			put "UPDATE definitions_en_cours SET edit_timeout = '0' WHERE  
> abo_email = '" & tparam2 & "' AND editable_grid = '" & tparam3 & "'"  
> into sqlStatement
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the UPDATE statement ..."
> 			else return ""
> 	
> 		else if tparam1 is "update_tarif_webstore" then
> 		
> 			put "UPDATE abonnes SET abo_tarif_webstore = '" & tparam3 & "'  
> WHERE abo_email = '" & tparam2 & "'" into sqlStatement
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the UPDATE statement ..."
> 			else return tparam3
> 		
> 		else if tparam1 is "tarif_webstore" then
> 		
> 			put "SELECT * FROM abonnes WHERE abo_email = '" & tparam2 & "'"  
> into sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
>
> 			if dbCursorID is not a number
> 			then return "There was en error submitting the SELECT statement!"
> 			else
>
> 				if revNumberOfRecords(dbCursorID) is "1"
> 				then return word 1 to -1 of  
> revDatabaseColumnNamed(dbCursorID,"abo_tarif_webstore")
> 				else return ""
>
> 			end if
>
> 		else if tparam1 is "addgridtoedit" then
>
> 			put "INSERT INTO definitions_en_cours SET abo_email = '" &  
> tparam2 & "', editable_grid = '" & tparam3 & "', edit_timeout = '" &  
> the seconds & \
> 				"', abo_directory = '" & tparam4 & "'" into sqlStatement
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the INSERT statement ..."
> 			else return ""
> 			
> 		else if tparam1 is "display_clues" then
> 			
> 			put "SELECT * FROM definitions_en_cours WHERE abo_email = '" &  
> tparam2 & "' AND editable_grid = '" & tparam3 & "'" into sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> 			if dbCursorID is not a number
> 			then return "error while submitting the SELECT statement ..."
> 			else if revNumberOfRecords(dbCursorID) is not "0" then
> 			
> 				return revDatabaseColumnNamed(dbCursorID,"h_grid_clues") & "|" &  
> revDatabaseColumnNamed(dbCursorID,"v_grid_clues") & "|" &  
> revDatabaseColumnNamed(dbCursorID,"xml_plot")
> 			
> 			end if
> 			
> 		else if tparam1 is "accepted_clues" then
>
> 			put "SELECT * FROM definitions_en_cours WHERE abo_email = '" &  
> tparam2 & "' AND editable_grid = '" & tparam3 & "'" into sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> 			if dbCursorID is not a number
> 			then return "error while submitting the SELECT statement ..."
> 			else if revNumberOfRecords(dbCursorID) is not "0" then
>
> 				return revDatabaseColumnNamed(dbCursorID,"h_grid_clues") & "|" &  
> revDatabaseColumnNamed(dbCursorID,"v_grid_clues") & "|" & \
> 					revDatabaseColumnNamed(dbCursorID,"xml_plot") & "|" &  
> revDatabaseColumnNamed(dbCursorID,"CCL")
>
> 			end if
> 					
> 		else if tparam1 is "publish_target" then
> 		
> 			replace "|" with " " in h_grid_clues
> 			replace "|" with " " in v_grid_clues
> 				
> 			put "INSERT INTO" && tparam2 && "SET abo_email = '" & tparam3 &  
> "', editable_grid = '" & tparam4 & \
> 				"', h_grid_clues = '" & tparam5 & "', v_grid_clues = '" &  
> tparam6 & \
> 				"', xml_plot = '" & tparam7 & "', colab_copy_name = '" & tparam8  
> & "', publish_date = '" & the seconds & "'" into sqlStatement
> 				### ajouter tarif de mise à prix ###
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the INSERT statement ..."
> 			else
> 				put "DELETE FROM definitions_en_cours WHERE abo_email = '" &  
> tparam3 & "' AND editable_grid = '" & tparam4 & "'" into sqlStatement
> 				revExecuteSQL myDatabaseID,sqlStatement
> 				if the result is not a number
> 				then return "error while submitting the DELETE statement ..."
> 				else return ""
> 			end if
> 			
> 		else if tparam1 is "save_clues" then
>
> 			put "UPDATE definitions_en_cours SET h_grid_clues = '" & tparam2  
> & "', v_grid_clues = '" & tparam3 & \
> 				"' WHERE abo_email = '" & tparam4 & "' AND editable_grid = '" &  
> tparam5 & "'" into sqlStatement
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the UPDATE statement ..."
> 			else return "OK"
> 			
> 		else if tparam1 is "defs_validation_request" then
>
> 			put "UPDATE definitions_en_cours SET h_grid_clues = '" & tparam2  
> & "', v_grid_clues = '" & tparam3 & "', validation = '" & tparam4 & \
> 				"', CCL = '" & tparam5 & "', xml_plot = '" & tparam8 & "' WHERE  
> abo_email = '" & tparam6 & "' AND editable_grid = '" & tparam7 & "'"  
> into sqlStatement
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the UPDATE statement ..."
> 			else return "OK"
> 			
> 		else if tparam1 is "listgriddefstovalidate" then
> 		
> 			put "SELECT * FROM definitions_en_cours WHERE abo_email = '" &  
> tparam2 & "' AND validation = 'defs_validation_requered'" into  
> sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> 			if dbCursorID is not a number
> 			then return "error while submitting the SELECT statement ..."
> 			else if revNumberOfRecords(dbCursorID) is not "0" then
> 				repeat until revQueryIsAtEnd(dbCursorID)
> 					if revDatabaseColumnNamed(dbCursorID,"CCL") is "true"
> 					then put " >> CCL" into cw_status
> 					else put " >> WebStore" into cw_status
> 					put "<input type=hidden name=" & tparam3 && "value=" & word 1  
> to -1 of revDatabaseColumnNamed(dbCursorID,"editable_grid") & ">" && \
> 						"grille" && word 1 to -1 of  
> revDatabaseColumnNamed(dbCursorID,"editable_grid") && \
> 						cw_status & ", en attente de relecture" & cr after aaa2
> 					revMoveToNextRecord dbCursorID
> 				end repeat
> 				return aaa2
> 			else return ""
> 			
> 		else if tparam1 is "unaccepted_clues" then	
> 			
> 			put "UPDATE definitions_en_cours SET clues_reject_notes = '" &  
> tparam2 & "', edit_timeout = '" & tparam3 & \
> 				"', validation = '', CCL = '' WHERE abo_email = '" & tparam4 &  
> "' AND editable_grid = '" & tparam5 & "'" into sqlStatement
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the UPDATE statement ..."
> 			else return "OK"	
> 					
> 		else if tparam1 is "listgridtoedit" then
> 				
> 			put "SELECT * FROM definitions_en_cours WHERE abo_email = '" &  
> tparam2 & "' AND (validation IS NULL OR validation = '')" into  
> sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> 			if dbCursorID is not a number
> 			then return "error while submitting the SELECT statement ..."
> 			else if revNumberOfRecords(dbCursorID) is not "0" then
> 				repeat until revQueryIsAtEnd(dbCursorID)
> 					if revDatabaseColumnNamed(dbCursorID,"edit_timeout") <  
> -604800+the seconds then
> 						put "DELETE FROM definitions_en_cours WHERE grid_id_num = '" &  
> revDatabaseColumnNamed(dbCursorID,"grid_id_num") & "' LIMIT 1" into  
> sqlStatement
> 						revExecuteSQL myDatabaseID,sqlStatement
> 					else put "<input type=radio name=grid_editor value=" & word 1  
> to -1 of revDatabaseColumnNamed(dbCursorID,"editable_grid") & ">" && \
> 						" grille" && word 1 to -1 of  
> revDatabaseColumnNamed(dbCursorID,"editable_grid") && \
> 						" >> réservée jusqu'au :" &&  
> to_timeout_date(word 1 of  
> revDatabaseColumnNamed(dbCursorID,"edit_timeout")) & cr after aaa
> 					revMoveToNextRecord dbCursorID
> 				end repeat
> 				return aaa
> 			else return ""
> 			
> 		else if tparam1 is "listgridtoccl" then
>
> 			put "SELECT * FROM grilles_pub_en_ccl WHERE abo_email = '" &  
> tparam2 & "'" into sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> 			if dbCursorID is not a number
> 			then return "error while submitting the SELECT statement ..."
> 			else if revNumberOfRecords(dbCursorID) is not "0" then
> 				repeat until revQueryIsAtEnd(dbCursorID)
> 					put "grille" && word 1 to -1 of  
> revDatabaseColumnNamed(dbCursorID,"editable_grid") && \
> 						" >> Creative Commons le :" && to_time_date(word 1 of  
> revDatabaseColumnNamed(dbCursorID,"publish_date")) & cr after aaa
> 					revMoveToNextRecord dbCursorID
> 				end repeat
> 				return aaa
> 			else return ""
>
> 		else if tparam1 is "listgridtowebstore" then
>
> 			put "SELECT * FROM grilles_sur_webstore WHERE abo_email = '" &  
> tparam2 & "'" into sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> 			if dbCursorID is not a number
> 			then return "error while submitting the SELECT statement ..."
> 			else if revNumberOfRecords(dbCursorID) is not "0" then
> 				repeat until revQueryIsAtEnd(dbCursorID)
> 					put "grille" && word 1 to -1 of  
> revDatabaseColumnNamed(dbCursorID,"editable_grid") && \
> 						" >> WebStore le :" && to_time_date(word 1 of  
> revDatabaseColumnNamed(dbCursorID,"publish_date")) & cr after aaa
> 					revMoveToNextRecord dbCursorID
> 				end repeat
> 				return aaa
> 			else return ""
> 		
> 		else if tparam1 is "abo_notes_read" then
> 		
> 			put "SELECT * FROM abonnes WHERE abo_email = '" & tparam2 & "'"  
> into sqlStatement
> 			put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> 			if dbCursorID is not a number
> 			then return "error while submitting the SELECT statement ..."
> 			else if revNumberOfRecords(dbCursorID) is not "0"
> 			then return word 1 to -1 of  
> revDatabaseColumnNamed(dbCursorID,"abo_notes_perso")
> 			else return ""
>
> 		else if tparam1 is "abo_notes_update" then
> 		
> 			put "UPDATE abonnes SET abo_notes_perso = '" & tparam2 & "' WHERE  
> abo_email = '" & tparam3 & "'" into sqlStatement
> 			revExecuteSQL myDatabaseID,sqlStatement
> 			if the result is not a number
> 			then return "error while submitting the UPDATE statement ..."
> 			else return "OK"
>
> 		end if
> 		
> 		revCloseDatabase(myDatabaseID)
> 	end if
> end MySQL_idesk_lib

Best,


Le 27 janv. 10 à 02:32, Rick Harrison a écrit :

> Hi there,
>
> Does anyone have a good example of opening a MySQL database on On-Rev
> using a Rev stack on a local machine?

The security model of on-rev allows localhost connections only (and  
it's good !) --> your stack need to speak to an irev script --> this  
irev script is allowed to speak to the on-rev localhost MySQL db. The  
provided working "irev to mySQL" example is just  out of my http://www.woooooooords.com 
  on-rev app.
>
> I've looked at the documentation thus far, and found it to not be  
> adequate.
>
> I just keep getting the following message, which I believe is  
> telling me
> that it just didn't connect at all in the first place.
>
> "Lost connection MySQL server during query"
>
> I don't know if this a password permissions problem or path problem  
> or what.
>
> Suggestions?
>
> Thanks in advance!
>
> Rick
>
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>

--
Pierre Sahores
mobile : (33) 6 03 95 77 70

www.woooooooords.com
www.sahores-conseil.com