|
Hi. So I know I've done this before but I can't get it to work despite every example on the internet telling me it should... I'm trying to call a procedure from code. I keep getting an "Invalid SQL statement" exception. Any ideas why? And yes, the procedure compiles and gives no errors when calling from an oracle client.
create procedure DoSomething
as
begin
end DoSomething;
I try to execute from code like so:
Using conn = New OracleConnection(MyConnectionString)
Using cmd = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "DoSomething"
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
At which point I get the aforementioned exception.
What am I doing wrong?
Sam.
|
|
|
|
|
Does it work when you execute the stored procedure through Oracle?
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Yes. This is a only a problem calling the procedure from code.
|
|
|
|
|
You have already posted this question in the Database forum[^].
You've been here six and a half years, and you still haven't realized that cross-posting questions isn't acceptable behaviour?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Oh, I'm terribly sorry that I added another few hundred duplicate bytes the massive amount of data stored here. However will this site possibly be useful now that I've completely gummed it up????
What in incredibly silly thing to get upset about. Report me to the admins, quick!
BTW, since I don't actually know the traffic on the individual discussions and since this question very clearly spans multiple topics, I don't see the problem here. So if you don't like my post and have nothing to contribute other than being a pompous jerk, feel free to pass over my posts.
|
|
|
|
|
USAFHokie80 wrote: Oh, I'm terribly sorry that I added another few hundred duplicate bytes the massive amount of data stored here. However will this site possibly be useful now that I've completely gummed it up????
WRONG. What you've done is place the same question in multiple places, splitting up the possibility of getting a collaborated answer. Now the answers you get could possibly conflict with each other, confusing YOU.
This has nothing to do with the number of bytes you posted.
USAFHokie80 wrote: BTW, since I don't actually know the traffic on the individual discussions and since this question very clearly spans multiple topics, I don't see the problem here. So if you don't like my post and have nothing to contribute other than being a pompous jerk, feel free to pass over my posts.
By the error message alone, it's only a database question. It has nothing to do with VB.NET or C#. It's simple. Something in the string you built and sent to Oracle wasn't correct. Since you didn't show the complete statement you sent, it's impossible to tell you what you did wrong.
|
|
|
|
|
Thanks for the concern, but I think that I'm probably the best one to decide what is or isn't confusing to me.
As for the error, you apparently don't understand. The procedure signature is identical to what I've posted. Only the name of the procedure is different. And as I stated, the procedure itself works perfectly well when executed from SQLLite command line client as "execute DoSomething;". So the problem actually is with the way it's being called from code and -not- the database. That is the complete statement, again, with only the actual name changed.
|
|
|
|
|
Wow. It's all about how this affects you and you alone, isn't it.
USAFHokie80 wrote: The procedure signature is identical to what I've posted. Only the name of the procedure is different. And as I stated, the procedure itself works perfectly well when executed from SQLLite command line client as "execute DoSomething;". So the problem actually is with the way it's being called from code and -not- the database.
If all you're doing is calling the procedure by name and not passing any parameters (which your code suggests,) the error is in the stored procedure.
|
|
|
|
|
I cannot see how my asking this question could negatively affect anyone. It seems like people on here, like most of the internet, just want to complain about something. This is tantamount to chastising me for using an incorrect homonym in my sentences.
And once again, the procedure runs flawlessly when execute from an Oracle client. If there were a problem with the code in the procedure I would have gotten an error. But, just to satisfy this useless endeavor, I'll indulge you.
create or replace
procedure UpdateUnitType as
cnt number(10,0):=0;
begin
for u in (select * from unit where type_cd = 1)
loop
cnt := 0;
select count(billet_id)
into cnt
from billet
where fiscal_year = u.fiscal_year
and uic = u.uic;
if cnt > 0 then
update unit
set type_cd = 2
where unit_id = u.unit_id;
end if;
end loop;
end updateunittype;
|
|
|
|
|
USAFHokie80 wrote: If there were a problem with the code in the procedure I would have gotten an error. But, just to satisfy this useless endeavor, I'll indulge you.
Don't ever assume this. What you're doing in the "client" is not exactly the same as what you're doing from code. Also, what you do in the stored procedure declaration can also affect what you have to do in your code depending on which provider library you're using.
For example, "CALL stored procedure name" in the "client" will not work if you're using Oracle's ODBC provider, but it works if you use MS's ODBC provider in your code.
What does the connection string look like?
|
|
|
|
|
He tried to help you....
End of the story.
|
|
|
|
|
Hai,
Am working windows application and using list view control but i can't increase row height. Please help me. very urgent.
Thanks
D.Manivelan
|
|
|
|
|
Good Day Sir/Mam
I would like to ask if there is an ocx/dll/third party sotfware for visual basic that checks a grammar in a text and can be use to a 64bit windows OS.
Any suggestions will much appreciated. Thank you.
modified 25-Apr-14 4:52am.
|
|
|
|
|
Many many moons ago I recall we used the Microsoft Word API to achieve this - it was a bit of a PITA though. Bear in mind that a VB6 program will not be using 64bit windows though - it will run under WOW64 - i.e. 32bit.
|
|
|
|
|
VB.net is not my strongest forte but I posted this solution http://www.codeproject.com/Answers/764387/TextBox-BackColor-Change#answer4 to a post and it's been downvoted. I don't care about the downvote but I do care if I've led the OP and others astray. Basically I suggested using one function to handle all of the Enter and Leave events...is that bad practise?
|
|
|
|
|
If it does not affect your program execution then probably no. However each event serves a specific purpose and should maybe treated as that.
Edit:
I just had a quick look at your suggestion and there is absolutely nothing wrong with what you suggested. The upside of that is it reduces the amount for coded lines and since there are 6 textboxes why on earth would I want to navigate past 5 identical Sub's each referring to another similar control.
You probably got down voted by a noob who does not really know what all that means.
|
|
|
|
|
Thank you! I've already thanked Richard Deeming as well on his comment to my solution post (which is handy if anyone comes across it)
|
|
|
|
|
I don't see anything wrong with your answer. It's certainly better than any of the others! 
Edit: Apart from the fact that you're relying on Option Strict Off; you should probably add a DirectCast in the handlers.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I want to download data every five minutes from a Davis Weather Station. The code that actually talks to the hardware is in a DLL, and I don't have access to the source (or to terribly good documentation, sadly!). My code that does the downloading (below) works reliably and without problems if called in the normal way. If it is called from a System.Timer OnElapsed thread, the program (run in Debug mode) crashes immediately without reporting what is going wrong. The variables and structures starting with g are defined outside any Functions in a Module, so they have global scope, as do WSInitialized and USBSerialNumber. I think that everything else is fairly obvious. Commenting out the one line which retrieves text data into a StringBuilder seems to fix the problem, and I can do without that data, but I would like to know what is going on, since I may want to do something in the future where the use of StringBuilders to receive data (if that is what is causing the problem) is less optional.
Private Sub Get_Davis_WeatherData(ByRef gwdcurrent As WeatherData)
Dim dtsTemp As New DateTimeStamp
Dim sbWindDir As New StringBuilder(5)
If Not WSInitialized Then
CloseUSBPort_V()
USBSerialNumber = GetUSBDevSerialNumber_V()
If (OpenUSBPort_V(USBSerialNumber) = 0) Then
If (InitStation_V() = COM_ERROR) Then
MsgBox("Cannot initialize the USB port for the Weather Station")
Exit Sub
Else
SetCommTimeoutVal_V(100, 100)
WSInitialized = True
End If
Else
MsgBox("Cannot open the USB port for the Weather Station")
Exit Sub
End If
End If
If (GetStationTime_V(dtsTemp) = 0) Then gwdcurrent.Time = DateStamp2UnixTime(dtsTemp)
If LoadCurrentVantageData_V() = 0 Then
gwdcurrent.OutTempF = GetOutsideTemp_V()
gwdcurrent.OutHum = GetOutsideHumidity_V()
gwdcurrent.BPressure = GetBarometer_V()
gwdcurrent.WindChillF = GetWindChill_V()
gwdcurrent.DewPointF = GetDewPt_V()
gwdcurrent.RainTotal = GetTotalRain_V()
gwdcurrent.Rain24h = GetDailyRain_V()
gwdcurrent.Rain1h = GetRainRate_V()
gwdcurrent.WindSpeedMPH = GetWindSpeed_V()
gwdcurrent.WindRDir = GetWindDir_V()
gwdcurrent.WindDir = GetWindDirStr_V(sbWindDir).ToString
gsngRainMTD = GetMonthlyRain_V()
End If
If gsnsSensor.IsUpToDate Then
gwdcurrent.InHum = gsnsSensor.Humidity
gwdcurrent.InTempF = gsnsSensor.TempF
ElseIf tcHouse.Online Then
gwdcurrent.InTempF = tcHouse.CurrentTemp
gwdcurrent.InHum = tcHouse.CurrentHumidity
End If
End Sub
|
|
|
|
|
If you're using the System.Timers.Timer (there is no System.Timer!), the Elapsed callback is done on a background thread. If the library you're using is not written to handle that, it may screw up the library like you're seeing. You'll have to check with the people who wrote that library to find out.
If possible, you can swap out the System.Timers.Timer with the Timer object in the Toolbox. That's the Forms-based Timer and runs on windows messaging Events, but needs to be used in a Windows Forms app, not a Console app or WPF. These events are always raised on the UI (startup) thread and shouldn't give you the problem that you're seeing.
|
|
|
|
|
Yes, it is/was a System.Timers.Timer. I had a vague memory of this sort of problem responding to switching to a Forms timer, so I did try that (my app normally keeps a main Form instantiated, even though it was written as a Console App). This, however, also resulted in a crash when the timer fired and the code executed - I left the Form open for the test.
|
|
|
|
|
Dim sbWindDir As New StringBuilder(5)
gwdcurrent.WindDir = GetWindDirStr_V(sbWindDir).ToString
So you pass an empty StringBuilder instance with a capacity of 5 characters to the GetWindDirStr_V function. What does happen in that function? How does the signature of the function of the thirdparty dll look like, and how does your DllImport statement look like?
Other values called seem to be numeric types, and this one is a string type. Hence I guess the problem is actually in the marshalling of the non-managed "string" to the managed string. Also the length of the string could be wrong: what about "SouthEast" with 9 characters or "NorthNorthWest" with 14? I.e. a simple buffer overflow, actually independent from threading issues.
|
|
|
|
|
The documentation of the DLL is (to put it politely) somewhat sketchy - for this routine:
'char* GetWindDirStr_V (char* dirStr)
Description
This function gets the currenct (sic!) wind direction in string representation.
Return Values
current wind direction
"---" represents no data available.'
The DllImport statement is:
<DllImport("VantagePro.dll", CharSet:=CharSet.Ansi, CallingConvention:=CallingConvention.StdCall)> _
Public Function GetWindDirStr_V(dirStr As StringBuilder) As StringBuilder
End Function
While string length overflow is an obvious possibility, the longest string actually passed is three characters (e.g. 'NNE'), and buffers for text wind direction strings which are explicitly sized elsewhere in Structures passed to and from the DLL are always char(5). In addition, the code was extensively exercised without any problems outside timer OnElapsed routines and worked fine - it only fails when called from interrupt code.
|
|
|
|
|
If you enclose the line gwdcurrent.WindDir = GetWindDirStr_V(sbWindDir).ToString in a try-catch block, can you catch an exception? If so, what's the message - and GetLastError's message?
|
|
|
|
|
I had not previously tried that, but I did, with Debug.Print commands inserted to display the information. Unfortunately, the crash is a Windows "This application has stopped working" one, which is apparently not prevented by exception-trapping the line of code that provokes it, and it won't let me 'Debug' it with the open instance of VS.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
