Do you use ODBC or OLEDB or another API?

N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
When you set up connection strings to applications from the databases which API do you use?

I'm pretty green however I have always used ODBC, but recently I have been using OLEDB and I like it so much better. When used with VBA the flexibility it so much greater. I build prototypes using Access and Excel a lot and now I am able to add additional interactive features to the dashboards, etc.


  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    If connecting to SQL Server, my order of preference is SQLNCLI, OLE, ODBC. Don't know if Access supports SQLNCLI, but anything is better than JET.
    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    I apologize I connect to a SQL server database all the time. If I use access I am passing queries through to the real database :)

    I usually use Excel however if the data set returns starts to get over a million rows then I have to leverage Access, that's the other reason why I use Access.

    I've never heard of or used SQLNCLI. Can you tell me more about it, why you like it? I really like OLE now to be honest, but I am always up for suggestions.
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    I primarily use SQLNCLI (SQL Server Native Client) because it is the new standard. My understanding is that SQLOLEDB has been deprecated and won't be supported after SQL Server 2012. SQLNCLI is 'supposedly' faster and has 'more features', though I've never tested the performance difference myself or seen a table comparing feature-by-feature. I would use OLE or ODBC only if I needed to be able to easily change from, say, SQL Server or Oracle or another RDBMS.

    This MSDN article might help clear things up.
    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    I've read a few articles this one I have no though. Thanks for following up I appreciate it.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    Carl I find that the native client is limited when working with VBA Excel and Access. I like using OLE, or ADO when working with databases and returning to Access or Excel. I find the querying in VBA makes more sense and I am able to build message boxes to pass variable tot he where clause in the VBA. Are you able to do that with SQLNCLI using C#?
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    Yes, but it's important to use parameterized queries, especially if you are using input from the user. This helps prevent SQL injection attacks. C# provides a special class to help with this, called SqlParamater. So you could do something like this:

    //you have accepted intId as an input parameter and already sanitized it at some level to ensure it is an integer

    //Normally you would get this from config or another resource, not hard code it
    string connectionString = "Provider=SQLNCLI;Server=Server\Instance;Database=DBName;Uid=UserName;Pwd=Password;"

    //set up the connection
    using (SqlConnection conn = new SqlConnection(connectionString))

    //set up the command object
    SqlCommand command = new SqlCommand();
    command.CommandText = "SELECT * FROM customers WHERE id = @id";

    //set up the parameter
    SqlParameter idParam = new SqlParameter();
    idParam.ParameterName = "id";
    idParam.Value = intId;

    //add the parameter to the command

    //execute the query and get the results
    //Choose how you want to execute and retrieve your results here

    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    Carl sorry for the late reply.

    I am reviewing your code I think I understand the premise.

    In VBA you would declare the dimension then set it. In the select I would ' variable ' then build a message box after the SQL was scripted. Calling the the variable from the message box.

    So when the procedure is ran the message box fires and then you have your input parameter.
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    That is a common way to do it, but what happens if, rather than a search parameter, the user inputs a single quote to terminate your SQL, types in some SQL of their own and then a comment char to terminate the rest of your SQL? This is called a SQL Injection attack.

    This the reason for parameterization. It is much like encoding user input on a web site. Once the SQL is parameterized, the user's input is treated exactly as you specify.

    In this case, you want to treat whatever is input as a string and not allow it to escape from the string context to the SQL command context. A SQLParameter object accomplishes this for you. This might not be a concern for you depending on your user base.

    Try this SQL out to demonstrate:
    CREATE TABLE #myTable(
    num int,
    name varchar(max)
    CREATE TABLE #protectedData(
    ssn varchar(max)
    INSERT INTO #myTable (num, name)
    VALUES (1, 'Foo')
    INSERT INTO #protectedData (ssn)
    SELECT * FROM #myTable
    SELECT * FROM #protectedData
    --Your intended query. The user tyes foo
    SELECT * FROM #myTable WHERE name = 'foo'
    --Now instead of foo the user types ' SELECT * FROM #protectedData --
    SELECT * FROM #myTable WHERE name = 'foo ' SELECT * FROM #protectedData--'
    A much scarier result:

    (Edited to add images)
    When you go the extra mile, there's no traffic.
Sign In or Register to comment.