|
I need a perl script that I can run as a cron job.. The script needs to connect to an ftp server and grab a csv file and create a MySQL table from that file. I then need to know if its any different for a .txt file.
Any help is appreciated
|
|
|
|
|
This will be a long post but any insight will be greatly appreciated.
Process Description:
I have to write a C# method which will take as its parameter a System.Data.DataTable and then write this DataTable to a new dBASE IV table on disk.
Current Solution: (not a good one)
Here is my current solution in C# which is very very very slow (It takes 5+ minutes to output a 10MB .DBF on a dual 2.4 Zeon machine with 2GB of RAM running a RAID 5).
public void WriteTable(DataTable table)
{
string columnString="";
foreach(DataColumn column in table.Columns)
{
string type = "";
switch(column.DataType.ToString())
{
case "System.String":
type = "varchar(" + MaxLength(column) + ")";
break;
case "System.Int32":
type = "int";
break;
case "System.Double":
type = "double";
break;
default:
throw new ArgumentException("Data type not found.", column.DataType.ToString());
}
columnString += column.ColumnName + " " + type + ", ";
}
columnString = columnString.Substring(0, columnString.Length-2);
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + directory + ";Extended Properties=dBase IV";
sqlString = "CREATE TABLE " + name + " (" + columnString + ")";
connection = new OleDbConnection(connectionString);
command = new OleDbCommand(sqlString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
command.Connection.Open();
int columnCount = table.Columns.Count;
foreach(DataRow row in table.Rows)
{
string valueString="";
for(int i=0; i < columnCount; i++)
{
switch(row[i].GetType().ToString())
{
case "System.String":
valueString += "'" + ((string)row[i]).Replace("'", "''") + "', ";
break;
case "System.Int32":
valueString += System.Convert.ToString((int)row[i]) + ", ";
break;
case "System.Double":
valueString += System.Convert.ToString((double)row[i]) + ", ";
break;
case "System.DBNull":
valueString += "'', ";
break;
default:
throw new ArgumentException("Data type not found.", row[i].GetType().ToString());
}
}
valueString = valueString.Substring(0, valueString.Length-2);
sqlString = "INSERT INTO " + name + " VALUES (" + valueString + ")";
command.CommandText = sqlString;
command.ExecuteNonQuery();
}
command.Connection.Close();
}
Questions:
- Any suggestions on a much better way to do this?
- Do you think it would be possible/better to try and see if I could use an OleDbDataSet/OleDbDataAdapter solution?
- Can anyone give me some insight as to why this solution is incredibly slow other than the fact that there is a very large amount of records to write out?
Any comments, suggestions, samples, etc. will be greatly appreciated.
Thanks
Mark Sanders
|
|
|
|
|
Mark Sanders wrote:
Do you think it would be possible/better to try and see if I could use an OleDbDataSet/OleDbDataAdapter solution?
No, probably this will be slower.
Mark Sanders wrote:
Can anyone give me some insight as to why this solution is incredibly slow other than the fact that there is a very large amount of records to write out?
Use parametrized queries: this will give you a huge performance boost. See the OleDbParameter class for examples.
Notice that using parametrized queries you won't need anymore to replace "dangerous" chars like the quotes.
This probably will eliminate the need for the switch on the type and all the string conversions, too.
For another tip, do not make massive string concatenations using the String class. This is slow, and will kill the performance of the garbage collector. Actually, this can lead to hard-to-find performance problems. Use the System.Text.StringBuilder instead.
My latest article: GBVB - Converting VB.NET code to C#
|
|
|
|
|
I have been trying to find some examples of setting up an INSERT into a .dbf using OleDbParameter class. I have been unsuccessful. Can you point me to some examples? I understand how to use parameters with SQL Server stored procedures but I seem to be missing something when it comes to using parameters for and Ole connection to a .dbf.
Here is what I have so far but it is not working.
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties=dBase IV";
string sqlString = "INSERT INTO TestTabl (Column) VALUES (?)";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand(sqlString, connection);
command.Parameters.Add("@Column", OleDbType.VarChar);
command.Parameters["@Column"].Value = "It Worked";
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
Thanks
Mark Sanders
|
|
|
|
|
I adjusted my code to use parameterized queries but have not gained a significant enough performance boost. After some performance tests I have found that my OleDbCommand.ExecuteNonQuery() takes an average of 0.004 seconds to execute (with the max occurance being 0.015 seconds). With my current mid-range test I do this for 145,160 records for a total of 9.85 minutes! After some more research it appears the general concensus is that ADO.NET is unable to do this type of operation any faster.
So, my new question is...
I have no choice in the requirements. I must take a DataTable and write it to disk as a dBASE IV table. Does anyone have any ideas on how to do this in the fastest possible way?
Mark Sanders
|
|
|
|
|
When I work with DBF files I always use microsoft's ODBC data provider classes, ODBC Adapters etc. So far it works fast and effective.
You can dowload it somewhere from the MSDN
Good luck.
"I only Play for Sport"
Lara H. Croft
modified 17-Oct-21 21:01pm.
|
|
|
|
|
Working with a table containing 2 million records I want to get the next SeqNum for each group and display an order field.
Example:
Table:
ID Field1 Field2 DateEntered SeqNum
1 tom a 12/12/00 1
2 tom b 1/1/01 2
3 jack d 2/2/01 1
4 tom d 1/2/02 null
5 me a 1/2/02 1
6 jack a 2/2/02 null
7 me d 3/3/03 null
8 me a 3/3/03 null
Desired Output:
ID Field1 Field2 DateEntered SeqNum Order
1 tom a 12/12/00 1 1
2 tom b 1/1/01 2 2
3 jack d 2/2/01 1 3
4 tom d 1/2/02 3 4
5 me a 1/2/02 1 5
6 jack a 2/2/02 2 6
7 me d 3/3/03 2 7
8 me a 3/3/03 3 8
Could someone point me in the right direction on how to replace the null values with the appropriate next SeqNum and output the Order fields.
Because of the size of the table using a cursor is out of the question. Anyone know of a good way this can be done.
Thanks,
Jason W.
|
|
|
|
|
Depends on the database you are using. If it is SQL Server or Sybase, consider creating an identity column for automatic numbering.
|
|
|
|
|
Jason Weibel wrote:
Could someone point me in the right direction on how to replace the null values with the appropriate next SeqNum and output the Order fields.
Because of the size of the table using a cursor is out of the question. Anyone know of a good way this can be done.
Jason, long time no see, not sure if you got this fixed or not but you should consider using a CASE statement in the stored procedure where you can replace a null value with an identity value for each record.
-Nick Parker
|
|
|
|
|
Man I thought you fell off the face of the planet. What’s up? I found a work around for the problem, it isn’t pretty but it works.
Jason W.
|
|
|
|
|
Jason Weibel wrote:
Man I thought you fell off the face of the planet. What’s up? I found a work around for the problem, it isn’t pretty but it works.
Did you get the email I sent you a few days ago?
-Nick Parker
|
|
|
|
|
Hi all,
I am constructing a oledbconnection using the connection string which obtained through data link properties dialog.
After getting a connection string we can easily create oledbconnection irrespective of the database. My problem is I should know the oledbconnection is with an oracle database.
How can I know? The only way I know is checking the database provider in the connection string that it is from oracle. But what is the problem with this, this is always possible only if we know all the oracle oledb providers. That also will fail, if they change the version in future. Without using the provider in the connection string, is there any other way to determine whether the connection is with an oracle database?
Thanks.
|
|
|
|
|
Hi All,
I have the following scenario:
in a Bound windows form, I have a master-detail relationship, the first part of the form is the Master(represented by text and combo boxes) and retrives it's Data from a single SQL data table, The Details Section is represented by a DataGrid, and retrives its Data from Multi Tables Sql Query.
I wand to Add/ Insert/ Update and Delete from this Related sections at once, but still facing some problems affecting the Details Section, the modifications are done on the Master only.
The relation between them is build in the run time.
plz, if you have any Idea how to solve such a problem, that will be appreciated.
Thanx All.
I.M.A
|
|
|
|
|
HI,
I created tables in MS Access.. and am quite familiar in accessing the tables from the ASP page for my site. I then saw somethin like relationships.. and started playing around it. I got the followinf stuff....
Employee (table)
- EmpId
- DeptId
- Name
Department (table)
- DeptId
- DeptHead
- Description
The Dept head at the second table is linked with the EmpId in the first table by means of the relationships....
NOW, will it be possible for me to access the first table data, by querying the second table. (In access screen I was able to see the first table data in the second table by clicking + on the field which joins them)
Please help
I was born intelligent Education ruined me!.
|
|
|
|
|
Hi,
> The Dept head at the second table is linked with the EmpId in the first table by means of the relationships....
From your table definitions, I think DeptId is a foreign key that links the Employee to a Department.
> NOW, will it be possible for me to access the first table data, by querying the second table.
It depends of what you want to do. What do you mean by "access the first table data" ? Do you want to know the employees for a specific departement ?
Just a DB design tip :
- don't use prefix on your field names : Employee.Id or Department.Id are ok, Employee.EmpId or Department.DeptId/DeptHead is a redondance
JM. Molina
Web: http://goa.ifrance.com
|
|
|
|
|
how to use ado.net access paradox database(tables)?
thanks.
|
|
|
|
|
i have a table in my Sql Server database which contains data in a hierarchical manner is as below
table-1
child_id parent_id
1 2
2 3
3 4
4 5
5 6
11 5
17 4
Now i want to retrive all the children,grand children,grand-grand children of parent_id=5.How i can achive it through SQL query .Please help me its very very urgent
Thank u
|
|
|
|
|
In Standard-SQL you got to program every level
i.e.
SELECT child_id, parent_id
FROM table
WHERE parent_id=5
SELECT child_id, parent_id
FROM table
WHERE parent_id IN (SELECT child_id FROM table WHERE parent_id=5)
etc.
you can find examples when looking for 'partlists'.
however, if you use Oracle, you can use the CONNECT BY clause:
SELECT child_id, parent_id
FROM table
CONNECT BY parent_id = child_id
|
|
|
|
|
You can use nested sets, I just submitted an article on it, have a look in the General/Database section, Look for "Improving Hierarchy Performance"
Cheers, James
James Simpson
Web Developer
Crown Management Systems
(http://www.crown.uk.com)
imebgo@hotmail.com
|
|
|
|
|
I am trying to install MySQL 4.0.12 on Windows 2000. I am getting very frustrated as I thought this would be simple. What a fool I was. Can someone help me out?
The current problem is that the MySQL installation instructions for Windows do not mention anything about requiring Cygwin, but scripts like bin/mysqlinstall_db.sh aren't going to run unless Cygwin or something like it are present, no? So,
1. Does one need Cygwin on Windows 2000 to install MySQL, and if not, what does one do when one encounters instructions like "run mysqlinstall_db.sh" in the install notes, and
2. is there a set of instructions for installing MySQL on Windows that is better than the manual that comes with the MySQL dsitribution?
Andrew
|
|
|
|
|
|
Hi,
Simply download the Windows Binaries, install it and that's all. You don't need Cygwin unless you want to compile MySQL by yourself.
JM. Molina
Web: http://goa.ifrance.com
|
|
|
|
|
Could someone help me figure out what's wrong with this code? I'm using Microsoft SQL 7
SELECT DISTINCT UserID,
(
SELECT COUNT (UserID) AS test
FROM pollusers, polls
WHERE polls.UserID = pollusers.UserID
) AS CountID
FROM pollusers
This is the error I am getting in Cold Fusion: Invalid column name 'UserID'
|
|
|
|
|
It doesn't know which 'UserID' column to count, since the column exists in both of the tables you are joining. Try this instead
SELECT DISTINCT UserID,
(
SELECT COUNT (polls.UserID) AS test
FROM pollusers, polls
WHERE polls.UserID = pollusers.UserID
) AS CountID
FROM pollusers
Just guessing, though. What is the query supposed to accomplish?
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.
|
|
|
|
|
SQL is getting confused what column belongs to what table. Alias the table and it should work.
SELECT DISTINCT UserID,
(
SELECT COUNT (pu.UserID) AS test
FROM pollusers pu, polls po
WHERE pu.UserID = po.UserID
) AS CountID
FROM pollusers
If that still doesn't work, make sure that this works first:
select UserID from pollusers
select UserID from polls
|
|
|
|