|
hello
I'm using "OPEN-FOR", not "EXECUTE IMMEDIATE" because I want to do multi rows query. However, "OPEN-FOR" won't take a variable "strSQL" in its FOR clause, it only takes fixed strings?
create or replace function fnEnumSystemUser
(
LogonFilter nvarchar2,
...
AdditionalWhereClause nvarchar2,
OrderByClause nvarchar2
)
RETURN xxxxx.cursorType
AS
strSQL nvarchar2(2000);
FormatAdditionalWhereClause nvarchar2(2000);
FormatOrderByClause nvarchar2(2000);
SystemUserCursor xxxxx.cursorType;
BEGIN
IF AdditionalWhereClause IS NULL THEN
FormatAdditionalWhereClause := ' ';
ELSE
FormatAdditionalWhereClause := TRIM(AdditionalWhereClause);
END IF;
IF OrderByClause IS NULL THEN
FormatOrderByClause := ' ';
ELSE
FormatOrderByClause := TRIM(OrderByClause);
END IF;
strSQL := 'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id ';
-- This won't work, simply says "Warning: compiled but with compilation errors" when I tried to create function
open SystemUserCursor FOR strSQL;
-- This won't either, on invoke "ORA-01006: bind variable does not exist"
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x :y ' using LogonFilter, OrderByClause ;
-- This also failed (on invocation, "ORA-00900: invalid SQL statement")
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x' || OrderByClause using LogonFilter;
RETURN SystemUserCursor ;
END;
This is how I invoked it:
declare
NumItemsSelected int;
lstResult xxxxx.cursorType;
TYPE SystemUserRecordType IS RECORD
(
Id numeric(19,0),
FirstName nvarchar2 (50),
MiddleName nvarchar2 (50),
LastName nvarchar2 (50),
PrimaryEmail nvarchar2 (190),
PersonType int,
CreateDate timestamp,
CreatedBy numeric(19,0),
LastUpdate timestamp,
LastUpdateBy numeric(19,0),
Logon nvarchar2 (75),
PasswdHash int,
IsSuspended char,
ExpiryDate timestamp
);
oUser SystemUserRecordType;
begin
lstResult := fnEnumSystemUser (... '%',... ' Person.PrimaryEmail like ''%a%'', ' order by Logon ASC',0,10,NumItemsSelected);
LOOP
fetch lstResult into oUser;
exit when lstResult%notfound;
dbms_output.put_line('Id: ' || oUser.Id);
END LOOP;
end;
I need ability to append to SQL string because in the end say the least I need to append:
1. ORDER BY clause
2. Call a FUNCTION in WHERE Clause
3. Paging and ROW_NUMBER() clause
What can I do? Thanks!
dev
|
|
|
|
|
Hi,
I would like to know your opinion on which software would be the best RAD around? Actually, I develop in Filemaker, when you are good it's damn fast to develop a solution, but it's quite limited in features.
Is there something similar in the open-source market?
Thank for your idea
|
|
|
|
|
I'm working on a project which will be completely contained on a 1 gig USB drive. I'm trying to determine whether I should use SQL Server CE or some other flavor of SQL. I remember there being some type of SQL that created an SDF file which seemed to be a standalone SQL database. I also remember that one including the ability to encrypt data. So, my questions are:
1) Can SQL Server CE work on a USB stick that's plugged into a computer or is it only for handheld devices?
2) Does SQL Server CE support encryption?
3) What was the version of SQL Server that produced the SDF file?
4) Does that version support encryption?
5) What do I use to create that version?
And finally (whew!)
6) What do you recommend I use and why?
I'd rather stay with SQL Server becuase the database this product will have to sync with is a SQL Server (full) database and I figure a SQL Server flavor will play nicer with it.
Thanks!!!
Denise "Hypermommy" Duggan
|
|
|
|
|
Hi,
I do recall a thread on "SQL Server Compact Edition", if was described as "just a couple of dlls that let you use a single .sdf file as a database. Similar limits to Express (4gig db limit etc) - but it is also missing stored procs."
Hope this helps.
modified on Tuesday, May 5, 2009 3:21 PM
|
|
|
|
|
Hypermommy wrote: 1) Can SQL Server CE work on a USB stick that's plugged into a computer or is it only for handheld devices?
You can run it from USB without any problems.
Hypermommy wrote: 2) Does SQL Server CE support encryption?
Yes, explained on MSDN here[^].
Hypermommy wrote: 3) What was the version of SQL Server that produced the SDF file?
I don't quite understand this question. The latest version of SQLCE is 3.5, you can get an overview of the different versions here[^].
Hypermommy wrote: 5) What do I use to create that version?
I'm mostly creating them from code. I tried using "SQL Server Management Studio", and it can handle .sdf files as well.
Hypermommy wrote: 6) What do you recommend I use and why?
SQLCE has the advantage that you needn't have a SQL Server instance installed on the local machine. If there's only one user accessing the data and the datamodel is relative simple, go for SQLCE. Keep in mind that SQLCE isn't a full database-server and that some features will be lacking.
There's a nice article on replication here[^]
I are troll
|
|
|
|
|
Hi, thanks for looking
Im devoloping a system (to buy and sell houses), and im having some isues with my current DB design and .net datasets, so i wonder how you guys make it, any advices will be appreciated, thanks
The table is.....Addresses
The thing is that many objects in y system can have an address, for example:
Customer
House
Notary
Employee
Office
And some others
So i decided to put the AddressID in the table corresponding to the object
Table Customers
CustomerID
...
AddressID
Table Houses
HouseID
...
AddressID
Table X
XID
...
AddressID
The thing is that im using .net datasets with foreing keys, wich wont let me add a house row if i havent created the address row, so i have to add automatically an address row before the house row, this has some isues, like i have to set all nonnullable values in address row to something before adding this row. It wouldnt make any sense to ask for the adress before the customer or house general info.
So im just wondering, what would be some other approach
Aditional Info:
Each object can only have one address
1 customer = 1 address
1 house = 1 address
...
Thanks in advance for any feedback
Alexei Rodriguez
|
|
|
|
|
Your initial design motivation was a good one, but is it really necessary to extract the House Address in your database to a separate table? (Same is true for Customer)
The idea behind making a foreign key reference is to eliminate storing duplicate data. In your situation, if you store the House and Customer Address in their respective tables, you wouldn't be storing duplicate data.
IMHO, This sounds like an "overdesign" of your DB schema. Oops.
|
|
|
|
|
I didn't get that from his explanation, I got that there is 1 address table and a FK to all the object table. This is a correct design and I would stick with it.
I would however change the FK constraint so there can be a customer without an address etc.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft, glad to hear that the design is not that bad
I decided to go with this approach, because i didnt want to repeat columns (street, number, city...) in the tables for customers, houses, employees... so all of them have an AddressID column wich is a foreing key to the Address table
Thanks
Alexei Rodriguez
|
|
|
|
|
You're design is correct and good, I think your problem may be that the FK forces a constraint that an address record is required, relax this constraint and you are then able to have a cunstomer without an address record and everything works out nicely!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks David for the suggestion
Ill add the address info in the same table for some objects
And use the separate address table for others like customers, because one never knows... what if later they decide to add more than one address to a customer, employee...
Alexei Rodriguez
|
|
|
|
|
|
Hi,
I am trying to Export Data From SqlServer Table Into Multiple Excel Worksheets. I have more than 65000 records which can not fit into one Excel worksheet.
I am using BCP command in a stored procedure to export. I have searched in google and could not get solution to export to mulitple sheets from stored procedure. Please let me know if anyone can help me on this.
Thanks for helping.
|
|
|
|
|
What I would do is create ranges to export. For example, say the data is customer data, you could easily create 26 separate files by exporting customers by the first letter of their last name. Create an A file, B file, etc.
If your table is accounting in nature, use ranges of the account numbers.
Ex: Account: 1000-1999, 2000-2999, etc
|
|
|
|
|
Thank you for your answer. I am looking at the same Excel File with different sheets.
Please let me know.
|
|
|
|
|
You can still divide the data extract into multiple extracts, then import them into individual sheets.
Maybe I didn't understand your problem.
I thought you were extracting data, then manually importing it into Excel, but after reading your question again I see that you are exporting data directly into Excel.
I would still break your extract into multiple chunks and import those chunks into separate worksheets in the same Excel workbook.
Take a look at this article, maybe it will be helpful ...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926[^]
|
|
|
|
|
Hi to everyone,
I wanted to give a password in my MS access database, which I will be accessing from different systems in the LAN through a VB.NET application.
Now when i give a password (by opening in exclusive mode) and try to test the connection(using a con.udl file which i created in desktop in order to get the connection string), it shows an error saying workgroup information missing.
is my approach correct? Please help me for the same(all i want is to give a password and access it in the network from an vb.net application)
Thanks in advance
|
|
|
|
|
There are two methods for securing a Microsoft Access database;
1) Database-password
This is like password-protecting a zipfile. You'll need the password to open the database. This can be done from within the Access-application.
2) System.mdw
In that case, every user has a username and a password. The workgroup information is stored in the "System.mdw" file.
You'd want the first option, I believe. A connectionstring with a database-wide password can be found here[^].
Good luck
I are troll
|
|
|
|
|
thanks for your reply
But could you please tell me why am I getting the error.(when I try to get the exact connection string using con1.udl which I have created in the desktop )
thanks in advance
-----------------------------
I am a beginner
|
|
|
|
|
Probably because MS Access expects a workgroup-database. More information on that can be found here[^].
I are troll
|
|
|
|
|
Good Morning All
i have the Following Results
DESCR CYCLES
---------------------------------------
Galloway A Ms 20 21
Gama, E 20 21
Giatsi M Ms 20 21
Gibbon F Prof 20 21
Gibson C Ms 20 21
Gilfillin, P 20 21
Galloway A Ms 21 20
Gama, E 21 20
Giatsi M Ms 21 20
Gibbon F Prof 21 20
Gibson C Ms 21 20
Gilfillin, P 21 20
Now all this are Correct but i need to get rid of a duplicates. Check the Bolded part
Gilfillin, P 21 20 . i have reproduced this table from the Following
I have a First Stored Procedure doing the Following
ALTER PROC [dbo].[sp_Get_Staff_Cycles]
(
@ACTIVITY_ID INT
)
AS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]
SELECT S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN tbl_Cycles_Staff C
ON C.IDL = MTN.ID
WHERE MTN.ACTV = @ACTIVITY_ID
and lets Follow the Final table and the following Stored Procedure that does the Following
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_Get_Staff_Cycles_internal]
AS
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL_NEW_FINAL]'))
DROP TABLE FINAL_NEW_FINAL
--ADD THIS TO A NEW TABLE NAMED 'FINAL_NEW_FINAL'
Select t1.DESCR, STR(t1.CYCLETEMPLATE) + ''+ STR(t2.CYCLETEMPLATE) AS [CYCLES]
INTO FINAL_NEW_FINAL From FINAL t1, FINAL t2
Where t2.DESCR = t1.DESCR
And t2.CYCLETEMPLATE <> t1.CYCLETEMPLATE;
--Get the Missing Records
--INSERT THE ONE THAT WAS MISSING
INSERT INTO FINAL_NEW_FINAL
SELECT DESCR ,CYCLETEMPLATE
FROM FINAL
WHERE DESCR NOT IN (SELECT DESCR FROM FINAL_NEW_FINAL)
-- Final Data for Rendering
SELECT DESCR,CYCLES FROM FINAL_NEW_FINAL
Now this above sp produce those Duplicates
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: INSERT INTO FINAL_NEW_FINAL SELECT DESCR ,CYCLETEMPLATE FROM FINAL
Change your 'SELECT' to 'SELECT DISTINCT'.
Tim
|
|
|
|
|
Good day Tim
The "DISTINCT" is not doing the Job, still it brings Duplicates.
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
And if you change
And t2.CYCLETEMPLATE <> t1.CYCLETEMPLATE
to
And t2.CYCLETEMPLATE > t1.CYCLETEMPLATE
Wout Louwers
|
|
|
|
|
That is the Most Amazing Answer i have ever got Today.
You have made my day
It worked Like Charm
Vuyiswa Maseko
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|