file analysis then update MySQL database

06 Nov '11, 05:48 PM
50,005 Views
No Forum Badges

hi to all. Fist sorry for my stupid question, i'm a newbye.

I've done the ajax file upload in my application (described in tutorial). Now I need to transfer the content of my uploaded file (.txt file that contains some server logs) into a mysql database wich contains a table preposed for store this information. I think i've to use the SCRIPT unit using groovy.

my question is: exist an example/tutorial for this operation? If not, exist a tutorial for using groovy for:

a) read a file 1 row at time

b) analyze the row and do some insert in mysql

c) return at point a) if !eof

thanks in advance

sam. s.

 
x 0
Follow
Answer Answer at this question and get points!
Forum Hero - Level 9

Hi!

You can try to use a Script Unit in order to accomplish your task.

There are some useful articles in our WebRatio Wiki:

  • Getting started with the Script unit
  • Getting started with Groovy
  • Script units accessing database
  • Querying with Hibernate

Let's make a simple example on how you can write the Groovy script to perform your task. Suppose you have an uploaded file and you want to save the content of this file inside one Entity of your Data Model. In particular consider that each row of the uploaded file has a value and you want to save this value in the attribute ""Value"" of your Entity (i.e. ""LogRecord"").

So your script should perform these tasks:

1) Take as input the uploaded file

2) Read the uploaded file

3) Parse each row of the file

4) Save the content of each row in the entity LogRecord

First of all you have to define the input of the script (i.e. the uploaded file):

#input RTXBLOBData file

Then let's import some packages that you will use

import java.io.*
import com.webratio.webapp.LogRecord

The last import is the class which represents the JavaBean of the entity ""LogRecord""

Now you have to recover the database session

/*Recover db session
Suppose that our database id is ""db1""
*/
def dbId = ""db1""
def session = getDBSession(dbId)

At this point you have to create a File object from the input file, read it and save its content to the entity ""LogRecord""

 try
 {
/*Create temporary file to parse from RTXBLOBData*/
def tempFile=new File(file.getName()); 
InputStream inputStream= file.openFileInputStream() 
OutputStream outStream=new FileOutputStream(tempFile);
def buf=new byte[1024]
def len
while((len=inputStream.read(buf))>0)
{
    outStream.write(buf,0,len)
}

Now you have the temporary File object that can be read row by row

 /*Read temporary file row by row until !EoF*/
tempFile.eachLine{i->
    /*Parse your file row*/
    In our case each row has only one value 
    /*Save data*/
    //Create an instance of our entity
    LogRecord log = new LogRecord()
    //Use the setter method exposed by the JavaBean to set the value
    log.setValue(i)
    //Save the data in the entity
    session.save(log)
}
//Close the InputStream and OutputStream instances in order to release any system resources associated with the streams
outStream.close()
inputStream.close()
//Perform the commit of the database session in order to physically write the changes on the database
commit(session)

  }catch(Exception e){
println(e.getMessage())}

   
x 0
Answer at this question and get points!

Related questions

502 proxy error-generate and run add html Adding and deleting from the database Add logging capability to a Groovy script (unit) Add select all to Checkable List Agregar boton de cambio de contraseña en el login AJAX events problem to external JavaScript Alimentar graficas con base de datos Atributos Condicionales heredados Attribute comparison in a selector unit Automatic file upload Ayuda Simple List Component Layout personalizado [beginner] - How to store and browse data collected with BPM app? Business object data Button to go on another page Cancellare campi e tabelle dal database Cannot add a Database in the Domain Model Cannot connect to MySQL Cannot insert an entity in the database Cannot invoke method selectSingleNode() on null object Can't generate and run database or start tomcat server! Change uplod directory and protect anauthorized access to files Como mostrar una imagen en una lista o en la componente detalles? Como recuperar todos los atributos de una relacion N:N y insertar registros? Conexion a Oracle 12g Connection refused. connect to Cloud Content module page variable won't assign. Convert data in a Calculated attribute Convert selector component result to single value Creación DB Apache Derby - Modelado BPMN Criptare dati sensibili nel database CSV Component with utf8 encoding Custom components Custom descriptors Custom Plug-in Custom Time Unit Custom Unit and AJAX / SOAP Custom Unit: Difficult example in Guide ... any suggestions? Database connection, Host and Port number Database password encrypting Database properties Database resource consumption Database Tables Creation After Remote Deploy Data flow from a page which is not visible anymore DB in Data Service Project Deploy in Cloud Amazon Beanstalk with Postgres Dynamic data type Dynamic link labelling for Custom Unit Empty record mistakenly saved Encoding Confirm Dialog Error updating metadata process EverLasting error "The TCP/IP connection to the host has failed. Connection refused" Execute truncate sql query Find Model Problemas Foriegn key relationship Generate and run locally on webratio professional edition. Get size of Multi Form with Pictures Object Array getting data from database Getting the absolute path of a RTXBLOBData Google Map Unit Groovy and Database Groovy script numer after an error Guardar Saltos de LINEA Hide a form if condition if true How can I add a credit card payment gateway or integrate paypal in my web app logic? How can i add html into a page? How can I import a view in the Domain Model as an attribute of an Entity? how can i open a pdf file using download dialog How complex can Custom Unit output be? How database or datamodel migration? How I can receive the primary key value after adding into database from OK Port. How to block empty string in Database How to calculate dates between start and end point? How to disble derby database? How to fix Network error(status 0) How to Retreive data from Multi Selection Field Array How to run the example from Custom Unit Guide? How to show certain values derived from relations in the domain model? I can't run any application with WebRatio (Table/View 'APP.POST' does not exist) Image in List field Importing JAR Libraries into Script Unit Importing jar library Insert data into database Is it possible to create a derived relationship between databases? Is there any custom unit store or repository? Do you create/sell custom units? JAR importing java.lang.NullPointerException JDBC4 Postgresql Driver JDBC connection with mysql on mac Job not start correctly Mapping database of the entity '...' is unspecified Merge the result of 2 (or more) flows [MOBILE] Can't synchronize domain model with beck end DB [MOBILE] Script doesn't work! Modify generated code / inject own HTML, Javascript code into existing generted code. More Custom Unit questions: example of Content and Operation Unit? Mostrar datos de diferentes tablas/ show dates from diferentes entities MS SQL server jdbc file multi selections field problem MySQL Driver jdbc Need help with Landmark No Apache Derby driver found No Database on Cloud account No database on my cloud account Overlays for Google Map Unit. Passing JSON Object to Model Personalizzare lo stile di un progetto Personalizzazione del login component PostgreSQL Predefined values in database Problem accessing MySQL with WebRatio 6 Problema Grafo da una lista gerarchica Problema ¨HTTP Status 404" Problema Invocation Unit Problemas con el Operations View Delete Problem saving multi selection from input Problems to connect Sql Server DB from Webratio Problems with Custom components: Can't get values from output parameters on ok flow. Problems with Database Metadata Problem with fields with accents used in attribute condition Problem with query unit! Problem with stored procedure Problem with the deletion of more than 50.000 records in a table publish web service for storing information in PostgreSQL DB following XSD Resource query a database Refresh and Synchronize DB Problems Relaciones Removing the User-Group-Module tagles Retrieve a value and add some value to it reverse engineering, problema con le relazioni di default "Run Now" on a job scheduled to run later script component Script eseguito alla User Login Script Unit e Mysql ScritpUnit and JSON data Selezione sul giorno "Events Planning" Setting Upload directory Share database connection Shuffling Script Solve "Generation Error" with Generate and Run option Standard confirm dialog replacement Statistics Component for WebRatio > 7.2.0 Status ProcessInstance and activityInstance in spanish Stored procedure component Sybase JConnect 4 JAR replace Sync DataService Project with Mobile Project Table 'freeapp43.profesion' doesn't exist Tarea de servicio no me evalua el valor Trying to Synchronize Domain Model Twitter Unit - Get Geo location of twitt Unable to execute the script java.lang.NullPointerException Unknow property on Dynaclass after generation Unspecified BPM Engine Database Update mutiple records with the same value upload file and the move it to other location Usage of the Gantt Unit User Login Timestamp Username come chiave Using own User and Group tables ¿Visor de imágenes y reproductor de video para la versión 7.2? Image viewer and video player for version 7.2? WebRatio 6.1.2: Groovy Version / Versione Groovy Webratio Add-ons no permite login Web ratio data model Webratio don't recognize JSON library WebRatio - list results of joined tables Web Ratio Mobile and Foreign Key Web Ratio Mobile - Remote data mapping problem WebRatio no imports all attributes (fields) of a table WebRatio Platform 7.2 Community Cloud Edition Where is the ACME demo data stored?! White report