|
Hi, I'm trying to write a dynamic query in a procedure called by a service. From examples i've found in the oracle docs, I think this is the right method, but I keep getting an exception. It seems to be a problem with an operator, but I don't see anything wrong. Anyone care to take a look? Thanks,
Exception:
ORA-00920: invalid relational operator
ORA-06512: at "SINC.EQUIPMENT_MGMT_PKG", line 89
ORA-06512: at line 1
The second error is pointing at the whitespace after the line "v_where:= ltrim(v_where, ' and');"
Procedure:
procedure FetchItemTechData_PRC
( c_items out T_CURSOR,
p_Niin IN CHAR,
p_Fsc IN CHAR,
p_Description in VARCHAR2,
p_Idn IN CHAR,
p_Tam IN CHAR,
p_Cos IN CHAR,
p_Scos IN CHAR,
p_Ec IN VARCHAR2,
p_LocalTam in VARCHAR2,
p_Status in Number,
p_PageNum in Number
) AS
v_where varchar2(2000);
BEGIN
if p_Niin is not null then
v_where:= concat(' p_Niin like ''%', concat(p_Niin, '%'''));
end if;
if p_Fsc is not null then
v_where:= concat(concat(v_where, ' and itd.fsc like ''%'), concat(p_Fsc, '%'''));
end if;
if p_Description is not null then
v_where:= concat(concat(v_where, ' and lower(itd.description) like lower(''%'), concat(p_Description, '%'')'));
end if;
if p_Idn is not null then
v_where:= concat(concat(v_where, ' and lower(itd.idn) like lower(''%'), concat(p_Idn, '%'')'));
end if;
if p_Tam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.tam) like lower(''%'), concat(p_Tam, '%'')'));
end if;
if p_Cos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.cos) like lower('), concat(p_Cos, ')'));
end if;
if p_Scos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.scos) like lower('), concat(p_Scos, ')'));
end if;
if p_Ec is not null then
v_where:= concat(concat(v_where, ' and lower(itd.ec) like lower(''%'), concat(p_Ec, '%'')'));
end if;
if p_LocalTam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.local_tam) like lower(''%'), concat(p_LocalTam, '%'')'));
end if;
if p_PageNum > -1 then
v_where:= concat(concat(v_where, ' and rownum > '), p_PageNum * 100);
v_where:= concat(concat(v_where, ' and rownum < '), (p_PageNum+1) * 100);
end if;
v_where:= ltrim(v_where, ' and');
open c_items for
'select
itd.niin,
itd.fsc,
itd.description,
itd.idn,
itd.tam,
itd.cos,
itd.scos,
itd.ec,
itd.local_tam,
itd.local_ind,
itd.serialized_ind,
itd.ui,
itd.unit_price,
itd.status,
itd.created_id,
itd.created_dt,
itd.modified_id,
itd.modified_dt
from item_tech_data itd
where :whereClause' using v_where;
END FetchItemTechData_PRC;
|
|
|
|
|
I could be wrong, but I don't think you can use bind variables in that manner. Typically they should contain a value that can be operated on. In your case the bind variable contains all the operators and values. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I'm relatively new to oracle... so I'll assume you're right. I modified the proc a bit so it doesn't use the bind variable and instead just concats the string together. Now I get a different exception...
ORA-00909: invalid number of arguments
ORA-06512: at SINC.EQUIPMENT_MGMT_PKG, line 116
ORA-06512: at line 1
where line 116 is again the whitespace line just before opening the cursor. I'm basing this off of this - see section "Sample Multiple-Row Query Using Native Dynamic SQL". Maybe i'm just going about this wrong.. ?
procedure FetchItemTechData_PRC
( c_items out T_CURSOR,
p_Niin IN CHAR,
p_Fsc IN CHAR,
p_Description in VARCHAR2,
p_Idn IN CHAR,
p_Tam IN CHAR,
p_Cos IN CHAR,
p_Scos IN CHAR,
p_Ec IN VARCHAR2,
p_LocalTam in VARCHAR2,
p_Status in Number,
p_PageNum in Number
) AS
v_where varchar2(2000);
BEGIN
if p_Niin is not null then
v_where:= ' itd.niin like ''%' || p_Niin || '%''';
end if;
if p_Fsc is not null then
v_where:= v_where || ' and itd.fsc like ''%' || p_Fsc || '%''';
end if;
if p_Description is not null then
v_where:= v_where || ' and lower(itd.description) like lower(''%' || p_Description || '%'')';
end if;
if p_Idn is not null then
v_where:= v_where || ' and lower(itd.idn) like lower(''%' || p_Idn || '%'')';
end if;
if p_Tam is not null then
v_where:= v_where || ' and lower(itd.tam) like lower(''%' || p_Tam || '%'')';
end if;
if p_Cos is not null then
v_where:= v_where || ' and lower(itd.cos) like lower(' || p_Cos || ')';
end if;
if p_Scos is not null then
v_where:= v_where || ' and lower(itd.scos) like lower(' || p_Scos || ')';
end if;
if p_Ec is not null then
v_where:= v_where || ' and lower(itd.ec) like lower(''%' || p_Ec || '%'')';
end if;
if p_LocalTam is not null then
v_where:= v_where || ' and lower(itd.local_tam) like lower(''%' || p_LocalTam || '%'')';
end if;
if p_PageNum > -1 then
v_where:= v_where || ' and rownum > ' || (p_PageNum * 100);
v_where:= v_where || ' and rownum < ' || ((p_PageNum+1) * 100);
end if;
v_where:= ltrim(v_where, ' and');
v_where:= 'where ' || v_where;
open c_items for
('select
itd.niin,
itd.fsc,
itd.description,
itd.idn,
itd.tam,
itd.cos,
itd.scos,
itd.ec,
itd.local_tam,
itd.local_ind,
itd.serialized_ind,
itd.ui,
itd.unit_price,
itd.status,
itd.created_id,
itd.created_dt,
itd.modified_id,
itd.modified_dt
from item_tech_data itd '
|| v_where);
END FetchItemTechData_PRC;
|
|
|
|
|
The only thing I can think of is making sure that the 'out' parameter you supply when you call the procedure has the correct number and type of fields to match the select list.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Yeah... it's just a reference cursor, so it works like it is. It was working before I added all of the if's. lol. But i need to be able to filter this stuff depending on which fields are populated.
|
|
|
|
|
Post the code using code blocks.
You can debug by removing the dynamic blocks until only the first one is left.
If that works then add one dynamic block at a time.
Also rather than trying to remove the last term add a first fixed term. Thus your constructed where would look something like...
where 1=1 and p_Niin like '%x%' ...
|
|
|
|
|
Thanks... I didn't even think about fixing the first base where clause string like that. Much easier. and yeah, I've just been moving through the if statements. I'm sure there's just a quote or something missing.
|
|
|
|
|
Hi there! is there a way to disallow multiple connections for a login? During login, I would like the server to check whether the login is already connected or not. If it is, I would also want to send a message to the user's client that the login used is already connected. I just want to avoid the case where a user may run a client more than once with each instance connected to the server. Thanks in advance.
|
|
|
|
|
Assuming you are using SQL SErver (we need to know this as servers are different).
I would expect this to be a client requirement, most apps use a SQL Server set of credentials to connect. However sql server can identify the workstation connection if it is in the connection string and if you are using Windows authentication yeck the database knows who you are.
You can always rat through the sysobjects and find this information, it will be a challenge getting the connection event and informing the client. As said this should be done at the client.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes. I was wondering whether there is a command to set this check on the server. With the presence of the 'InfoMessage' event in the 'ADO.Net' provider, I thought of sending the message from the server so that it will be trapped in the 'InfoMessage' event for the client.
My first try was checking the connection and login from 'master..sysprocesses' using the client. I think I will have to stick to that if it will be difficult doing it on the server and also send the message.
|
|
|
|
|
I don't think what you describe would work very well -- what if the client crashes and tries to get reconnected?
What I do is have the login process assign a GUID to the "session" and the client must provide that GUID with each following access. But I have a second login effectively log the other session out.
|
|
|
|
|
Dan_K wrote: I just want to avoid the case where a user may run a client more than once with
each instance connected to the server.
Why? That is really a problem for the client app, not the database.
|
|
|
|
|
I do understand your point jschell. But I want to know how the database server can provide support. In Win32 API programming, FindWindow() can be used to detect whether the client application is already running on the computer so that I disallow multiple instances. I wrote the client application in .Net but I do not know the way around it in this Framework.
But even if that is solved, a user can run the client application from different computers using the same login name. That's what I want to avoid and the reason why I am thinking of a support from the database server.
|
|
|
|
|
Dan_K wrote: a user can run the client application from different computers using the same login name
That is a different issue to the multiple logons. PieBalds response addresses this, if you don't kill the initial session you are going to run into timeout issues. How does the server know if the initial client logon has not in fact logged off the client - you don't.
There is not built in server functionality for this as it is not a server function. There have been many kludges to address the issue, keeping track of connections/logons etc. All have their shortcomings.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dan_K wrote: That's what I want to avoid...
Still back to my point - why is that a problem?
What business requirement is going to fail if it does happen?
|
|
|
|
|
Well, users will be working on confidential data. I have been thinking of more possible threats and thought that a user may give his/her login details to someone not supposed to be part of the work who may connect from another system just to influence the results in their favour. jschell, do you think it cannot happen?
|
|
|
|
|
Dan_K wrote: Well, users will be working on confidential data.
Define what type of "users". A help desk person? Operations? A consumer?
Dan_K wrote: I have been thinking of more possible threats and thought that a user may give
his/her login details to someone not supposed to be part of the work who may
connect from another system just to influence the results in their favour.
I don't know what the last part of that means but as for the first exactly what are you doing to protect from the following scenario?
User A gives their credentials to X.
User A works on something then logs off.
User X logs on then does something 'wrong' then logs off.
|
|
|
|
|
Your client can place locking records in your database to point out that a user has loged in (like semaphore locks). At closure of the client the record must be removed.
In our system we have a special server application that checks every minute whether a lock is still valid, and otherwise removes it. Upon a crash of a client you wont have a lockout for long.
To be able to see who has accessed the data, you could make an audit system:
Log which user signed in from which computer, what he has done etc.
This can be done by various methods:
- the client app writing extra records
- triggers in the database
- the higher versions of SQLServer have tables that store information about performance of performed queries etc. Maybe those are helpfull also.
- ...
Regards ... OttO
|
|
|
|
|
Hi Dan, I don't know what programming language you are using, but here is the logic. I'm sure you can apply it on any language;
1. Create a shared folder with read/write authorization for Everyone on the server.
2. In my apps, every user has their own GUID primary key on the database.
3. Suppose one user has a primary key 0D5FF2C1-9710-47EF-86FE-164E6170EEFD . Each time this user log in to the system, check if there is a text file named 0D5FF2C1-9710-47EF-86FE-164E6170EEFD.TXT in the shared folder on the server. If it doesn't exist, go to step (4). If it exists, try to delete it. If the deletion success, then continue to step (4). If the deletion fail, then other workstation is using this login ID is being used. You might want to show some message and redirect the program flow to the exit routine.
4. Create a text file named 0D5FF2C1-9710-47EF-86FE-164E6170EEFD.TXT on the server and keep it open while the apps is open (don't close the file handle).
5. Upon exit, close the TXT file handle and delete the text file.
By locking the handle, as long as the user is login, other client could not delete the file. If the initial client crash somehow, then the lock to the text file would be released by the server OS, thus, other workstation would be able to delete the text file (and login with the user id).
Others might have better solution. Yes, this is not a SQL solution. And might not be elegant enough for most programmers/system developer. But it works
hth,
foxyland
modified 8-Mar-12 4:13am.
|
|
|
|
|
Hello
I did a stock control app in vb.net that uses an Access 2007 db.
I connect using ACE.OLEDB.12.0
The Access db is located on the server, and the app is loaded on the workstations.
They make a backup of the Access file every night. (Simply copy and paste to another location)
There are about 10 users, reading and writing the same tables
They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database"
From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc)
They are a small company, so a licensed SQL2008 server is not practical financially.
MySQL seems like a logical free option?
My Questions:
1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea?
2) Is MySQL the best choice for a db in this situation?
3) How easy it it to install and configure a MySQL database
4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?)
5) How do you back up a MySQL Db - do you need special agents?
6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers
I would be appreciative of any comments!
Thanks in advance
Richard
modified 26-Feb-12 1:20am.
|
|
|
|
|
Have you given any thoughts on using SQL Server Express?
It can do everything Access can do better, integrates very well with .net, gives you powerful upgrade options and is for free. (until you upgrade that is)
|
|
|
|
|
1) This is valid when you have both the UI and the data in Access, your UI is in VB.net so this scenario does not apply.
2) No - MySql has less support than SQL Server and Oracle (and there are other good options out there). I would recommend SQL Server Express (relevant limitation is 4gb database size) as there are a MUCH wider resource base for support and training. There is a valid upgrade path though you then need to pay. And there are many more human resources (potential employees) with SQL Server skills.
3) Very easy, the gotchas are minimal, case sensitivity (none) and collation are the one I hate, just accept the default.
4) Connection strings
5) No idea but there will be a MySQL support site dedicated to this type of question (I just don't know it)
6) SQL server used to provide an upgrade wizard for Access to SQL Server, not sure it MySQL does the same.
As you may infer I am not a fan of MySQL (also no experience) and would urge you to use SQL Server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
2) No. I use MySQL only when I have to. For small configurations like yours I recommend SQL Server Express 2008R2; it works very well, much better than Access, and has excellent support in .NET
|
|
|
|
|
2) No. SQL Server Express.
4) An ADO.net connector (not ODBC) for MySQL is downloadable; I use it to read MySQL databases created by others when I really need to.
|
|
|
|
|
Just like the rest, Use SQL Server Express 2008 R2 with management tools.
It has a 10GB limit per database. Other then taht it sure beats the hell out of Access.
If you really wanna go for open source then I reccomend PostGreSql over MySQL.
Still, I highly recommend SQL Server Express 2008R2 for the simple fact that your app is in .net.
All the best,
Dan
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
