|
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.
|
|
|
|
|
You should never store passwords in clear text. If anyone manages to obtain the database (downloading the file, executing a SELECT via a SQL injection attack, etc), your entire authentication mechanism is compromised. Users will not be happy if they've used the same password for multiple systems/websites/whatever, and you've disclosed it.
You should calculate a secure hash (using a cryptographic hashing/message digest scheme, not one you invent yourself) and store that in the database (you'll probably need to convert the bytes to hexadecimal). When authenticating a user, you again hash the provided password and compare with the stored one.
To calculate a cryptographic hash, you can use the CryptHashData function in the Windows API, the CAPICOM object library (if using VB 6 or a scripting language), or the System.Security.Cryptography.HashAlgorithm .NET Framework class.
There are various other techniques that are an extension of this which can provide more security, such as encrypting the hash, and/or adding a random 'salt' to the password before hashing.
|
|
|
|
|
Hi everyone,
Really new to using SQL Server. I've installed MSDE and I'm using the osql utility to access the db and I had a question about formatting output. I know in Oracle I could do something like
column Item format a18 and that would format the column so that it was only 18 character wide. Does anyone know how I would accomplish this same thing with MSDE using the osql utility?
Thanks.
- monrobot13
|
|
|
|
|
LEFT(<field_name>, 18) or SUBSTRING(<field_name>, 0, 18)
|
|
|
|
|
Exactly what I was looking for. Thanks!
- monrobot13
|
|
|
|
|
Hi,
I'm trying to use the SqlDataAdapter, but I need to know how many rows the SqlDataAdapter had. There is some way to know more easy that:
<br />
SqlDataAdapter datareader;<br />
int i = 0;<br />
while(dataReader.Read()){<br />
i++;<br />
}<br />
Thanks
----
hxxbin
|
|
|
|
|
I'm not sure there is way to get the count of rows on a sqlDataAdapter.
The sqlDataAdapter.Fill method fills a Dataset or a DataTable. Once the dataset or table is populated you can get the row count from the
Dataset.Tables(0).Rows.Count or DataTable.Rows.Count method.
Dim ds As Dataset
sqlDA.SelectCommand.Connection = cn
sqlDA.Fill(ds)
sqlDA.SelectCommand.Connection = Nothing
Return dataset
With the dataset or DataTable populated you can obtain the rowcount by doing the following:
ds.Tables(0).Rows.Count
dt.Rows.Count
Michael
|
|
|
|
|
I think you meant SqlDataReader (the rest of the code looks like that).
There's no way to find out in advance how many rows are going to be retrieved. The SqlDataReader won't allow you to rewind anyway.
If you need to store the data temporarily (maybe you need to do multiple passes over it?), consider a DataTable. If that has too much overhead for you, use some automatically-growing data structure such as an ArrayList - you can always use the ArrayList's CopyTo method to copy it to an array once you've retrieved all the data.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
