DevX.com Forums - Database

SqlExec() doesnt work correctly

hoangnnm — Sat, 21 Nov 2009 03:40:29 GMT

Hi all , i m using visual foxpro and Sql 2000 .
When i try to excute a SqlExex( spContent ) it return 1 but nothing changed in the database . But if i use the sql query analyzer , paste the spContent 's content and run directy from there , every thing is ok ! I cant find the problem , may someone help !
Ps : the variable spContent' s length is about 5000 and was created with a Text ... Exdtext Statement .

OO representation of relational database

secretagentman — Wed, 18 Nov 2009 16:34:15 GMT

I'm trying to convert 3 relational db tables (ones just like in access) into OO conceptual representations in which I need to note connectivity and (mandatory or optional) participation.

I have a "computer" table, a "made in" table, and a "description" table.

The computer table includes the made in & description PK's as FK's - How do I reference them in a simple OO diagram?

Custom Auto Number (Access 2007)

dualution — Tue, 17 Nov 2009 04:05:47 GMT

I have a database that requires an auto number sequence as such,

Box 1 A1
Box 1 A2
.
.
.up to>
.
.
Box 1 A10
Box 1 B1
.
.
.up to>
.
.
Box 1 J10

After Box 1 J10>

Box 2 A1
Box 2 A2

etc.

Any help with how to create this sequence automatically appear would be much appreciated.

Thanks in advance.

Passing parameters from short cuts/Icons and VFP

equsnarnd — Sat, 14 Nov 2009 20:38:51 GMT

If I pass a 0 to as a parameter into a VFP program it interprets it as data type integer - which is what I would expect. But when I set up an icon to call an VFP exe and use 0 as a parameter it is changed to data type character. Is there a way to force it to integer? I put code in the program to change it back to an integer but it annoys me that the OS seems to pass my integer parameter as data type character.

Thanks

Help with optimizing stored procedure

n3tx — Wed, 11 Nov 2009 00:47:23 GMT

Hey!
I Need help with optimizing my sp.
The sp works fine, but it takes away long time to load. Like 30 sec, that is toooo much.
So I would apreciate any help.

Here's the sp:

ALTER PROCEDURE [dbo].[sp_GetCompaniesByKeywords]
@KeyWhat varchar(150) = NULL, -- what to search for
@KeyWhere varchar(150) = NULL, -- where to search
@PageSize int = NULL, -- page size
@PageNumber int = NULL -- current page

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RowStart int
DECLARE @RowEND int

IF (@PageNumber > 0)
BEGIN
IF (@PageNumber <=1)
SET @RowEND = @RowStart + @PageSize +1

SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber+1
SET @RowEND = @RowStart + @PageSize - 1;

DECLARE @sql nvarchar(4000)
SELECT @sql =
'Select DISTINCT ROW_NUMBER() OVER (ORDER BY C.[Name]) AS RowNumber, COUNT(*) OVER() AS Count,
c.ID, C.[Name] AS CompanyName, C.Orgnumber AS orgnr,
a.Address, a.zipcode, a.county,
p.Phone,
act.[Name] AS Activity
FROM Companies as C
JOIN Addresses AS a ON a.Company_ID = C.ID
JOIN PhoneNumbers AS p ON p.Company_ID = C.ID
JOIN Activities AS act ON act.ID = C.Activity_ID
WHERE 1=1'

IF (@KeyWhat IS NOT NULL AND @KeyWhat <> '')
SELECT @sql = @sql + ' AND (C.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') +
' OR (C.Orgnumber LIKE ' + quotename(@KeyWhat + '%', '''') +
' OR p.Phone = ' + quotename(@KeyWhat, '''') +
' OR act.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') + '))'

IF (@KeyWhere IS NOT NULL AND @KeyWhere <> '')
SELECT @sql = @sql + ' AND (a.address LIKE ' + quotename('%' + @KeyWhere + '%','''') +
' OR (a.zipcode LIKE ' + quotename('%' + @KeyWhere + '%','''') +
' OR a.county LIKE ' + quotename('%' + @KeyWhere + '%','''') + '))'

--PRINT @sql

CREATE TABLE #Result
(
RowNumber int,
[Count] int,
id int,
companyName varchar(150),
orgnumber varchar(20),
address varchar(150),
zipcode varchar(5),
county varchar(20),
phone varchar(20),
activity varchar(100)

)
INSERT INTO #Result(rownumber, [count], id, companyname, orgnumber, address, zipcode, county, phone, activity)
EXEC sp_executeSQL @sql

SELECT * From #Result WHERE RowNumber Between @RowStart and @RowEND
DROP Table #Result
END
END

search stored procedurre

n3tx — Mon, 09 Nov 2009 23:53:24 GMT

Hello, I am having some problems with a search query.
The problem I am having is that I get duplicate values when I am joining two tables.
Everything worked fine until I joined a second table (Addresses)

Table Companies (ID (unique), CompanyName, Orgnumber ...)
Table Addresses (Company_ID (not unique), Address .. )
-- table can have more than one address with the same Company_ID

Here is my sp:

ALTER PROCEDURE [dbo].[sp_GetCompaniesByKeywords]
@KeyWhat varchar(150) = NULL, -- what to search for
@KeyWhere varchar(150) = NULL, -- where to search
@PageSize int = NULL, -- page size
@PageNumber int = NULL -- current page

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RowStart int
DECLARE @RowEND int

IF (@PageNumber > 0)
BEGIN
IF (@PageNumber <=1)
SET @RowEND = @RowStart + @PageSize +1

SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber+1
SET @RowEND = @RowStart + @PageSize - 1;

DECLARE @sql nvarchar(4000)
SELECT @sql =
'Select DISTINCT ROW_NUMBER() OVER (ORDER BY C.[Name]) AS RowNumber, COUNT(*) OVER() AS Count,
c.ID, C.[Name] AS CompanyName, C.Orgnumber AS orgnr, cat.Name AS Genre
FROM Companies as C
JOIN Categories AS cat ON cat.ID = c.Genre_ID
JOIN Addresses AS a ON a.CompanyID = C.ID
WHERE 1=1'

IF (@KeyWhat IS NOT NULL AND @KeyWhat <> '')
SELECT @sql = @sql + ' AND C.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') +
' OR cat.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') +
' OR C.Orgnumber LIKE ' + quotename(@KeyWhat + '%', '''')

IF (@KeyWhere IS NOT NULL AND @KeyWhere <> '')
SELECT @sql = @sql + ' AND a.address LIKE ' + quotename('%' + @KeyWhere + '%','''') +
' OR a.zipcode LIKE ' + quotename('%' + @KeyWhere + '%','''') +
' OR a.county LIKE ' + quotename('%' + @KeyWhere + '%','''') +
' OR a.othertext LIKE ' + quotename('%' + @KeyWhere + '%','''')

--PRINT @sql

CREATE TABLE #Result
(
RowNumber int,
[Count] int,
id int,
companyName varchar(150),
orgnumber varchar(20),
kategori varchar(100)

)
INSERT INTO #Result(rownumber, [count], id, companyname, orgnumber, kategori)
EXEC sp_executeSQL @sql

SELECT * From #Result WHERE RowNumber Between @RowStart and @RowEND
DROP Table #Result
END
END

// Daniel

pl/sql: dynamic record type?

astehman — Mon, 09 Nov 2009 14:31:38 GMT

I am new to writing PL/SQL (usually have DBA to do this) and have tried googling how to do this, but think I am not searching with the right terminology. (Please pardon me if I don't get the terminology right here either!) Would extremely appreciate any tips/references on this, as I've pored thru several online docs and haven't found what I need yet.

What the pl/sql needs to do:
1. select multiple columns from table A (master table), which will return only 1 row
2. select 1 column from table B (a one-to-many table), which will return N rows
3. return the data as if it were all one row, with the data from step 2 appearing to be columnar, i.e. COL_1, COL_2, ..., COL_N.

So the pl/sql would need to create N # of columns for the return record type, 1 column for each row returned in step 2.

Specifics: There is a document table which has a 1-many table containing the CC's for that document. Only one row will ever be returned from the master doc table, and 0-n rows from the CC table. There could be no CC's for the doc, or there could be any number. I need to be able to return all the columns from the document table PLUS the CC's as if they were all one row in a table.

So example of a return row might be a doc with n # of cc's

DOC_ID | DOC_TYPE | DOC_OWNER | | CC_1 | CC_2 | ... | CC_n

or a doc with all its fields and no cc's

DOC_ID | DOC_TYPE | DOC_OWNER |

I hope this makes sense. Thanks to any one who can help out the java code slinger. :)

Sub query question

landslide — Thu, 05 Nov 2009 15:48:07 GMT

I have am trying to write a subqueriy.I have three columns, Column A has a code like break, lunch the other two columns have a date and time. I am trying to do a sub query so I can group by column A times and count how many. Then do the same with column 2 in the same query. I can do it in seperate querys but not in a sub query or is thier an eaiers way here is my code.

Code:

SELECT Count(nettalley.STATE_CODE_1), Nettalley.STATE_CODE_1

FROM Nettalley

WHERE FormatDateTime([nettalley.STATE_TIME_1],4)="00:00"

GROUP BY Nettalley.STATE_CODE_1;

Convert Record Set to View - SQL Server 2005

stoneheart — Wed, 04 Nov 2009 20:42:07 GMT

I'm hoping someone can point me to the right direction for this. I want to be able to convert my existing query which returns multiple records into a view that would look sort of like a spreadsheet.

My query that returns something like this:

1 Number_of_Bolts 11
2 Number_of_Nails 325
3 Number_of_Pins 1355

The query itself is:

select question_id, question_name, question_value
from question_data d
inner join question_dictionary i
on d.question_id = i.question_id
where master_id = '2111'

Given this, how do I get it into a format where all the records are on the same row spreadsheet style and the key identifier like Number_of_Bolts is a cell header with 11 as the value underneath it? I want this be a view in SQL Server.

Thanks for any hints you all can provide.

Stored Procedures Help please

badmanmc — Sun, 01 Nov 2009 06:24:17 GMT

I have made stored procedures in Oracle...but need to export them to mysql. I have searched on google but cant figure out the syntax.

Here is the oracle procedure:

create or replace procedure delgroup
(g_id in number)

is

begin

DELETE FROM grouptable WHERE GROUP_ID=g_id;

commit;
end;

I jus basically want to delete the group from the group table..depending on which group id (the arguement) the user types in.

How would i do this exact same procedure in mysql?

Please some assistance guys :)

Relationship to Entity Sets

supernix — Fri, 30 Oct 2009 02:40:44 GMT

Hello,

I am working on creating a relationship schema from Access for class.
I was wondering if I should have a link from each and every entity set to the others? Our teacher is allowing one to many and one to one but not many to many. It is a video store database project for class.

Thanks in advance for the help.

ORacle...

unenforcibility — Thu, 29 Oct 2009 01:23:14 GMT

Is it used for buisness only?

Database Design Help

SCass2009 — Mon, 26 Oct 2009 21:33:14 GMT

First of all, hi to everyone and thanks in advance for any help!!! :)

problem is this - I'm creating a database/front end for a friend who runs his own computer business. He needs to store customer details as well as job details, ranging from repairs, data recovery, website design and so on. any database work i've done before has had one product or "job", so would each "job" here be stored in its own separate table, linked by another orders table

eg a customer books a data recovery job, all details of the job are stored in tblDataRecoveryJobs

tblCustomerDetails ---> tblJobs ---> tblDataRecoveryJobs
CustID JobID JobTypeID
CustID
JobTypeID

at first I had one giant table for jobs, with details of all types jobs being stored in it, although by going through the normalisation process wouldn't the one big table be broken down into the smaller ones anyways?

sorry if it sounds a bit confusing, any help at all is greatly appreciated :)