Database Discussion Boards

Re: parameter with the In function

jschell28-Oct-10 8:29

jschell

28-Oct-10 8:29

Tamimi - Code wrote:
then how you can pass the selected ids to a stored procedure ??

Presuming that you really did mean stored procedure then...

First step, determine based on to create the stored procedure in the language supported by the database. Options that I have used for variable argument lists.
- Arrays
- Varchar with values as a comma separated values in that list.
- Proc with up to X args and of which can be null.
- Several procs each one with an increasing number of parameters: first has 5, second has 10, etc.
- dynamic SQL, run in a proc (only suitable for situations where input is known to be secure.)

Once you do in fact have a proc then you write code which populates the parameters dependent on the type of proc that actually exists.

Conversely without a proc, and just using SQL, one creates the SQL from scratch using code (for loops, string concatenation, etc) with the appropriate number of bind variables. Then one populates the bind variables. Then you run it.

To my mind the last option is easier than any solution with procs.

Re: parameter with the In function

John Gathogo27-Oct-10 0:01

John Gathogo

27-Oct-10 0:01

I guess your parameter might be comprising of a delimited string, say, "1,2,4,8" or even "{Guid1},{Guid2},...". If such is your requirement, you can always use some little tweaks.

Like
Declare a temporary table to store the Ids, like declare @Ids table(Id int)
Then, use some string functions to split the string and insert the ids into the table. (Like, CHARINDEX, SUBSTRING, etc)
After that, you can then use the temporary table on your WHERE ... IN ... clause

Like
select * from tbl_Name where id in (select Id from @Ids)

Re: parameter with the In function

Tamimi - Code27-Oct-10 0:18

Tamimi - Code

27-Oct-10 0:18

thank you

this is nice. i will give it a try.

or simply Big Grin | :-D

string strIds = "1,2,3,4";
SqlCommand com="select * from tbl_name where id in(" + strIds + ")";

When you get mad...THINK twice that the only advice
Tamimi - Code

Re: parameter with the In function

David Mujica27-Oct-10 3:26

David Mujica

27-Oct-10 3:26

Note: I'm not sure about MS-SQL, but in Oracle there is a limit to the number of values you can have in an "IN" clause. Back in Oracle 9, I believe the max was 1000.

for example: select * from myTable where ID IN (1,2,3,....,1000,1001) would fail because there was more than 1000 values listed.

I'm not sure if this is still the case, but you might want to consider it in your design.

Good luck. Thumbs Up | :thumbsup:

Re: parameter with the In function

jschell28-Oct-10 8:42

jschell

28-Oct-10 8:42

David Mujica wrote:
Back in Oracle 9, I believe the max was 1000.

And at least in 10 as well.

http://forums.oracle.com/forums/thread.jspa?threadID=428758[^]

Re: parameter with the In function

Goutam Patra27-Oct-10 1:47

Goutam Patra

27-Oct-10 1:47

Use Table valued parameter[^]

and try google for more example.

Re: parameter with the In function

Tamimi - Code27-Oct-10 2:42

Tamimi - Code

27-Oct-10 2:42

this is new to me Smile | :)

thank you... will read about it

When you get mad...THINK twice that the only advice
Tamimi - Code

Oracle to SQL porting [modified]

Arudya26-Oct-10 17:54

Arudya

26-Oct-10 17:54

Hi,

I am pretty new to database section. I tried porting a table created in Oracle to SQL Server 2005. In Oracle, i could see a column defined holding the user details. The column is defines as given below.
CREATE_USER VARCHAR2(255 BYTE) DEFAULT USER NOT NULL

My doubt is regarding default value USER.

I would like to know
1) What will be the value keyword "USER" will be returning in ORACLE. Is it an in built property or function which returns the default user?
2) And the equivalent value to be used for "USER" in SQL Server 2005?

Please help

Thanks,
Arudya

modified on Wednesday, October 27, 2010 12:01 AM

Re: Oracle to SQL porting

Chris Meech27-Oct-10 3:14

Chris Meech

27-Oct-10 3:14

The value returned for USER will be the userid that was supplied to establish the connection. If SQL*Plus is available, login to an oracle database and try the command select user from dual. It should return the same value as you supplied as the userid when you logged in. As for the equivalent in SQL Server, I'd suggest try googling for an equivalent. Smile | :)

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]

Inserting in Multi table With one Store Procedure

future383926-Oct-10 12:29

future3839

26-Oct-10 12:29

Hi,
I have a form which including different sort of information. for instance, some information is regarding to personal data which related to Table1 and some are for Table2 and the some are for Table3. all this information are in 1 form.

Now, I just wondering to know, when I wanna insert data via Store procedure do I have to write SP for each of them?
is there any approach to insert data with writing just one SP.

how can I write SP, inserting data in Multi table. is it possible

Re: Inserting in Multi table With one Store Procedure

PIEBALDconsult26-Oct-10 15:07

PIEBALDconsult

26-Oct-10 15:07

If you insist on using stored procedures then there should be one that performs all the required statements in a transaction that can be rolled back.

Many practitioners seem to forget that by definition a procedure should contain more than one statement.

Re: Inserting in Multi table With one Store Procedure

Mycroft Holmes26-Oct-10 20:13

Mycroft Holmes

26-Oct-10 20:13

future3839 wrote:
is there any approach to insert data with writing just one SP

This is a basic aspect of an SP

Receive values via variables in the procedure declaration

CREATE PROC StoreDumy
-- Declare
	@Var1 VARCHAR(100),@Var2 VARCHAR(100),@Var3 VARCHAR(100),@Var4 VARCHAR(100),
	@Var5 VARCHAR(100),@Var6 VARCHAR(100),@Var7 VARCHAR(100)

Insert the variables in the tables required. You may want to wrap the inserts in a transaction so that if 1 insert fails all inserts are rolled back and you are not left with orphaned data

As

DECLARE @ID int

begin TRAN	
	INSERT dbo.Country (Fld1, Fld2)
	VALUES(@Var1,	@Var2)
	
	SET @ID = SCOPE_IDENTITY()
	
	INSERT dbo.State (CtryID, Fld1, Fld2)
	VALUES(@ID, @Var3,	@Var4)
	
	SET @ID = SCOPE_IDENTITY()
	
	INSERT dbo.Town (StateID, Fld1, Fld2)
	VALUES(@ID, @Var5,	@Var6)	
	
COMMIT TRAN

Never underestimate the power of human stupidity
RAH

Official PostgreSQL forum

Jassim Rahma26-Oct-10 4:51

Jassim Rahma

26-Oct-10 4:51

anyone has the URL for the english official forum of PostgreSQL NOT the Postgre Advanced Server..

Re: Official PostgreSQL forum

Mycroft Holmes26-Oct-10 12:24

Mycroft Holmes

26-Oct-10 12:24

jrahma wrote:
anyone has the URL

And you want to use a database product that you cannot even find the URL for. What level of support do you think you are going to get for this DB. Oh wait, you are looking here, a MS centric site, for support Confused | :confused:

Never underestimate the power of human stupidity
RAH

Re: Official PostgreSQL forum

Corporal Agarn27-Oct-10 0:56

Corporal Agarn

27-Oct-10 0:56

Mycroft Holmes wrote:
Oh wait, you are looking here, a MS centric site, for support

Thanks, good point, I could not fathom why the question got down voted. I guess I look at things too narrowly.

Re: Official PostgreSQL forum

Mycroft Holmes27-Oct-10 1:13

Mycroft Holmes

27-Oct-10 1:13

djj55 wrote:
I could not fathom why the question got down voted

Some twat who probably has not heard of Postgre.

I nearly got involved in it in the 90s but chose Access spit instead and from there went naturally into SQL Server. I had just spent some years working with SuperBase only to have it sold 5 times in as many years and it got totally marginalised. I'll stick to mainstream!

Never underestimate the power of human stupidity
RAH

deploy PostgreSQL database

Jassim Rahma26-Oct-10 3:56

Jassim Rahma

26-Oct-10 3:56

How can I deploy PostgreSQL database in InstallShield 2010 so it will install the database server and create my database as well?

create script for entire database in PostgreSQL

Jassim Rahma26-Oct-10 3:55

Jassim Rahma

26-Oct-10 3:55

how can I create a script for the entire database inPostgreSQL including the structure, rows, functions, views, etc?

Re: create script for entire database in PostgreSQL

Jules VDV26-Oct-10 4:31

Jules VDV

26-Oct-10 4:31

You could use PgAdminIII and it's database backup function. This allows you to select which objects you would like to re-create. This could then be run using restore from either PgAdmin or the command line, depending on your requirements

SQL Server CE : How to create a database from C++ with ADO

Leif Goodwin26-Oct-10 2:11

Leif Goodwin

26-Oct-10 2:11

I have a demo application that uses SQL Server via ADO to create and populate a database. I connect to the master database and then execute a "CREATE DATABASE ..." SQL statement. Here is the connect:

bool CMsSqlDemoView::Connect(LPCTSTR szDatabase)
{
Disconnect();

m_DBConnection.CreateInstance(__uuidof(Connection));
if (!m_DBConnection)
{
m_listErrors.AddString("Failed to create ADO::Connection instance");
return(false);
}

m_DBConnection->CursorLocation = adUseClient;

// Note: The "sqloledb" provider does not fully support XML.
// For XML support, the Provider and DataTypeCompatibility values must be set as indicated.

CString strConnection;
strConnection.Format(
"Provider=SQLNCLI10;DataTypeCompatibility=80;Integrated Security=SSPI;Data Source=%s;Database=%s;Trusted_Connection=Yes;",
m_strDataSource.GetString(),
szDatabase);

HRESULT hr = E_FAIL;
bool bResult = true;
try
{
hr = m_DBConnection->Open(strConnection.GetString(), "", "", adConnectUnspecified);
}
catch(_com_error &e)
{
CString strText;
strText.Format("Error code '%d' : '%s'", e.Error(), (LPCTSTR)e.Description());
m_listErrors.AddString(strText);
bResult = false;
}
return(bResult);
}

So far so good. I have now created a second demo application that uses SQL Server CE 3.5. I set the provider as "Microsoft.SQLSERVER.CE.OLEDB.3.5" but I am unable to create a connection object, and I have no idea how to create a database since I cannot connect to a master database. Anyone know how to do this (C++)? Thanks. BY the way I don't want to use managed C++, or OLEDB.

modified on Tuesday, October 26, 2010 8:52 AM

Re: SQL Server CE : How to create a database from C++ with ADO => SOLVED

Leif Goodwin26-Oct-10 3:33

Leif Goodwin

26-Oct-10 3:33

In case anyone else needs the answer, I had to use the MS_ADOX::_Catalog COM interface which is imported from msadox.dll. For example:

MS_ADOX::_CatalogPtr catalogPtr;
catalogPtr.CreateInstance(__uuidof(MS_ADOX::Catalog));
catalogPtr->Create("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\Temp\\sqlcedemo.sdf;");

The above crap non error checking code creates the C:\\Temp\\sqlcedemo.sdf database, which can then be opened and populated using ADO in the normal fashion.

Field ordering in a table

john john mackey25-Oct-10 7:10

john john mackey

25-Oct-10 7:10

is there an advantage to laying out fields of certain data types in a particular order?

For example, in MS Access 2007, is it best to have all Memo fields at the end of the table definition?

Thanks,
JJ

Re: Field ordering in a table

J4amieC25-Oct-10 21:21

J4amieC

25-Oct-10 21:21

No, there's not.

Re: Field ordering in a table

i.j.russell25-Oct-10 23:32

i.j.russell

25-Oct-10 23:32

Blog post by Kimberly Tripp

Re: Field ordering in a table

Mycroft Holmes26-Oct-10 3:04

Mycroft Holmes

26-Oct-10 3:04

While there is no technical benifit I have always followed these rules

Primary keys is the first field (I almost never use complex keys) and is usually an identity or a GUID inder duress

Foreign key fields (you know all this ####ID fields)

Primary description field(s)

All other fields

Modified and ModifiedBy are generally the last 2 fields on most tables.

Never underestimate the power of human stupidity
RAH

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