|
Hi every body,
i got the java code for generation of some code based on 2 inputs..i need to
convert into sql server function..?? i was tried but getting some syntax errors..pls have a look below...and thanks in advnace..
------------------------------------------------------------------------------
CREATE FUNCTION getGridNum (@ref1 bigint,@ref2 bigint)
RETURNS bigint AS
BEGIN
declare @gridA bigint
DECLARE @gridB bigint
DECLARE @osGridNum1 bigint
DECLARE @osGridNum2 bigint
DECLARE @osGridReference bigint
--@osGridNum1 = @REF1 -- for use later to get number section of the grid ref
--@osGridNum2 = @REF2 -- for use later to get number section of the grid ref
/*get the first number of the number of the
grid reference and concat '00000' so it can be c
compared in the lookup table - unless it is '0'*/
--if (gridA.length < 5)
if len(@REF1)<5
begin
-- @REF1 ='0' + @REF1
@REF1 = '0' + CAST(@REF1 AS VARCHAR(10))
--- gridA = @REF1.substring(0,1)
@REF1 = substring(@REF1,0,1)
end
else
begin
if (gridA != '0')
gridA = gridA + '00000'
end
/*get the second number of the number of the
grid reference and concat '00000' so it can be c
compared in the lookup table - unless it is '0'*/
-- if (gridB.length < 5)
if len(gridB)<5
begin
gridB ='0' + gridB
-- gridB = gridB.substring(0,1)
gridB = substring(gridB,0,1)
end
else
begin
if (gridB != '0')
gridB = gridB + '00000'
end
-- send substringed letter to searchLetter function to find the corresponding OS grid letters
oSgridLetter = searchLetters(gridA, gridB)
/*The next if statements take the oringinal numbers and substring them to get
valid numbers for the OS grid format. Of five numbers it is the middle 3*/
--if (osGridNum1.length < 5)
if len(osGridNum1) < 5
begin
osGridNum1 = '0' + osGridNum1
-- osGridNum1 = osGridNum1.substring(1,4)
osGridNum1 = Substring(osGridNum1,1,4)
end
--if (osGridNum2.length < 5)
if len(osGridNum2) < 5
osGridNum2 = '0' + osGridNum2
-- osGridNum2 = osGridNum2.substring(1,4)
osGridNum2 = substring(osGridNum2,1,4)
-- Now concatinate the Letters and the the OS letters and the two sets of os grid numbers together to get the correct formate
osGridReference = oSgridLetter + osGridNum1 + osGridNum2
return osGridReference
end
END
----------------------------------------------------------------------
Server: Msg 170, Level 15, State 1, Procedure getGridNum, Line 26
Line 26: Incorrect syntax near '@REF1'.
Server: Msg 170, Level 15, State 1, Procedure getGridNum, Line 33
Line 33: Incorrect syntax near 'gridA'.
Server: Msg 170, Level 15, State 1, Procedure getGridNum, Line 44
Line 44: Incorrect syntax near 'gridB'.
Server: Msg 170, Level 15, State 1, Procedure getGridNum, Line 53
Line 53: Incorrect syntax near 'gridB'.
Server: Msg 170, Level 15, State 1, Procedure getGridNum, Line 69
Line 69: Incorrect syntax near 'osGridNum1'.
Server: Msg 170, Level 15, State 1, Procedure getGridNum, Line 77
Line 77: Incorrect syntax near 'osGridNum2'.
|
|
|
|
|
I can see you've just not translated some Java properly. You should also be aware that Java and SQL are different language types. Java is procedural (i.e. you specify the procedure you want it to take) where as SQL is, for the most part, declarative (i.e. you specify what you want and it figures out the details) - but SQL can also operate in a procedural way also but that is often very inefficient. The procedural elements exist only to suppliment the declarative (set based) elements. If you can use a set based operation then SQL is very efficient.
What you are doing here is imposing a procedural model.
GBal wrote: @REF1 = '0' + CAST(@REF1 AS VARCHAR(10))
Should be
SET @REF1 ...
GBal wrote: if (gridA != '0')
!= in Jave is <> in SQL
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
|
Hi Colin,,Thanks for ur reply..i was changed that function..But here im getting another java code function and i need to translate it into SQL server again..im confused with the for loop in the below code..pls help me..
/* Function will take grid numbers and use lookup array to match the correct OS grid letters */
CREATE FUNCTION searchLetters (let1 bigint,let2 bigint)
RETURNS bigint AS
BEGIN
DECLARE @LEN BIGINT
SET @LEN = LEN(OSgrid)
for (var i=0; i
|
|
|
|
|
|
I have written a SQL script to run from my code using executesql command..meaning that i have framed the sql script and have it in a string and i pass the string to the ExecuteSQL funtion which executes my sql script..my scripts functionality is to insert some 1000 rows into a table..when my code executes the script runs(which i came to know from profiler) but the rows are not inserting..
can anyone tell me as wat could be the reason??
NOTE: there are no conditions in the script that ll prevent the insertion happenning
its a bit urgent..
thanks in advance..
|
|
|
|
|
I suspect there is an error in the script.
Best regards,
-----------
Igor Sukhovhttp://sukhov.net
|
|
|
|
|
|
the script doesnt have any error..
My script is running fine in Query Analyzer and it doesnt have any GO statements..so is GO statement required for my script?? My script will be a maximum of 70 lines thats it..kindly suggest..
any other clues pls let me know..
|
|
|
|
|
|
Just for another thought, if you are using MSSQL 2000 there is a limit of 4000 characters for nvarchar (unicode 'wide' varchar) and 8000 for regular varchar.
Your string may be longer than that and as a result it gets truncated when passed to a stored procedure.
"Democracy is two wolves and a sheep voting on what to have for dinner" - Ross
Edbert
Sydney, Australia
|
|
|
|
|
I'm trying to force a window to jump to a specific row in my database when I click a button. I'm using a "details" display of the table, and there is a binding navigator(supplied with the drag and drop creation of the form).
An example would be (I'm using c# btw)
[Parts list]
1.
2.
3.
...
Select Part [2.]
New Form with more details on part 2.
Edit/Delete/New part
I can use a modified SQL query to return just the one row, but then the add/delete/new buttons dont work because the mapping is lost to the original data. I can't imagine this is something hard to do, but I've been all over the net posting for an answer and nobody has even attempted. Any help is appreciated.
|
|
|
|
|
I found this article here[^]
I've tried to follow because that sounds like my problem, so my code looks like this.
this.tbAdapter.fill(dataSet.tb);
this.dataSet.tb.select();
that still gives me record 1, instead of the one I want. What am I doing wrong?
-- modified at 16:41 Tuesday 27th June, 2006
|
|
|
|
|
Hello again PyroManiak,
I took a quick look at the code in the link you gave, and I noticed that the poster in that message was using the IndexOf function, which he said was returning -1. The way I understood his reply post with the answer was that he called select but then called IndexOf after that. This is just speculation, since I haven't tested it yet, but I wanted to ask you if you were calling IndexOf?
If you are and it still doesn't work, could you post the rest of your code to do with loading the "edit" form?
Hope that helps.
Sincerely,
Alexander Wiseman
|
|
|
|
|
I think I've got a solution. It seems to work without problem.
myBindingSource.Position = variable;
-- modified at 19:11 Tuesday 27th June, 2006
My older post got answered in a rather rude way, thanks for not treating me like an idiot.
|
|
|
|
|
Don't suppose you know how to capture a single attribute from a row in a datagridview do you?
-- modified at 19:57 Tuesday 27th June, 2006
I'm trying things like datagridview.rows.indexof();
but it says I must give it a variable, well, if I had the variable I wouldn't need it to tell me! =P
|
|
|
|
|
|
I'm sorry, its probably just me. It is the end of the quarter and this project was dumped in my lap as a last minute deal and it wasn't planned out other then some oddball requirements. As far as a book goes, I'm been waiting to get a book on ADO.net 2.0 (Pro ADO.NET 2.0 - Sahil Malik) but they wont budget anything else until next month when the new quarter kicks in.
This is what happens when people find out a hardware engineer also has background in software development.
|
|
|
|
|
I have an app with a DAL and so far I have been calling sprocs that require no input parameters and all works fine. I now have to call a sproc with requires an input paramter. The sproc I am calling is as follows:
ALTER PROCEDURE dbo.prc_QA_SysCodes_sel_drop_fill
(
@sysCodeType as int
)
As
BEGIN
DECLARE @Err Int
SELECT
A.[SysCodeID],
A.[SysCodeDesc]
FROM
[Contract].[dbo].[QA_SysCodes] A
WHERE
A.SysCodeType = @sysCodeType
order by
A.[SortOrder],
A.[SysCodeID]
Set @Err = @@Error
RETURN @Err
End
I need to pass in the @sysCodeType but can't figure out how to pass it to the sproc. Thoughts, words of wisdom?
-- modified at 13:16 Tuesday 27th June, 2006
|
|
|
|
|
Add parameter to your procedure:
yourSP.Parameters.Add(ParamName, SqlParamType, length); //length is optional
Pass value to the param
yourSP.Parameters[ParamName].Value = ParamValue;
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
Hi
I can execute single line statements against a db, no problem...
string SQLQuery = "select VERSION, DSC from exo_version where SYSTEM = 'DATABASE'";
string strConnect = "DSN="+ dbName +";UID=abc; PWD=cde";
OdbcConnection conn = new OdbcConnection(strConnect);
conn.Open();
DataSet fullSet = new DataSet();
OdbcDataAdapter dataAdapter = new OdbcDataAdapter();
OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter);
dataAdapter.SelectCommand = new OdbcCommand(SQLQuery, conn);
dataAdapter.Fill(fullSet);
Cache["dbVersion"] = fullSet;
but my client wants a web based front end developing where the user can dump a load of statements in one big text block, select the DB and the server to execute them.
The 'old' way of doing this was to open up PLSQL, paste the script into a command window and execute. I could parse the text for the semicolon and process each line independantly, but is there a way to throw a whole script, which will include inserts, deleted, modification of schema, index creation etc at the database in one go?....and then capture the output!!
Any info at all on this would be appreciated.
Regards
Angel
*********************************************
The sooner you fall behind, the longer you have to catch up.
|
|
|
|
|
Need help on how to integrate data from one database-1 to the other database-2 using sql... event w/w/out knowing the fields of the other database-2.
Thanks
|
|
|
|
|
You need to know what the fields are in the other database.
|
|
|
|
|
|
|
What do you mean technology? I'm writing this in C# using the data adapters and all that built into the IDE.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
