0

I have been struggling with my Mongo-SQL code for a while and still need your help :)

I have a problem while transferring data from an SQL server database to MongoDB. My problem is that I can't do calculations like AVERAGE() or SUM() on my data since I saved them as string in MongoDB. I thought that the numbers would be integers since I got them from my SQL server database where they are stored as integers using the code below. I see now that I use getString() when getting the values. Is that why the numbers are strings in MongoDB? How can I get them as integers? I really want to be able to manipulate them as numbers! Also, some values in SQL server are datetime, so I will need a lot of 'if' statements and different 'get' methods to get all the types right in MongoDB. Does anyone have a good solution to this problem?

StringBuilder orderstatus = new StringBuilder();
orderstatus.append("SELECT * FROM dbo.fact_orderstatus");
PreparedStatement t = connect.prepareStatement(orderstatus.toString());  

DBCollection orderstat = db.getCollection("Orderstatus");

ResultSet v = t.executeQuery();
ResultSetMetaData rsm = t.getMetaData();
int column = rsm.getColumnCount();

while (v.next()) {
BasicDBObject orderObj = new BasicDBObject();
for(int x=1; x<column +1; x++){
String namn= rsm.getColumnName(x);

String custNum = (v.getString(x));
if (custNum != null && !custNum.trim().isEmpty()
&& custNum.length() != 0)
orderObj.append(namn, custNum);

}


orderstat.insert(orderObj)
Wes Crow
  • 2,971
  • 20
  • 24
glaring
  • 59
  • 8
  • I would expect that you have schema for the SQL table, right? Can you just look at the type each column is and make appropriate gets based on that? – Asya Kamsky May 01 '13 at 19:59
  • yes of course but then i have to get the columns one by one, and i have 10 tables with at least 40 columns in each. I would really like to solve this with some kind of loop! – glaring May 03 '13 at 11:46

1 Answers1

0

You can utilize the getColumnType() method on your result set.

Since you're using SQL-server I would suggest reading this first: http://msdn.microsoft.com/en-us/library/ms378668.aspx

This SO question/answer is likely to be helpful too: Most efficient conversion of ResultSet to JSON?

Community
  • 1
  • 1
Asya Kamsky
  • 41,784
  • 5
  • 109
  • 133