|
Hi
I have a question of database.
If you have some solutions, please tell me.
Now I'm developing tool getting and filing trigger_body from database using ODP.NET. As a ODP.NET case, I can set fetch size value, so I set value. But Trigger_body I'd like to get is heavy, so I couldn't get it all( I could get 80%).
I investigated ODP.NET, I noticed there is maximum fetch size is exist(32KB).
So I am looking for how to get it using .NET Framework and etc.
I usually use Toad, this have a function display trigger_body. So I tried to display trigger_body over 32KB. I got it. I noticed that how to get trigger_body over 32KB was exist using program.
thank you in advance for your kindness.
best regards,
yu-yu
|
|
|
|
|
The trigger body is saved in a LONG field, I guess you are using the LONG Type included in the ODP.NET.
Can you send the code you are using to extract the trigger_body from DB ?
Free your mind...
|
|
|
|
|
hi,
thank you for your mail.
The code getting trigger_body from DB using ODP.NET is the following.
------------------------------------------------------------
string sSQL = "select trigger_body from user_triggers where trigger_name='" + sName + "'";
OracleCommand com = conn.CreateCommand();
com.CommandText = sSQL;
com.InitialLONGFetchSize = 32767; // Maxvalue
OracleDataReader reader = com.ExecuteReader();
string sResultString = string.Empty;
reader.Read();
if (!reader.IsDBNull(0))
{
sResultString = reader.GetOracleString(0);
}
reader.Close();
-------------------------------------------------------------
best regards,
yu-yu
|
|
|
|
|
Try using this approach...
First, on the DB run this to create a Stored Procedure
CREATE OR REPLACE
PROCEDURE get_trigger(iName IN VARCHAR2, iBody OUT LONG) IS
BEGIN
SELECT TRIGGER_BODY INTO iBody FROM USER_TRIGGERS WHERE TRIGGER_NAME=iName;
END;
/
Then try this...
string GetTriggerBody()
{
OracleCommand vCommand;
OracleParameter vParameter;
OracleString vString;
string vTriggerBody;
vCommand = new OracleCommand();
vCommand.Connection = this.mConn;
vCommand.CommandText = "get_trigger";
vCommand.CommandType = CommandType.StoredProcedure;
vCommand.Parameters.Add(new OracleParameter("iName", OracleDbType.Varchar2, ParameterDirection.Input));
vCommand.Parameters["iName"].Value = "TR_AC_DOCUMENTOS_FECHAFIN";
vParameter = new OracleParameter("iBody", OracleDbType.Long);
vParameter.Direction = ParameterDirection.Output;
vParameter.Size = 80000;
command1.Parameters.Add(vParameter);
command1.ExecuteNonQuery();
vString = (Oracle.DataAccess.Types.OracleString)vParameter.Value;
vTriggerBody = vString.Value;
return vTriggerBody;
}
Hope this helps...
Free your mind...
|
|
|
|
|
hi,
I tried to do it, but I couldn't.
I have a question.
command1.Parameters.Add(vParameter);
command1.ExecuteNonQuery();
what is "command1" ?
I thought it's vCommand, so I changed it.
but Error occuered at pl/sql Ora-06502
ORA-06512: 1 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(
Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String procedure, String[] args)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String[] args)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at ConsoleApplication2.Class1.test() in d:\testing\consoleapplication2\class1
.cs:line 162
at ConsoleApplication2.Class1.Main(String[] args) in d:\testing\consoleapplication2\class1.cs:line 37
best regards,
yu-yu
|
|
|
|
|
Sorry, it's vCommand, not command1.
The Oracle Error happens because the trigger you are trying to get doesn't exist.
Free your mind...
|
|
|
|
|
Really?
Thanks, I will try again.
After I will announce the result for you.
Best Regards,
yu-yu
|
|
|
|
|
Hi
I tried it, but same situation occuered.
The test code is the following.
----------------------------------------------------------
OracleConnection conn = new OracleConnection("Data Source=hora9;user id=aaa;password=aaa");
conn.Open();
OracleTransaction trn = conn.BeginTransaction();
OracleCommand vCommand = new OracleCommand();
OracleParameter vParameter;
OracleString vString;
string vTriggerBody;
vCommand.Connection = conn;
vCommand.CommandText = "get_trigger";
vCommand.CommandType = CommandType.StoredProcedure;
vCommand.Parameters.Add(new OracleParameter("iName", OracleDbType.Varchar2, ParameterDirection.Input));
vCommand.Parameters["iName"].Value = "T_T1URIA";
vParameter = new OracleParameter("iBody", OracleDbType.Long);
vParameter.Direction = ParameterDirection.Output;
vParameter.Size = 80000;
vCommand.Parameters.Add(vParameter);
vCommand.ExecuteNonQuery();
vString
= (Oracle.DataAccess.Types.OracleString)vParameter.Value;
vTriggerBody = vString.Value;
---------------------------------------------------------
I will try again,
If you see the code and notice some cause, please tell me.
Best regards,
yu-yu
|
|
|
|
|
What is the Oracle Error you are receiving ?
Usually, Oracle errors has this pattern.. ORA-#####. Need the number to check what is happening.
Free your mind...
|
|
|
|
|
Hi
I found the error cause.
It is the volume of trigger_body.
The procedure parameter of trigger_body is LONG.
This style miss, I think.
Because I tried to get short volume trigger_body, succeeded it.
But I think about it. Do you know Toad ? This is database tool.
As a this tool case, this can get heavy trigger_body without procedure.
Best regards,
yu-yu
|
|
|
|
|
Yes, I've used Toad. But I really prefer SQL Navigator.
This style miss, I think.
Because I tried to get short volume trigger_body, succeeded it.
I don't understand what you are saying here friend. Sorry 
Free your mind...
|
|
|
|
|
hi,
Sorry, my explanation is luck.
When We run "select trigger_body from user_triggers" using Toad and after check the result, we usually get the contents of trigger_body, do you know ?
This get not only short volume trigger_body but also heavy volume trigger_body. So I think we can get heavy trigger_body without procedure, but I don't know how to get it, use database control.
As a short volume trigger case, I could get it using ODP.NET library.
best regards,
yu-yu
|
|
|
|
|
Sorry friend, but I don't know what you mean with short and heavy volume triggers.
When I run "select trigger_body from user_triggers" on SQL Navigator, I get a result with a LONG field containing the trigger boddy.
This worked fined, but I don't have such a big trigger.
I have a policy, that is when the trigger is getting too big, just fragment it into Stored Procedures or Packages. That's why I don't have such a big trigger.
I would really like to know what you mean with short and heavy triggers, and I'm willing to help you solve the "big-trigger-pain"
Free your mind...
|
|
|
|
|
Whats the best way to get an empty Typed DataTable ? I've been using an SQL statement that returns zero rows, but the overhead of such a statement is going to get too big once the SQL table gets large.
|
|
|
|
|
Using the SQL statement is proably the easiest way. There shouldn't be much overhead regardless of the SQL statement if no rows are returned and the WHERE clause is set if an Key column not have a value in the table.
SELECT * FROM Employees WHERE EmployeeID = 0
Regardless of how many table are in the SQL statement, just make sure they are all joined correctly.
After you obtain the DataTable the 1st and only time, just cache it or use a singleton.
Michael
|
|
|
|
|
Here's a statement that uses less overhead since it won't require any kind of table-scan or index lookup:
SELECT * FROM Employees WHERE 1=2
|
|
|
|
|
we are working in a project in vb.net2003 using msaccess database...
we want to know the code to search or rowfilter by letters
(or by word match)
if any one can help us please connect us at this e-mail
masdotnet@yahoo.com
|
|
|
|
|
Are you talking about a query or filtering a datatable?
SQL in .Net use the '%' wildcard
e.g. Select LastName From tblUsers Where LastName Like '%bri%'
This will retrieve all rows with 'bri' in the last name.
DataTable in .Net the '%' wildcard is used as well.
Dim dv as DataView
Dim dt as DataTable
Dim dr as DataRow
Dim r as Integer 'loop for each row
'Populate the DataTable
...
...
'Now take the populated dt and make it a DataView and Filter it.
dv = dt.DefaultView
dv.RowFilter = 'LastName Like '%bri%'
For r = 0 to dv.Count-1 'Rows in the dataview
dr = dv.item(r).Row
'Print the values for LastName column
System.Diagnostics.debug.WriteLine(dr.Item("LastName"))
Next
Now this was taken off the top of my head so I am sure there may be some syntax errors, but you should get the idea.
Hope this is what you were looking for.
Michael
|
|
|
|
|
I have a table with the field type as datetime. So, this stores the date with time upto msec precession..
I have a searvh query that will look for records created in certain time period... How do I do this 'between' kind of query.. please help
I was born intelligent
Education ruined me!.
|
|
|
|
|
You don't need to give the string up tp msec. There is no problem with giving date value in BETWEEN.
SELECT * FROM mytable WHERE datecolumn BETWEEN '24/01/2004' AND '24/01/2004'
You can give time in long format or other formats,SQL have no problem with that. I don't understand whats your problem , it seems you know the way.
Mazy
No sig. available now.
|
|
|
|
|
Mazdak wrote:
it seems you know the way
Blah.... I was messing around with the dates by the '#' symbol and didnt realise that I have come to SQL server a long time before 
I was born intelligent
Education ruined me!.
|
|
|
|
|
<br />
SELECT ...<br />
WHERE<br />
cast(Convert(nvarchar, tr.TransDate, 1) as datetime) <= cast(Convert(nvarchar, @dtTo, 1) as datetime) AND<br />
cast(Convert(nvarchar, tr.TransDate, 1) as datetime) >= cast(Convert(nvarchar, @dtFrom, 1) as datetime)<br />
this code, I think, is better cuz it will take into account locale.
Regards,
Serge (Logic Software)
|
|
|
|
|
I want to check if the user name and password exists in the database. Table name is tblUser. User name is brendanvogt, and password is for example codeProject. I have a problem in that I can type codeproject in capital letters, and it will still accept the password. I can write a mixture of capitol letter and small letters, and it still accepts the password. I tried using the LIKE keyword, and it still does not work. Here is my SQL statement. I am using an Access database.
SELECT User_Username FROM tblUser WHERE User_Username = 'brendanvogt' AND User_Password LIKE('codeProject')
|
|
|
|
|
Misleading title - it isn't that password validation doesn't work, it's just that the SQL query is not case sensitive. You'd have to retrieve the password for a given username and compare it in code. I'm not aware of a way to make Access case sensitive, although you can do it for SQL Server (and hence MSDE).
Steve S
|
|
|
|
|
It is refer to your database(or table or column) COLLATION. You have to set it to a case sensitive value. You can change collation with ALTER TABLE or in Enterprise Manager in the Design View of table for each column at the bottom of page you can change it. You have to select one of values which contain CS , means Case Sensetive.
Mazy
No sig. available now.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
