Correct syntax for using SQL queries

Help
1

I’ve searched the forums and the resources available but actual syntax to execute a query doesn’t seem to be available.

I’m running some basic queries attempting to access MyTable within MyDatabase, the connection is good but I keep getting object errors when it reaches my FROM.

Can someone share correct syntax when writing queries using the database activities pack?

Select Top 10 From MyDatabase.MyTable Where XXX?
Select Top 10 From MyDatabase.dbo.MyTable Where XXX?

2

You only need table name if you have initialised the connection to that database already.
Either will work:
Select Top 10 * From MyTable Where XXX OR Select Top 10 * From dbo.MyTable Where XXX

You also havent declared what top 10 you want to select - include * from all columns otherwise specify which column. (more likely to be your issue)

3

HI @mgeatches

What’s the database you have? SQL server or MySQL? The way you write it is bit different in the two…

SQL Server
"Select top 10 * from MyDatabase.dbo.MyTable Where ColumnName = '" + StrValue + "'"

MySQL
"SELECT * FROM MyDatabase.dbo.MyTable Where ColumnName = '" + StrValue + "' LIMIT 10"

Let me know whether this helps :slight_smile:

4

Thanks for the replies, I’m looking for all columns to begin with while I’m testing. I’m still getting an “Invalid object name” from all 3 I’ve tried: MyTable, dbo.MyTable, MyDatabase.dbo.MyTable

5

Can you show us the exact error and the screenshot of the query you are passing in?

6

Exact error is:

Source: Execute Query
Message: Invalid object name ‘dbo.MyTable’.
Exception Type: System.Data.SQLClient.SqlException

Query is:

Select Top 10 SpecificColumnName from dbo.MyTable (have also tried just MyTable - same error)

7

Based on what the error says, it looks like it doesn’t have a table with that name. Have you double checked what the exact table name is in the database? May be there is some difference…

8

Same error across multiple tables and views from within that Database.
Is the syntax any different when hitting a view instead of a table?

9

Nope. Syntax are the same for tables and views.

Have a look at this as well…

Also try giving the table names within square brackets

[Mydb].[dbo].[table]

If you have SQL server management studio, try running the same query there as well to get it validated to make sure it’s running. So that we can easily identify whether it’s a problem with the query or with UiPath

1 Like
10

MySQL
"SELECT * FROM MyDatabase.dbo.MyTable Where ColumnName = '" + StrValue + "' LIMIT 10"

Can you explain this in detail?
Specifically why use the + signs around the variable (is that a necessary part to get the syntax correct), also why use a single quote followed by double quote when wrapping the variable? – what is the purpose of the single quote?

Thanks, I’m trying to do a longer query much like this example you gave above.

1 Like
11

Hi @css

Let me explain it to you like this using an example. We will use the same query, and say I assign a value to StrValue variable…

StrValue = “London”

Now, I will answer your questions one by one…

Yes, it is required to get the syntax correct. Think of two strings… How do we combine it?

“Hello” + " World"

The result of this is “Hello World”… It’s similar when it comes to Queries too… The query is also a string… and to combine the value in the variable and the string, we use the simple + sign just like above…

So, for now, just forget about this string stuff…

In databases, how do we write a query that has a condition under “Where” clause? simple example…

select * from myDatabase.dbo.MyTable Where ColumnName = ‘London’

That’s how we write a select query that filter the data in the result to the value of London… So in this query, you see that string values are wrapped with a single quote… That’s usual syntax in SQL

Now, coming back to your question,…

Here, the query is passed as a String and we need to pass the value we need the results to be filtered into using a varibale which is StrValue…

Earlier, we combined the two strings. Now, we need to get the SQL syntax right for the string values… So say we write the command like this

"SELECT * FROM MyDatabase.dbo.MyTable Where ColumnName = " + StrValue + " LIMIT 10"

If we print this String, the output will be

“SELECT * FROM MyDatabase.dbo.MyTable Where ColumnName = London LIMIT 10”

So… if you compare this with the actual syntax of the SQL, London will give you and error correct?

So how do we make it correct? its by adding those two single quotes… :slight_smile:

Why we do this is because when this is passed to the database, it actually sends the entire command as a string. So what we do here is actually building the SQL command with proper syntax :slight_smile:

Make sense?

1 Like
12

This does, now what if we had to actually put in a value that was itself a string, so for example rather than ColumnName = StrValue , you were actually putting in ColumnName = London . How would the quotes work for a non-variable value within the query string?

1 Like
13

It would go like this

"select * from myDatabase.dbo.MyTable Where ColumnName = 'London'"

1 Like