Database Discussion Boards

Create table and choosing a datetime format ?

Bart-Man19-Nov-02 9:05

19-Nov-02 9:05

Greetings,

I recently created a table which has a datetime varible which stores the date and time such as this "11/18/2002 1:30:33 PM".

But now I want to have milliseconds stored in this as well, such as shown below.
"11/18/2002 1:30:33.33 PM".

By default it does not allow me to add the ".33", so I am assuming it is just a formating setting when I create my table, but I am not sure how to do this? Any suggestions on how to do this?

Here is a sample section of my code of how I create my table with the date, and how I do insertion of the data. I am using sql server 2000, and ADO via Visual C++ 6.

CREATE TABLE MyTestSql
(
MyTestDate datetime
}

INSERT INTO MyTestSql(MyTestDate)
VALUES('11/18/2002 1:30:33 PM')

Thanks in advance for help?

Solution to my problem

Bart-Man19-Nov-02 10:34

Bart-Man

19-Nov-02 10:34

Greetings,

As usual if you work on something, right after you make the post you find the solution to your problem.

It seems that microsoft sql server rounds up the time value's millisecond, so that it doesn't show up in the database. It had nothing to do with formating the date and time.

Here is a link that explains a solution on how to get your milliseconds back.
http://support.microsoft.com/default.aspx?scid=KB;en-us;q186265

Move data from SQL Srever to Access

Ravish18-Nov-02 23:28

Ravish

18-Nov-02 23:28

Hai all,
We have a sql server table (which contains a Guid col + others). For processing the data in this table (about 0.1 million records a day), we move the data to access db on the user's desktop.

Currently we are using ADO to access the sql server and update the local access db (using batch update). It takes about 10-15 minutes for the whole operation. Is there a faster way to do this ? We have a requirement to move records from SQL to access based on certain filter criteria .

Any pointers will be greatly appreciated

Re: Move data from SQL Srever to Access

Michael P Butler18-Nov-02 23:38

Michael P Butler

18-Nov-02 23:38

Have you looked at DTS? I'm not sure if it'll be any faster but I use it a lot to move data from one place to another.

Michael

Life’s not a song.
Life isn’t bliss.
Life is just this.
It’s living. -- Buffy the Vampire Slayer: Once more, with feeling

Re: Move data from SQL Srever to Access

Paul Watson19-Nov-02 10:57

Paul Watson

19-Nov-02 10:57

Ravish wrote:
Currently we are using ADO to access the sql server and update the local access db (using batch update). It takes about 10-15 minutes for the whole operation. Is there a faster way to do this ? We have a requirement to move records from SQL to access based on certain filter criteria .

As Michael suggest, use DTS. It will work (with filtering and all sorts of other options) but you won't have any fun. DTS is painful IMO.

Paul Watson
Bluegrass
Cape Town, South Africa

Colin Davies wrote:
...can you imagine a John Simmons stalker !

SQL help...actually mySQL sorry

Hockey17-Nov-02 19:52

Hockey

17-Nov-02 19:52

I am trying to (re)write a knowledgebase, cuz it sux a$$.

Anyways...I have a keywords field in the database and the user enters keywords into an input field.

The code constructs a query which looks something like this:

SELECT * FROM kb_articles WHERE Keywords LIKE '%Show%' OR Keywords LIKE '%Me%' OR Keywords LIKE '%the%' OR Keywords LIKE '%money%'

I'm not a whiz at SQL so i'll explain myself here a little incase my syntax if off or something...

Obviously i'm selecting records from a table called kb_articles which has the following structure:

PKID | Question | Answer | Keywords |

Keywords and Answer should be searched for potential results. As far as I can tell, the above won't work (i'm using mySQL BTW) because LIKE chokes on multiple words.

Example?

Question entry page:

Question: How can I make lots of money?
Keywords: Show me the money

The script then parses Keywords and Questions into individual array elements (seems logical) and those items are what get compared to each record inside kb_articles keywords field. Only the keywords field can also have multiple keywords and these aren't parsed into indy words, so basically what's happeneing is this:

kb_articles.keywords = "How, To, Show, The, Make, Money"

SELECT * FROM kb_articles WHERE keywords LIKE '%Show%'....

LIKE i think chokes on multiple words inside the database keyword field...

So my question is this...Is there a way I can use SQL regex to compare I have no idea about anything REGEX... Blush | :O

or am I going to have to execute multiple queries constructed using PHP...?

Thanks in advance Smile | :)

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Re: SQL help...actually mySQL sorry

Mazdak17-Nov-02 20:12

Mazdak

17-Nov-02 20:12

Sorry ,I can't understand what say. Blush | :O

In the example that you say,what does the query return?

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: SQL help...actually mySQL sorry

Jason Henderson18-Nov-02 5:52

Jason Henderson

18-Nov-02 5:52

That SELECT statement looks like it should work but you will have to put more LIKEs in there if you want a better search. For example, you may want to use LIKE '%Show me the money%' or LIKE '%Show%me%the%money%' or LIKE '%show%me%' etc.

Jason Henderson
^{start page ; articles}
_{henderson is coming}
^{henderson is an opponent's worst nightmare}
^{* googlism *}

what kind of DB uses .idx and .dat files

Robert Smit14-Nov-02 23:32

Robert Smit

14-Nov-02 23:32

Well,

I can't find out what kind of database uses .idx and .dat files for storage of data. I have tried several ODBC drivers but they won't work.

I hope someone has an answer to these questions,

thanx in advance,

Robert Smit

Re: what kind of DB uses .idx and .dat files

Michael P Butler14-Nov-02 23:45

Michael P Butler

14-Nov-02 23:45

I've come across several in-house databases that have used those extensions, I've not come across a commercial one with these extensions. Most tend to be of the flat-file variety which usually means no ODBC support.

I have used those extensions for a flat-file database in the past but I doubt you'll be trying to connect to that Smile | :)

Michael

Life’s not a song.
Life isn’t bliss.
Life is just this.
It’s living. -- Buffy the Vampire Slayer: Once more, with feeling

Re: what kind of DB uses .idx and .dat files

Robert Smit15-Nov-02 0:48

Robert Smit

15-Nov-02 0:48

Micheal,

Thank you for your answer I think you could be right. It is only a small program that stores data to those idx and dat files. The database is used only for storing pupil en group results for math exercises. So it is not a complicated DB with a lot of stored procedures.

My question to you is how you read and write to those files in C++.

Robert

Re: what kind of DB uses .idx and .dat files

Michael P Butler15-Nov-02 1:07

Michael P Butler

15-Nov-02 1:07

Without the file structures you'll have a hard task.

In the good old days when I used to try and integrate with Pegasus Accounts who used a similar system, I usually saved a record into the database using their app and then hex-edited the file to figure out what had been updated and changed.

Michael

Life’s not a song.
Life isn’t bliss.
Life is just this.
It’s living. -- Buffy the Vampire Slayer: Once more, with feeling

Best way to protect MDB file?

LukeV13-Nov-02 9:59

LukeV

13-Nov-02 9:59

Hello,

like subject says, is there something more than setting a password for the database that I can do to make sure that people don't open an MDB file and mess with it?

Thanks!

---------------
Concentrating on Ideas
http://www.edovia.com

Re: Best way to protect MDB file?

Vasudevan Deepak Kumar17-Nov-02 23:44

Vasudevan Deepak Kumar

17-Nov-02 23:44

Hey LukeV,

You may choose to open an MDB in exclusive mode and set password to it. So that each time the MDB file is opened, it will ask password.

Deepak Kumar Vasudevan
http://deepak.portland.co.uk/

ADO Memory usage - leak?

Gizz13-Nov-02 5:43

Gizz

13-Nov-02 5:43

Hi.

I've got a basic ADO AddNew call, running in a loop, against Access 2002. Running on VC++6, Win2K, MDAC 6 sp2. See code below.

It seems to use memory constantly, even when the recordset object is released. It uses approx 10MB per 100,000 rows written. My system needs to be on line and writing data for a week at a time so this is going to be a problem for me. Any ideas?

Ta.
Gizz.

The following function is called from another big loop...

void fn()
{
using namespace ADODB;

_RecordsetPtr pRst = NULL;
_ConnectionPtr pConnection = NULL;

_bstr_t strCnn("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\data.MDB");

try
{
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
pConnection->Open(strCnn,"","",adConnectUnspecified);

TESTHR(pRst.CreateInstance(__uuidof(Recordset)));

pRst->Open("tblMyTable",_variant_t((IDispatch *) pConnection, true),
adOpenKeyset,adLockOptimistic,adCmdTable);

_variant_t strGBID("GBID");
_variant_t strTSID("TimestampID");

static long i = 0;
static long imax = 1000;
for (;i < imax; i++)
{
pRst->AddNew();

{_variant_t vtFld;
vtFld.vt = VT_I4;
vtFld.lVal = i;
pRst->Fields->GetItem(strGBID)->Value = vtFld;}

{_variant_t vtFld;
vtFld.vt = VT_I4;
vtFld.lVal = i+1;
pRst->Fields->GetItem(strTSID)->Value = vtFld;}

pRst->Update();
}

imax += 1000;
pRst->Close();
pConnection->Close();
pConnection.Release();
pRst.Release();
}
catch(_com_error &)
{}
}

Re: ADO Memory usage - leak?

Mazdak14-Nov-02 5:47

Mazdak

14-Nov-02 5:47

I don't know where is the problem,But about pConnection.Release(); and pRst.Release(); I have to say you don't need them because you use these inrefaces as an pointer and they will release automaticlly. But you problem MAYBE because of access,I know it has limitation on number of records,and after you insert 100,000 rows in a table in crash or ....You didn't which line cause memory leak!

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Terminal Services Web Connection via ado(using c++)

BP potato12-Nov-02 16:44

BP potato

12-Nov-02 16:44

Hi,
I am new to database programming in c++ and come across some quest. which i can't find in books.
I am programming in c++ mfc platform, using ADO to access to the sql database.
However, my database is using MS windows 2000 Terminal Services Web Connection
and i don't know how to link to the database. In Short, how is the connection string parameters to be like.

Thanks

Decimal precision Creating Table

Anonymous12-Nov-02 8:19

Anonymous

12-Nov-02 8:19

Creating a database this way:
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
sSql.Format( "CREATE TABLE Sounding ([Pressure (mb)] FLOAT,[Height(m)] FLOAT)");
database.ExecuteSQL(sSql);

sSql.Format("INSERT INTO Sounding VALUES('%f','%f')",mPressure,mHeight);
database.ExecuteSQL(sSql);
....

If mHeight is 17.60 it is inserted 17600000, if I change to '%.f' it is inserted 17, and if '%.2f' 1760. What is the solution to insert the right decimals?
Thanks.

Re: Decimal precision Creating Table

Mazdak12-Nov-02 18:56

Mazdak

12-Nov-02 18:56

There are two point here:

1.Are you sure that mPressure and mHeight are float value or maybe you define them as int.

2.It could be in your database.You set the Scale of it tp 0.Its is zero by default so you forgot to change it,So SQLServer or access omit them.

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: Decimal precision Creating Table

Anonymous13-Nov-02 5:40

Anonymous

13-Nov-02 5:40

Yes, mPressure and mHeight are float values.
How could I change the default Scale?
Thank you.

Re: Decimal precision Creating Table

Mazdak13-Nov-02 8:07

Mazdak

13-Nov-02 8:07

Do you use Access or SQLServer?

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: Decimal precision Creating Table

José Luis Sogorb14-Nov-02 5:03

José Luis Sogorb

14-Nov-02 5:03

Well, I'm using it into an MFC app, with Visual C++, and getting the ODBC Microsoft Excel Driver. It´s the first time I include a database utility in an applicattion, so I don´t know exactly what I'm using. If it helps, I use Cdatabase class with function member ExcecuteSQL.
Thanks.

Re: Decimal precision Creating Table

Mazdak14-Nov-02 5:08

Mazdak

14-Nov-02 5:08

José Luis Sogorb wrote:
ODBC Microsoft Excel Driver

I never used it,but I don't know how do you want to create table or other things in it. May I know why do you choose Excel driver?!!!!!!!!!!

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

problem saving binary data into a file

trozner12-Nov-02 6:16

trozner

12-Nov-02 6:16

Hi,
I'm trying to write a binary data from a recordset field into a file. I'm
creating an ADODB.Stream object (adTypeBinary), write the rs field into it
and then save it to a file.
The problem is that the file is not written well; it's size is double than
it should be.
Can somebody help me?
Thanks!

Here's a part of the code:

Dim objStream
Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 1 'adTypeBinary
objStream.Open
objStream.Write rs.Fields.Item("Object").Value
objStream.SaveToFile filePath, 2
objStream.Close
Set objStream=nothing

Yukon (SQL Server 2003) Questions?

Larry Maccherone12-Nov-02 5:50

Larry Maccherone

12-Nov-02 5:50

How will Yukon change the way we develop database apps? We are in the requirements gathering stage of a new product development effort and we're thinking that since Yukon will be out before we are shipping, that we may want to take advantage of it's new and supposedly revolutionary capabilities.

I'm particularly interested in how the inclusion of the .NET CLR into the database will be implemented. I get the impression from the articles that I've read that you'll be able to bypass some of the mid-layer technologies necessary to now do data access. Certainly we won't need TSQL, but what else will we be able to bypass? SQL itself? ADO/ADO.NET? OLE DB/ODBC? I wonder, will we be able to implement a data-connected class in C# running on Yukon almost exactly as we would implement an internal type within a windows app and have Yukon automatically implement the actual data storage in the database? In other words, will we be able to treat data just like objects? If so, this is a huge pardigm shift and it bears investigating before we start developing with traditional techniques.

Has anyone played with Yukon yet? Is it included in the latest MSDN subscription?

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