Database Discussion Boards

Colin Angus Mackay10-Apr-05 20:53

10-Apr-05 20:53

A batch is a group of SQL commands, including calls to stored procedures, that are processed in sequence.

A stored procedure is a procedure made up of SQL commands which you can then call from other SQL code.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Sql Stored procedure

turbochimp11-Apr-05 10:24

turbochimp

11-Apr-05 10:24

In addition to the previous post, stored procedures are compiled.

The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

ExecuteReader in Business layer?

IlanTal9-Apr-05 23:02

IlanTal

9-Apr-05 23:02

I have an MFC project which I want to update to ADO.NET. The user can choose which columns to query on and display, so it looks to me that ExecuteReader is more appropriate than a fixed dataset.

I more or less see how do it directly:
m_OleDbConnection->Open(); // Open up the connection
m_OleDb = new OleDbCommand(S"select * from Persons", m_OleDbConnection);
m_Reader = m_OleDb->ExecuteReader();
where I basically import the string of the OleDbCommand from an outside source.

What I'd like to know is: can this (or should this) be done with a 3 tier setup with a business layer? The examples I've seen basically fill a dataset, using the Merge command on the client side.

Is it possible (or desirable) to return a reader object, which I can then parse on the client side? If so what command is used in place of Merge? Any suggestions on sample code?

Thanks,
Ilan

Re: ExecuteReader in Business layer?

Mike Dimmick10-Apr-05 9:25

Mike Dimmick

10-Apr-05 9:25

OleDbDataReader derives from MarshalByRefObject. You wouldn't get a copy of the data; instead (if it worked at all) you'd get a proxy which would marshal calls back from the client tier to the business object tier. This is likely to be really, really slow as the DataReader interface is quite chatty.

I'd recommend either sticking to DataSet or using custom objects.

Stability. What an interesting concept. -- Chris Maunder

Re: ExecuteReader in Business layer?

IlanTal10-Apr-05 19:30

IlanTal

10-Apr-05 19:30

Thanks Mike for the advice. I had the feeling I was on the wrong track and you confirmed my feeling.
I still have the feeling that I should be using a business layer because I've got several machines interfacing to the same database. It looks like a good idea to pack away the database connections in a different layer.
My "problem" with the dataset is that each time I ask for a different combination of columns. Is this a real problem, or am I putting up a straw man? In other words, can I make a dataset with all the columns and then just fill some of them, or do I need to create each time a dataset on the fly with just the columns I am actually filling?

If I suppose that I need to create a dataset on the fly, then another question comes up. In the end, I show the data in a list control box. The question is: would it be better to make a temporary dataset and then read the data from it into the list control, or pass a pointer to the list control and fill it directly? What bothers me here is that it seems that I'm passing part of the application logic to the business layer.

So my question returns to: can I use one, predefined dataset, where I only fill in part of the columns, or do I need to define each time a temporary dataset with just the columns I fill?

Thanks,
Ilan

SQL Server: Views over Select Statements

matthias s.9-Apr-05 10:29

matthias s.

9-Apr-05 10:29

Hi there,

what is the advantage of using a View instead of directly sending a query to the table? The only thing that comes into my mind is to restrict direct table access and allow the developer only to mess around with the view. Are there performance advantages?

Thanks for your time!

/matthias

I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]

Re: SQL Server: Views over Select Statements

Colin Angus Mackay9-Apr-05 16:36

Colin Angus Mackay

9-Apr-05 16:36

Matthias Steinbart wrote:
The only thing that comes into my mind is to restrict direct table access and allow the developer only to mess around with the view

The conditions that make up the view get precompiled so that the query operates marginally faster than sending the query direct.

You base the view on multiple tables which means you can access normalised data in a denormalised form as if it were one table.

Unioning many physical tables in to one large view. On one project I work on there is a physical table for each year because the quantity of data is so large. It can be accessed through a view which represents the whole set. IIRC, this is called horizontal partioning.

I don't use views much, so there may be other uses. I tend to use stored procedures for everything.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: SQL Server: Views over Select Statements

matthias s.9-Apr-05 23:45

matthias s.

9-Apr-05 23:45

Hi Colin,

thanks for your reply. I actually didn't know that views get compiled.

Colin Angus Mackay wrote:
IIRC, this is called horizontal partioning.

What does IIRC mean? I made up "If I remember correctly"... Unsure | :~

Is this correct?

I've just reviewed a pretty large and well functioning Web-App and I've seen that those people as well used stored procedures for just anything. Made sense to me.

BTW, I yesterday read your article about SQL injections. Very interesting topic and well explained. You got my 5 on that one Smile | :)

/matthias

I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]

Re: SQL Server: Views over Select Statements

Colin Angus Mackay10-Apr-05 0:29

Colin Angus Mackay

10-Apr-05 0:29

Matthias Steinbart wrote:
What does IIRC mean? I made up "If I remember correctly"...

Yes, that's right - I shouldn't used these abbreviations, especially as I get on at others for using "U" and "R" and other forms of text/sms-speak.

Matthias Steinbart wrote:
I yesterday read your article about SQL injections. Very interesting topic and well explained. You got my 5 on that one

Thanks. Smile | :)

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

ADO connection to Access Database with HTA

D4damage9-Apr-05 7:44

D4damage

9-Apr-05 7:44

I've done the following HTA to connect to MyDB.MDB and open all records in MyTable, the connection seems ok, but when I go to display it in a span (DBData), it get the following error:

Object required: 'DBData'

Any help with this would be appreciated.

<html>
<head>
<title>Database Connection</title>

<HTA:APPLICATION ID="objDBCreate" APPLICATIONNAME="DBCreate" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="normal">
</head>

<style>

BODY
{
background-color: buttonface;
font-family: Helvetica;
font-size: 8pt;
margin-top: 2px;
margin-left: 8px;
margin-right: 3px;
margin-bottom: 3px;
}

</style>

<script language=VBScript>
set conn=CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "MyDB.MDB"

set rs=CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM MyTable", conn
rs.MoveFirst

strHTML = "<table border='1' " & _
"style='border-collapse: collapse' " & _
"bgcolor='white' bordercolor='black' " & _
"width='50%' id='Table1' >"
Do Until rs.EOF
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("Computername") & "</td>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("IPAddress") & "</td>"
strHTML = strHTML & "</tr>"
rs.MoveNext
Loop
strHTML = strHTML & "</table>"

rs.Close
conn.Close
Set rs=Nothing
Set conn=Nothing
DBData.InnerHTML = strHTML

</script>

<body>

<span id="DBData"></span>

</body>
</html>

Re: ADO connection to Access Database with HTA

samuelalvesg28-Apr-21 14:03

samuelalvesg

28-Apr-21 14:03

try to declare the body and span before the script

HTML

<pre><html>
<head>
<title>Database Connection</title>

<HTA:APPLICATION ID="objDBCreate" APPLICATIONNAME="DBCreate" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="normal">
</head>

<style>

BODY
{
background-color: buttonface;
font-family: Helvetica;
font-size: 8pt;
margin-top: 2px;
margin-left: 8px;
margin-right: 3px;
margin-bottom: 3px;
}

</style>

<body>

<span id="DBData"></span>

<script language=VBScript>
set conn=CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "MyDB.MDB"

set rs=CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM MyTable", conn
rs.MoveFirst

strHTML = "<table border='1' " & _
"style='border-collapse: collapse' " & _
"bgcolor='white' bordercolor='black' " & _
"width='50%' id='Table1' >"
Do Until rs.EOF
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("Computername") & "</td>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("IPAddress") & "</td>"
strHTML = strHTML & "</tr>"
rs.MoveNext
Loop
strHTML = strHTML & "</table>"

rs.Close
conn.Close
Set rs=Nothing
Set conn=Nothing
DBData.InnerHTML = strHTML

</script>

</body>
</html>

Insert Binary value using QueryAnalyzer

WDI9-Apr-05 0:35

WDI

9-Apr-05 0:35

How i can insert a crypted string into a field of record(its type is Binary)using Query Analyzer?

Crystal Reports - Filtering by 'Guid' field

Kfir.C8-Apr-05 14:49

Kfir.C

8-Apr-05 14:49

Hi everyone,

My problem:
I have a DB where primary/foreign keys are of type Guid.
I was trying to filter in my report all records where foreign key is Guid X.

Somehow, no records are selected!

Observing the sql query reveals:
....WHERE `tbA`.`ParentID`='{guid {B44D446E-F50C-43DD-882C-66A6130D0EEF}}'

What am I doing wrong? Confused | :confused:

I'm using CR (Ver. 9) for Visual Stodio .Net 2003

Questions about "go"

mtone8-Apr-05 9:43

mtone

8-Apr-05 9:43

I am using ODBC.Net and am having a problem executing some sql. Why is it that if my statment has a 'go' batch terminators i can not execute them thru ODBC.NET. I need to remove the go and it works. But some of my Sql does not like it if I remove the go. I have no control over the sql so I can not recode my sql.

Can someon tell me why this is and is there a way around the go.

Thanks

Re: Questions about "go"

Scott Serl8-Apr-05 10:42

Scott Serl

8-Apr-05 10:42

GO is not a TSQL command. SQL server does not recognize it; it is a SQL tool command which tells the tool to send the pending commands to the server for processing. If your statements do not work without the GO, then you will have to send them to the server in separate commands just like query analyzer.

Re: Questions about "go"

mtone8-Apr-05 16:24

mtone

8-Apr-05 16:24

After I posted this and had a brief discussion with a co-worker I realized this. My solution is to parse the files into a collection of code blocks and execute each block individually then go to the next file.

Thanks for the help

shift the contents of one list box to another

Anonymous8-Apr-05 2:51

Anonymous

8-Apr-05 2:51

hi everyone, howz life?

I have a problem, working with list boxes.

My requirement is,
I want to move the contents of one list box to the other list box, on the button click event. In ASP.

Please, Can anyone help me out in this.

Re: shift the contents of one list box to another

Colin Angus Mackay8-Apr-05 3:08

Colin Angus Mackay

8-Apr-05 3:08

You might want to try the ASP.NET forum - this forum is for databases (SQL / ADO / ADO.NET)

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

connecting to MSDE 2000 with ado.net

Anonymous7-Apr-05 23:32

Anonymous

7-Apr-05 23:32

First, is it possible to connect to MSDE 2000 server from another computer with ado.net? I only managed to connect from the same computer that is running the server.

If it is possible, what aspects should i take into account (or simply how should i do it). And is it possible to use an IP adress in the connectionstring?
What about firewalls?

I have never done client/server applications and im trying to build one to learn something new.

If you need more info please ask, and PLEASE anserw.

R
Johan

Re: connecting to MSDE 2000 with ado.net

Mike Dimmick8-Apr-05 13:11

Mike Dimmick

8-Apr-05 13:11

Yes, it's possible. New MSDE installs, by default, have the network protocols disabled. This is a security mitigation - if the server cannot be reached over the network, it cannot be hacked remotely.

To enable network protocols, use the Server Network Utility, svrnetcn.exe in Microsoft SQL Server\80\Tools\Binn. You can also configure this at install time by setting the DISABLENETWORKPROTOCOLS property to 0.

See this article[^] for more.

Stability. What an interesting concept. -- Chris Maunder

manual incrementation of a field

cristina_tudor7-Apr-05 21:16

cristina_tudor

7-Apr-05 21:16

Hi there,

I have a table with a column that I need to fill in with consecutive numbers (the field, currently, has the same number for all the rows).
I want to do this in the SQL Server Query Analyzer.

Could you please give me some hints about how can I do this?

Thanks a lot,

Cristina

Re: manual incrementation of a field

Ritesh12348-Apr-05 3:58

Ritesh1234

8-Apr-05 3:58

Hi,
Try this ..........

<br />
DECLARE @Counter as int<br />
SET @Counter = 1<br />
<br />
DECLARE @ID as int<br />
<br />
DECLARE Table_Cursor CURSOR FOR<br />
SELECT TableID<br />
FROM TableName <br />
<br />
<br />
OPEN Table_Cursor<br />
<br />
FETCH NEXT FROM Table_Cursor INTO @ID<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
    Update TableName<br />
    SET<br />
	SerialCol = @Counter<br />
    WHERE TableID = @ID	 	<br />
    FETCH NEXT FROM Table_Cursor INTO @ID<br />
END<br />
<br />
CLOSE Table_Cursor<br />
DEALLOCATE Table_Cursor

I hope this will help u...............:->

Reagrds,
Ritesh

Re: manual incrementation of a field

Ritesh12348-Apr-05 20:48

Ritesh1234

8-Apr-05 20:48

oops!!!! Forgot to increment @Counter i.e

FETCH NEXT FROM Table_Cursor INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
Update TableName
SET
SerialCol = @Counter
WHERE TableID = @ID
SET @Counter = @Counter + 1
FETCH NEXT FROM Table_Cursor INTO @ID
END

Regards,
Ritesh

Passing ref cursor between 2 databases

laavsjj7-Apr-05 20:29

laavsjj

7-Apr-05 20:29

I want to execute a procedure in database2 from a procedure in database1. i want to pass a refcursor and get back the resultset. Is it possible to pass cursor variable in RPC? If not please let me know, how it can be done.

Thanks

reporting services sql server 2000

tvprithiv6-Apr-05 23:19

tvprithiv

6-Apr-05 23:19

how to create a custom data source in sql server 2000 reporting services

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.