Stored Proc and SqlDataAdapter - Database Discussion Boards

mjackson113-Dec-04 8:07

3-Dec-04 8:07

I am trying to load very large amounts of data into a table (Access for now, SQL server later) from a C++/ADO application. There are about 200,000 rows consisting of 7 columns, all numbers. I have used the regular ADO RecordSet object AddNew method (~15 minutes), same but disconnecting and then reconnecting the RecordSet with a batch update (~5 minutes), and finally creating a SQL INSERT statement for direct execution (~5 minutes). I have removed most indexes on the database to facilitate insertions.

Is there a faster way to do this? Given that the computations to create the data only take 10 seconds, it seems like a waste to spend 15 minutes uploading it to a database.

Thanks

Mark Jackson

Re: Uploading Large numbers of records

Alex Korchemniy4-Dec-04 16:56

Alex Korchemniy

4-Dec-04 16:56

SQL should be a lot faster. And if network speed is a bottleneck consider running the application on the server.

SQL insert is not the fastest way of doing things. Consider looking into bulk insert methods.

This posting is provided "AS IS" with no warranties, and confers no rights.

Alex Korchemniy

Re: Uploading Large numbers of records

mjackson118-Dec-04 9:42

mjackson11

8-Dec-04 9:42

FWIW, I got around this by creating a huge buffer to hold the data, pasting it to the clipboard, and using a COM object to get Access to paste the data. It is ugly as it appears you cannot import the Access library due to bugs, so I used old-fashioned COM. It was worth the effort as program run time went from over 5 minutes to under 40 seconds.

Mark Jackson

void mrgVaRProdList::uploadList(void)
{
#define APPEND_LINE_SIZE 120

mrgDataSrc * ds;
mrgRecSet rs;
list<mrgVaRPoint *>::iterator iter;
int i;
unsigned long _l, counter;
Date bd, d1, d2;
CString str, buf;
char * chr;
size_t bufLen;
HGLOBAL h;
HWND wh = GetConsoleHWND();

ds = _mParent->getDataObj();
rs.setDataSrc(ds);
bd.setDate(_mParent->getAsOfDate());
str = bd.getDateStr();
i = (int)_uploadList.size();
bufLen = i * APPEND_LINE_SIZE;
h = GlobalAlloc(GHND, bufLen);
try
{
if (!OpenClipboard(wh))
{
throw("OpenClipboard");
}
if (!EmptyClipboard())
{
throw("EmptyClipboard");
}
rs.ExecSQL("Delete * from VaRData");
iter = _uploadList.begin();
counter = 1;
_l = 0;
chr = (char *)GlobalLock(h);
while (iter != _uploadList.end())
{
buf.Format("%u\t%s\t", counter, str);
buf.AppendFormat("%u\t%u\t", (*iter)->idOne, (*iter)->idTwo);
d1.setDate((*iter)->bukOne);
d2.setDate((*iter)->bukTwo);
buf.AppendFormat("%s\t%s\t", d1.getDateStr(), d2.getDateStr());
buf.AppendFormat("%f\t%f\t%f\r\n", (*iter)->rho, (*iter)->sigmaOne, (*iter)->sigmaTwo);
for (i = 0; i < buf.GetLength(); i++)
{
chr[_l] = buf.GetAt(i);
_l++;
}
counter++;
iter++;
}
GlobalUnlock(h);
if (SetClipboardData(CF_TEXT, h) == NULL)
{
throw("SetClipboardData");
}
CloseClipboard();
}
catch(_com_error e)
{
PrintADOError(_T("ADO Error mrgVaRProdList.uploadList() : "), &e);
}
catch(const char *c)
{
LPVOID lpMsgBuf;
FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM |
FORMAT_MESSAGE_IGNORE_INSERTS,
NULL,
GetLastError(),
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), // Default language
(LPTSTR) &lpMsgBuf,
0,
NULL
);
str.SetString((LPTSTR)lpMsgBuf);
LocalFree(lpMsgBuf);
cout << "Error in mrgVaRProdList.uploadList()-" << c << ":" << str.AllocSysString() << endl;
}
try
{
// our ID's
CLSID clsid;
IUnknown *pUnk;
IDispatch *pDisp;
IDispatch *pDispDoCmd;
DISPID dispid_DoCmd;
DISPID dispid_OpenTable;
DISPID dispid_RunCommand;
//parameter setup
DISPPARAMS dispparamsNoArgs = {NULL, NULL, 0, 0};
OLECHAR FAR* szFunction;
// for getting/passing results
HRESULT hr;
VARIANT varResult;
// arguments for methods
VARIANT varArgs[1];
DISPPARAMS dpArgs;
BSTR bstrTemp;

CLSIDFromProgID(L"Access.Application", &clsid);

// Get an interface to the running instance, if any..
hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);
if (hr < 0)
{
// TODO - we need to start an instance
throw("Error GetActiveObject");
}

// Get IDispatch interface for Automation...
hr = pUnk->QueryInterface(IID_IDispatch, (void **)&pDisp);
if (hr < 0)
throw("Error QueryInterface - IID_IDispatch");
// Release the no-longer-needed IUnknown...
pUnk->Release();

szFunction = OLESTR("DoCmd");
hr = pDisp->GetIDsOfNames(IID_NULL, &szFunction, 1, LOCALE_USER_DEFAULT, &dispid_DoCmd);
if (hr < 0)
{
pDisp->Release();
throw("Error GetIDsOfNames for DoCmd");
}
hr = pDisp->Invoke(dispid_DoCmd, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET,
&dispparamsNoArgs, &varResult, NULL, NULL);
if (hr < 0)
{
pDisp->Release();
throw("Error Invoke for DoCmd");
}
pDispDoCmd = varResult.pdispVal;

szFunction = OLESTR("OpenTable");
hr = pDispDoCmd->GetIDsOfNames(IID_NULL, &szFunction, 1, LOCALE_USER_DEFAULT, &dispid_OpenTable);
if (hr < 0)
{
pDisp->Release();
pDispDoCmd->Release();
throw("Error GetIDsOfNames for OpenTable");
}

bstrTemp = ::SysAllocString(OLESTR("VaRData"));
varArgs[0].vt = VT_BSTR;
varArgs[0].bstrVal = bstrTemp;
dpArgs.cArgs = 1;
dpArgs.cNamedArgs = 0;
dpArgs.rgvarg = varArgs;

//Invoke the OpenTable Method
hr = pDispDoCmd->Invoke(dispid_OpenTable, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpArgs, NULL, NULL, NULL);
::SysFreeString(bstrTemp);
if (hr < 0)
{
pDisp->Release();
pDispDoCmd->Release();
throw("Error Invoking OpenTable");
}

szFunction = OLESTR("RunCommand");
hr = pDispDoCmd->GetIDsOfNames(IID_NULL, &szFunction, 1,
LOCALE_USER_DEFAULT, &dispid_RunCommand);
if (hr < 0)
{
pDisp->Release();
pDispDoCmd->Release();
throw("Error GetIDsOfNames RunCommand");
}

varArgs[0].vt = VT_I2;
varArgs[0].iVal = 0x26; // acCmdPasteAppend
dpArgs.cArgs = 1;
dpArgs.cNamedArgs = 0;
dpArgs.rgvarg = varArgs;
hr = pDispDoCmd->Invoke(dispid_RunCommand, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpArgs, NULL, NULL, NULL);

// release interface pointers
pDispDoCmd->Release();
pDisp->Release();
}
catch(_com_error e)
{
PrintADOError(_T("ADO Error mrgVaRProdList.uploadList() : "), &e);
}
catch(const char *c)
{
cout << "Error in mrgVaRProdList.uploadList()-" << c << endl;
}
return;
}

ADO.NET columns are magic numbers..

giorgos_gr3-Dec-04 5:09

giorgos_gr

3-Dec-04 5:09

i need to use the column names instead of passing magic numbers in my code (i.e. the column position in the select statement).. but it seems that i can't(??) do this??

OracleDataReader reader..;

1st way: NOT READABLE
while (reader.Read())
{
// get the value of 1st column for current row
if (!reader.IsDBNumm(0))
string s = reader.GetString(0);
..
}

2nd way: i can use reader["column_name"], but there is no overload function to test for null value!!! --> STILL NOT READABLE!!!??
while (reader.Read())
{
// get the value of 1st column for current row
if (!reader.IsDBNumm(0))
string s = reader["name"];
..
}

so.. how can i get rid of the magic numbers???

g.

SQL Tree Hierarchy Problem

Dirk Kok2-Dec-04 22:24

Dirk Kok

2-Dec-04 22:24

Hi
Please help me out here with some hierarchies. It’s blowing my mind.
Here’s the table…

CREATE TABLE [dbo].[Tree] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL ,
[Lineage] [varchar] (50) NULL ,
[Name] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

Here’s some data…

SET IDENTITY_INSERT Tree ON

INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (1, NULL, '.0001.', 'Root')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (2, 1, '.0001.0002.', 'I')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (3, 1, '.0001.0003.', 'H')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (4, 1, '.0001.0004.', 'G')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (5, 2, '.0001.0002.0005.', 'F')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (6, 2, '.0001.0002.0006.', 'E')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (7, 2, '.0001.0002.0007.', 'D')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (8, 4, '.0001.0004.0008.', 'C')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (9, 4, '.0001.0004.0009.', 'B')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (10, 3, '.0001.0003.0010.', 'A')

SET IDENTITY_INSERT Tree OFF

You have the above tree data. You can ignore the lineage if you don’t want to work with it.

There problem is as follows…

Please sort the tree nodes according to the hierarchy, BUT the nodes must also be in ALPHABETICAL order.

So “SELECT * FROM Tree ORDER BY Lineage” will look like so…

Root
I
F
E
D
H
A
G
C
B

But I need it to be sorted in that hierarchy but also alphabetically like so

Root
G
B
C
H
A
I
D
E
F

Can someone figure this out for me please?

Regards
Dirk

Re: SQL Tree Hierarchy Problem

Michael Potter3-Dec-04 4:34

Michael Potter

3-Dec-04 4:34

Sounds like homework Smile | :)

Try:
SELECT Name FROM Tree ORDER BY (Lineage + Name) DESC

Re: SQL Tree Hierarchy Problem

Chris Meech3-Dec-04 6:44

Chris Meech

3-Dec-04 6:44

Just a comment. Think a little more about your 'lineage' field. It seems like you are trying to re-create a character representation for data that already exists through the 'ID' and 'ParentID' fields. I think if you create a view that self joins the table based on the ParentID column you might be farther ahead. Smile | :)

Chris Meech

I am Canadian. [heard in a local bar]

Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]

Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]

Stored Proc and SqlDataAdapter

ccosser2-Dec-04 22:03

ccosser

2-Dec-04 22:03

Hi,

Hoping someone can help me. I am trying to implement a custom built paging control. I need to retrieve the total number of records as well as only select the number of records I require from the stored procedure and extract these two variables using a sqlDataAdapter.

My sql query is as follows ... would prefer not to use "tomato sauce" but this is the only solution I can think of - however i'm still not getting results .....

And how do I extract these two variables into my sqldataAdapter ...?????

Any advices would be very welcome .. thank you.

My stored procedure ...

alter PROCEDURE [dbo].[prc_tbUser_GetAllPaged1]

--create variable for NumRec
@From int,
@To int

AS
DECLARE @SQL varchar
DECLARE @SQLCount varchar
DECLARE @sSQL varchar
DECLARE @NumRec varchar

set @NumRec = @To - @From

set @SQLCount = 'Declare @TotalRecords int, @NumRec int '
set @SQLCount = @SQLCount + 'SELECT @TotalRecords = count(UserId) from tbUser'

set @SQL = 'SELECT a.Name, a.UserID, a.Login, b.Name as OrganisationName, c.Description as Status
FROM tbUser a
INNER JOIN tbOrganisation b
ON a.OrganisationID = b.OrganisationID
INNER JOIN tbStatus c
ON a.StatusID = c.StatusID'

set @sSQL = @SQLCount + ' SELECT top ' + @NumRec + ' @TotalRecords as NumRecords ' + @SQL

print @sSQL

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--prc_tbUser_GetAllPaged1 20,40

and this is the sqlDataAdapter ...

connection = new SqlConnection( Configuration.ConnectionString );

command = new SqlCommand( "prc_tbUser_GetAllPaged1", connection );

command.CommandType = CommandType.StoredProcedure;

//From value

command.Parameters.Add( "@From", SqlDbType.Int ).Value = iFrom;

//To value

command.Parameters.Add( "@To", SqlDbType.Int ).Value = iTo;

//need to add another column to read total number of records return

command.Parameters.Add("@TotalRecords", SqlDbType.Int);

// Open the connection

connection.Open();

//create the data adapter

SqlDataAdapter oDA = new SqlDataAdapter(command);

//Gets the parameters set by the user when executing an SQL SELECT statement.

oDA.GetFillParameters();

//execute the reader

oDA.Fill(oDS,iFrom,iTo - iFrom, "tbUser");

return oDS

Attaching a database to MSDE

mattie202-Dec-04 20:31

mattie20

2-Dec-04 20:31

Hi everybody
I have created an installer using NSIS,but I am realy having trouble finding the code to attach the database within the installer.I dont know if its maybe my RegKey or what,but the database does not want to attach.If anyone knows anything about NSIS,or maybe a diff RegKey that I can try,or even some coding to help me,please do.

Thanks so much.
Kind Regards
Mattie20 Laugh | :laugh:

Hello.Thanks for your help

Nested Transaction with SQL Server

Wouter van Eck2-Dec-04 5:21

Wouter van Eck

2-Dec-04 5:21

Hi,

Can anybody tell me how I can get nested transactions going with ADO.Net and the SqlClient classes?

Any articles or sample code would be helpfull.

thanks

Wouter

How can i avaoid negative values in SQL?

john kuruvila2-Dec-04 5:14

john kuruvila

2-Dec-04 5:14

hi,
What is the SQL querry for not having negative values in the table?

thanks,jijo

Jijo kuruvila
software developer
trivandrum

Re: How can i avaoid negative values in SQL?

Colin Angus Mackay2-Dec-04 6:42

Colin Angus Mackay

2-Dec-04 6:42

john kuruvila wrote:
What is the SQL querry for not having negative values in the table?

When you create the table you set a constraint like this:

CREATE TABLE cust_sample
    (
    cust_id                int        PRIMARY KEY,
    cust_name            char(50),
    cust_address            char(50),
    cust_credit_limit    money,
    CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
    )

If the table is already created you can use ALTER TABLE in order to add the constraint (See the SQL Help files for how to use ALTER TABLE)

Does this help?

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

Re: How can i avaoid negative values in SQL?

john kuruvila2-Dec-04 7:30

john kuruvila

2-Dec-04 7:30

thanx....

Jijo kuruvila
software developer
trivandrum

not allowing negative values

sujithapril2-Dec-04 5:10

sujithapril

2-Dec-04 5:10

i have an sql table.it has one column totalitems.it should not be negative values.how can i accomplish this task?
thanks in advance

Re: not allowing negative values

Colin Angus Mackay2-Dec-04 6:41

Colin Angus Mackay

2-Dec-04 6:41

When you create the table you set a constraint like this:

CREATE TABLE cust_sample
    (
    cust_id                int        PRIMARY KEY,
    cust_name            char(50),
    cust_address            char(50),
    cust_credit_limit    money,
    CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
    )

If the table is already created you can use ALTER TABLE in order to add the constraint (See the SQL Help files for how to use ALTER TABLE)

Does this help?

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

Re: not allowing negative values

sujithapril2-Dec-04 7:23

sujithapril

2-Dec-04 7:23

thank you,it worked well.When a user purchases some things the totalitems in ptable is decremented according to it.when totalitems in ptable becomes 0, user cannot update.this was the problem.yes, i solved it using consraints.can i make the validation at the client side ?

Re: not allowing negative values

Colin Angus Mackay2-Dec-04 7:26

Colin Angus Mackay

2-Dec-04 7:26

sujithapril wrote:
can i make the validation at the client side ?

Probably. You don't say what the client side is so I cannot guide you further.

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

Re: not allowing negative values

sujithapril2-Dec-04 7:50

sujithapril

2-Dec-04 7:50

the userinterface has a textbox and a button.the user must enter the number of items he wishes to buy and then enter the submit button.the stock table in server side contains the TotalItems of a particular product.if TotalItems is zero,the user should not be allowed.I have added the check constraint in serverside "check(TotalItems>=0)" as you said and done it successfully.so the question is can it be done at client side?

Re: not allowing negative values

Colin Angus Mackay2-Dec-04 7:59

Colin Angus Mackay

2-Dec-04 7:59

sujithapril wrote:
so the question is can it be done at client side?

Yes, it can.

You can add a RangeValidator to your page. Or you can check in your submit button event before you call the SQL Code.

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

Recordset "LIKE" filter of a number column

Menny Even Danan1-Dec-04 21:51

Menny Even Danan

1-Dec-04 21:51

I've posted a message here yesterday, but it seems that i was mis-understood.

I'm using vb6 ADO's Recordset.
i want to use the recordset's Filter property.
but the target column is numeric (the ID column of the table, int).

i want to filter the table, so i'll stay with the rows that begins with a certain number, just as a varchar column i'll use "LIKE" filter.
when trying to do it, i get an error "Filter can not be opened."

any ideas?

thanks

Re: Recordset "LIKE" filter of a number column

jlawren72-Dec-04 5:27

jlawren7

2-Dec-04 5:27

you could put a textbox on your form, and in the code, use the textboxes text property in your filter. create a varible and set the val(textbox) = to your varible and search that way

ex.
dim SQLSearch as integer
sqlSearch = Val(Text1.Text)
"SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
the more you type in the textbox, the closer your search will be

please note the use of the % in the SQL Query

you could even put your search into the textboxes textchange ()
if your using ado
ex. ado.recordsource = "SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
ado.refresh

so as you type, the the search gets smaller

Hope this helps

Help is great only if you ask correctly Smile | :)

can't DELETE records in excel

scoroop1-Dec-04 11:15

scoroop

1-Dec-04 11:15

i built a database in excel sheet. everything seems to be ok except deleting records. i defined everything and opened database.

INSERT INTO works properly but when i use

Sql_string = "DELETE FROM test_base WHERE ID='"+ID_act+"'";
database.ExecuteSQL(sSql);

i get an error - "database error: could not delete from specified tables"

i have no clue why... anyone can provide some working code snippet? Smile | :)

Re: can't DELETE records in excel

Christian Graus1-Dec-04 12:27

Christian Graus

1-Dec-04 12:27

Is the ID a string ? If it's an integer, try removing the quotes.

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Re: can't DELETE records in excel

scoroop1-Dec-04 12:36

scoroop

1-Dec-04 12:36

it's a string. just converted integer with some zeros glued in front of it to make all IDs 6-digit long - to make it sensibly sortable :]

Re: can't DELETE records in excel

jonathan152-Dec-04 23:21

jonathan15

2-Dec-04 23:21

WHERE ID='"+ID_act+"'

I tohught it was '" & ID_act & "' for a string. At least thats what i use and works OK in Access & SQL Server.

Jon

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.