Android Question RDC, SQL-Server and Stored Procedures

Hagen Schloemer said:

Hello!

I am using the rdc-function from the rdc-tutorial:

http://www.b4x.com/android/forum/th...r-rdc-connect-to-any-remote-db.31540/#content

Everthing works well, but i like to use stored procedures from the ms-sql-server. I have defined the call in the config file like this:

sql.select_sp=exec NameOfTheSP

but i receive a "Server-Error". Is it possible to use SP's with this mechanism and if yes, how i can pass parameters to the sp? My SP works well in the Query Analyzer and the return value is a recordset. I like to use Stored Procedures they are simple to implement and i can create my own resultsets with temporare tables, with the generic rdc-mechanism i cannot create "multiple step" sql-querys.

best regards
Hagen

Click to expand...

try to add schema dbo.NameOfTheSP

Thanks for your answer.

No that solves not my problem. I think is it deeper. The errors in the debug-output differs from "syntax error " (the SP runs wll in Query analyzer) to "Parameter-failure" and other. I have tested a small native sql-string (without sp) to insert data into a db-table and i receive the same errors only if i pass parameters by array to the placeholder(?). I have no plan why this is not functions, but i think i cannot resolve the Problem with the stored procedures until "normal", small sql-querys dont run. My system is a sql-2000 server with all servicepacks, installed jdbc 3.0 driver (4.0 dont run, it means that the major server version is not accepted)

client code:

cmd.Name = "select_hbw"
cmd.Parameters =Array As Object(2)
reqManager.ExecuteQuery(cmd, 0,"HBWListe")

with config:

sql.select_hbw=SELECT a.col1 ,a.col2 from tbl1 WHERE (IDDevice = ?)

creates failures

and

cmd.Name = "select_hbw"
'cmd.Parameters =Array As Object(2)
reqManager.ExecuteQuery(cmd, 0,"HBWListe")

with config

sql.select_hbw=SELECT a.col1 ,a.col2 from tbl1 WHERE (IDDevice = 2)

runs well.

Whats wrong with it?????


best regards
Hagen

SELECT a.col1 ,a.col2 from tbl1 a WHERE IDDevice = ?

if the col IDDevice is string - maybe
Array As Object("2")

why you don't install sql server express higher then 2000?

No, the syntax is ok. The same error, if the parameter is a string or a int. The database-size is more than 100GB, it is not possible to create these sizes with express editions. Anything is working well, it is an "island-application" until now there was no reason to upgrade to a new version. I think i have a general problem with sql server 2000 and jdbc, i can read all data (without parameters) but i cannot write. If there is any parameter in the query the system generates a syntax error......

Ok, i think i have solved the problem. JDBC 3 and JDBC 4 from Microsoft dosnt work. I have found a free jdbc-driver:JTDS. if the connection-sting is changed and the jar-file is installed, everything works well-parameterized querys and call stored procedures!

here is the link:

http://sourceforge.net/projects/jtds/?source=typ_redirect

best regards
Hagen