Tech Off Thread

12 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

traditional ADO insert

Back to Forum: Tech Off
  • User profile image
    Jachin

    I am writing a vbs script that parses a series of files and inserts selected information into a database. This script will be run at midnight ever night. So i am faced with this problem:

    1. For each record i'd like to insert i need to check if it is there first
    2. How can i avoid a new query for each row that i need to insert?

  • User profile image
    blowdart

    Jachin wrote:

    I am writing a vbs script that parses a series of files and inserts selected information into a database. This script will be run at midnight ever night. So i am faced with this problem:

    1. For each record i'd like to insert i need to check if it is there first
    2. How can i avoid a new query for each row that i need to insert?



    Wrap it in a stored procedure that does the check as well? If it's there, don't insert, don't error. Or are you stuck with a database that doesn't do stored procs? If so, depending on how many records, you could pull your unique keys out first, for a large one time hit at startup and then check existance in the script.

  • User profile image
    gregoryw

    You could use an INSERT statement with a WHERE clause to check whether the data you plan to insert already exists.

    Something like this:

    INSERT INTO MyTable (id)
        SELECT id
           FROM SourceData
           WHERE id NOT IN (SELECT id FROM MyTable)

    or this:

    INSERT INTO MyTable (id)
        SELECT id
        FROM SourceData
        WHERE NOT EXISTS (SELECT id FROM MyTable WHERE MyTable.id = SourceData.id)

  • User profile image
    blowdart

    gregoryw wrote:
    You could use an INSERT statement with a WHERE clause to check whether the data you plan to insert already exists.

    Something like this:

    INSERT INTO MyTable (id)
        SELECT id
           FROM SourceData
           WHERE id NOT IN (SELECT id FROM MyTable)

    or this:

    INSERT INTO MyTable (id)
        SELECT id
        FROM SourceData
        WHERE NOT EXISTS (SELECT id FROM MyTable WHERE MyTable.id = SourceData.id)


    Parameterise it please! @id, then even if you can't use stored procs you can still use ADO parameters and not have to worry about sql injection.

  • User profile image
    W3bbo

    blowdart wrote:
    you can still use ADO parameters and not have to worry about sql injection.


    news to me, please do tell more Smiley

  • User profile image
    blowdart

    W3bbo wrote:
    blowdart wrote:you can still use ADO parameters and not have to worry about sql injection.


    news to me, please do tell more Smiley


    Oh! Really?

    Ok, consider

    insert into insecticide (description) 
    values ('Another channel9 post about it!');
    heh.

    Now I'm sure we all know the dangers here, so I'm not going to explain them.

    However you can mitigate by using sql parameters.

    insert into insecticide (description) 
    values (@description);
    Now it looks just like a stored proc would, correct? Thing is it doesn't have to be one; simple create an ADO command using the line above and attach a parameter to it named @description;

    using( SqlConnection con = yourConnection ) 
    {
    con.Open();
    using( SqlCommand cmd =
    new SqlCommand("insert into insecticide (description) values (@description);",
    con) )
    {
    cmd.Parameters.Add("@description", descriptionValue);
    using( SqlDataReader rdr = cmd.ExecuteReader() )
    {
    ...
    }
    }
    }
    ADO will take care of escaping and all the other bits and pieces for you. Or it should anyway.

    So even if you're stuck with an older version of MySQL you can still protect yourself.

  • User profile image
    footballism

    W3bbo wrote:
    blowdart wrote:you can still use ADO parameters and not have to worry about sql injection.


    news to me, please do tell more Smiley

    For more infomation about SQL injection attack, please refer to this article in MSDN

    Sheva

  • User profile image
    W3bbo

    footballism wrote:
    W3bbo wrote:
    blowdart wrote:you can still use ADO parameters and not have to worry about sql injection.


    news to me, please do tell more Smiley

    For more infomation about SQL injection attack, please refer to this article in MSDN

    Sheva


    I know about SQL injection, and I'd heard about ADO Params a while back, but I didn't think they were used like the way blowdart demonstrated.

  • User profile image
    blowdart

    W3bbo wrote:


    I know about SQL injection, and I'd heard about ADO Params a while back, but I didn't think they were used like the way blowdart demonstrated.


    Well enjoy Smiley

  • User profile image
    Maurits

    Jachin wrote:

    1. For each record i'd like to insert i need to check if it is there first
    2. How can i avoid a new query for each row that i need to insert?


    Depends on the table...

    In some circumstances you can get away with declaring a "unique" index on the relevant fields, and checking the "ignore duplicates" box.  Then inserts that violate the uniqueness are ignored - no effect, no error.

  • User profile image
    Jachin

    In the end the best approach seemed to be a disconnected recordset. I'm working with about 2500 records so it seems fairly fast.

    What I did:
    1. created a disconnected recordset
    2. used the Find method to see if the values was already there
    3. If there, update otherwise insert.

    Works a treat too.

  • User profile image
    sappyvcv

    blowdart wrote:
    Oh! Really?
    I didn't know about that. That actually makes for cleaner code too, thanks! edit: Quote tag isn't working, d'oh

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.