So, I'm hoping someone can help me with this. I'm working on my first Android app and using the net.sourceforge.jtds.jdbc.Driver to connect back to our primary database. I'm able to connect and retrieve data, which is great. Now, what I'd like to do is to call a stored procedure and send a 'datatable' (result set?) parameter. I'm getting stuck on the syntax and haven't found any examples of how to do this, exactly. Perhaps it's not feasible? Technically, for clarity's sake, on the SQL SP it's a user defined table type parameter.
My goal, in case this isn't somthing I can directly accomplish in this manner, is to take data from the local database on the device and bulk-update to a central database. I want to avoid looping through each record and writing individually, as that will be exremely inefficient.
Thanks in advance for your help!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
That being said I don't think that having a SQL Server instance publicly available on the internet is safe. So you might want to throw up a simple webapp in front of it.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK, so this gets me one step closer to where I need to be, the second step I need to take is to ensure I don't add the same record multiple times. The way I would normally accomplish this would be an insert statement with a "where keyfield not in (select from table where keyfield = keyfieldparameter). In the DB design this isn't looking at the primary key field, but at a field that should be unique within a calendar year. There's a long-story reason for this, but the options for "Insert Ignore" or "On Duplicate" that I'm finding in the jdbc documentation doesn't seem to fit my need.
Technically, if I can pass the info to a stored procedure as a datatable, that solves all of my problems. However, if addBatch is the way to go... is a "Where not exists" clause feasible?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My expertise is that this venture is not lively. Somewhat disappointing to me as we've liked having one driving force for MSSQL and Sybase like this, but in case you're having problem with SQL Server, I'd advise the use of MS's motive force. I don't know that there is any advantage to jTDS at this point.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
So, I'm hoping someone can help me with this. I'm working on my first Android app and using the net.sourceforge.jtds.jdbc.Driver to connect back to our primary database. I'm able to connect and retrieve data, which is great. Now, what I'd like to do is to call a stored procedure and send a 'datatable' (result set?) parameter. I'm getting stuck on the syntax and haven't found any examples of how to do this, exactly. Perhaps it's not feasible? Technically, for clarity's sake, on the SQL SP it's a user defined table type parameter.
My goal, in case this isn't somthing I can directly accomplish in this manner, is to take data from the local database on the device and bulk-update to a central database. I want to avoid looping through each record and writing individually, as that will be exremely inefficient.
Thanks in advance for your help!
Brenda, you can use Statement.addBatch() -- see https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html or search Google for "jdbc batch" -- to send multiple insert/update statements at once so you don't have to do repeated roundtrips.
That being said I don't think that having a SQL Server instance publicly available on the internet is safe. So you might want to throw up a simple webapp in front of it.
Thank you for the link, Alin!
OK, so this gets me one step closer to where I need to be, the second step I need to take is to ensure I don't add the same record multiple times. The way I would normally accomplish this would be an insert statement with a "where keyfield not in (select from table where keyfield = keyfieldparameter). In the DB design this isn't looking at the primary key field, but at a field that should be unique within a calendar year. There's a long-story reason for this, but the options for "Insert Ignore" or "On Duplicate" that I'm finding in the jdbc documentation doesn't seem to fit my need.
Technically, if I can pass the info to a stored procedure as a datatable, that solves all of my problems. However, if addBatch is the way to go... is a "Where not exists" clause feasible?
Well, if there's a solution to the question, I'd love to learn it, but I've been able to restructure my table's key fields to make this all work.
My expertise is that this venture is not lively. Somewhat disappointing to me as we've liked having one driving force for MSSQL and Sybase like this, but in case you're having problem with SQL Server, I'd advise the use of MS's motive force. I don't know that there is any advantage to jTDS at this point.