mysql connect and insert into

[cd1168 0]

what am i doing wrong here.. it is going through code , no error, yet sql insert is not happening

 

loop(#QuantityidsUpload) {
    plugin command("DatabaseCommands.dll", "connect to database", "server=xx;uid=xx; pwd=xxx1; database=newdatabasephase1; port=3306; pooling=false") {
        plugin command("DatabaseCommands.dll", "query", "INSERT into newdatabasephase1.idScrape (idSCR, every) value(\'{$table cell(&scrapedData, #QuantityidsUpload, 0)}\',\'{$table cell(&scrapedData, #QuantityidsUpload, 1)}\')")
        increment(#QuantityidsUpload)
    }
}

Edited September 22, 2014 by cd1168

[arunner26 51]

cd1168

The best way to debug mySQL commands is to go into phpMyAdmin in your CPANEL and run the command that you want UBOT to run.

Once the command runs perfectly in phpMyAdmin then put it into UBOT with the variables that change.

[Pete_UK 9]

?? SPACE after INSERT INTO

[arunner26 51]

Pete_UK

Good one. My eyes where reading the INTO as part of the name.

[cd1168 0]

hi and thank you for the responses.. it seems to hang.. this is part of a bigger process where i this portion is trying to upload the scrape data .. i edited above into this.. in mysql this runs ok, excepr for the ubot table i use hard coded vales instead of '{$table cell(&scrapedData, #QuantityidsUpload, 0)}  etc...

 

loop(#QuantityidsUpload) {
    plugin command("DatabaseCommands.dll", "connect to database", "server=xx;uid=xx; pwd=xxx1; database=newdatabasephase1; port=3306; pooling=false") {
        plugin command("DatabaseCommands.dll", "query", "INSERT into newdatabasephase1.idScrape (idSCR, every) value(\'{$table cell(&scrapedData, #QuantityidsUpload, 0)}\',\'{$table cell(&scrapedData, #QuantityidsUpload, 1)}\')")
        increment(#QuantityidsUpload)
    }
}

[arunner26 51]

d1168

Your MySQL command above looks OK. For testing I would make a simple SELECT query not within a loop to verify that the connection to the database is being made and is working OK.

Then I would move on to the more complicated INSERT within a loop.

[cd1168 0]

i tried this..  seems pretty simple... everything increments et nothing is inserted...

 

loop(#IDsUpload) {
    plugin command("DatabaseCommands.dll", "connect to database", "server=xx;uid=xx; pwd=xx; database=xx port=3306; pooling=false") {
        plugin command("DatabaseCommands.dll", "query", "INSERT into `scrpae `(idSCR, every)` `value`(\'123xxxx\',\'cccccccceeeeedddddd')")
        increment(#UploadRow)
    }
}

 

another query that i run earlier in the program to create the id's to scrape, works perfectly and updates.. somehere i am making a small mistake, i am sure, just do not see it

 

loop while($comparison(#idsRow, "<", #QuantityVINs)) {
    plugin command("DatabaseCommands.dll", "connect to database", "server=xxuid=xx; pwd=txx; database=xx; port=3306; pooling=false") {
        plugin command("DatabaseCommands.dll", "query", "UPDATE `production_queue_1` SET `processing` = 1, `Failed`= `Failed`+1, `SentTimestamp` = \'{#SentTimestamp}\' where `vin` = \'{$table cell(&VINsToProcess, idsRow, 1)}\'")
    }
    increment(#idsRow)
}

[cd1168 0]

when you say space after insert into, 

 

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

 

 

is this not correct format with space after into

[cd1168 0]

ok, so i know i am connecting, this inserts.. now i try insert from the ubot list, wish me luck

 

loop(#idsUpload) {
    plugin command("DatabaseCommands.dll", "connect to database", "server=xx;uid=xx; pwd=xx; database=xx1; port=3306; pooling=false") {
        plugin command("DatabaseCommands.dll", "query", "INSERT into `idScrapeDumpGOV` (idSCR, every) value('123xxxx','cccccccceeeeedddddd')")
        increment(#idsUploadRow)
    }
}

Edited September 22, 2014 by cd1168

[cd1168 0]

really weird, this returns 20 rows (expetcted).. but when i add a second column from same table only returns 1 row. for example, this works. i know i am close and i am doing something dumb 

 

loop(#QuantityidsUpload) {
    plugin command("DatabaseCommands.dll", "connect to database", "server=xx;uid=xx; pwd=xx; database=txx; port=3306; pooling=false") {
        plugin command("DatabaseCommands.dll", "query", "INSERT into `ScrapeDump` (idSCR, every) value(\'{$table cell(&scrapedData, #idsUploadRow, 0)}\','hhhhh')")
        increment(#idsUploadRow)
    }
}

 

but, this will only return 1 row

 

loop(#QuantityidsUpload) {
    plugin command("DatabaseCommands.dll", "connect to database", "server=xx;uid=xx; pwd=xx; database=txx; port=3306; pooling=false") {
        plugin command("DatabaseCommands.dll", "query", "INSERT into `ScrapeDump` (idSCR, every) value(\'{$table cell(&scrapedData, #idsUploadRow, 0)}\',\'{$table cell(&scrapedData, #idsUploadRow, 1)}\')")

        increment(#idsUploadRow)
    }
}

[arunner26 51]

cd1168

I like to write the code like below so if I am having a problem I can put an ALERT before the plugin command to look at the finished #MailerUpdateQuery or other database command. That way I may see the problem with the finished complete command to be executed.

 

define UpdateMailer {

    set(#IDtoUpdate, $table cell(&OneMailer, 0, 0), "Global")

    set(#MailerUpdateQuery, "UPDATE MailingInProcess SET DateMailed = Current_Timestamp WHERE ID = {#IDtoUpdate};", "Global")

    plugin command("DatabaseCommands.dll", "connect to database", #ConnectionString) {

        plugin command("DatabaseCommands.dll", "query", #MailerUpdateQuery)

    }

}

[cd1168 0]

i have it! mysql does not like the apostraphe's that are being scraped ...

[arunner26 51]

That make sense. Glad you found it.

[cd1168 0]

finally got it working, with a replace regular expression, removing the " ' " from the scrape attribute. .. i want to thank all who helped me ...really nice people ...

[UBotDev 276]

finally got it working, with a replace regular expression, removing the " ' " from the scrape attribute. .. i want to thank all who helped me ...really nice people ...

You are loosing some data that way....you could also escape that special character...

[cd1168 0]

hmm... i have been looking but where do i accomplish this.. i assume it is using regular expression when it sees a  '   to replace it with \' ?

[UBotDev 276]

Yes, you could use regular expression for that. Here are the replacements you need to make: http://dev.mysql.com/doc/refman/5.0/en/string-literals.html#character-escape-sequences