Database

Re: how to get select parameter too in input parameter in stored procedure

28-Jun-12 8:49

Dhyanga wrote:
I don't know whelther we can send fieldname as input parameter to get the value of that field.

You could join on the system-tables, but to what use? You'll be concatenating constants in a way that can hardly be considered helpfull. If writing Sql is that much of a problem, consider a ORM-framework.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Dhyanga28-Jun-12 8:55

Re: how to get select parameter too in input parameter in stored procedure

28-Jun-12 8:55

I had got the required output simply writing the sql query but I wanted it on stored procedure. I know i can do it something like this in my query.

string colname;
int ID;
string query;
.
.
.
query = "select " + colname + " from ItemList where ID = " + ID ;
SqlCommand cmd = new SqlCommand(query, sqlconn);
.
.
.
.

This had solved my problem but I wanted it on stored procedure. and I was stuck giving the column name itself as input parameter.

suchita

Eddy Vluggen28-Jun-12 9:16

Re: how to get select parameter too in input parameter in stored procedure

28-Jun-12 9:16

I can only point to jschells' comment Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Dhyanga28-Jun-12 9:17

Re: how to get select parameter too in input parameter in stored procedure

28-Jun-12 9:17

Thanks Eddy for your time and views. Smile | :)

suchita

jschell28-Jun-12 8:53

jschell

28-Jun-12 8:53

Dhyanga wrote:
I don't know whelther we can send fieldname as input parameter

You can't.

Your choices are
1. Create the SQL dynamically in C# and then execute it.
2. Create the SQL dynamically in SQL and then execute it in SQL.
3. Use a fixed set of fixed SQL statements and select one based on the data passed in.

Re: how to get select parameter too in input parameter in stored procedure

Dhyanga28-Jun-12 8:56

Re: how to get select parameter too in input parameter in stored procedure

28-Jun-12 8:56

Thanks Jschell.. Just curious if that can be possible. If not, i can just execute it without using stored procedure. I was trying to use better way than that if I could...

suchita

jschell29-Jun-12 8:31

jschell

29-Jun-12 8:31

Dhyanga wrote:
i can just execute it without using stored procedure

My solutions work with or without stored procs.

Re: how to get select parameter too in input parameter in stored procedure

Karthik Harve28-Jun-12 20:13

Karthik Harve

28-Jun-12 20:13

Hi,

try like this..

SQL

CREATE PROCEDURE [dbo].[SelectItemList]
    @ColName string,
    @ID int

AS
BEGIN TRANSACTION
    SELECT @ColName = ColumnName FROM ItemList where ID = @ID 

COMMIT TRANSACTION

Karthik Harve

Re: how to get select parameter too in input parameter in stored procedure

Mycroft Holmes28-Jun-12 23:46

Mycroft Holmes

28-Jun-12 23:46

Am I missing something, you need dynamic SQL to meet this requirement, you solution does not work!

Never underestimate the power of human stupidity
RAH

Re: how to get select parameter too in input parameter in stored procedure

Michael Potter29-Jun-12 3:25

Michael Potter

29-Jun-12 3:25

You have to use sp_executesql and construct the query string yourself. Do realize that you can open yourself up to SQL injection attacks if you don't properly test the variables.

SQL

CREATE PROCEDURE SelectItemList
(
     @ColName string,
     @ID int
)
AS

     DECLARE @sql NVARCHAR(4000)

     SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10))

     EXEC sp_executesql @sql

Re: how to get select parameter too in input parameter in stored procedure

Dhyanga29-Jun-12 3:38

Re: how to get select parameter too in input parameter in stored procedure

29-Jun-12 3:38

Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.

suchita

Michael Potter29-Jun-12 3:44

Michael Potter

29-Jun-12 3:44

Glad I could help.

Re: how to get select parameter too in input parameter in stored procedure

jschell29-Jun-12 8:56

jschell

29-Jun-12 8:56

Keep in mind that as formatted that solution is open to SQL injection attacks.

Regarding mdf file

lalitN26-Jun-12 22:48

lalitN

26-Jun-12 22:48

Hi All,
How we make the .mdf file of a database ?
I want a script or C# code that could be used in a utility in C#.Net.

Please help !!

Regards,
Lalit

modified 27-Jun-12 4:58am.

Eddy Vluggen27-Jun-12 0:22

27-Jun-12 0:22

lalitN wrote:

How we make the .mdf file of a database ?

That extension is used both for Sql Server as well as Microsoft Access. Which are you targetting?

Bastard Programmer from Hell Suspicious | :suss:

lalitN27-Jun-12 0:24

lalitN

27-Jun-12 0:24

I am using SQL Server 2008 R2.

Eddy Vluggen27-Jun-12 0:37

27-Jun-12 0:37

The command to create a database is documented here[^], examples are at the bottom of the page.

Bastard Programmer from Hell Suspicious | :suss:

lalitN27-Jun-12 1:41

lalitN

27-Jun-12 1:41

Hi,

Can we create the mdf file by taking the backup of one existing database ?

Regrds,
Lalit

Eddy Vluggen27-Jun-12 1:46

please help me with sqlite3 problem.

27-Jun-12 1:46

Yes. Backup the existing database, and restore[^] it. Keep in mind that this procedure will convert "older" database versions (backups from 2005) to the format of your server (2008R2).

Bastard Programmer from Hell Suspicious | :suss:

mimi052526-Jun-12 20:04

26-Jun-12 20:04

Hi, everyone, I'm using sqlite 3.6.22 in an ARM-linux based application, and I was annoyed a lot by a memory-leak problem.
this is the problem:
sqlite3_free_table(char **result) doesn't exactly release the memory allocated by sqlite3_get_table function.
To verify it I made it called dirrectly after sqlite3_get_table() function, but I can still access the azResult parameter.
bellow is part of my code:

sqlite3 *db;
char *sqlcmd;
int nrow ;
int ncolumn ;
char **azResult;
char *zErr;
int rc;
rc = sqlite3_open("/usr/YY_AEACard.db", &db);
if (rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return -1 ;
}

sqlcmd = sqlite3_mprintf("select CardID,CardType,BgnDate,EndDate,ValidTimes,CardRight,CardFlag, EnabledPorts,LastAccessPort,CardPsw from tb_CardInfo where CardID=%d and EnabledPorts = %d\n",card->CardID, nPort );
sqlite3_get_table( db, sqlcmd, &azResult, &nrow, &ncolumn, &zErr );
sqlite3_free( sqlcmd );

///////////////////////////////////////////////////////////
sqlite3_free_table(azResult);
///////////////////////////////////////////////////////////
// it still works when i do this here.

if (rc != SQLITE_OK) {
if (zErr != NULL) {
fprintf(stderr, "SQL error: %s\n", zErr);
sqlite3_free(zErr);
}
}

if(nrow <= 0)
{
printf("No data from db!\n");
return -1;
}
if(ncolumn < 9)
{
printf("Columns are not enough for card table!\n");
return -1;
}
bzero(card, sizeof(card));
card->CardID = atoi(azResult[ncolumn+0]);
card->CardType = atoi(azResult[ncolumn+1]);
strcpy(card->BgnDate, azResult[ncolumn+2]);
strcpy(card->EndDate, azResult[ncolumn+3]);
card->ValidTimes = atoi(azResult[ncolumn+4]);
card->CardRight = atoi(azResult[ncolumn+5]);
card->CardFlag = atoi(azResult[ncolumn+6]);
card->EnabledPorts = atoi(azResult[ncolumn+7]);
card->LastAccessPort = atoi(azResult[ncolumn+8]);
strcpy(card->CardPsw, azResult[ncolumn+9]);
sqlite3_free_table(azResult);
//this is a test for the memory leak
printf("%s\n", azResult[4]);
//funny enough, it still works here.
sqlite3_close(db);
return 0;

please help me, thank you.

Peter_in_278026-Jun-12 21:02

Peter_in_2780

26-Jun-12 21:02

There is no guarantee that the memory pointed to is overwritten when it is released. In my experience, only a very few debugging memory managers bother to overwrite released memory blocks. So what you observe is not evidence of a memory leak. Your system may have some functions to query the memory manager for such things as the total allocated memory size.

Cheers,
Peter

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

mimi052526-Jun-12 21:17

26-Jun-12 21:17

Thank you Peter, we usually meet a "Segment fault" when we access to the memory released, and further more, the memory of my application keeps expanding with the size of the data loaded from database, never the end.

Peter_in_278026-Jun-12 21:35

Peter_in_2780

26-Jun-12 21:35

My guess is that sqlite is keeping various things in memory as long as you have the database open. Is all the memory freed when you close the database(s)? Or, at least, does the memory usage stop increasing when you close and reopen the database?

Peter

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

mimi052526-Jun-12 22:03

26-Jun-12 22:03

Yes, Peter, SQL Server manages its memory this way, I don't exactly know how sqlite does with it. but what ever, it shouldn't expand to crash the operation system.

mimi052526-Jun-12 22:13