Re: [GENERAL] Problems with MS Visual Basic 6.0
Brigitte ROLLAND wrote:
Hello !
I need to intreact from VB6 with an PG 7.4.3 database, I've
psqlodbc-7_03_0200 installed.
I use the Microsoft ADO Data Control 6.0, and I've installed the SP5 for
VB6.
Luc,
I have never used the ADO Data Control, so I can't be much help there -
I can tell you what I have done with a project I work on, which I have
converted to use PG via ODBC. Please note that I won't tell you
everything here, I have posted numerous times to both this list
[GENERAL] and the ODBC list on this issue - so search the archives.
When I use the adodc control I see the data but I've problems with :
- Update, on existing records : do nothing, I've to MoveFirst to store
in database ! Why ?
For my project, I use the ActiveX Data Objects 2.7 Library for my
access. Even so, I have found that I need to do a
.movelast/.movefirst sequence in order to use the recordset I
return. I am not sure why this is, either - I have just accepted it and
moved on. Maybe the pointer in the recordset is on the last record, not
the first? Dunno...
- Edit Method don't exist for that object, so EditMode properties is non
signifiant,
Correct. Same for what I am doing. This is part of ADO (vs. DAO).
- I can't update if I :
use CheckBox control,
use textBox control with a date.
Because you are using a control, and not the method I am using - I am
not sure how you can resolve this. I have found that for date type
fields that I can update them by using cdate():
ie, ![datefield] = cdate(text1.text)
Checkboxes I store in boolean fields, by taking the absolute value of
the checkbox value and storing that:
ie, ![booleanfield] = abs(checkbox.value)
Alternatively, you can also say:
![booleanfield] = (checkbox.value = 1)
How can I resolve these points ?
Regards.
Luc
The basics of what I do to get a recordset for update/reading is as follows:
Dim conn As New ADODB.Connection
Dim recs As New ADODB.Recordset
'
' Open DSN-less ADO connection to database
'
Set conn = New ADODB.Connection
'
' In the following, replace the ??? in the connection string with values
' appropriate for your application.
'
conn.Open driver={PostgreSQL};server=???;port=???;database=???;_
uid=???;pwd=???;ksqo=;
'
' Retrieve recordset from table
'
Set recs = New ADODB.Recordset
'
recs.CursorLocation = adUseServer
'
recs.Open SELECT * FROM ???, conn, adOpenDynamic, adLockOptimistic,_
adCmdText
'
' Manipulate recordset
'
With recs
'
If Not (.BOF And .EOF) Then
'
.MoveLast
.MoveFirst
'
' Update/Add/Read stuff here
'
End If
'
.Close
'
End With
'
Set recs = Nothing
'
' Close database connection
'
Set conn = Nothing
---
More information on all this, as I noted, is in the archives of this
list and the ODBC list (in fact, most of the it is probably in the ODBC
list, since I was posting there quite a lot when I did the conversion).
I know that the above is probably vastly different from using the ADO
control itself, but hopefully it might give you some ideas and insight
to allow you to get your application working...
Hope this helps,
Andrew Ayers
Phoenix, Arizona
-- CONFIDENTIALITY NOTICE --
This message is intended for the sole use of the individual and entity to whom it is
addressed, and may contain information that is privileged, confidential and exempt
from disclosure under applicable law. If you are not the intended addressee, nor
authorized to receive for the intended addressee, you are hereby notified that you may
not use, copy, disclose or distribute to anyone the message or any information
contained in the message. If you have received this message in error, please
immediately advise the sender by reply email, and delete the message. Thank you.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Installation Postgresql
Chris wrote: [SuSE] I would appreciate if someone can give me hand so I can have a good experience installing postgresql . If you're a complete *nix newbie you should consider using the precompiled PostgreSQL package that comes with your copy of SuSE. You can install it with Yast. Yes - I would reccommend this as well, unless the version (of PostgreSQL) that comes with SuSE does not have certain options you need, or you have other needs to install from source. Also, be aware that there are tools that can be installed on you system to convert a .tar.gz to an RPM or other package style so you can install the packages using you package manager (though this might be more work than what you want to do for your project). The ./configure, make, ... steps are NOT necessary then, they're for compiling from source. Compiling from source should only be done if you can't find the package in a format for you package manager, if it didn't come with your set of packages, or if you need certain features that aren't available in any pre-made packages. Or, of course, if you have a specific need for the source (ie, customized source, or you're developing for PG, etc). For getting started with PostgreSQL, you can start it by using the service menu entry from the SUSE GUI (I'm not using this, so I'm guessing the name...) Likely, if you install it using YAST, you can shutdown and reboot and it should automatically start up via the start up scripts (not as elegant as doing it manually - and you should learn to start/shutdown service daemons - but you also want to see if it auto starts, too - if the startup goes by to quickly (or is masked by a graphical overlay), log in and cat /var/log/messages | more to see if it did). You should be able to access the database by using the command line tools from a terminal window: createdb, psql. Try to see the man pages, or look up the books you cited (or ask again), if you get stuck. Tons of reference material and people out there - if there is one thing good to say about Linux, it is that reference material on just about every subject and problem exists out there, all one has to do is look for it (now, sometimes that material is difficult to understand, but nothings perfect). Andrew Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Installation Postgresql
on Linux (and other open source projects), go to http://www.oreilly.com/ and order a few books - you won't be sorry. Good luck - and welcome to PostgreSQL and Linux! Andrew Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Storing images in a field
Együd Csaba wrote: Hi All, I use PG 7.3.2 an 7.4.1 on RH 7.1. I'd like to store small (~ 100x100 px jpeg, or gif) images in a field in binary format. I tried to understand the documentation of the large objects but I'm afraid I coluldn't. I found some functions to import and export binary files into the db, but these functions work on the server side. How can I insert a block of binary data into a given field form the client (WinXP, pgsqlODBC 7.03.01)? And on the other side how can I retrive that data from there, how can I delete or modifay that? In general which data type is the most powerful for this. The only purpose of this field to store a photo or drawing of the product represented by the record. It's not required to be parsed. Is it possible to have it compressed or it should be done by the client? Numerous methods exist - large objects, bytea fields, TEXT fields (ie, base64 convert the data and store as a big string), and of course, storing a path in the filesystem to the image (has pros and cons). In fact, only about a week ago was there a discussion on this topic. Which makes me wonder: Since this is such a common request, shouldn't there be a FAQ regarding the procedure? Heck, even if all it consisted of was the archived messages strung together, there should be enough qa involved in all of those exchanges to answer this question many times over. I know, I know - itch, scratch, why don't I do it? If I had the time (and if I had actually ever done the exercise), I would make a FAQ or something on the topic and post it. Unfortunately, I have never had the need to store images in a DB (shocking!). However, I do know enough about the topic to know that it is a common question, one that should already be answered in just about every manner possible. In fact, it likely is - so why does this same question seem to pop up on a regular basis? I realize that there are basic questions, and all need to be answered. I also realize the everybody was a beginner at one time or another. But this question should be completely and fully answered in some form on the internet - because it is a *basic* component of every e-commerce site. Are people really reinventing the wheel this much? Andrew Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Parser: parse error - please help...
All,
I am having a problem with an INSERT onto a table I have created. First
off, here is the table:
---
CREATE TABLE reg (customer number SERIAL PRIMARY KEY, company name
VARCHAR(50) NULL, address VARCHAR(100) NULL, city VARCHAR(50) NULL,
state VARCHAR(2) NULL, zip VARCHAR(50) NULL, phone VARCHAR(50) NULL,
alt_phone VARCHAR(50) NULL, fax VARCHAR(50) NULL, alt_fax VARCHAR(50)
NULL, iaccess BOOL NULL DEFAULT FALSE, browser VARCHAR(50) NULL,
provider VARCHAR(50) NULL, software VARCHAR(50) NULL, sversion
VARCHAR(10) NULL, ynintegrated BOOL NULL DEFAULT FALSE, ynnewtest BOOL
NULL DEFAULT FALSE, login VARCHAR(50) NULL, password VARCHAR(50) NULL,
txtnetid VARCHAR(11) NULL, alias_1 VARCHAR(50) NULL, alias_2 VARCHAR(50)
NULL, url VARCHAR(255) NULL, approval_password VARCHAR(20) NULL,
ynauthalert BOOL NULL DEFAULT FALSE, loi_date VARCHAR(50) NULL,
contract_eff VARCHAR(50) NULL, contract_term VARCHAR(50) NULL, term_code
VARCHAR(255) NULL, main_eff VARCHAR(50) NULL, main_term VARCHAR(50)
NULL, live_eff VARCHAR(50) NULL, renewal_eff VARCHAR(50) NULL, cs_rep
VARCHAR(50) NULL, txtactmanager VARCHAR(50) NULL, txtsalesrep
VARCHAR(50) NULL, txtprojmanager VARCHAR(50) NULL, modem_number
VARCHAR(50) NULL, dbc_users VARCHAR(50) NULL, intbillableusers INT4
NULL, country VARCHAR(50) NULL, mmowarning TEXT NULL, mmositefileflags
TEXT NULL, verified VARCHAR(50) NULL, buildtimedate VARCHAR(50) NULL,
ynhipaaba BOOL NULL DEFAULT FALSE);
---
I have a process which loops and inserts multiple records into this
table. After processing approximately 200 records, it attempts to do the
following insert:
---
INSERT INTO reg(customer number, company name, address, city, state,
zip, phone, alt_phone, fax, alt_fax, iaccess, browser, provider,
software, sversion, ynintegrated, ynnewtest, login, password, txtnetid,
alias_1, alias_2, url, approval_password, ynauthalert, loi_date,
contract_eff, contract_term, term_code, main_eff, main_term, live_eff,
renewal_eff, cs_rep, txtactmanager, txtsalesrep, txtprojmanager,
modem_number, dbc_users, intbillableusers, country, mmowarning,
mmositefileflags, verified, buildtimedate, ynhipaaba) VALUES ('855',
'Test Company', '1234 West Jazz Street', 'Tempe', 'AZ', '85203', '(602)
123-4567', '(602) 123-4567', '(602) 123-4567', '(602) 123-4567', true,
'Internet Explorer', DEFAULT, 'HP3.5', '13', false, false, 'eci855',
DEFAULT, DEFAULT, 'Test', 'Tester', 'http:\\www.eldocomp.com\', DEFAULT,
false, DEFAULT, '09/11/2002', DEFAULT, DEFAULT, '09/11/2002', DEFAULT,
DEFAULT, DEFAULT, 'Chris Riesgraf ext. 244', 'Keri Daminelli ext. 246',
'Kerry Winkle ext. 229', DEFAULT, DEFAULT, DEFAULT,
'0', 'USA', 'afdsf', DEFAULT, DEFAULT, DEFAULT, false);
---
This generates the following error (from within VB):
Error -2147467259 - ERROR: parser: parse error at or near 09 at
character 847
---
When I try the above insert in psql, I get a prompt like:
dbname'
Here, if I input ' followed by RETURN, the prompt changes to:
dbname(
Input ), the prompt changes to:
dbname-
Input ;, the error occurs:
ERROR: parser: parse error at or near 09 at character 847
---
Something is obviously wrong - but what it is, I cannot tell. I have
tried to simplify the problem by comparing prior inserts to this one, to
see what the differences are, to no avail. It is like it is missing a
paren or a quote, or has an extra - but that doesn't seem to be the case.
Am I missing something obvious, can somebody help?
Thank you,
Andrew L. Ayers
Phoenix, Arizona
-- CONFIDENTIALITY NOTICE --
This message is intended for the sole use of the individual and entity to whom it is
addressed, and may contain information that is privileged, confidential and exempt
from disclosure under applicable law. If you are not the intended addressee, nor
authorized to receive for the intended addressee, you are hereby notified that you may
not use, copy, disclose or distribute to anyone the message or any information
contained in the message. If you have received this message in error, please
immediately advise the sender by reply email, and delete the message. Thank you.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Newbie-question
Victor Spång Arthursson wrote: Are presently converting from mysql to postgresql, and my first newbiequestion is how to make all the rows in a result from a select just swosh by? That is, I dont want to see them page for page; just to scroll by so I can se the last line with the number of corresponding rows. You seem to want to see the number of corresponding rows, not the stuff swish by - or at least, I would hope you are more interested in the count and not just text flying by... In that case, do a select count(*) from SQL select... Andrew Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] About GPL and proprietary software
Dann Corbit wrote:
This is what I find odd about GPL software:
{an illustration}
A man named George opens a spoon factory. People flock to his shop to
make spoons with no charge for their labor. You see, it is a company in
combat with the mighty Oneida which makes lots of spoons and people
could sure use some cheap, reliable spoons. If anyone uses these
spoons, then the knife, fork, plate and glass also now belong to George.
Strangely, George charges as much for these spoons (manufactured at no
cost to him) as Oneida.
Your illustration is interesting, but it doesn't tell the whole story. A
better illustration would be:
A man named George opens a spoon factory. People flock to his shop to
make spoons, but they are not paid for their labor, nor do they expect
to be paid.
George's company is in competition with the mighty Oneida, which makes
lots of spoons, but they cost a bit of money to buy. People sure would
like to have cheap, reliable spoons, so George's company provides just
that. In fact, the spoons are so cheap, George *gives* them away, along
with the plans for making the spoons.
How can George do this? Well, George tells everyone who takes a spoon
(as a form of license):
You can take a spoon, and you can make new spoons from my plans, as
well as improve upon my spoons. But if you sell them or give them away,
you must tell those you sell or give them to what I have told you, and
also give them the plans so they may make spoons as well.
Now, you may chuckle and think that George will go out of business - now
everyone knows his secret to making spoons - why buy or take from George
when you can just make spoons for yourself? But you agreed to a license
with George. Now what?
Well, you can make spoons all day long, for yourself - even for your
immediate family. But if you give (or sell) them to anybody else - you
*must* tell them what George told you, and give them the plans for
making spoons.
Spoons may be made all day long, and improvements to those spoons as
well. George, in fact, thinks Hey, some of those improvements will come
back my way - they have to, eventually - and I can improve upon my own
spoons, and then give or sell those as well! Better spoons for everyone!.
One day, George hears of an improvement made to his spoon, and is given
a copy (along with the revised plans) - this spoon has little prongs
on the end (and strangely enough, is called a Fork). George likes this
new spoon so much - he wants to sell it, and keep the secret to
himself. But he can't, at least not with the plans he has, because he
would have to abide by the same license he himself set out. So George
decides to do something different, and figure out a different way to
make these new spoons without using any of the changes detailed in the
plans he recieved with the new spoon.
George isn't as successful - but he does come up with a much more useful
(?) variant of spoon and fork - he calls it a Spork, because it seems
to combine the best attributes of both. While he started with his
original spoon design, since he is the owner of that design he can
license/contract it in any manner he wants - and this time he decides
that he wants to sell this new spoon, and not tell anyone how it is made.
Now, the original spoon didn't cost George anything to make - but he had
to pay someone (even if it was just himself) to come up with the idea of
a Spork, so he is justified in selling it for whatever people will pay -
even though people may still get spoons from him and others, and there
are also these mysterious forks floating around. Some may choose to
buy sporks - others can get spoons and forks (for free, though some
makers provide a 1-900 number service to learn how to properly use them).
---
I think even my analogy has flaws, but it is much closer to the truth
about the GPL...
Andrew Ayers
Phoenix, Arizona
-- CONFIDENTIALITY NOTICE --
This message is intended for the sole use of the individual and entity to whom it is
addressed, and may contain information that is privileged, confidential and exempt
from disclosure under applicable law. If you are not the intended addressee, nor
authorized to receive for the intended addressee, you are hereby notified that you may
not use, copy, disclose or distribute to anyone the message or any information
contained in the message. If you have received this message in error, please
immediately advise the sender by reply email, and delete the message. Thank you.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Limited varchar, unlimited varchar, or text?
Curtis Hawthorne wrote: According the the page there's no performance difference between the types so I would lean towards using unlimited varchar or text to avoid having an arbitrary limit, but are there any other hidden problems with using these types? If not, which one should I use? I tend to wonder this as well - if a VARCHAR with no length specifier is used, it seems (from the docs) that it is supposed to work the same as TEXT. If it has a length specified, it only allows up to that length string to be put into it. Then people say there is no difference, internally it is the same to PG. So - why the two types? Why not just one type or the other: VARCHAR or VARCHAR(x) TEXT or (the nonexistent) TEXT(x) Personally, if such a change were to come about, I would lean toward adding the TEXT(x) to the system, and deprecating VARCHAR. Leave both in for a few versions, then rip VARCHAR support out. Why? Because from a human-centric perspective - everyone knows what text is - but what the hell is a varchar (yeah, us geeks know what they mean - but most real people who use and build db tables may or may not). But, maybe I am talking out my rear here - I haven't actually looked at much of PG's codebase, so there may be a good valid reason for VARCHAR (also, IIRC - isn't it a SQL compliance type? That would be good enough - so maybe we just need VARCHAR only). --- Personally, for the case of the user name - I would use VARCHAR(50) or VARCHAR(255). It seems after a quick google search that the maximum LDAP user name length is around 32 characters or so - but it may be user-definable. If the latter is the case, speak to the admin of the LDAP system, and find out what he has it set for. Choose one of the 50 or 255 lengths for the VARCHAR setting based on what you are told. If you use TEXT, and you switch to another DB that doesn't support the TEXT keyword, or it is dropped from PG - VARCHAR is likely to stay behind (especially if it is an SQL compliant keyword - can't remember). If TEXT is SQL compliant, maybe just use that. As far as there being problems with Access and ODBC - there could be if your ODBC driver isn't set up, and I have found problems relating to using DAO as well when updating TEXT fields (when migrating from the Access MEMO type). I found that using ADO made the problems I was experiencing disappear (look in the GENERAL and ODBC mailing list archives for my emails on this). Andrew L. Ayers Phoenix, Arizona Thanks! Curtis H. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 0/1 vs true/false
Greg Stark wrote: Instead I have to have =='f' and =='t' strewn throughout my code everywhere making it harder to read and extremely fragile. If I forget one anywhere I silently get subtly broken semantics. Why did you do that? Why not create a single function (isTrue()?) that you pass the field through and let the function evaluate it. It doesn't solve the issue, but it would make your code more stable, and if things ever changed (ie, from a t to a 1 or something) - you just update that one function. In fact (and my C/C++ memory is hazy) - you should be able to create a set of functions or methods in you code/classes that could take a variety of data types as the passed type, and have each of those funnel down to one type to evaluate and pass up the chain - so you could call the function isTrue(int) or isTrue(char *) or something, and it would always work OK (ie, a polymorphic function/method). That way, your code could always call the *same* function/method, and always know it was going to return the same value - but behind the scenes, it would be doing all the funky exercises needed to return the proper value. If it ever radically changes (or you change the DB), you change one function, and the system keeps working (no having to hunt and change 'n' lines of code). Yes, there would be a little more overhead - but future maintenance coders (or yourself, down the line) will thank you for your foresight. Andrew L. Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why does it not use the index?
Doug McNaught wrote: It's not the VACUUM that's necessary; it's the ANALYZE. The query planner uses table statistics to make its decisions, and ANALYZE is what collects those statistics. Without an ANALYZE the planner will make default assumptions that are rarely correct. :) I am not the original poster, but I am a PG newbie, so: So - are you saying that if you have a table, and you create an index on that table, you need to perform an ANALYZE in order for PG to use the index. Otherwise, the index goes unused (or used improperly)? I am just trying to understand this for a database I have set up, where I set up a variety of indexes, assumming (wrongly?) that the indexes would be used once they were created. Andrew L. Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
