Web Services - write XML data to database

Classic Servoy
1

Hi,

I am new to Servoy. I would like to create a service to save the values(from an XML data) to database table.
Given this Sample DATA


1
Laptop
100


2
Keyboard
20

I have just deployed a solution with ws_create method as a web service.

  1. How can I traverse the Object and pick the keys(e.g. ID, NAME) and values(e.g. 1,Laptop)in order to create the INSERT SQL Statement?
  2. Or is should the XML data be formatted in a different manner to make it easier?

Thanks!

-Rogel

2

One example:

function xmlDemo() {	
	var input = <xml>
					<products>
						<product>
							<id>1</id> 
							<name>Laptop</name> 
							<price>100</price> 
						</product>
						<product>
							<id>2</id> 
							<name>Keyboard</name> 
							<price>20</price> 
						</product>
						<product>
							<id>3</id> 
							<name>Mouse</name> 
							<price>7</price> 
						</product>
					</products>
				</xml>
				
	for (var i = 0; i < input.products.product.length(); i++) {
		var currentNode = input.products.product[i]
		var newRec = foundset.getRecord(foundset.newRecord()) 
		newRec.t_name = currentNode.name
		newRec.t_price = parseFloat(currentNode.price)
	}	
}
3

rogel:
…in order to create the INSERT SQL Statement?

Note that with Servoy you rarely do INSERT statements. Instead Servoy has two objects that automatically generate the SQL for you: controller and foundset. If you’re new to Servoy, a good review here:

http://www.servoymagazine.com/home/2011 … taset.html

4

david:

rogel:
…in order to create the INSERT SQL Statement?

Note that with Servoy you rarely do INSERT statements. Instead Servoy has two objects that automatically generate the SQL for you: controller and foundset. If you’re new to Servoy, a good review here:

http://www.servoymagazine.com/home/2011 … taset.html

Thanks for the tip.

I am weighing which is a better design for the logic?
a. Fill the foundset will ALL the records from the XML. Then for each record in foundset check the product rules and delete from the foundset if it fails. When all the records in the foundset is done then call saveData?
b. As you go along the records from the XML, add new record in the foundset check the product rule, if it fails delete it from the foundset else call savedata right away?

Btw, the table(e.g. Product) might have extension/lookup tables(e.g. Product_Ext). What obstacles can you see based on my choices? I am not sure if I need to create another foundset for the extension tables or is there a quicker way?

Comments will be highly appreciated.

Thanks!

5

For some ideas check out the CSV Import Automator Module on SourceForge.

6

rogel:
I am weighing which is a better design for the logic?
a. Fill the foundset will ALL the records from the XML. Then for each record in foundset check the product rules and delete from the foundset if it fails. When all the records in the foundset is done then call saveData?
b. As you go along the records from the XML, add new record in the foundset check the product rule, if it fails delete it from the foundset else call savedata right away?

Both of these options sound a little too intensive. Is there any reason that for each record in the XML, BEFORE you add it to the foundset you check the product rules and then if it passes, then you can add it to the foundset?

7

jgarfield:

rogel:
I am weighing which is a better design for the logic?
a. Fill the foundset will ALL the records from the XML. Then for each record in foundset check the product rules and delete from the foundset if it fails. When all the records in the foundset is done then call saveData?
b. As you go along the records from the XML, add new record in the foundset check the product rule, if it fails delete it from the foundset else call savedata right away?

Both of these options sound a little too intensive. Is there any reason that for each record in the XML, BEFORE you add it to the foundset you check the product rules and then if it passes, then you can add it to the foundset?

Thanks for the suggestion. Actually, there is already a product rules validation involving a form though I am still studying how I can re-use.