R
RoyF
I am having a problem with OracleDataAdapter.FillSchema. I get the following error messag
"Syntax Error: found 'FROM' inside an expression.
The OracleDataAdapter parser is apparently choking on a sub-select, within the SQL statement.
So for example, suppose OracleDataAdapter.SelectCommand.CommandText was set to the following SQ
SELEC
EMPLOYEE_NAME
(SELECT COUNT(*) FROM EMPLOYEE E2 WHERE E2.EMPLOYEE_NAME <= E.EMPLOYEE_NAME) SORT_ORDE
FRO
EMPLOYEE
WHER
EMPLOYEE_ID = 1234
(This SQL returns the employee name and the number of other employees having a name less than or equal to the given name. This is valid Oracle SQL. I believe valid starting with Oracle 9i. I guess the current .NET framework only supports Oracle 8.
With the SQL as above, the following code will fai
procedure TSelectProxy.Fill_ds
begi
OraDataAdapter.FillSchema(ds, SchemaType.Mapped, FQueryName)
OraDataAdapter.Fill(ds.Tables[0])
end
ds is the DataSet. The first line fails. With the error message that I stated above. It just will not accept the sub-select.
If I re-write the SQL so that the sub-select is replaced with an stored Oracle function (my own defined function), then I am guessing that the error will go away. I do not like having to create functions like that. I end up with all these database functions that are only used once. Seems pointless. That is why they put the sub-select in there, to avoid having to create all those functions.
If I remove the FillSchema, and do something like the followin
procedure TSelectProxy.Fill_ds
va
dt: DataTable
begi
dt := DataTable.Create(FQueryName)
OraDataAdapter.Fill(dt)
ds.Tables.Add(dt)
end
This is OK. Will not fail. However, I end up missing schema information. In particular, it will not embed the size information for strings. I need that sizing information because later in the code I use it to create Oracle parameters.
"Syntax Error: found 'FROM' inside an expression.
The OracleDataAdapter parser is apparently choking on a sub-select, within the SQL statement.
So for example, suppose OracleDataAdapter.SelectCommand.CommandText was set to the following SQ
SELEC
EMPLOYEE_NAME
(SELECT COUNT(*) FROM EMPLOYEE E2 WHERE E2.EMPLOYEE_NAME <= E.EMPLOYEE_NAME) SORT_ORDE
FRO
EMPLOYEE
WHER
EMPLOYEE_ID = 1234
(This SQL returns the employee name and the number of other employees having a name less than or equal to the given name. This is valid Oracle SQL. I believe valid starting with Oracle 9i. I guess the current .NET framework only supports Oracle 8.
With the SQL as above, the following code will fai
procedure TSelectProxy.Fill_ds
begi
OraDataAdapter.FillSchema(ds, SchemaType.Mapped, FQueryName)
OraDataAdapter.Fill(ds.Tables[0])
end
ds is the DataSet. The first line fails. With the error message that I stated above. It just will not accept the sub-select.
If I re-write the SQL so that the sub-select is replaced with an stored Oracle function (my own defined function), then I am guessing that the error will go away. I do not like having to create functions like that. I end up with all these database functions that are only used once. Seems pointless. That is why they put the sub-select in there, to avoid having to create all those functions.
If I remove the FillSchema, and do something like the followin
procedure TSelectProxy.Fill_ds
va
dt: DataTable
begi
dt := DataTable.Create(FQueryName)
OraDataAdapter.Fill(dt)
ds.Tables.Add(dt)
end
This is OK. Will not fail. However, I end up missing schema information. In particular, it will not embed the size information for strings. I need that sizing information because later in the code I use it to create Oracle parameters.