Database Discussion Boards

Re: How to create directory in SQL with Space

Chris Quinn6-Nov-13 1:08

Chris Quinn

6-Nov-13 1:08

Surround the directory string with double quotes

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: How to create directory in SQL with Space

Samarjeet Singh@india6-Nov-13 1:22

Samarjeet Singh@india

6-Nov-13 1:22

Thank you very much for your reply

but I already did as

Exec master.dbo.xp_cmdshell '"C:\Temp\My Test "'

but still getting facing problem

Samar

Re: How to create directory in SQL with Space

thatraja6-Nov-13 2:06

thatraja

6-Nov-13 2:06

Samarjeet Singh@india wrote:
Exec master.dbo.xp_cmdshell 'C:\My Test'\<<DATE>> then its throwing
error.

What's that? always include those details in your question.

thatraja

Please update this article Useful Reference Books, it's urgent for Q/A section

Re: How to create directory in SQL with Space

Samarjeet Singh@india6-Nov-13 2:21

Samarjeet Singh@india

6-Nov-13 2:21

For example if I tried to run in sql

Exec master.dbo.xp_cmdshell '"C:\Temp\My Test"'

Then I am getting following 3 rows of messages

1- 'C:\Temp\My' is not recognized as an internal or external command

2-operable program or batch file.

3- NULL

Thanks

Re: How to create directory in SQL with Space

Richard Deeming6-Nov-13 2:57

Richard Deeming

6-Nov-13 2:57

You need to use xp_create_subdir, not xp_cmd_shell:
http://www.mssqltips.com/sqlservertip/1460/sql-server-script-to-create-windows-directories/[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to create directory in SQL with Space

Samarjeet Singh@india7-Nov-13 4:47

Samarjeet Singh@india

7-Nov-13 4:47

Thank u very much !!!!!

its working in sql2008 but client is running in older version(Sql2000) and in older version its unable to find the reference and producing following msg

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'master.dbo.xp_create_subdir'.

is any other method so that its working in SQL2000 as well

Thanks in advance

Re: How to create directory in SQL with Space

Richard Deeming7-Nov-13 8:33

Richard Deeming

7-Nov-13 8:33

For SQL 2000, you'll need to stick with xp_cmdshell, but you need to put mkdir in front of the path you want to create:

SQL

Exec master.dbo.xp_cmdshell 'mkdir "C:\My Test\<<DATE>>"'

http://www.sqlservercentral.com/Forums/Topic604168-8-1.aspx[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to create directory in SQL with Space

Samarjeet Singh@india21-Nov-13 0:57

Samarjeet Singh@india

21-Nov-13 0:57

Thank you very much it working for me

Re: How to create directory in SQL with Space

jschell7-Nov-13 9:26

jschell

7-Nov-13 9:26

You might want to tell everyone that SQL 2000 is well past the end of life - including extended support.

http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL[^]

Re: How to create directory in SQL with Space

Minghao Xue20-Nov-13 22:03

Minghao Xue

20-Nov-13 22:03

below procedure should be the solution

create procedure sp_create_directory
(
@full_path varchar(500)
)
as
set nocount on

declare @command varchar(1000)
set @command = 'mkdir ' + @full_path

exec master..xp_cmdshell @command , no_output

set nocount off
GO

Re: How to create directory in SQL with Space

Samarjeet Singh@india21-Nov-13 0:57

Samarjeet Singh@india

21-Nov-13 0:57

Thanks for your great help

Sudden cast problem using executescalar

Gennady Oster5-Nov-13 22:25

Gennady Oster

5-Nov-13 22:25

Hi !

Here is the snip of code in question:

.......
                sql = "select " + picField + " from " + table + " where " + where;
                conn = new SqlConnection(connectionstring);
                byte[] picture = (byte[])ExecuteScalar(sql);  // Shows error
                Image source = Image.FromStream(new MemoryStream(picture));
                Image th = source.GetThumbnailImage(w, h, null, IntPtr.Zero);
.......
        public object ExecuteScalar(string strCommand)
    {
        object res=null;
        conn.Open();
        SqlCommand cmd = new SqlCommand(strCommand, conn);
        try
        {
            res = cmd.ExecuteScalar();
        }
        catch(Exception e)
        { res = e.Message; }
        finally
        {
            conn.Close();
        }
        return res;
    }

It worked like a sharm some time ago.
Code was not changed. The table definition also was not changed and field declaration is as follows:
[Picture_Image] [image] NOT NULL

But now I get error:
unable to cast object of type 'system.string' to type 'system.byte '
I suppose that something may be changed in the server and/or DB settings. But what ??

Can somebody please shed a light on this issue?

Thanks in advance.
Regards,
Gennady

My English is permanently under construction. Be patient !!

Re: Sudden cast problem using executescalar

Richard Deeming6-Nov-13 1:22

Richard Deeming

6-Nov-13 1:22

Gennady Oster wrote:
catch(Exception e){ res = e.Message; }
return res;

There is an error in your query which is causing the ExecuteScalar method to throw an exception. Due to the incredibly poor decision to return the exception message as a valid result from your ExecuteScalar method, the calling code is trying to cast the string containing the error message to a byte[], which will never work. Remove the catch clause from your method to allow the exception to propagate to the calling code.

The fact that you're specifying a WHERE clause but not passing any parameters suggests that your code is susceptible to SQL Injection[^]. Update your code to use parameterized queries before you get a visit from little Bobby Tables[^].

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Sudden cast problem using executescalar

Gennady Oster6-Nov-13 1:46

Gennady Oster

6-Nov-13 1:46

Thanks, Richard.

Saying truth, I've already understand where I made a mistake.
I anyway want ExecuteScalar to return db error, so I simply added the test is byte for result before casting:

Object sqlResult = ExecuteScalar(sql);
if (!(sqlResult is byte))
{
    return sqlResult.ToString();
}

This is the internal module, and WHERE clause is generated programmatically, so I don't see the ability to SQL Injection.

Thanks once more.

Regards,
Gennady

My English is permanently under construction. Be patient !!

how can left join two table on multiple column???

mhd.sbt5-Nov-13 9:22

mhd.sbt

5-Nov-13 9:22

hi to all
i have two table same below :
tableA with this columns:
ID
CodeGuid
Name
Family

---------------------
and tableB with this columns:
ID
CodeGuid
mycomulns

----------------------------
i want to left join this two table on ID and CodeGuid but i dont want use code same this:

select * from tableA Left JOIN
tableB on (tableA.ID = tableB.ID)
UNION
select * from tableA Left JOIN
tableB on (tableA.CodeGuid= tableB.CodeGuid)
----------------------------
is there another way to handle this output ?
thanks in advance

Re: how can left join two table on multiple column???

Richard Deeming5-Nov-13 9:58

Richard Deeming

5-Nov-13 9:58

If both columns must match:

SQL

SELECT 
   ...
FROM 
   tableA
   LEFT JOIN tableB
   ON tableA.ID = tableB.ID
   AND tableA.CodeGuid = tableB.CodeGuid

If either column can match:

SQL

SELECT 
   ...
FROM 
   tableA
   LEFT JOIN tableB
   ON tableA.ID = tableB.ID
   OR tableA.CodeGuid = tableB.CodeGuid

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Mysql select query with multiple conditions

Member 102635195-Nov-13 1:50

Member 10263519

5-Nov-13 1:50

hi,
am facing problem with the following;
my table is having 6 columns.
i want to select the data based on "from" and "to" dates,and also if i enter value in any one /two/three/.../all of the fields data should be displayed from the database.
am using this:

SQL

if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == ""))
           {
               query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
           }
           else if(R_Tag.Text != "")
           {
               query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "') AND tag_id='" + R_Tag.Text + "' ";
           }
           else if (R_Category.Text != "" && R_Tag.Text != "")
           {
               query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' AND (tag_id='" + R_Tag.Text + "' OR category_id='" + R_Category.Text + "' OR product_id='" + R_Product.Text + "'OR product_name='" + R_ProName.Text + "' OR gate_id='" + R_Gate.Text + "')";
           }

Re: Mysql select query with multiple conditions

Richard Deeming5-Nov-13 2:36

Richard Deeming

5-Nov-13 2:36

Start by reading about SQL Injection[^], and then fixing your code to use parameterized queries.

Your current approach will need 64 different queries to satisfy every possible combination. A simpler approach would look something like:

var queryBuilder = new StringBuilder();
queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate");
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);

queryBuilder.Append("AND (1 = 1");

if (!string.IsNullOrEmpty(R_Tag.Text))
{
    queryBuilder.Append(" OR tag_id = @RTag");
    command.Parameters.AddWithValue("@RTag", R_Tag.Text);
}
if (!string.IsNullOrEmpty(R_Category.Text))
{
    queryBuilder.Append(" OR category_id = @RCategory");
    command.Parameters.AddWithValue("@RCategory", R_Category.Text);
}
// Repeat for other fields...

queryBuilder.Append(")");
command.CommandText = queryBuilder.ToString();

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Mysql select query with multiple conditions

Member 102635195-Nov-13 20:22

Member 10263519

5-Nov-13 20:22

thanks for reply.
but am getting fatal error at da.Fill(ds).
and :You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1 = 1) OR tag_id = 'E2002076990B019322902641')' at line 1
here is my code: in button click;

i entered tag_id dynamically

// Connect to Mysql
String query;
System.Data.DataTable dt = new System.Data.DataTable();
String conString = "Server=localhost;Database=asset;Uid=root;pwd=root";
MySqlConnection con = new MySqlConnection(conString);
MySqlCommand command = new MySqlCommand();
command .Connection = new MySqlConnection(conString);
command.CommandType = CommandType.Text;
//command.CommandText =query;
con.Open();
MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", con);

string fromDate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string toDate = dateTimePicker2.Value.ToString("yyyy-MM-dd");
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "')";
if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == ""))
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
command.CommandText = query;

da = new MySqlDataAdapter(command);
ds = new DataSet();
da.Fill(ds);
//System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);

}
else
{
try
{

var queryBuilder = new StringBuilder();
queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate");
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);

queryBuilder.Append("AND (1 = 1");

if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = @RTag");
command.Parameters.AddWithValue("@RTag", R_Tag.Text);
}
if (!string.IsNullOrEmpty(R_Category.Text))
{
queryBuilder.Append(" OR category_id = @RCategory");
command.Parameters.AddWithValue("@RCategory", R_Category.Text);
}
if (!string.IsNullOrEmpty(R_Product.Text))
{
queryBuilder.Append(" OR product_id = @RProduct");
command.Parameters.AddWithValue("@RProduct", R_Product.Text);
}
if (!string.IsNullOrEmpty(R_ProName.Text))
{
queryBuilder.Append(" OR product_name = @RProName");
command.Parameters.AddWithValue("@RProName", R_ProName.Text);
}
if (!string.IsNullOrEmpty(R_Gate.Text))
{
queryBuilder.Append(" OR gate_id = @RGate");
command.Parameters.AddWithValue("@RGate", R_Gate.Text);
}

queryBuilder.Append(")");
command.CommandText = queryBuilder.ToString();

// command.CommandTimeout = 600;
da = new MySqlDataAdapter(command);
ds = new DataSet();
da.Fill(ds);

da.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
} }

modified 6-Nov-13 7:17am.

Re: Mysql select query with multiple conditions

Richard Deeming6-Nov-13 1:08

Richard Deeming

6-Nov-13 1:08

Member 10263519 wrote:
queryBuilder.Append("AND (1 = 1)");

You've put an extra closing bracket in that line. It needs to be:

queryBuilder.Append("AND (1 = 1");

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Mysql select query with multiple conditions

Member 102635196-Nov-13 1:38

Member 10263519

6-Nov-13 1:38

eventhough am getting same error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1=1OR tag_id = 'E2002076990B019322902641')' at line 1

Re: Mysql select query with multiple conditions

Richard Deeming6-Nov-13 1:54

Richard Deeming

6-Nov-13 1:54

I don't have a copy of MySQL to test, but why does the error message include a specific value rather than a parameter name? I would expect it to read near '(1 = 1 OR tag_id = @RTag)'.

There seems to be contradicting information on how to pass parameters to a MySQL query. You might want to try using ?name instead of @name to see if that makes any difference:

if (!string.IsNullOrEmpty(R_Tag.Text))
{
    queryBuilder.Append(" OR tag_id = ?RTag");
    command.Parameters.AddWithValue("?RTag", R_Tag.Text);
}

If it still doesn't work, try running the query in the MySQL Workbench to see if that gives you any more information.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Mysql select query with multiple conditions

Member 102635196-Nov-13 18:10

Member 10263519

6-Nov-13 18:10

if i keep , ? instead of @. i got error as:

Fatal error encountered during command execution .at da.Fill(ds) i have placed "Allow User Variables=True" in connection string.plz help me . how to run this query in workbench.

Re: Mysql select query with multiple conditions

Member 102635196-Nov-13 19:41

Member 10263519

6-Nov-13 19:41

thanks for the help:i solved it in another way:
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "'";
if(R_Tag.Text != "")
{
query=query+ " AND " + "tag_id=" + "'" + R_Tag.Text + "' ";
}
if (R_Category.Text != "")
{
query = query + " AND " + "category_id=" + "'" + R_Category.Text + "' ";
//query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' AND (tag_id='" + R_Tag.Text + "' OR category_id='" + R_Category.Text + "' OR product_id='" + R_Product.Text + "'OR product_name='" + R_ProName.Text + "' OR gate_id='" + R_Gate.Text + "')";
}
if (R_Product.Text != "")
{
query = query + " AND " + "product_id=" + "'" + R_Product.Text + "' ";
}
if (R_ProName.Text != "")
{
query = query + " AND " + "product_name=" + "'" + R_ProName.Text + "' ";
}
if (R_Gate.Text != "")
{
query = query + " AND " + "gate_id=" +"'" + R_Gate.Text + "' ";
}

unique random alphanumeric in MySQL

Jassim Rahma5-Nov-13 0:14

Jassim Rahma

5-Nov-13 0:14

hi,

I want to know how can I created a unique random alphanumeric PIN in MySQL? I have job_order_code field and I want everytime to set a unique random alphanumeric code?

Thanks.

Technology News @ www.JassimRahma.com

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