creating a looped SQL QUERY using RODBC in R
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
First and foremost - thank you for taking your time to view my question, regardless of if you answer or not!
I am trying to create a function that loops through my df and queries in the necessary data from SQL using the RODBC package in R. However, I am having trouble setting up the query, since the parameter of the query change through each iteration (example below)
So my df looks like this:
ID Start_Date End_Date
1 2/2/2008 2/9/2008
2 1/1/2006 1/1/2007
1 5/7/2010 5/15/2010
5 9/9/2009 10/1/2009
How would I go about specifying the start date and end date in my sql program?
here's what i have so far:
data_pull <- function(df) {
a <- data.frame()
b <- data.frame()
for (i in df$id)
{
dbconnection <- odbcDriverConnect(".....")
query <- paste("Select ID, Date, Account_Balance from Table where ID = (",i,") and Date > (",df$Start_Date,") and Date <= (",df$End_Date,")")
a <- sqlQuery(dbconnection, paste(query))
b <- rbind(b,a)
}
return(b)
}
However, this doesn't query in anything. I believe it has something to do with how I am specifying the start and the end date for the iteration.
If anyone can help on this it would be greatly appreciated. If you need further explanation, please don't hesitate to ask!
r rodbc
add a comment |
First and foremost - thank you for taking your time to view my question, regardless of if you answer or not!
I am trying to create a function that loops through my df and queries in the necessary data from SQL using the RODBC package in R. However, I am having trouble setting up the query, since the parameter of the query change through each iteration (example below)
So my df looks like this:
ID Start_Date End_Date
1 2/2/2008 2/9/2008
2 1/1/2006 1/1/2007
1 5/7/2010 5/15/2010
5 9/9/2009 10/1/2009
How would I go about specifying the start date and end date in my sql program?
here's what i have so far:
data_pull <- function(df) {
a <- data.frame()
b <- data.frame()
for (i in df$id)
{
dbconnection <- odbcDriverConnect(".....")
query <- paste("Select ID, Date, Account_Balance from Table where ID = (",i,") and Date > (",df$Start_Date,") and Date <= (",df$End_Date,")")
a <- sqlQuery(dbconnection, paste(query))
b <- rbind(b,a)
}
return(b)
}
However, this doesn't query in anything. I believe it has something to do with how I am specifying the start and the end date for the iteration.
If anyone can help on this it would be greatly appreciated. If you need further explanation, please don't hesitate to ask!
r rodbc
Never usepaste
concatenation for SQL queries, risking at best syntax errors, at worst SQL injection (whether intentional or not). Instead, useRODBCext
(related question: stackoverflow.com/q/16178640/3358272) (unlessRODBC
has started doing it directly, I'm really surprised they have not implemented it yet as it's rather fundamental to good DB practices). cran.r-project.org/web/packages/RODBCext/vignettes/…
– r2evans
Jan 3 at 16:18
1
thank you @r2evans I will learn this and try and implement this.
– yungpadewon
Jan 3 at 16:21
1
Second, don't use repetitiverbind
like that, it will crush performance in the long run (each call torbind
makes a complete copy of all data preceding and being added). SinceRODBCext::sqlExecute
directly supports vectorizing with a frame (see cran.r-project.org/web/packages/RODBCext/vignettes/…, section 2.3.2), there's no need for a loop at all.
– r2evans
Jan 3 at 16:24
Yes,rbind
run inside a loop results in quadratic copying. Always avoid growing objects in loops
– Parfait
Jan 3 at 16:39
thank you guys @Parfait I am gonna try and re-write this
– yungpadewon
Jan 3 at 16:40
add a comment |
First and foremost - thank you for taking your time to view my question, regardless of if you answer or not!
I am trying to create a function that loops through my df and queries in the necessary data from SQL using the RODBC package in R. However, I am having trouble setting up the query, since the parameter of the query change through each iteration (example below)
So my df looks like this:
ID Start_Date End_Date
1 2/2/2008 2/9/2008
2 1/1/2006 1/1/2007
1 5/7/2010 5/15/2010
5 9/9/2009 10/1/2009
How would I go about specifying the start date and end date in my sql program?
here's what i have so far:
data_pull <- function(df) {
a <- data.frame()
b <- data.frame()
for (i in df$id)
{
dbconnection <- odbcDriverConnect(".....")
query <- paste("Select ID, Date, Account_Balance from Table where ID = (",i,") and Date > (",df$Start_Date,") and Date <= (",df$End_Date,")")
a <- sqlQuery(dbconnection, paste(query))
b <- rbind(b,a)
}
return(b)
}
However, this doesn't query in anything. I believe it has something to do with how I am specifying the start and the end date for the iteration.
If anyone can help on this it would be greatly appreciated. If you need further explanation, please don't hesitate to ask!
r rodbc
First and foremost - thank you for taking your time to view my question, regardless of if you answer or not!
I am trying to create a function that loops through my df and queries in the necessary data from SQL using the RODBC package in R. However, I am having trouble setting up the query, since the parameter of the query change through each iteration (example below)
So my df looks like this:
ID Start_Date End_Date
1 2/2/2008 2/9/2008
2 1/1/2006 1/1/2007
1 5/7/2010 5/15/2010
5 9/9/2009 10/1/2009
How would I go about specifying the start date and end date in my sql program?
here's what i have so far:
data_pull <- function(df) {
a <- data.frame()
b <- data.frame()
for (i in df$id)
{
dbconnection <- odbcDriverConnect(".....")
query <- paste("Select ID, Date, Account_Balance from Table where ID = (",i,") and Date > (",df$Start_Date,") and Date <= (",df$End_Date,")")
a <- sqlQuery(dbconnection, paste(query))
b <- rbind(b,a)
}
return(b)
}
However, this doesn't query in anything. I believe it has something to do with how I am specifying the start and the end date for the iteration.
If anyone can help on this it would be greatly appreciated. If you need further explanation, please don't hesitate to ask!
r rodbc
r rodbc
asked Jan 3 at 16:15
yungpadewonyungpadewon
707
707
Never usepaste
concatenation for SQL queries, risking at best syntax errors, at worst SQL injection (whether intentional or not). Instead, useRODBCext
(related question: stackoverflow.com/q/16178640/3358272) (unlessRODBC
has started doing it directly, I'm really surprised they have not implemented it yet as it's rather fundamental to good DB practices). cran.r-project.org/web/packages/RODBCext/vignettes/…
– r2evans
Jan 3 at 16:18
1
thank you @r2evans I will learn this and try and implement this.
– yungpadewon
Jan 3 at 16:21
1
Second, don't use repetitiverbind
like that, it will crush performance in the long run (each call torbind
makes a complete copy of all data preceding and being added). SinceRODBCext::sqlExecute
directly supports vectorizing with a frame (see cran.r-project.org/web/packages/RODBCext/vignettes/…, section 2.3.2), there's no need for a loop at all.
– r2evans
Jan 3 at 16:24
Yes,rbind
run inside a loop results in quadratic copying. Always avoid growing objects in loops
– Parfait
Jan 3 at 16:39
thank you guys @Parfait I am gonna try and re-write this
– yungpadewon
Jan 3 at 16:40
add a comment |
Never usepaste
concatenation for SQL queries, risking at best syntax errors, at worst SQL injection (whether intentional or not). Instead, useRODBCext
(related question: stackoverflow.com/q/16178640/3358272) (unlessRODBC
has started doing it directly, I'm really surprised they have not implemented it yet as it's rather fundamental to good DB practices). cran.r-project.org/web/packages/RODBCext/vignettes/…
– r2evans
Jan 3 at 16:18
1
thank you @r2evans I will learn this and try and implement this.
– yungpadewon
Jan 3 at 16:21
1
Second, don't use repetitiverbind
like that, it will crush performance in the long run (each call torbind
makes a complete copy of all data preceding and being added). SinceRODBCext::sqlExecute
directly supports vectorizing with a frame (see cran.r-project.org/web/packages/RODBCext/vignettes/…, section 2.3.2), there's no need for a loop at all.
– r2evans
Jan 3 at 16:24
Yes,rbind
run inside a loop results in quadratic copying. Always avoid growing objects in loops
– Parfait
Jan 3 at 16:39
thank you guys @Parfait I am gonna try and re-write this
– yungpadewon
Jan 3 at 16:40
Never use
paste
concatenation for SQL queries, risking at best syntax errors, at worst SQL injection (whether intentional or not). Instead, use RODBCext
(related question: stackoverflow.com/q/16178640/3358272) (unless RODBC
has started doing it directly, I'm really surprised they have not implemented it yet as it's rather fundamental to good DB practices). cran.r-project.org/web/packages/RODBCext/vignettes/…– r2evans
Jan 3 at 16:18
Never use
paste
concatenation for SQL queries, risking at best syntax errors, at worst SQL injection (whether intentional or not). Instead, use RODBCext
(related question: stackoverflow.com/q/16178640/3358272) (unless RODBC
has started doing it directly, I'm really surprised they have not implemented it yet as it's rather fundamental to good DB practices). cran.r-project.org/web/packages/RODBCext/vignettes/…– r2evans
Jan 3 at 16:18
1
1
thank you @r2evans I will learn this and try and implement this.
– yungpadewon
Jan 3 at 16:21
thank you @r2evans I will learn this and try and implement this.
– yungpadewon
Jan 3 at 16:21
1
1
Second, don't use repetitive
rbind
like that, it will crush performance in the long run (each call to rbind
makes a complete copy of all data preceding and being added). Since RODBCext::sqlExecute
directly supports vectorizing with a frame (see cran.r-project.org/web/packages/RODBCext/vignettes/…, section 2.3.2), there's no need for a loop at all.– r2evans
Jan 3 at 16:24
Second, don't use repetitive
rbind
like that, it will crush performance in the long run (each call to rbind
makes a complete copy of all data preceding and being added). Since RODBCext::sqlExecute
directly supports vectorizing with a frame (see cran.r-project.org/web/packages/RODBCext/vignettes/…, section 2.3.2), there's no need for a loop at all.– r2evans
Jan 3 at 16:24
Yes,
rbind
run inside a loop results in quadratic copying. Always avoid growing objects in loops– Parfait
Jan 3 at 16:39
Yes,
rbind
run inside a loop results in quadratic copying. Always avoid growing objects in loops– Parfait
Jan 3 at 16:39
thank you guys @Parfait I am gonna try and re-write this
– yungpadewon
Jan 3 at 16:40
thank you guys @Parfait I am gonna try and re-write this
– yungpadewon
Jan 3 at 16:40
add a comment |
1 Answer
1
active
oldest
votes
A couple of syntax issues arise from current setup:
LOOP: You do not iterate through all rows of data frame but only the atomic ID values in the single column,
df$ID
. In that same loop you are passing the entire vectors ofdf$Start_Date
anddf$End_Date
into query concatenation.DATES: Your date formats do not align to most data base date formats of 'YYYY-MM-DD'. And still some others like Oracle, you require string to data conversion:
TO_DATE(mydate, 'YYYY-MM-DD')
.
A couple of aforementioned performance / best practices issues:
PARAMETERIZATION: While parameterization is not needed for security reasons since your values are not generated by user input who can inject malicious SQL code, for maintainability and readability, parameterized queries are advised. Hence, consider doing so.
GROWING OBJECTS: According to Patrick Burn's Inferno Circle 2: Growing Objects, R programmers should avoid growing multi-dimensional objects like data frames inside a loop which can cause excessive copying in memory. Instead, build a list of data frames to
rbind
once outside the loop.
With that said, you can avoid any looping or listing needs by saving your data frame as a database table then joined to final table for a filtered, join query import. This assumes your database user has CREATE TABLE
and DROP TABLE
privileges.
# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
End_Date = as.Date(End_Date, format="%m/%d/%Y")
})
# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)
# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance
FROM Table t
INNER JOIN myRData r
ON r.ID = t.ID
AND t.Date BETWEEN r.Start_Date AND r.End_Date"
final_df <- sqlQuery(dbconnection, q)
Thank you so much for taking your time to go through this. I really appreciate it.
– yungpadewon
Jan 3 at 20:40
No problem. Happy New Year and happy coding!
– Parfait
Jan 3 at 21:16
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54026026%2fcreating-a-looped-sql-query-using-rodbc-in-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
A couple of syntax issues arise from current setup:
LOOP: You do not iterate through all rows of data frame but only the atomic ID values in the single column,
df$ID
. In that same loop you are passing the entire vectors ofdf$Start_Date
anddf$End_Date
into query concatenation.DATES: Your date formats do not align to most data base date formats of 'YYYY-MM-DD'. And still some others like Oracle, you require string to data conversion:
TO_DATE(mydate, 'YYYY-MM-DD')
.
A couple of aforementioned performance / best practices issues:
PARAMETERIZATION: While parameterization is not needed for security reasons since your values are not generated by user input who can inject malicious SQL code, for maintainability and readability, parameterized queries are advised. Hence, consider doing so.
GROWING OBJECTS: According to Patrick Burn's Inferno Circle 2: Growing Objects, R programmers should avoid growing multi-dimensional objects like data frames inside a loop which can cause excessive copying in memory. Instead, build a list of data frames to
rbind
once outside the loop.
With that said, you can avoid any looping or listing needs by saving your data frame as a database table then joined to final table for a filtered, join query import. This assumes your database user has CREATE TABLE
and DROP TABLE
privileges.
# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
End_Date = as.Date(End_Date, format="%m/%d/%Y")
})
# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)
# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance
FROM Table t
INNER JOIN myRData r
ON r.ID = t.ID
AND t.Date BETWEEN r.Start_Date AND r.End_Date"
final_df <- sqlQuery(dbconnection, q)
Thank you so much for taking your time to go through this. I really appreciate it.
– yungpadewon
Jan 3 at 20:40
No problem. Happy New Year and happy coding!
– Parfait
Jan 3 at 21:16
add a comment |
A couple of syntax issues arise from current setup:
LOOP: You do not iterate through all rows of data frame but only the atomic ID values in the single column,
df$ID
. In that same loop you are passing the entire vectors ofdf$Start_Date
anddf$End_Date
into query concatenation.DATES: Your date formats do not align to most data base date formats of 'YYYY-MM-DD'. And still some others like Oracle, you require string to data conversion:
TO_DATE(mydate, 'YYYY-MM-DD')
.
A couple of aforementioned performance / best practices issues:
PARAMETERIZATION: While parameterization is not needed for security reasons since your values are not generated by user input who can inject malicious SQL code, for maintainability and readability, parameterized queries are advised. Hence, consider doing so.
GROWING OBJECTS: According to Patrick Burn's Inferno Circle 2: Growing Objects, R programmers should avoid growing multi-dimensional objects like data frames inside a loop which can cause excessive copying in memory. Instead, build a list of data frames to
rbind
once outside the loop.
With that said, you can avoid any looping or listing needs by saving your data frame as a database table then joined to final table for a filtered, join query import. This assumes your database user has CREATE TABLE
and DROP TABLE
privileges.
# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
End_Date = as.Date(End_Date, format="%m/%d/%Y")
})
# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)
# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance
FROM Table t
INNER JOIN myRData r
ON r.ID = t.ID
AND t.Date BETWEEN r.Start_Date AND r.End_Date"
final_df <- sqlQuery(dbconnection, q)
Thank you so much for taking your time to go through this. I really appreciate it.
– yungpadewon
Jan 3 at 20:40
No problem. Happy New Year and happy coding!
– Parfait
Jan 3 at 21:16
add a comment |
A couple of syntax issues arise from current setup:
LOOP: You do not iterate through all rows of data frame but only the atomic ID values in the single column,
df$ID
. In that same loop you are passing the entire vectors ofdf$Start_Date
anddf$End_Date
into query concatenation.DATES: Your date formats do not align to most data base date formats of 'YYYY-MM-DD'. And still some others like Oracle, you require string to data conversion:
TO_DATE(mydate, 'YYYY-MM-DD')
.
A couple of aforementioned performance / best practices issues:
PARAMETERIZATION: While parameterization is not needed for security reasons since your values are not generated by user input who can inject malicious SQL code, for maintainability and readability, parameterized queries are advised. Hence, consider doing so.
GROWING OBJECTS: According to Patrick Burn's Inferno Circle 2: Growing Objects, R programmers should avoid growing multi-dimensional objects like data frames inside a loop which can cause excessive copying in memory. Instead, build a list of data frames to
rbind
once outside the loop.
With that said, you can avoid any looping or listing needs by saving your data frame as a database table then joined to final table for a filtered, join query import. This assumes your database user has CREATE TABLE
and DROP TABLE
privileges.
# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
End_Date = as.Date(End_Date, format="%m/%d/%Y")
})
# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)
# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance
FROM Table t
INNER JOIN myRData r
ON r.ID = t.ID
AND t.Date BETWEEN r.Start_Date AND r.End_Date"
final_df <- sqlQuery(dbconnection, q)
A couple of syntax issues arise from current setup:
LOOP: You do not iterate through all rows of data frame but only the atomic ID values in the single column,
df$ID
. In that same loop you are passing the entire vectors ofdf$Start_Date
anddf$End_Date
into query concatenation.DATES: Your date formats do not align to most data base date formats of 'YYYY-MM-DD'. And still some others like Oracle, you require string to data conversion:
TO_DATE(mydate, 'YYYY-MM-DD')
.
A couple of aforementioned performance / best practices issues:
PARAMETERIZATION: While parameterization is not needed for security reasons since your values are not generated by user input who can inject malicious SQL code, for maintainability and readability, parameterized queries are advised. Hence, consider doing so.
GROWING OBJECTS: According to Patrick Burn's Inferno Circle 2: Growing Objects, R programmers should avoid growing multi-dimensional objects like data frames inside a loop which can cause excessive copying in memory. Instead, build a list of data frames to
rbind
once outside the loop.
With that said, you can avoid any looping or listing needs by saving your data frame as a database table then joined to final table for a filtered, join query import. This assumes your database user has CREATE TABLE
and DROP TABLE
privileges.
# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
End_Date = as.Date(End_Date, format="%m/%d/%Y")
})
# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)
# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance
FROM Table t
INNER JOIN myRData r
ON r.ID = t.ID
AND t.Date BETWEEN r.Start_Date AND r.End_Date"
final_df <- sqlQuery(dbconnection, q)
answered Jan 3 at 20:39
ParfaitParfait
54.4k104872
54.4k104872
Thank you so much for taking your time to go through this. I really appreciate it.
– yungpadewon
Jan 3 at 20:40
No problem. Happy New Year and happy coding!
– Parfait
Jan 3 at 21:16
add a comment |
Thank you so much for taking your time to go through this. I really appreciate it.
– yungpadewon
Jan 3 at 20:40
No problem. Happy New Year and happy coding!
– Parfait
Jan 3 at 21:16
Thank you so much for taking your time to go through this. I really appreciate it.
– yungpadewon
Jan 3 at 20:40
Thank you so much for taking your time to go through this. I really appreciate it.
– yungpadewon
Jan 3 at 20:40
No problem. Happy New Year and happy coding!
– Parfait
Jan 3 at 21:16
No problem. Happy New Year and happy coding!
– Parfait
Jan 3 at 21:16
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54026026%2fcreating-a-looped-sql-query-using-rodbc-in-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Never use
paste
concatenation for SQL queries, risking at best syntax errors, at worst SQL injection (whether intentional or not). Instead, useRODBCext
(related question: stackoverflow.com/q/16178640/3358272) (unlessRODBC
has started doing it directly, I'm really surprised they have not implemented it yet as it's rather fundamental to good DB practices). cran.r-project.org/web/packages/RODBCext/vignettes/…– r2evans
Jan 3 at 16:18
1
thank you @r2evans I will learn this and try and implement this.
– yungpadewon
Jan 3 at 16:21
1
Second, don't use repetitive
rbind
like that, it will crush performance in the long run (each call torbind
makes a complete copy of all data preceding and being added). SinceRODBCext::sqlExecute
directly supports vectorizing with a frame (see cran.r-project.org/web/packages/RODBCext/vignettes/…, section 2.3.2), there's no need for a loop at all.– r2evans
Jan 3 at 16:24
Yes,
rbind
run inside a loop results in quadratic copying. Always avoid growing objects in loops– Parfait
Jan 3 at 16:39
thank you guys @Parfait I am gonna try and re-write this
– yungpadewon
Jan 3 at 16:40