Count and datatype of unnamed params

[cytrinox]

Hi,

I need to find out how many parameters a specific SQL statement (SELECT or CALL) has and the datatype of these parameters.

The SQL is generated from an external application, for example: "SELECT * FROM MYTBL WHERE ID = ? AND XYZ = ?;" or procedure calls.


I have to implement an API for this application which provides the following functions:

myapi_prepare(sql);
myapi_param_count();
myapi_type_of_param(index);
myapi_set_param_int(index, data);
myapi_set_param_string(index, data);
myapi_set_param_float(index, data);
..and so on.
myapi_execute();
+ fetch and cursor functions.


These functions must operate on a TZConnection.
At first, I thought about using ZQuery, but there is no prepare() (first introduced in 7.x) nor unnamed-param support nor datatype detection.

So the next idea was to use TZConnection.DbcConnection.PrepareStatement, but how can I get the param count and the types?


Hint: The TZConnection is used with Firebird 2.0 or MSSQL Server.

I've to implement this API in the next few weeks with zeos 6.x, so I can't wait for 7.x

Edit: Oh, and I need the possibility to set a filter like ZQuery.filter. But I think if it is impossible I can create a workaround and check the current record manually.

[mdaems]

This is not supported at all. Best you can do is substitute the parameters instead of the question marks before you add them to the query, or replace the question marks by :par1, :par2, ... And set the parameters to the right values afterwards.

So :
- myapi_prepare : accepts the sql and replaces the ? by :parX then assigns this to the SQL property of the TZQuery
- myapi_count : gives the number of replaced question marks
- myapi_set_XXX : sets the parameter value with the name 'PAR<index>'
- myapi_execute : opens (or executes) the query.

Mark

[cytrinox]

UAHH

okay, but what about myapi_get_paramtype()?

With paramCheck := true the Params are added, but the type is 0 (unknow).

[mdaems]

Sorry, but that's the responsibility of the program generating the SQL, I believe. If it can send query parameters it should know what dataype they need.
AFAIK databases are quite good in doing automatic conversions anyway. So when you pass a number as a string that may work out quite well.
And passing a string as a number won't work anyway using Set_param_int().

So I think you're almost done this way...
Just make sure you fail gracefully when there's a query error of any form. Just because you can never trust any user data...

The prepared statement interface as it is now in 7.X doesn't set parameter types depending on the preparation result. Even more : when taking mysql as an example there's even no way to determine the datatype of the parameters that is expected.

Mark

[cytrinox]

Hm okay, an Integer param can be set with asString := '2' and asInteger := 2;.

But how can I set a date value? I have tried asString := '24.12.2008' and many other formats, but none works.

[mdaems]

What's your databases normal date format? Don't know what database you're using. For Mysql I believe a good format might be 'YYYY-MM-DD hh:mi:sec'.

But can't you simply use ParamByName(const Value: string).asDate to set the parameter value?

Mark

[cytrinox]

The current API spec. requires the exact type, so if I always assume the type is string, the application allways calls set_param_asString.

But I think I can change the API so the application must call set_param(index, datatype, databuf); because you're right - the application should know the exact type.


But another question: Does ZQuery can also handle calls to stored procedures with input parameters? I know, there's an extra component (TZStoredProc), but I've only the function myapi_prepare(), so it would be nice to use only ZQuery internally.

[mdaems]

Depends on how you use them and how the database server reacts to them.
For mysql I'm pretty sure you will have trouble as there usualy multiple resultsets are returned.
Databases where you can write select from <procedurename> will do fine. (Postgres or Firebird? I'm not sure)

Mark

[cytrinox]

I'm only use Firebird or MSSQL and the Stored Procedures are only procs with input params and one result set.

[mdaems]

So : just try and Good Luck!