|
Have a look at OpenDBDiff[^]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
This doesn't seems to be used from inside a vb.net application , but as a external tool.
|
|
|
|
|
You may or may not be able to integrate the supplied source code
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
For this I need some help
|
|
|
|
|
You know how this site works, right? You start yourself and if you run into a specific problem you can ask 
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
No , I don't know. Thank you for telling me.
But what if I don't know how to start ?
Can't this forum help someone how to start ? 
|
|
|
|
|
I wrote this piece of code in the event of a textbox LEAVE where I enter the city automatically brought in CAP PR and the results of a query ....
Making debugging line by line VS 2010 jumps the line Dim laws as OdbcDataReader and therefore it does not perform the query and gives me error as if there were no data.
I made a few mistakes in the code?
Private Sub txtPaese_Leave_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPaese.Leave
Try
Dim cn As New OdbcConnection("dsn=PHOENIXDB;uid=SYSDBA;pwd=masterkey;")
Dim com As String = txtPaese.Text
Dim strCn As String = "Select distinct * from TBL_COMUNI where COMUNE='" & com & "'"
Dim cmd As OdbcCommand = New OdbcCommand(strCn)
Dim leggi As OdbcDataReader
Dim daSelPaese As New OdbcDataAdapter
Dim dsSelPaese As New DataSet
cn.Open()
daSelPaese.SelectCommand = cmd
daSelPaese.SelectCommand.Connection = cn
daSelPaese.Fill(dsSelPaese, "TBL_COMUNI")
leggi = cmd.ExecuteReader
Me.txtCAP.Text = leggi.Item(11)
Me.txtPR.Text = leggi.Item(4)
leggi.Close()
cn.Close()
Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
End Try
End Sub
|
|
|
|
|
Your code is vulnerable to SQL Injection[^].
NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
You're executing your command twice - once to fill a DataTable, which you then throw away, and once with the ExecuteReader method.
You've then tried to access columns from the returned OdbcDataReader object without first calling its Read method. This will throw an exception.
Your query is selecting multiple rows from the table, but you're only using the first row. You should change it to only select a single row instead.
You're using SELECT * FROM ..., which returns every column in the table. You're then only using two columns. Change your query to return only the columns you need.
You should also wrap any objects that implement IDisposable in a Using block.
Private Sub txtPaese_Leave_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPaese.Leave
Try
Using cn As New OdbcConnection("dsn=PHOENIXDB;uid=SYSDBA;pwd=masterkey;")
Dim query As String = "SELECT TOP 1 * FROM TBL_COMUNI WHERE COMUNE = ?"
Using cmd As New OdbcCommand(query, cn)
cmd.Parameters.AddWithValue("p0", txtPaese.Text)
cn.Open()
Using reader As OdbcDataReader = cmd.ExecuteReader(CommandBehiavor.CloseConnection)
If reader.Read() Then
Me.txtCAP.Text = reader.Item(11)
Me.txtPR.Text = reader.Item(4)
Else
End If
End Using
End Using
End Using
Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
End Try
End Sub
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Grazie tantissimo.... Ora funziona tutto....
Thank you so much .... Now everything works ....
|
|
|
|
|
Hello Everyone,
I am Working on SCADA project in which I am writing real time data to excel worksheet. Now I want to apply a check on data that if worksheet is updating continuously or not if not then I will restart my windows form Application. I try following code but it doesn't work.
Private EventDel_CellsChange As Excel.DocEvents_ChangeEventHandler
Public Sub UseDelegate()
xlBook = excelWB
xlSheet1 = xlBook.Worksheets(2)
CType(xlSheet1, Excel._Worksheet).Activate()
EventDel_CellsChange = New Excel.DocEvents_ChangeEventHandler( _
AddressOf CellsChange)
AddHandler xlSheet1.Change, EventDel_CellsChange
xlApp.Visible = True
xlApp.UserControl = True
End Sub
Private Sub CellsChange(ByVal Target As Excel.Range)
MsgBox("Restrating Of Application Required")
End Sub
Any Help Please ................ thanks in advance.
|
|
|
|
|
I am trying to read a CLOB value from an Oracle database query using the GetChars method. I am doing this so that I can buffer very large CLOB values and write the results out to an XML file.
However, there seems to be an issue where the GetChars method of an OracleDataReader is only buffering half the characters it should. This causes me to continue buffering in a loop until GetChars returns 0, but I think this is very inefficient and is slowing down my program.
Does anyone know why this is happening? Here is the fragment of code that I am using:
Dim reader As Data.OracleClient.OracleDataReader
Dim bufferSize As Integer = 1024 * 2000
Dim outChar(bufferSize - 1) As Char
Using connection As New Data.OracleClient.OracleConnection(connectString)
Dim command As New Data.OracleClient.OracleCommand(statement)
command.Connection = connection
connection.Open()
reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)
Do While reader.Read()
startIndex = 0
Array.Clear(outChar, 0, bufferSize)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Do Until retval = 0
rawStr = New String(outChar)
rawStr = rawStr.Replace(Chr(0), "")
myxml.WriteRaw(rawStr)
myxml.Flush()
startIndex += rawStr.Length
Array.Clear(outChar, 0, bufferSize)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Loop
Loop
End Using
|
|
|
|
|
Aaron Rosenthal wrote: This causes me to continue buffering in a loop until GetChars returns 0
You are reading it in blocks of 1024 * 2000 chars. If you know the size of the blob, then you could see if you can fetch it completely.
Aaron Rosenthal wrote: I think this is very inefficient and is slowing down my program
Why do you think that?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I can't assume to know the size of the CLOB in my program because it may vary. Also, if I simply buffered the entire CLOB, what would be the point of buffering? I'm trying to avoid doing that so I don't run into memory issues for very large CLOBs.
What I don't get is why doesn't GetChars fill the entire outChar array? I know that it can fill more than it does. For example, on a further iteration of my loop GetChars only fetches FOUR characters, and there are still 3 more to fetch, so I have to continue iterating. Why does it do that?
|
|
|
|
|
Aaron Rosenthal wrote: Why does it do that?
I don't know the exact implementation details of the method, despite having it open in ILSpy.
The buffer will be filled to its maximum with the available data. Did you set the commandbehaviour to sequential on the reader?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
What type is the myxml variable? If it's an System.Xml.XmlWriter, you should probably be using the WriteRaw overload[^] that takes a char[], rather than creating two new strings every time:
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Do Until retval = 0
myxml.WriteRaw(outChar, 0, retval)
myxml.Flush()
startIndex += retval
Array.Clear(outChar, 0, retval)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Loop
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I don't think the problem is with the WriteRaw method.
When GetChars first returns, the value of retval is 1265918. However, the outChar array is only filled up to half of that amount (up to index 632958). I know this because all the values past that index are null (0x00). Also, when I convert the outChar char array to a string and remove the null values, the length of the resulting string is 632959.
On the second call to GetChars, the value of retval is 632959, but the outChar array is only filled up to index 316478.
And so on and so forth.
So again, my question is why does GetChars not fill the entire outChar array?
|
|
|
|
|
No idea why it's not filling the buffer, but creating two new strings on every pass through the loop is going to have a noticeable impact on your method's performance.
Since the real problem seems to be the performance of the code, it's got to be worth trying!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Possibly, but how would I determine the index in the outChar array where the null values begin? I don't feel comfortable assuming that it will always be half what retval returns. That seems to be asking for trouble. And looping through the entire outChar array until I encounter a null (0x00) value would probably be just as inefficient as converting the outChar array to a string and then stripping the null values, right?
|
|
|
|
|
Are you expecting to get null characters returned from the database?
As far as I can see, the only reason you're getting null characters in your string is because you're ignoring the retval value, and creating a string that represents the entire array. Since all of the array entries beyond retval will be set to the null character, you're ending up with a long list of nulls on the end, which you're then having to remove.
With your current code, you could use:
rawStr = New String(outChar, 0, retval)
which would avoid adding those null characters to the string in the first place.
With my version, the line:
myxml.WriteRaw(outChar, 0, retval)
also avoids writing null characters to the output.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You've touched upon the source of my problem, though. The value of retval is incorrect.
For example, the first time GetChars returns, it puts the value 1265918 into retval. However, if I actually try to access the value of the outChar array at index 1265917, I get a null value. This is because GetChars does not actually seem to be buffering as much as it says it does. In fact, it is only buffering up to index 632958.
GetChars is only filling my buffer array to half of what it says it did. The value that GetChars returns seems to be the actual size of the entire CLOB, not the number of characters that it filled the buffer with.
|
|
|
|
|
OK, so try an Array.IndexOf call to find the index of the first null character:
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Do Until retval = 0
Dim nullIndex = Array.IndexOf(outChar, Chr(0), 0, retval)
If nullIndex = -1 Then nullIndex = retval
myxml.WriteRaw(outChar, 0, nullIndex)
myxml.Flush()
startIndex += retval
Array.Clear(outChar, 0, retval)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Loop
That will perform a linear search on the array, but it will be much faster than creating a string containing a complete copy of the array, then iterating through every character in the string, copying every non-null character to a new string.
If the retval value is incorrect, you might also need to adjust the increment of the startIndex variable; otherwise, you might be skipping characters. You'd need to compare the final output with the database column to see if that's the case.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
But the very first value of retval with a startIndex of 0 is incorrect... I know this because the first call to GetChars does not fill the outChars as far is retval says it did. Given this, how could retval possibly be correct for any other value of startIndex?
|
|
|
|
|
One other thing that's worth considering: the System.Data.OracleClient assembly / namespace has been deprecated by Microsoft:
The types in System.Data.OracleClient are deprecated and will be removed in a future version of the .NET Framework.
There is a newer alternative from Oracle called ODP.NET[^], which is still supported. There are two NuGet packages available:
You might also want to install the developer tools[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
OK, one more suggestion! 
Try using the GetOracleLob method[^] instead. This will return an OracleLob object[^] which you can use to read the value.
Using lob As OracleLob = reader.GetOracleLob(0)
Using sr As New StreamReader(lob, Text.Encoding.Unicode)
retval = sr.Read(outChar, 0, bufferSize)
Do Until retval = 0
myxml.WriteRaw(outChar, 0, retval)
myxml.Flush()
Array.Clear(outChar, 0, retval)
retval = sr.Read(outChar, 0, bufferSize)
Loop
End Using
End Using
You might need to adjust the encoding passed to the StreamReader constructor. The GetChars method uses Unicode by default, but I suspect your string might be in a different encoding.
EDIT: Looking at the code in dotPeek, this will be much more efficient than calling GetChars:
- The
GetChars method creates a new OracleLob on each call, and reads its Value property. - The
Value property reads the entire string into memory on each call. - The
GetChars method then copies the string to a Char array. - The
GetChars method then copies the specified section of the array to the buffer you've passed in.
This is horrendously inefficient, and it's no surprise that your code is running slowly! 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
modified 23-Jul-15 12:50pm.
|
|
|
|
|
Thank you this is working perfectly! Moving to a different namespace was not an option for me at this moment in time, so this solution is perfect.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
